matrix subtotal

hi,

If I use the matrix to dynamically show the columns,but at the last column,I hope to get the average data ,But the subtotal can not help in this case,how can i do ?

thx for help!

Does anyone know how to do this? Doesnt seem possible in a matrix report?|||I think this thread can help you:

http://forums.microsoft.com/MSDN/showpost.aspx?postid=791849&siteid=1&message_id=524412&notification_id=524412
|||thanks for the link.

matrix subtotal

hi,

If I use the matrix to dynamically show the columns,but at the last column,I hope to get the average data ,But the subtotal can not help in this case,how can i do ?

thx for help!

Does anyone know how to do this? Doesnt seem possible in a matrix report?|||I think this thread can help you:

http://forums.microsoft.com/MSDN/showpost.aspx?postid=791849&siteid=1&message_id=524412&notification_id=524412
|||thanks for the link.

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 and Averages

My Matrix has the following structure
Product Jan Feb Mar Total
--
Prroduct1 2 4 1 6
How would I add an "average column"
Product Jan Feb Mar Total Avg
--
Prroduct1 2 4 1 6 2Okay, I solved my % Total problem.
In the data region, I split the cell into two, one is the original data, the
other is the % of the left cell to the scope. Then the Total and %Total
appear side by side if you do subtotal to the group. After that, hide the
%Total column using inscope function if it is in the scope.
But I am very interested in knowing if it is possible to do average though.
"Frank RS" wrote:
> I have the similar situation-- to add a %Total after the Total filed.
> It looks impossible because Total column is pretty much the last column in a
> matrix.
> Microsoft gurus, please help on this problem.
> At least let us know if it is doablt.
> Thanks in advance!
> Frank
> "SAcanuck" wrote:
> > My Matrix has the following structure
> > Product Jan Feb Mar Total
> > --
> > Prroduct1 2 4 1 6
> >
> > How would I add an "average column"
> >
> > Product Jan Feb Mar Total Avg
> > --
> > Prroduct1 2 4 1 6 2
> >|||I guess I got the answer to this question too.
Just modify the expression to change the sum function into average function.
I will post if I got time to play with it.
"Frank RS" wrote:
> Okay, I solved my % Total problem.
> In the data region, I split the cell into two, one is the original data, the
> other is the % of the left cell to the scope. Then the Total and %Total
> appear side by side if you do subtotal to the group. After that, hide the
> %Total column using inscope function if it is in the scope.
> But I am very interested in knowing if it is possible to do average though.
> "Frank RS" wrote:
> > I have the similar situation-- to add a %Total after the Total filed.
> > It looks impossible because Total column is pretty much the last column in a
> > matrix.
> > Microsoft gurus, please help on this problem.
> > At least let us know if it is doablt.
> > Thanks in advance!
> > Frank
> >
> > "SAcanuck" wrote:
> >
> > > My Matrix has the following structure
> > > Product Jan Feb Mar Total
> > > --
> > > Prroduct1 2 4 1 6
> > >
> > > How would I add an "average column"
> > >
> > > Product Jan Feb Mar Total Avg
> > > --
> > > Prroduct1 2 4 1 6 2
> > >