none
How to get Total of a column which is having an expression RunningValue() in SSRS

    Question

  • Hi,

    I have a report in SSRS 2008. It is having 4 columns (Purchase Order ID,Unit Price,Line Total,Running Value). This report is having group by Purchase Order ID. For Running Value Column I have written an expression IIf( RunningValue (Fields!PurchaseOrderID.Value, CountDistinct, Nothing) MOD 2, 1, 0) to get alternet value 1 and 0. Now I want sum for Running Value column. As we can't use nested aggregate function so I am not able to use expression Sum (IIf( RunningValue (Fields!PurchaseOrderID.Value, CountDistinct, Nothing) MOD 2, 1, 0)). Even I tried to give a name TXT_RUNNING_VALUE for text box which is having expression IIf( RunningValue (Fields!PurchaseOrderID.Value, CountDistinct, Nothing) MOD 2, 1, 0) and tried to use Sum(ReportItems!TXT_RUNNING_VALUE.value). It was throwing error that aggregate cant be use for Report Items.

     

    Below is the Report Data:

    Purchase Order ID        Unit Price         Line Total         Running Value

    1                                   50.26                 201.04               0

    3                                   16.086               8847.3               1

    4                                   57.0255             171.0765            0

    5                                   37.086                20397.3            1

    6                                   26.5965              14628.075         0

    7                                   106.701              58685.55          1

    8                                   231.126              693.378            0

    9                                   231.3885            694.1655          1

    10                                 115.689              1796.0355        0

    -------------------------------------------------------------------------------------------

    Sum                                                                              4 

    Please tell me how can I get sum of Running Value column?


    Thanks Shiven:)

    Friday, May 20, 2011 6:31 AM

Answers

  • Hi All,

    I resolved above issue by writing Custom Code.

    Here is the code:

    Public Dim TotalCount As Integer = 0
    Public Function CalculateSum(RunningValue as Integer) 
     TotalCount=TotalCount+RunningValue
     Return (TotalCount)
    End Function

    Add this code to Report->Report Properties->Code

    and add a group Total to group Purchase Order ID  and then add a Column (Sum) next right to running Value column and Write expression =Code.CalculateSum(ReportItems!Running_value.Value) in detailed row under column sum and Write expression =Code.TotalCount in Group Total Row, below Column (Sum) and will get expected output:

    Purchase Order ID        Unit Price         Line Total         Running Value  Sum

    1                                   50.26                 201.04               0                        0

    3                                   16.086               8847.3               1                        1

    4                                   57.0255             171.0765            0                        0

    5                                   37.086                20397.3            1                        1

    6                                   26.5965              14628.075         0                        0

    7                                   106.701              58685.55          1                        1

    8                                   231.126              693.378            0                        0

    9                                   231.3885            694.1655          1                        1

    10                                 115.689              1796.0355        0                        0

    -----------------------------------------------------------------------------------------------------------------

    Sum                                                                              4                        4

    Note: If you don't want to show new added column Sum to your report, just hide it. Please don't use Column Visibility to hide that column. Hide each text box by using Visibility property in Text Box Properties. If you delete Sum Column, Not getting sum 4, It is displaying 0. So You need to add one extra column and use =Code.CalculateSum(ReportItems!Running_value.Value) in detailed row under that added column and =Code.TotalCount in Group Total Row, below added Column and hide that column if not needed.

    

    Note: It displays Sum for each page. It will not display total sum for all pages at end of report. This is disadvatage. For getting total sum at the end of report refer this lin: http://www.ssrstips.com/carried-forward-page-totals

    

     

     


    Thanks Shiven:)
    • Marked as answer by S Kumar Dubey Monday, May 23, 2011 11:13 AM
    Monday, May 23, 2011 11:13 AM

All replies

  • Hi,

    I have a report in SSRS 2008. It is having 4 columns (Purchase Order ID,Unit Price,Line Total,Running Value). This report is having group by Purchase Order ID. For Running Value Column I have written an expression IIf( RunningValue (Fields!PurchaseOrderID.Value, CountDistinct, Nothing) MOD 2, 1, 0) to get alternet value 1 and 0. Now I want sum for Running Value column. As we can't use nested aggregate function so I am not able to use expression Sum (IIf( RunningValue (Fields!PurchaseOrderID.Value, CountDistinct, Nothing) MOD 2, 1, 0)). Even I tried to give a name TXT_RUNNING_VALUE for text box which is having expression IIf( RunningValue (Fields!PurchaseOrderID.Value, CountDistinct, Nothing) MOD 2, 1, 0) and tried to use Sum(ReportItems!TXT_RUNNING_VALUE.value). It was throwing error that aggregate cant be use for Report Items.

     

    Below is the Report Data:

    Purchase Order ID        Unit Price         Line Total         Running Value

    1                                   50.26                 201.04               0

    3                                   16.086               8847.3               1

    4                                   57.0255             171.0765            0

    5                                   37.086                20397.3            1

    6                                   26.5965              14628.075         0

    7                                   106.701              58685.55          1

    8                                   231.126              693.378            0

    9                                   231.3885            694.1655          1

    10                                 115.689              1796.0355        0

    -------------------------------------------------------------------------------------------

    Sum                                                                              4 

    Please tell me how can I get sum of Running Value column?


    Thanks Shiven:)


    Placing  sum expression in page footer will be an option for you ?

    Enable page footer type in this expression placing a text box there : Sum(ReportItems!TXT_RUNNING_VALUE.value).

    Thanks


    Rajkumar Yelugu
    Friday, May 20, 2011 7:07 AM
  • Hi Rajkumar Yelugu ,

    Thanks for your reply but I want sun in report body not in Report Footer. Please any other work around?


    Thanks Shiven:)
    Friday, May 20, 2011 7:13 AM
  • Hi,

    Any one can tell me the custome code to Get the Sum of Column Running Value. Please help me out. I have no idea of Custome code so pls provide complete code for below table

    Below is the Report Data:

    Purchase Order ID        Unit Price         Line Total         Running Value

    1                                   50.26                 201.04               0

    3                                   16.086               8847.3               1

    4                                   57.0255             171.0765            0

    5                                   37.086                20397.3            1

    6                                   26.5965              14628.075         0

    7                                   106.701              58685.55          1

    8                                   231.126              693.378            0

    9                                   231.3885            694.1655          1

    10                                 115.689              1796.0355        0

    -------------------------------------------------------------------------------------------

    Sum                                                                              4 


    Thanks Shiven:)
    Friday, May 20, 2011 12:35 PM
  • Hi,

    Any one can tell me the custome code to Get the Sum of Column Running Value. Please help me out. I have no idea of Custome code so pls provide complete code for below table

    Below is the Report Data:

    Purchase Order ID        Unit Price         Line Total         Running Value

    1                                   50.26                 201.04               0

    3                                   16.086               8847.3               1

    4                                   57.0255             171.0765            0

    5                                   37.086                20397.3            1

    6                                   26.5965              14628.075         0

    7                                   106.701              58685.55          1

    8                                   231.126              693.378            0

    9                                   231.3885            694.1655          1

    10                                 115.689              1796.0355        0

    -------------------------------------------------------------------------------------------

    Sum                                                                              4 


    Thanks Shiven:)


    Custom Code variables Might Reset as you navigate to pages :

    1) You can change the logic of  running value expression there to sql and display sum directly in Productid group footer .

    2) Place sum expression in Report Footer .

    Thanks .


    Rajkumar Yelugu
    Friday, May 20, 2011 1:05 PM
  • Hi Rajkumar Yelugu,

    Custom Code variables Might Reset as you navigate to pages :

    1) You can change the logic of  running value expression there to sql and display sum directly in Productid group footer .

    2) Place sum expression in Report Footer .

    I cant Change logic. My query is very complecated and report is having many complex Expressions and calculations. So please provide me Custom Code for calculating Sum of column Running Value. Is It possible to use Report Items in Custom code? I have no Idea about custom code, please guide me for the same.


    Thanks Shiven:)
    Monday, May 23, 2011 8:48 AM
  • Hi All,

    I resolved above issue by writing Custom Code.

    Here is the code:

    Public Dim TotalCount As Integer = 0
    Public Function CalculateSum(RunningValue as Integer) 
     TotalCount=TotalCount+RunningValue
     Return (TotalCount)
    End Function

    Add this code to Report->Report Properties->Code

    and add a group Total to group Purchase Order ID  and then add a Column (Sum) next right to running Value column and Write expression =Code.CalculateSum(ReportItems!Running_value.Value) in detailed row under column sum and Write expression =Code.TotalCount in Group Total Row, below Column (Sum) and will get expected output:

    Purchase Order ID        Unit Price         Line Total         Running Value  Sum

    1                                   50.26                 201.04               0                        0

    3                                   16.086               8847.3               1                        1

    4                                   57.0255             171.0765            0                        0

    5                                   37.086                20397.3            1                        1

    6                                   26.5965              14628.075         0                        0

    7                                   106.701              58685.55          1                        1

    8                                   231.126              693.378            0                        0

    9                                   231.3885            694.1655          1                        1

    10                                 115.689              1796.0355        0                        0

    -----------------------------------------------------------------------------------------------------------------

    Sum                                                                              4                        4

    Note: If you don't want to show new added column Sum to your report, just hide it. Please don't use Column Visibility to hide that column. Hide each text box by using Visibility property in Text Box Properties. If you delete Sum Column, Not getting sum 4, It is displaying 0. So You need to add one extra column and use =Code.CalculateSum(ReportItems!Running_value.Value) in detailed row under that added column and =Code.TotalCount in Group Total Row, below added Column and hide that column if not needed.

    

    Note: It displays Sum for each page. It will not display total sum for all pages at end of report. This is disadvatage. For getting total sum at the end of report refer this lin: http://www.ssrstips.com/carried-forward-page-totals

    

     

     


    Thanks Shiven:)
    • Marked as answer by S Kumar Dubey Monday, May 23, 2011 11:13 AM
    Monday, May 23, 2011 11:13 AM