none
Calculated Date Range Field

    Question

  • Hello All,

    I have what is probably a fairly easy question but being very new to this reporting I am just not sure how to do this.

    I am working with a created date field and looking at the prior four weeks.

    I would like to break out with each so that I can make that my columns in my table.

    For example, my criteria is 2.16.2013 to 3.15.2013 and I would like to make these ranges the columns

    2.16.2013 to 2.22.2013

    2.23.2013 to 3.1.2013

    3.2.2013 to 3.8.2013

    3.9.2013 to 3.15.2013

    Thank you

    Friday, March 15, 2013 3:45 PM

Answers

  • Assuming you meant that you cannot create or edit any datasets for the report...

    So the issue is that you are getting 4 weeks of data and want to group by week. There are several ways of accomplishing this in the report without modifying any data. It depends on the criteria. Since you stated that your report data returns for the previous 4 weeks, I am assuming that it is getting the past 28 days of data. So, if I run the report on a Wednesday, the first date of data chronologically should start with the Thursday 4 weeks ago. Since your example shows only 4 rows, I am assuming that each row in the report table will be for a 7 day period ending on the same day of the week that the report is run on. If the report is run on Tuesday, each of the 4 rows will be from Wednesday to Tuesday.

    To accomplish this, you need to group on an expression rather than a field. Assuming you are using a Table and will have a single row group (for your 7 day period) and that you are starting with a new table:

    1. Add the Table to the design surface of the report.
    2. Set the Dataset property to the appropriate dataset.
    3. Select the Table in the design surface.
    4. Click the dropdown next to the Details group listed in the Row Groups.
    5. Choose Add Group > Parent Group.
    6. In the Tablix group dialog, click the fx (Expression Builder) button next to the Group By dropdown.
    7. In the expression builder, enter the following formula (Change field names etc. to match your dataset)
      =IIf(Fields!Date.Value>DateAdd("d",-7,Parameters!EndDate.Value), "Week 4",IIf(Fields!Date.Value>DateAdd("d",-14,Parameters!EndDate.Value), "Week 3",IIf(Fields!Date.Value>DateAdd("d",-21,Parameters!EndDate.Value), "Week 2","Week 1")))
    8. Click OK to close Expression Builder.
    9. Click OK to close Tablix group dialog.

    You may wish different labels for your "weeks". Just change "Week 1", etc. to whatever you want displayed. This will group on a 7 day period ending on the same day of the week as the selected EndDate parameter value. If you do not have an EndDate parameter but instead always end your time period on the date the report runs, substitute Today() in place of Parameters!EndDate.Value.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Friday, March 15, 2013 5:15 PM
  • So my original expression is close except I sequenced the weeks in ascending order and you want descending order. Also, your example would use a Matrix instead of a Table. Your matrix will have a row group on Branch and a Column group as I described for the Row Group above:

    =IIf(Fields!Date.Value>DateAdd("d",-7,Parameters!EndDate.Value), "Week One",IIf(Fields!Date.Value>DateAdd("d",-14,Parameters!EndDate.Value), "Week Two",IIf(Fields!Date.Value>DateAdd("d",-21,Parameters!EndDate.Value), "Week Three","Week Four")))

    You will also want 2 detail cells per column group.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Friday, March 15, 2013 9:12 PM

