CODEWITHSUNDEEP
reporting-services
Rob1n
Rob1n

Reputation: 235

Exclude hidden table row from total

I've created a table with a group filter so some values stay hidden. However, the row that shows the sum() of the above values, still counts the filtered values.

Usually I would apply the filter to the query, but I'm also using this same dataset in other tables on the same report so that's not an option.

Am I missing something here, or is this a flaw in MS Reporting Services?

Upvotes: 3

Views: 4827

Answers (3)

Roberto D
Roberto D

Reputation: 143

I had the same issue but I had too many column totals where to enter the Iif(). I resolved it by removing the filter from the group and putting the filter into the Tablix Properties dialog box (Select Tablix, Right-click the top left corner grey box and choose Tablixc Properties).

Upvotes: 0

Aedna
Aedna

Reputation: 789

It's more of a question than answer...

How to resolve this issue if you know whether the row is hidden or not ONLY at the Row Group level?

i.e. if you're filtering on the totals by this Row Group, i.e. you cannot have a field indicating if Row is excluded or not in a dataset.

ex: I have AR Aging report which has totals per months, patients. I need to filter out negative totals per patients (Patient row group), but I have column groups as well - per month, so my dataset cannot be per-patient granularity and therefore, the only place SSRS can decide whether to hide or show the row is on the Row Group level...

Upvotes: 1

Ed Harper
Ed Harper

Reputation: 21505

This appears to be a "feature" of SSRS, apparently because it calculates values in table headers and footers before rendering the detail section of a data table.

One way around this would be to add a derived Boolean column to your dataset (assuming your data source is SQL Server, using a CASE statement or similar) showing whether the row should be excluded from the table where the filter is required.

You can then change your table filter to check the indicator, and use a expression to carry out a conditional sum to aggregate the correct total. Something like

=SUM(Iif(Fields!ExcludeRow.Value = True,0,Fields!ValueToSum.Value))

Upvotes: 4

Related Questions