Custom Date Groups based on a specified Interval?
I am needing to create the option for a user to select a BeginDate and EndDate that will generate groups based on a specified Interval (in days). For example: BeginDate is a Tuesday, June 28th 2011. The EndDate is 3 months later. The Interval period is 7 days. I need the report to generate groups starting on Tuesday (the BeginDate) through the next Monday (7 day Interval). It will continue to group each set of 7 days and stop at the EndDate. The datasource is a Cube that contains a TimeDate diminsion. Thoughts anyone? Thank you!
June 27th, 2011 8:41pm

Hi HygginsSQL, We can achieve the requirement by the following way: 1.Create the main dataset with two parameters @BeginDate and @EndDate, which will retrieve data within @BeginDate and @EndDate. 2. Set the data type of the two parameters @BeginDate and @EndDate as Data/Time. 3. Right-click the date column in detail row to add a parent row group using the expression below. The expression will divide all data into groups with 7 days intervals. =Int(DateDiff("d",Parameters!BeginDate.Value,Fields! DateColumn.Value)/7) Now, after you choosed the BeginDate and EndDate, the report will display each group as expected. For more information about Group Expressions, please refer: http://msdn.microsoft.com/en-us/library/bb630426(v=SQL.100).aspx. Any questions, feel free to ask. Thanks, Lola Wang Please remember to mark the replies as answers if they help.
Free Windows Admin Tool Kit Click here and download it now
July 1st, 2011 1:55am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics