question

Thameeraviraj avatar image
Thameeraviraj asked

Exclude hidden row values from total SSRS

I have a column called "Total" in SQL reporting servece report. When generating report I'm hiding some rows accoring to some formula, But calculating the total that hidden rows values also calculating. Can I know how to exclude hidden row value from total value?
ssrsssrs-2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thameeraviraj avatar image
Thameeraviraj answered
Not the parenthesis. I changed the formula like this. =SUM(IIF(((Parameters!ExcludeZeroPaid.Value) = True AND (Fields!PaidAmt.Value = 0)),Nothing,Fields!ToPay.Value)) Now this is working properly.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered
Here are some ideas: Could you use the same conditions in the expression for the total so it's the total of the rows that are hidden? Could you apply the conditions in the query so those rows are not returned, which would make the whole report part easy?
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thameeraviraj avatar image Thameeraviraj commented ·
I have no idea about how to write formula for get sum with same condition David.. :(
0 Likes 0 ·
Wilfred van Dijk avatar image Wilfred van Dijk commented ·
It's the "where" clause in the query, to hide all the hidden rows, there's something like "... where [is_hidden]=1 ..." You should also add this where clause in the calculation.
0 Likes 0 ·
Thameeraviraj avatar image Thameeraviraj commented ·
Hi, I tried that way. So my formula like following. =SUM(IIF(((Parameters!ExcludeZeroPaid.Value) = True AND (Fields!PaidAmt.Value = 0)),0,Fields!ToPay.Value)) But this is not working. After running report in total row showing #Error. And if I change formula to =SUM(IIF(((Parameters!ExcludeZeroPaid.Value) = True,0,Fields!ToPay.Value)) This is working. But I need to add both conditions to the formula. Any idea please?
0 Likes 0 ·
Venkataraman avatar image Venkataraman commented ·
It should work, I think you have some problems with your paranthesis. Refer to this post for more information: http://stackoverflow.com/questions/15166452/nested-iif-with-multiple-conditions-ssrs
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.