Cumulative total in ssrs

Question

hi

i create a report in SSRS..in that report i m using 2 columns one is during this quarter another one is cumulative total..now i want to create promt to fetch during this quarter and cumulative total for all

Ex:

if i pass 4 th quarter of 2011

my during quarter data want to fetch only 4th -2011

and cumulative data want to fetch 2011 -1,2,3,4th quarters

how to do that in ssrs and also how to write query to fetch cumulative total in ssrs

Answer 1

HI there

I think what you could do you can have two dataset one for your quarter results and one for your total year cumulative data and use the lookup function to get the year cumulative data for the year  you have selected for your quarter query

You can find lot of help for lookup on the google your expression might look like

=lookup(Parameters!year.Value, Fields!year.Value, Fields!totalsum.Value, "Dataset2")

Where Parameters!year.Value is your parameter for year selection

, Fields!year.Value is your dataset2 field where you bring the year and totalsum is your total value for the year and offcourse "Dataset2" is the dataset  in which you bring the full year cumulative data

I hope this will resolve your problem.

If you have any question please ask.

Many thanks

Syed Qazafi

Answer 2

ya i write expression like this

in dataset2 for cumulative:

=(RunningValue (Fields!VALUE.Value,sum,nothing))

its working good and also here just i pass year as parameter

and in my 1st dataset i passed year and quarter as promt

so in that table exp i write expression like this:

=Lookup(Fields!YEAR_NUMBER.Value,Fields!YEAR_NUMBER.Value,  Fields!VALUE.Value , "DataSet2")

this expression is working but that data s wrong

how to solve that

waiting for reply

Answer 3

Hi There

Please do something like this I hope this will fix your problem

I hope this will help

Many thanks

Syed Qazafi Anjum

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

--DATASET2
SELECT
	 RE1.[Year],RE1.[Quarter], RE1.[TotalValue], 
	RunningTotal =
			(SELECT
			   SUM(RE2.[TotalValue]) 
			 from
			   ReportExample RE2 
			 where RE2.[Year] = RE1.[Year]
			and RE2.[Quarter] <= RE1.[Quarter]
			)
from [ReportExample] RE1
where
RE1.[Year]=@Year
ORDER BY RE1.[Quarter] 
--------------------------------------------
-- dataset1-------------
SELECT [Year]
      ,[Quarter]
      ,[TotalValue]
  
  FROM [dbo].[ReportExample]
where
[Year]=@Year
and Quarter=@Quarter
--------------------------------------
----expression-----------------
=Lookup(Fields!Quarter.Value,Fields!Quarter.Value,Fields!RunningTotal.Value,"DataSet2")

Please also look on this secreenshot