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