CODEWITHSUNDEEP
sql-serverreporting-servicesexpression
bltSandwich21
bltSandwich21

Reputation: 432

SSRS - stop RunningValue when encountering null

I have a table in SSRS that graphs cumulative amount spent for fiscal years 2019 and 2020. Fiscal year starts in Oct and ends in Sept.

Current Graph

Values: =RunningValue(Fields!total_cost.Value, Sum, "fiscal_year")

Category Groups: month

Series Groups: fiscal_year

The table has rows for all months (Oct-Sept) in FY19 but only some months (Oct-Feb) in FY20. However, when I try to plot both FY19 and FY20 in the same graph, because rest of FY20 data is incomplete, the RunningValue maintains the same value after February.

What I'm looking for is a way to stop RunningValue once it encounters a null value (in March) so that the resulting graph looks more like this:

Expected Graph

Thank you!

Upvotes: 0

Views: 301

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

This is completely untested but you could try something like this

=IIF(
    IsNothing(Fields!total_cost.Value), 
    Nothing, 
    RunningValue(Fields!total_cost.Value, Sum, "fiscal_year")
    )

This assumes NULL values rather than zeros, if you have zeros in the 'empty' datapoints then adjust accordingly.

Upvotes: 1

Related Questions