CODEWITHSUNDEEP
reporting-servicesssrs-2008
Menno
Menno

Reputation: 12651

Don't show row based on condition but sum values in parent group

Say I'm working with the following resultset:

+---------+-------------+------------+
|  Group  | Groupmember | Production |
+---------+-------------+------------+
| Group A | Member 1    |          5 |
| Group A | Member 2    |          3 |
| Group B | Member 3    |          2 |
| Group B | Member 4    |          5 |
| Group C | Member 5    |          1 |
+---------+-------------+------------+

Grouping on column Group will result in a report in the following syntax:

+---------+-------------+------------+
|  Group  | Groupmember | Production |
+---------+-------------+------------+
| Group A |             |          8 |
|         | Member 1    |          5 |
|         | Member 2    |          3 |
| Group B |             |          7 |
|         | Member 3    |          2 |
|         | Member 4    |          5 |
| Group C |             |          1 |
|         | Member 5    |          1 |
+---------+-------------+------------+

Now I'm trying to only show detailrows in case Production >= 5, though I want to count every record for the group. Expected:

+---------+-------------+------------+
|  Group  | Groupmember | Production |
+---------+-------------+------------+
| Group A |             |          8 |
|         | Member 1    |          5 |
| Group B |             |          7 |
|         | Member 4    |          5 |
| Group C |             |          1 |
+---------+-------------+------------+

Using a filter on the detailrow will however result in Production not being summed in the group.

How can I not show records based on a condition but sum them in the parentgroup?

Upvotes: 1

Views: 127

Answers (1)

Ian Preston
Ian Preston

Reputation: 39586

You're right that a filter will not work, but you can set the visibility of the detail rows to get your requirement.

I have created a simple table based on your data, which gives the full results as above:

enter image description here

enter image description here

enter image description here

Now, set the visibility of the detail row:

enter image description here

Right click, then set the Hidden expression to:

=IIf(Fields!Production.Value >= 5, False, True)

Now you get the required results:

enter image description here

Upvotes: 2

Related Questions