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
  • Learn & ask questions
Solved

Matrix report - Cumulative data in each Fiscal Year

Posted on 2014-12-19
2
127 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

856 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