Search code examples
reporting-servicesssrs-2008ssrs-2008-r2

SSRS Next 6 Months based on Year and Month Field Chart Filter

I have number of charts that I am creating using a data set. I have one chart which is only supposed to be subset of the data. Essentially It should show data for the next 6 months based on the current month. If i ran it today, it should show the entire month of August 2016 - Jan 2017.

Within the data-set I am returning Year and Month. However; How do I create a filter function? I have tried Fields!Month.Value >= Month(Today()), but it misses Jan 2017. I have also tried Fields!Year.Value >= Year(Today()) AND Fields@Month.Value(Today()). What am I missing? Would I be adding a filter to the chart properties, or the Category groups (Year and Month)?

Help would be immensely appreciated.

Solution

  • Create a filter in your chart by right click on it / chart properties and select the Filters tab. Add a new filter with the following settings.

    enter image description here

    In Expression use:

    =CINT(Fields!Year.Value & RIGHT("00" & Fields!Month.Value,2))

    If your Month field is an integer from 1 to 12 representing month number and it doesn't include leading zero. Ohterwise if your month field include leading zero use:

    =CINT(Cstr(Fields!Year.Value) & Cstr(Fields!Month.Value))

    Select Integer and Between for Operator.

    In the first Value use:

    =CINT(CSTR(Today.Year) & RIGHT("00" & CStr(Today.Month), 2))

    In the second Value use:

    =CINT(CSTR(Today.AddMonths(5).Year) & RIGHT("00" & CStr(Today.AddMonths(5).Month), 2))

    It should filter the rows to use in your chart to dates between 201608 and 201701 if you run the report in this month.

    Let me know if it helps.