Dynamic Grouping based on date field SSRS

Question

In my SSRS report which has a dataset with date field as below

The dataset returns the following type of data (just an example)

Voucher Amount  Date
R3221    € 3,223.00     1-Dec-17
R3222    € 123.00   28-Nov-17
R3223    € 1,233.00     19-Oct-17
R3224    € 442.00   27-Sep-17
R3225    € 123.00   17-Nov-17
R3226    € 423.00   29-Oct-17
R3227    € 1,234.00     8-Oct-17

What I would like to know is how to show this data grouped by Voucher and Due Date

User should be able to select the Start Date and the period type (Day, Week, month,) and the interval between the two columns (e.g 3 , 10 or 30 or any other number)

so the user should be able to select the period type, e.g if he select Day and interval as 3 then the report should show

**voucher       start date      <Dynamic grouping columns based on the selection criteria>**

    R3221
    R3222
    R3223
    R3224
    R3225
    R3226
    R3227

Any kind of hint will be much appreciated!

You need create a list of dates. stackoverflow.com/questions/1378593/… and then pivot — Juan Carlos Oropeza
This would be very easy if you returned the bucket each voucher falls in within your custom range. That is, if you can modify the reporting stored procedure and send in the start date and offsets then you could return the calculated offset with your data and simply group, column wise, on that field. This would be the easiest way. — Ross Bush

derloopkat derloopkat · Accepted Answer · 2017-12-01T16:22:31

This is only part of the solution. First generate date periods you want in the report, then do a LEFT JOIN against your table, filter and group.

declare @dateFrom date
declare @periodInDays int
declare @periods int --how many periods of 10 days
set @periodInDays = 10; 
set @periods = 15; --how many periods of 10 days
set @dateFrom = getdate();

with [dates] as (
    select @dateFrom as date --start
    union all
    select dateadd(day, @periodInDays, [date]) as date
    from [dates]
    where [date] < DATEADD(day, @periodInDays * @periods, @dateFrom) --end
)
select [date]
from [dates]
option (maxrecursion 0)

In this example returns 16 dates (1 per row), from start date (2017-12-01), every 10 days

2017-12-01, 2017-12-11, 2017-12-21, 2017-12-31, 2018-01-10, 2018-01-20 2018-01-30, 2018-02-09, 2018-02-19, 2018-03-01, 2018-03-11, 2018-03-21 2018-03-31, 2018-04-10, 2018-04-20, 2018-04-30

To filter by period get start date from the above date table, so the end date for that period can be calculated as DATEADD(day, @periodInDays, [date]) and we don't need to look into next row at this point.

Just let me know if someone find more straight forward solution.

Dynamic Grouping based on date field SSRS

1 Answers