SSRS Tablix Totals

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

Answer 1

Hi Zimiso,

Can you use below expression for Grand Totals.

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

<g class="gr_ gr_48 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Punctuation multiReplace" data-gr-id="48" id="48"></g>Please see below screenshot for your reference.

Answer 2

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:

Answer 3

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 [e-mail].

Answer 4

Hi Zimiso,

May I know how is your issue going currently? If your issue is solved please mark the appropriate answer as Mark as answer.  This will help other members to find a solution if they face the same issue. If you still have any questions, please feel free to ask.

Thanks for your understanding and support.
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 [e-mail].

Answer 5

Using the function, it worked perfectly!!!

Much thank you

Regards