Monday, March 12, 2012

matrix data area conditional formatting off max value

I have a matrix where i'm trying to have the report select the maximum value in the data area (not including the subtotals) and bold that value.

month day1 day2 day3 day4 total Jan 10 15 5 12 42 Feb 5 8 3 11 27 Total 15 23 8 23 69

I'm sure i will have to use the IIF function in conjunction with the InScope function.

However i'm unsure on how to isolate to the max value and make sure the Inscope function does not affect the totals.

any idea's are appreciated.

Bob

In your data column in the Font Weight expression try the following

IIf(Inscope("matrix1_Month") And InScope("matrix1_Day") ,IIf(Max(Fields!Day.Value),"Bold","Normal"),nothing) - I had a more complex Matrix but I was about to bold my column.

I hope this helps.

Ham

|||

Well that definately got me closer, but I found it highlights all the values in bold except the subtotals. so we accomplished not affecting the subtotals.

here is a view of the actual result table and the expression I wrote.

IIf(Inscope("matrix1_MONTH_NAME") And InScope("matrix1_Day_of_week") ,IIf(Max(Fields!FACT_CUT_RELEASE_Count.Value),"Bold","Normal"),"Normal")

Month/Year Workflow Title Type Type Detl Sun Mon Tue Wed Thu Fri Sat Total January 2006 60 632 961 602 855 918 21 4049 February 2006 10 477 294 500 334 300 8 1923 March 2006 3 610 558 856 359 1255 88 3729 April 2006 269 386 270 392 588 2 1907 May 2006 695 718 786 780 1149 67 4195 June 2006 6 733 800 999 1186 1554 17 5295 July 2006 13 991 940 1073 928 839 107 4891 August 2006 125 848 964 1249 1222 821 16 5245 September 2006 10 512 779 511 821 921 17 3571 October 2006 1046 775 564 432 481 27 3325 November 2006 13 720 671 1212 958 561 4 4139 December 2006 13 648 745 963 635 1666 100 4770 Total 253 8181 8591 9585 8902 11053 474 47039

I think i may need to have a function that determines the max value and then have the Fields!FACT_CUT_RELEASE_Count.Value compared against that in the nested iif statement

|||

Instead of using "......., IIf(Max(Fields!FACT_CUT_RELEASE_Count.Value),"Bold","Normal")," you need to specify the scope over which you are taking the maximum.

Try...

IIf(Inscope("matrix1_MONTH_NAME") And InScope("matrix1_Day_of_week") , IIf(Max(Fields!FACT_CUT_RELEASE_Count.Value,"matrix1_MONTH_NAME")=sum(Fields!FACT_CUT_RELEASE_Count.Value),"Bold","Normal"),"Normal")

No comments:

Post a Comment