CODEWITHSUNDEEP
matrixreporting-servicessql-server-2008-r2
NonProgrammer
NonProgrammer

Reputation: 1387

Adding cumulative totals at row level SSRS or SQL Server 2008r2

I need to create a report in SSRS that would display data by YEAR. Because I do not know the fixed years, I am creating this report using matrix (pivot) so years are dynamically created as a column.

What I need the report to look like: I want the report to do a cumulative add per year at row level.

Assume:

  1. GAAP Revenue for 2017 for Lease Assumption Key '20300' = $100.
  2. GAAP Revenue for 2018 for Lease Assumption Key '20300' = $120.
  3. GAAP Revenue for 2019 for Lease Assumption Key '20300' = $150.

Based on Assumption:

  1. I want 2017 GAAP Revenue for Lease Assumption Key 20300 to display = $100.
  2. I want 2018 GAAP Revenue for Lease Assumption Key 20300 to display = $220.
  3. I want 2018 GAAP Revenue for Lease Assumption Key 20300 to display = $370.

Similar should happen to "Cash Revenue" rows.

Is this possible in SSRS? If not, how would I go about doing this in SQL Server 2008r2?

Here's what I've done so far in SSRS:

Matrix Design: enter image description here

Matrix Result: enter image description here

Upvotes: 0

Views: 220

Answers (1)

SS_DBA
SS_DBA

Reputation: 2423

You should be able to use the RunningValue function. Try this 

=RunningValue(Fields!GAAPRevenue.Value, SUM,"datasetname")

Upvotes: 1

Related Questions