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")

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")

Matrix custom subtotals ? How is it possible ?

Hi ALL,
I have a problem and i becoming crazy !
I create a report like this using a Matrix in Reporting Services
Sales
Area 1 250
Customer 1 100
Customer 2 150
Area 2 60
Customer 3 40
Customer 4 20
I need to add a total row witch is the sum of AREA 1 + AREA 2 lke this
IMPORTANT : rows in matrix becomes directly from my query... they are
not grouping.
Sales
Area 1 250
Customer 1 100
Customer 2 150
Area 2 60
Customer 3 40
Customer 4 20
TOTAL 310
How can I express the contition... If I add a total to the matrix my
total is 620 (wrong !...)
Thank you !
Gas1970On Jun 13, 11:16 am, alberto.gasta...@.libero.it wrote:
> Hi ALL,
> I have a problem and i becoming crazy !
> I create a report like this using a Matrix in Reporting Services
> Sales
> Area 1 250
> Customer 1 100
> Customer 2 150
> Area 2 60
> Customer 3 40
> Customer 4 20
> I need to add a total row witch is the sum of AREA 1 + AREA 2 lke this
> IMPORTANT : rows in matrix becomes directly from my query... they are
> not grouping.
> Sales
> Area 1 250
> Customer 1 100
> Customer 2 150
> Area 2 60
> Customer 3 40
> Customer 4 20
> TOTAL 310
> How can I express the contition... If I add a total to the matrix my
> total is 620 (wrong !...)
> Thank you !
> Gas1970
Traditionally, in some cases, I obtain my sums/totals in the stored
procedure/query that is sourcing my matrix report. This should be a
good option in your case as well. Also, depending on the complexity of
the matrix report, you may need to use a while loop/cursor to
determine the sum/totals. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant