Matrix Report:Adding Average Column/Sorting based on last month/Conditional Formating

I have a matrix report with 2 column SaleAmount and ProfitAmounts by Month like

Sale Profit

Dealer 5/1/2007 6/1/2007 7/1/2007 5/1/2007 6/1/2007 7/1/2007

A 100 200 300 20 25 15

B 200 250 50 30 45 19

how can i do following 3 things

1)Add Total column for Sale and Average column for Profit

2)Sort report by lastMonth of Sale (here 7/1/2007) High to low

3)if last month of sale(here 7/1/2007) is less than second last month here (6/1/2007) whole row should be red

thanks

Abhijit Ramani wrote:

I have a matrix report with 2 column SaleAmount and ProfitAmounts by Month like

Sale Profit

Dealer 5/1/2007 6/1/2007 7/1/2007 5/1/2007 6/1/2007 7/1/2007

A 100 200 300 20 25 15

B 200 250 50 30 45 19

how can i do following 3 things

1)Add Total column for Sale and Average column for Profit

2)Sort report by lastMonth of Sale (here 7/1/2007) High to low

3)if last month of sale(here 7/1/2007) is less than second last month here (6/1/2007) whole row should be red

thanks

1) Since you want a total and an average, I would build this into your SQL statement. Buisness Intelligence Studio allows automatic summation, but you are asking for both.

In order for you to have a total and sale by month, you will most likely need to add a column in your table that is a Month Index. In other words, it is fairly difficult to get the average value of each month if you cannot uniquely identify the month.

2) Right click the column header for the month. Select properties then click the interactive sort tab. You want to check the "add an interactive sort to this textbox". Next, add the proper expression to sort by.

3) This may be a little tricky but to get you started: click the field which you want to change color. Click View -> properties window (if it isn't already open). In the color property, you want to put an expression.

Maybe something like this: =IIF(Fields!NextMonth.Value < Fields!PreviousMonth.Value,"Red", "Green")

sql

Matrix Page Breaks within a list / Visibility is conditional

Think I have found a bug.

I have a report that has a Parameter called "LevelOfDetail" This has 2 possible values "Summary, Detail, or Combined".

The report has a Summary Section and a subreport that holds the details.
WIthin the summary section is a Matrix (a list of all properties and some values)

If they choose to see the details, the matrix in the details section will show a break out of all this information summarized in the summary section.

The Details Matrix is set to have a page break at the beginning and end of the top level group.

I have a list control on the summary page that contains the details subreport and passes the appropriate parameter.

Everything works the way I want until I try to set visibility on sub report.

Once I set the conditional visibility of the report objects (based on the Level of Detail parameter) the page breaks are not recognized.

This is important as the user will never print the report, but will be downloading to Excel.

If the page breaks work correctly, each page is assigned a different worksheet in their downloaded workbook.

Any help, please let me know.

Page break and visibility condition can not be used for same componenet. I mean to say they are not supported by SSRS 2005. u need to go for a work around.

Priyank

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 Conditional Formatting

I have a matrix and want to put a border on only one column. I have
this expression in the right and left borderstyle property:
=iif(First(Fields!desc.Value)="Sent Census","Solid","None")
So if the value column = "Sent Census" then I want the column to have a
left and right border. My problem is that if the data cell has a 0 in it, the
border doesn't appear. I know why, I'm just looking for a work around to
have the whole column bordered even if a 0 is placed in the data cell.
Thanks!Seems like as soon as I post a question, I figure out the answer:
=iif(ReportItems("desc").Value="Sent Census","Solid","None")
You reference the textbox, not the data. doh.
"Sharon" wrote:
> I have a matrix and want to put a border on only one column. I have
> this expression in the right and left borderstyle property:
> =iif(First(Fields!desc.Value)="Sent Census","Solid","None")
> So if the value column = "Sent Census" then I want the column to have a
> left and right border. My problem is that if the data cell has a 0 in it, the
> border doesn't appear. I know why, I'm just looking for a work around to
> have the whole column bordered even if a 0 is placed in the data cell.
> Thanks!
>

Matrix columns - Conditional Formatting - Column Number

Hi,
I am trying to create a Matrix with each dynamically created column having a
differnet background colour. Any ideas on how to achieve this ?
If I could access the column number some how I could just apply conditional
formatting based on the column number, but I cant seem to find out how to get
at the column number.How are you creating the columns dynamically?
Do you know what is your maximum # or columns?|||1. I am not - I Just drag the field to the first column heading in the
matrix and reporting services does the dynamic bit.
2. No, it will be different on each implementation, but generally less than
10 - so I am happy to put a sensible limit on it and have the colours wrap
around after a set number of columns if the limit is exceeded - or base the
colour on some formula.
As an example :
Centre1 Centre2 ..... Centre N
Males x x ..... x
Females x x ..... x
...
...
more stats
I want the column for centre1 in blue, centre2 in yellow... and so on.
Each implementaion will have different centre names and centre ids, so I
dont want to tie the formatting into a particluar value in the data. One
solution would be to add a "rank" field to my data set and then use this
field in the conditional formatting, but I dont really want to add this
complexity to my data set and feel there must be an easier way than this.
"sorcerdon@.gmail.com" wrote:
> How are you creating the columns dynamically?
> Do you know what is your maximum # or columns?
>|||Best way to achieve it is to find some pattern in ur existing cloumns
but i m pretty sure you may have already done it. So cant you create a
custom column from your query having nothing but simply an integer
containing column no? It should give you the column no u want in the
matrix|||Yes this is one way to achieve it. This is what I was suggesting when I said
I could create a "rank" column.
I just think there should be a better way of doing this than having to amend
the data set. Adding a "column no" column to the dataset may not always be
straightforward and may add unnecessary complexity to the query.
I guess what I am asking is if there is a way of retrieving the column no
from the matrix and whether changing the dataset is the only solution.
"Techotsav" wrote:
> Best way to achieve it is to find some pattern in ur existing cloumns
> but i m pretty sure you may have already done it. So cant you create a
> custom column from your query having nothing but simply an integer
> containing column no? It should give you the column no u want in the
> matrix
>|||Hi Techotsav,
I am afraid it is not possible to do this easily like retrieve the column
no from the matrix and whether changing the dataset is the only solution.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Matrix - Subtotal Conditional Formatting - How do you do it?

My subtotal column's background color is dark gray and the text is bold
when the row group is collapsed, but I don't see this in other similar
matrix reports without a row group. This report was inherited from a
previous developer.
How did they do it? Even if I set the subtotal cell's colors to be
different from the entire column, they synchronize. I want to do this,
but can't figure out whether it's a setting or not.
Thanks!
MIkeThe subtotal heading should have a little green triangle on it. If you click
on that triangle, the VS properties window should show style settings that
specifically apply to subtotals row/columns.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bassist695" <Michael.EJ.Reynolds@.gmail.com> wrote in message
news:1128365078.625735.314000@.g49g2000cwa.googlegroups.com...
> My subtotal column's background color is dark gray and the text is bold
> when the row group is collapsed, but I don't see this in other similar
> matrix reports without a row group. This report was inherited from a
> previous developer.
> How did they do it? Even if I set the subtotal cell's colors to be
> different from the entire column, they synchronize. I want to do this,
> but can't figure out whether it's a setting or not.
> Thanks!
> MIke
>