Solved

Matrix report - Cumulative data in each Fiscal Year

Posted on 2014-12-19
2
126 Views
Last Modified: 2014-12-25
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

Open in new window


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.  

Thank you in advance!!
Scott
0
Comment
Question by:Scott Williams
2 Comments
 

Author Comment

by:Scott Williams
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

by:
Phillip Burton earned 500 total points
ID: 40512820
Try using

Null 

Open in new window


or

Nothing 

Open in new window


instead of

"Incurred"
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question