CODEWITHSUNDEEP
reporting-services
JNM
JNM

Reputation: 1195

SSRS columns group ignored while calculating total

I have a matrix in ssrs like this: 

+--------------------+-------------------------+
|                    | Date                    |
+                    +----------+--------------+
|                    | Quantity | All Quantity |
+----------+---------+----------+--------------+
| Employee | Total   |          |              |
+          +---------+----------+--------------+
|          | Product |          |              |
+----------+---------+----------+--------------+

In this table Employee is a Row group and Product is a child row group. Date is a column group. In Quantity field I have numbers which are comming from my database. In the All Quantity column I need to have the total amount of Quantity in this Date group. But using my expression, it calculates All Quantity using data from all Date groups.

I mean that for date "2012-07-13" All Quantity should be 1000 and for date "2012-06-12" it should be 500. But instead of that, in both dates it shows 1500. How should I solve this?

My expression is here:

Sum(Fields!Quantity.Value, "Employee")

A dataset would look like this: 

Employee1   Product1    200 2012-01
Employee1   Product1    500 2012-02
Employee1   Product1    900 2012-03
Employee1   Product2    300 2012-01
Employee1   Product2    500 2012-02
Employee1   Product2    40  2012-03
Employee2   Product1    450 2012-01
Employee2   Product1    50  2012-02
Employee2   Product1    30  2012-03
Employee2   Product2    0   2012-01
Employee2   Product2    50  2012-02
Employee2   Product2    120 2012-03

This is an example of what i have, what i get and what i need.

//IF I USE Sum(Fields!Quantity.Value)
                    Date1                       Date2
                    Quantity    All Quantity    Quantity    All Quantity
Employee1   Total   590         -               190         -
                    100         100             50          50
                    200         100             50          50
                    150         150             40          40
                    50          50              30          30
                    90          50              20          20

//IF I USE Sum(Fields!Quantity.Value, "Employee")
                    Date1                       Date2
                    Quantity    All Quantity    Quantity    All Quantity
Employee1   Total   590         -               190         -
                    100         780             50          780
                    200         780             50          780
                    150         780             40          780
                    50          780             30          780
                    90          780             20          780

//I NEED TO GET
                    Date1                       Date2
                    Quantity    All Quantity    Quantity    All Quantity
Employee1   Total   590         -               190         -
                    100         590             50          190
                    200         590             50          190
                    150         590             40          190
                    50          590             30          190
                    90          590             20          190

Upvotes: 1

Views: 2322

Answers (1)

Davos
Davos

Reputation: 5415

If you can do things in SQL, it will always be faster than in the RDL, however, there's at least one way you can do it on the report. Using your second option above, Sum(Fields!Quantity.Value, "Employee"):

On the Cell I've indicated below*, give it a name. It will be textbox11 or something, call it EmployeeTotal. In the cell I've marked with the caret^, enter this expression

=ReportItems!EmployeeTotal.Value

You'll then get what you want (see the image attached). 1

                    Date1                       Date2
                    Quantity    All Quantity    Quantity    All Quantity
Employee1   Total   590*         -              190         -
                    100         590^            50          190
                    200         590^            50          190
                    150         590^            40          190
                    50          590^            30          190
                    90          590             20          190

Next time, it's easier for us to help you if you provide a dataset example that matches what you provided that you want to be displayed.

e.g. in the mockup I ran to test this, I used this query to create what I think is your real dataset:

![SELECT        'Employee1' AS Employee, 'Product1' AS Product, 100 AS Quantity, '2012-01' AS Date
UNION ALL
SELECT        'Employee1' AS Employee, 'Product2' AS Product, 200 AS Quantity, '2012-01' AS Date
UNION ALL
SELECT        'Employee1' AS Employee, 'Product3' AS Product, 150 AS Quantity, '2012-01' AS Date
UNION ALL
SELECT        'Employee1' AS Employee, 'Product4' AS Product, 50 AS Quantity, '2012-01' AS Date
UNION ALL
SELECT        'Employee1' AS Employee, 'Product5' AS Product, 90 AS Quantity, '2012-01' AS Date
UNION ALL
SELECT        'Employee1' AS Employee, 'Product1' AS Product, 50 AS Quantity, '2012-02' AS Date
UNION ALL
SELECT        'Employee1' AS Employee, 'Product2' AS Product, 50 AS Quantity, '2012-02' AS Date
UNION ALL
SELECT        'Employee1' AS Employee, 'Product3' AS Product, 40 AS Quantity, '2012-02' AS Date
UNION ALL
SELECT        'Employee1' AS Employee, 'Product4' AS Product, 30 AS Quantity, '2012-02' AS Date
UNION ALL
SELECT        'Employee1' AS Employee, 'Product5' AS Product, 20 AS Quantity, '2012-02' AS Date

Upvotes: 1

Related Questions