locked
Cumulative total in ssrs RRS feed

  • 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

    Thursday, April 26, 2012 7:32 AM

Answers

  • 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

    Thursday, April 26, 2012 10:51 PM

All replies

  • 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

    Thursday, April 26, 2012 9:23 AM
  • 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


    • Edited by sql.anandan Thursday, April 26, 2012 1:29 PM
    Thursday, April 26, 2012 1:28 PM
  • 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

    Thursday, April 26, 2012 10:51 PM