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

Question

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 [email protected](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.

Answer 1

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.

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.