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