SSRS Line Chart Dynamic Y Axis

Question

I have a line chart where the Y value is set to

=CountRows()

and I have a Category Group which groups on

=CDate(Fields!END_MONTH_NB.Value)

I am trying to set the interval of the Y axis dynamically such that fractions do not appear when the range is too small (see below).

I tried setting the interval expression to

=iif(CountRows() <= 5, 1, "Auto")

which works fine if the total number of rows is less than or equal to 5, but what I really need is the maximum that CountRows() will return in the context of my Category Group.

Any idea how to accomplish this?

Answer 1

Since you're using SSRS 2012, you can use the aggregate of an aggregate functionality to achieve this.

Say I have a chart like this, similar to yours:

With the appropriate data, it has the same issue as your screenshot, even with an expression in the Y-Axis interval:

i.e. here there are 8 rows, so the expression will be set to Auto, but because the groups have no more than three rows we get fractions in the Y axis.

We can get around this by finding the maximum of each of these group counts.

The Category Group will have a name:

Here I've called it MonthGroup. With this, we can change the Y Axis interval expression:

=IIf(Max(CountRows("MonthGroup")) <= 5, 1, Nothing)

i.e. if <=5, interval is 1, otherwise just pass a NULL value, i.e. let SSRS determine the interval.

So now we're checking the Max of the Category Group level CountRows; this is 3 in my example so now we're getting the required axis intervals:

Edit note:

Previously I had the Y Axis expression as:

=IIf(Max(CountRows("MonthGroup")) <= 5, 1, "Auto")

But after posting I noticed this was causing a warning in some circumstances; presumably because Auto is not a valid interval; it's just a placeholder used by SSRS. The updated expression:

=IIf(Max(CountRows("MonthGroup")) <= 5, 1, Nothing)

Works as expected without warnings.