All replies

  • It is best that you pass this parameters to a custom function or a stored procedure to get the as Dataset.

    declare @Date varchar(4),
        @firstday varchar(20),
        @lastday varchar(20),
        @i int
    declare @t table([date] datetime,[weeknum] int)
    set @Date='2013'
    select @firstday=@Date+'-02-16',@lastday='2013-03-15'
    set @i=0
    while @i<=DATEDIFF(DAY,@firstday,@lastday)
    begin
     insert into @t select dateadd(day,@i,@firstday),DATEPART(WK,dateadd(day,@i,@firstday))
     set @i=@i+1
    end
    select weeknum,MIN([date]) startdate,MAX([date]) enddate from @t
    group by weeknum


    Please Mark posts as answers or helpful so that others can more easily find the answers they seek.


    • Edited by Sachi_SG Friday, March 15, 2013 4:03 PM added code
    Friday, March 15, 2013 3:54 PM
  • thank you but the system that i am working with does not alow for me to edit or change the actual dataset query.
    Friday, March 15, 2013 4:07 PM
  • You dont need to modify the exisiting dataset.

    Create 2nd dataset with ParamStartDate and ParamEndDate which will call a Stored procedure.

    Whenever parameters are set you need to invoke this SP to load the dropdown list with date range.

    Please go through this article for more detailed steps on how to pass parameter to get the dropdown data dynamically.

    http://www.mssqltips.com/sqlservertip/1904/optimize-report-parameter-dropdowns-in-sql-server-reporting-services/

    There is still more ways to do this as Expression are very much powerful.

    But since you are new it is good for you to use that above example.


    Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

    Friday, March 15, 2013 4:20 PM
  • Assuming you meant that you cannot create or edit any datasets for the report...

    So the issue is that you are getting 4 weeks of data and want to group by week. There are several ways of accomplishing this in the report without modifying any data. It depends on the criteria. Since you stated that your report data returns for the previous 4 weeks, I am assuming that it is getting the past 28 days of data. So, if I run the report on a Wednesday, the first date of data chronologically should start with the Thursday 4 weeks ago. Since your example shows only 4 rows, I am assuming that each row in the report table will be for a 7 day period ending on the same day of the week that the report is run on. If the report is run on Tuesday, each of the 4 rows will be from Wednesday to Tuesday.

    To accomplish this, you need to group on an expression rather than a field. Assuming you are using a Table and will have a single row group (for your 7 day period) and that you are starting with a new table:

    1. Add the Table to the design surface of the report.
    2. Set the Dataset property to the appropriate dataset.
    3. Select the Table in the design surface.
    4. Click the dropdown next to the Details group listed in the Row Groups.
    5. Choose Add Group > Parent Group.
    6. In the Tablix group dialog, click the fx (Expression Builder) button next to the Group By dropdown.
    7. In the expression builder, enter the following formula (Change field names etc. to match your dataset)
      =IIf(Fields!Date.Value>DateAdd("d",-7,Parameters!EndDate.Value), "Week 4",IIf(Fields!Date.Value>DateAdd("d",-14,Parameters!EndDate.Value), "Week 3",IIf(Fields!Date.Value>DateAdd("d",-21,Parameters!EndDate.Value), "Week 2","Week 1")))
    8. Click OK to close Expression Builder.
    9. Click OK to close Tablix group dialog.

    You may wish different labels for your "weeks". Just change "Week 1", etc. to whatever you want displayed. This will group on a 7 day period ending on the same day of the week as the selected EndDate parameter value. If you do not have an EndDate parameter but instead always end your time period on the date the report runs, substitute Today() in place of Parameters!EndDate.Value.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Friday, March 15, 2013 5:15 PM
  • Okay I get that, but what about putting in a range for a specific time frame that is already defined?
    Friday, March 15, 2013 6:24 PM
  • If you mean changing Week 1 to the actual date range:

    In place of     Use                                                                                                                           
    "Week 4"       CStr(DateAdd("d",-6,Parameters!EndDate.Value))+"-"+CStr(Parameters!EndDate.Value)
    "Week 3"       CStr(DateAdd("d",-13,Parameters!EndDate.Value))+"-"+CStr(DateAdd("d",-7,Parameters!EndDate.Value))
    "Week 2"       CStr(DateAdd("d",-20,Parameters!EndDate.Value))+"-"+CStr(DateAdd("d",-14,Parameters!EndDate.Value))
    "Week 1"       CStr(DateAdd("d",-27,Parameters!EndDate.Value))+"-"+CStr(DateAdd("d",-21,Parameters!EndDate.Value))

    You may wish to use Format to control the date format as well.

    If you mean that the timeframes are defined in a SQL table somewhere or something similar I would need to the details.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.


    Friday, March 15, 2013 7:24 PM
  • Okay not exactly, I am going to try to get it for you because sometimes I have a real hard time trying to write what my head thinks. 

    the time frames that I am doing are 2.16.2013 to 3.15.2013.  the first week I would like all the data from 3.09.2013 to 3.15.2013 to be grouped together as Week One the next one I would like all the data from 3.2.2013 to 3.8.2013 to be grouped together are Week Two and the next one I would like all the data from 2.23.2013 to 3.1.2013 to be grouped together and then the last week to be all the data from 2.16.2013 to 2.22.2013 to be grouped together.  These are going to be the groupings for my columns so my table will look something like this

                                          Week One                             Week Two                     Week Three                   Week Four

                                       #                     $                    #          $                            #       $                         #            $

    Branch One              54              123,225                25          55,000                  10         100,000            1       1,000 

    Branch Two                23              350,000                12        12,000                  2           50,000              0         0,00

    Branch Three             1                 10,000                  52             450,000           3          25,000                2      10,000

    Total

    I hope that i did a better job trying to explain

    I REALLY appreciate all the help

    Friday, March 15, 2013 8:24 PM
  • So my original expression is close except I sequenced the weeks in ascending order and you want descending order. Also, your example would use a Matrix instead of a Table. Your matrix will have a row group on Branch and a Column group as I described for the Row Group above:

    =IIf(Fields!Date.Value>DateAdd("d",-7,Parameters!EndDate.Value), "Week One",IIf(Fields!Date.Value>DateAdd("d",-14,Parameters!EndDate.Value), "Week Two",IIf(Fields!Date.Value>DateAdd("d",-21,Parameters!EndDate.Value), "Week Three","Week Four")))

    You will also want 2 detail cells per column group.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Friday, March 15, 2013 9:12 PM