How to do colour formatting in drilldown pivot report

1
votes

I have made a Drill down report in SSRS 2008 with one parent group and 4 child group each group field aggregates value under a pivot column.I have to do conditional formatting to change the background color of the pivot field if the particular aggregate value exceeds the input value on each drilldown field.

I have tried multiple expression With 'IIF' and 'SWITCH' condition to change the background color in pivot field in each drill down field. 

=switch(
 Fields!CIRCLE.Value,"DataSet1" AND (fields!TOTAL.Value>30000,"DataSet1"),"Red",

       (Fields!ZONE.Value,"DataSet1" AND (fields!TOTAL.Value>100,"DataSet1"),"Red",

        (Fields!CLUSTER.Value,"DataSet1" AND (fields!TOTAL.Value>5000,"DataSet1"),"Red",

        (Fields!NODE.Value,"DataSet1" AND (fields!TOTAL.Value>3000,"DataSet1"),"Red","White"
))))

I want the Pivot field Hour with sum as aggregates to turn red on circle level zone level,cluster level and node level like example if the Sum field under pivot column is 700 in 3rd hour and it exceeds 300 then the value at 3rd hour should turn red on circle level.

4
reporting-services
Could you post an example of the report with data to help clarify what it is you actually need help with? – SuperSimmer 44
Data Set in the SSRS report are Circle,Zone,Cluster,Node,Date,Hour,Total,Parent group Date,Child group Circle ,Zone,Cluster,Node,Pivot Field Hour(0-23) aggregate pivot field sum(total).There is row field and column field Total which calculates Total sum of all the drilldown fields. – Aditi Singh

4 Answers

1
votes

Try this method:

=switch( Fields!CIRCLE.Value > 30000 AND Fields!TOTAL.Value >30000,"Red",
         Fields!ZONE.Value> 100 AND Fields!TOTAL.Value > 100,"Red",
         Fields!CLUSTER.Value > 5000 AND Fields!TOTAL.Value > 5000,"Red",
         Fields!NODE.Value > 3000 AND Fields!TOTAL.Value > 3000 ,"Red")
1
votes

enter image description here@Aditi Singh we are on the correct track, have a look at images below, let me know your thoughts:

enter image description here

1
votes

@SuperSimmer44 Below is the link image for the desried Output- Drilldown Report sampleScenario: Circle>4000(Red), Zone>3000(Red), Cluster>500(Red)

0
votes

Try this method (you will need to extend to hour value 10+):

=switch(Fields!HOUR1.Value = "1" and Fields!TOTAL.Value >= "100", "RED",
        Fields!HOUR1.Value = "2" and Fields!TOTAL.Value >= "200", "RED",
        Fields!HOUR1.Value = "3" and Fields!TOTAL.Value >= "300", "RED",
        Fields!HOUR1.Value = "4" and Fields!TOTAL.Value >= "400", "RED",
        Fields!HOUR1.Value = "5" and Fields!TOTAL.Value >= "500", "RED",
        Fields!HOUR1.Value = "6" and Fields!TOTAL.Value >= "600", "RED",
        Fields!HOUR1.Value = "7" and Fields!TOTAL.Value >= "700", "RED",
        Fields!HOUR1.Value = "8" and Fields!TOTAL.Value >= "800", "RED",
        Fields!HOUR1.Value = "9" and Fields!TOTAL.Value >= "900", "RED",
        )