SQLTeam.com | Weblogs | Forums

SSRS Indicator at Subtotal level


#1

I have a report that is grouped by name. I have an indicator at the detail level that works fine. However at the subtotal level (by name) I do not want the indicator to display using the aggregated values. It's a simple indicator that is a true/false green/red. Then at the subtotal level, I want it to merely display the 'worst' value from the details of the group. So if there are 7 records for that particular name and they are all 'Green' (true) then the subtotal level indicator would display 'Green'. Additionally, if there are 7 records and 6 are 'Green' (true) and 1 record is 'Red' (false) then I want the subtotal level indicator to display RED. After numerous attempts and looking on sites everywhere, I cannot find how to do this. Is this even possible??


#2

please provide sample data to work with?


#3

I have this expression for the detail rows and the Start and End for 'Red' icon is 0,0. The Start and End for 'Green' icon is 1,1.
=iif((Fields!Blower_Door_Final.Value <= ((Fields!Target.Value * .10) + Fields!Target.Value)),1,0)

I'm including a screen shot of what I'm trying to accomplish. The cell that is circled red (when the name is collapsed) is where I want the expression to go that will display 'red' if there is even just 1 'red' in the detail, otherwise if all 'green' then it would display 'green'.


#4

use case statement

case when ColumnName<=(othercolumnName*0.1+othercolumnName) then 1 else 0 End

Thanks
Basit.


#5

Thank you for the hint! I've tried several complicated formulas to try to get this to work because I didn't think MIN/MAX would work for this. Although I can't reference the indicator in the expression, I created a calculated field that calculated the value and hid the column. Then I used the indicator to look at this field and used the MIN function.

=IIF(MIN(Fields!GoalResult.Value) = 0, "0","1")