Solved

Matrix report - Cumulative data in each Fiscal Year

Posted on 2014-12-19
134 Views
I have an SSRS report that currently shows transaction data that occurs each fiscal year.  Here is my dataset:

``````Select ClaimantID,
'6/30/'+Substring(dbo.GetF<wbr ></wbr>iscalYear(<wbr ></wbr>r.Processe<wbr ></wbr>dDate),6,4<wbr ></wbr>) as ProcessedFY,
SUM(r.ReserveAmount) as TotalIncurred
FROM dbo.Reserve r ( NOLOCK )
LEFT JOIN dbo.ReserveType rt ON r.ReserveTypeID = rt.ReserveTypeID

WHERE r.ProcessedDate BETWEEN @FYStart AND @FYEnd

GROUP BY ClaimantID, '6/30/'+Substring(dbo.GetF<wbr ></wbr>iscalYear(<wbr ></wbr>r.Processe<wbr ></wbr>dDate),6,4<wbr ></wbr>)
HAVING SUM(r.ReserveAmount) >0.00
``````

I need to change the report or query (not sure which) to total each FY cumulative.
So If I run the data from FY 2000 to FY2012 then FY will have the data from 2000 and FY 2001 will be a total of 2000 and 2001.

I would REALLY appreciate the experts help on this as I am stuck.

Scott
0
Question by:Scott Williams
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

Author Comment

ID: 40510007
I found the RunningValue formula.  But it seems I can't replace my value in the matrix table with this.  Anybody know how to use this =RunningValue(Fields!TotalIncurred.Value,Sum,"Incurred") in a matrix?
0

LVL 24

Accepted Solution

Phillip Burton earned 500 total points
ID: 40512820
Try using

``````Null
``````

or

``````Nothing
``````

"Incurred"
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month5 days, 13 hours left to enroll