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?
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.
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?
I have no idea about how to write formula for get sum with same condition David.. :(
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.
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?
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
15 People are following this question.
