Search code examples
datereporting-servicesparameterssubreport

Filter SSRS subreport for 12 months from first day of month to last day of month

I have a SSRS report that has typical Start Date / End Date parameters that will filter out Sales Order information based on the OrderDate field. With this report, I have a subreport. This subrebort is basically nothing more than an aggregation of the same info, by month. This aggregate information should be from the first day of the month, one year prior, through the last day of the End Date's parameters month. This does not take the main subreports Start Date parameter into consideration at all.

This is where it gets a little tricky. For example, lets say I made the start date / end date parameters on my main report:

 10/15/2016 - 11/15/2016

(just remember, for this purpose, the start date is irrelevant)

I would want the subreport to show the sales totals, per month, for the ENTIRE month of December 2015 through the ENTIRE month of November 2016, even though my end date was 11/15/2016.

If I were to put in those same dates for my parameters in the report right now, for the aggregation of December 2015, I would only get sales from the 15th through the 31st. Currently I have my subreport to filter on the OrderDate by:

 Fields!OrderDate.Value>= DateAdd(DateInterval.Month, -12,Parameters!EndDate.Value)

I know that this filter is not currently set up to have an end date for the parameter, just a greater than argument, which is wrong since I want the 12 month history to stop at the last day of the month for the month of my End Date parameter, but I don't know how to make that happen either.

I hope I have explained this well. Any help on this would be greatly appreciated.

Solution

  • You can set a filter to get only dates between a specific range in your subreport dataset, tablix and some visualizations:

    In DataSet properties or Tablix Properties in the Filter tab use these settings:

    enter image description here

    In the Value textboxes you should use the expressions to calculate your date range.

    StartDate

    =DateSerial(Parameters!EndDate.Value.Year-1,Parameters!EndDate.Value.Month,1).AddMonths(1)

    EndDate

    =DateSerial(
 Parameters!EndDate.Value.Year,Parameters!EndDate.Value.Month,1).AddMonths(1).AddDays(-1)

    It should filter your data from 12/01/2015 to 11/30/2016 if your EndDate parameter is set to 11/15/2016

    Let me know if this helps.