none
SSRS ReportItems! total RRS feed

  • Question

  • Hi,

    I have a report design as per the below:

    For the 'TOTAL' column, I am using the below expression:

    =ReportItems!value2.Value * ReportItems!value3.Value

    How can I achieve the Grand Total?

    Please assist

    Friday, June 23, 2017 10:33 AM

Answers

  • Hi Zimiso,

    In your scenario, you seems to use matrix to design the report, right? If I have something misunderstood, please correct me.

    Below is my design, I assume that your dataset might like below

    If your dataset structure is not similar to mine, please inform me your dataset structure with screenshots.

    Then I add a matrix in report, add[name] in Row, add [cost] in Data, [type1] in Column, and right click [type1] to choose Add Group->adjacent group to add [type2] and [quantity]

    Then add custom code below in Report Properties->code

    Public Shared Value as Integer=0
      Public Shared Function GetValue(Item as Integer) as Integer
         value= value + Item
         return Item
      End Function
      Public Shared Function GetTotal()
         return value
      End Function


    Right click last column to choose Insert column->outside column, and right click [name] to add total, then use expressions below in corresponding cells

    =Code.GetValue(ReportItems!cost.Value* ReportItems!quantity.Value)
    =Code.GetTotal()
    

    You will get the result below

    If you have any question, please feel free to ask.

    Best regards,

    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, June 27, 2017 7:09 AM
    Moderator

All replies

  • Hi Zimiso,

    Can you use below expression for Grand Totals?

    =Sum(Fields!COST.Value*Fields!QUANTITY.Value)

    Please see below screenshot for your reference.


    • Proposed as answer by DeviantLogic Saturday, June 24, 2017 6:48 AM
    Saturday, June 24, 2017 3:19 AM
  • In addition to Nr5952's solution, you can calculate the total column in the SQL query, then just do the same sum function you're using for the cost and quantity columns.

    Dataset query used for testing:

    DECLARE @tmp_test TABLE
    (
        [ItemName] VARCHAR(30)
       ,[Cost] MONEY
       ,[Quantity] INT
    );
    
    INSERT INTO @tmp_test
    VALUES
     ('6009545400126', 126.00, 1)
    ,('6009545401079', 15.50, 10)
    ,('6009545401178', 307.00, 2)
    ,('6009545403011', 49.00, 1)
    ,('6009704138808', 15.50, 6)
    ,('6009704138822', 61.00, 3);
    
    SELECT [ItemName]
          ,[Cost]
          ,[Quantity]
          ,[Cost] * [Quantity] AS [Total]
    FROM @tmp_test;

    Report design view:

    Report output:


    Saturday, June 24, 2017 6:48 AM
  • Hi,

    My tablix design, is per the below:

    The row groupiing is on item_name. total expression is =ReportItems!value2.Value * ReportItems!value3.Value

    Regards

    Saturday, June 24, 2017 11:17 AM
  • Hi Zimiso,

    In your scenario, you seems to use matrix to design the report, right? If I have something misunderstood, please correct me.

    Below is my design, I assume that your dataset might like below

    If your dataset structure is not similar to mine, please inform me your dataset structure with screenshots.

    Then I add a matrix in report, add[name] in Row, add [cost] in Data, [type1] in Column, and right click [type1] to choose Add Group->adjacent group to add [type2] and [quantity]

    Then add custom code below in Report Properties->code

    Public Shared Value as Integer=0
      Public Shared Function GetValue(Item as Integer) as Integer
         value= value + Item
         return Item
      End Function
      Public Shared Function GetTotal()
         return value
      End Function


    Right click last column to choose Insert column->outside column, and right click [name] to add total, then use expressions below in corresponding cells

    =Code.GetValue(ReportItems!cost.Value* ReportItems!quantity.Value)
    =Code.GetTotal()
    

    You will get the result below

    If you have any question, please feel free to ask.

    Best regards,

    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, June 27, 2017 7:09 AM
    Moderator
  • Hi Zoe,

    This works perfectly for me!!

    Many thanks

    Regards

    Wednesday, June 28, 2017 3:53 AM