Matrix subtotal row question

I have created a matrix that looks like the following example:
APRIL MAY JUNE
PRODUCT X 10 20 30
PRODUCT Y 20 30 40
where the data is profit per unit sold (=PROFIT/# of UNITS)
I need to add a row that is the average profit for each month. I know
how to get the subtotal row to show up, but this justs adds the rows
(which is meaningless for me). Even the simple average (e.g. (10 +
20)/2) won't do - I need a weighted average per month (e.g. ALL PROFIT
for APRIL/ALL UNITS sold for APRIL). A data example is:
for April I sold 10 units of X for a $100 profit and I sold 20 units
of Y for a $400 profit
As in the table above, the profit per unit is (100/10) $10 for X and
(400/20) $20 for Y
But the average profit I want is not ($10 + $20) / 2 ($15). It is $500
profit / 30 units = ~$17
So two questions:
How do I add a average row to the matrix?
How do I make this a weighted average?
Sorry if my explanation is confusing or the answer is basic... new to
this and can't find an answer anywhere!It sounds like your current expression is something like this:
=Sum(Fields!ProfitPerUnit.Value)
What you really need is something more like this:
=Sum(Fields!TotalProfit.Value)/Sum(Fields!Units.Value)
If you are only returning ProfitPerUnit and Units in your query, you could
do this instead:
=Sum(Fields!ProfitPerUnit.Value*Fields!Units.Value)/Sum(Fields!Units.Value)
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"coldfact" <bryan@.coldfact.com> wrote in message
news:249185cd.0407151323.79162246@.posting.google.com...
> I have created a matrix that looks like the following example:
> APRIL MAY JUNE
> PRODUCT X 10 20 30
> PRODUCT Y 20 30 40
> where the data is profit per unit sold (=PROFIT/# of UNITS)
> I need to add a row that is the average profit for each month. I know
> how to get the subtotal row to show up, but this justs adds the rows
> (which is meaningless for me). Even the simple average (e.g. (10 +
> 20)/2) won't do - I need a weighted average per month (e.g. ALL PROFIT
> for APRIL/ALL UNITS sold for APRIL). A data example is:
> for April I sold 10 units of X for a $100 profit and I sold 20 units
> of Y for a $400 profit
> As in the table above, the profit per unit is (100/10) $10 for X and
> (400/20) $20 for Y
> But the average profit I want is not ($10 + $20) / 2 ($15). It is $500
> profit / 30 units = ~$17
> So two questions:
> How do I add a average row to the matrix?
> How do I make this a weighted average?
> Sorry if my explanation is confusing or the answer is basic... new to
> this and can't find an answer anywhere!|||Very nice - works now - thanks for your help!
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message news:<uONOoHsaEHA.1656@.TK2MSFTNGP09.phx.gbl>...
> It sounds like your current expression is something like this:
> =Sum(Fields!ProfitPerUnit.Value)
> What you really need is something more like this:
> =Sum(Fields!TotalProfit.Value)/Sum(Fields!Units.Value)
> If you are only returning ProfitPerUnit and Units in your query, you could
> do this instead:
> =Sum(Fields!ProfitPerUnit.Value*Fields!Units.Value)/Sum(Fields!Units.Value)
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "coldfact" <bryan@.coldfact.com> wrote in message
> news:249185cd.0407151323.79162246@.posting.google.com...
> > I have created a matrix that looks like the following example:
> >
> > APRIL MAY JUNE
> > PRODUCT X 10 20 30
> > PRODUCT Y 20 30 40
> >
> > where the data is profit per unit sold (=PROFIT/# of UNITS)
> > I need to add a row that is the average profit for each month. I know
> > how to get the subtotal row to show up, but this justs adds the rows
> > (which is meaningless for me). Even the simple average (e.g. (10 +
> > 20)/2) won't do - I need a weighted average per month (e.g. ALL PROFIT
> > for APRIL/ALL UNITS sold for APRIL). A data example is:
> > for April I sold 10 units of X for a $100 profit and I sold 20 units
> > of Y for a $400 profit
> > As in the table above, the profit per unit is (100/10) $10 for X and
> > (400/20) $20 for Y
> > But the average profit I want is not ($10 + $20) / 2 ($15). It is $500
> > profit / 30 units = ~$17
> >
> > So two questions:
> > How do I add a average row to the matrix?
> > How do I make this a weighted average?
> >
> > Sorry if my explanation is confusing or the answer is basic... new to
> > this and can't find an answer anywhere!|||Very nice - works now - thanks for your help!
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message news:<uONOoHsaEHA.1656@.TK2MSFTNGP09.phx.gbl>...
> It sounds like your current expression is something like this:
> =Sum(Fields!ProfitPerUnit.Value)
> What you really need is something more like this:
> =Sum(Fields!TotalProfit.Value)/Sum(Fields!Units.Value)
> If you are only returning ProfitPerUnit and Units in your query, you could
> do this instead:
> =Sum(Fields!ProfitPerUnit.Value*Fields!Units.Value)/Sum(Fields!Units.Value)
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "coldfact" <bryan@.coldfact.com> wrote in message
> news:249185cd.0407151323.79162246@.posting.google.com...
> > I have created a matrix that looks like the following example:
> >
> > APRIL MAY JUNE
> > PRODUCT X 10 20 30
> > PRODUCT Y 20 30 40
> >
> > where the data is profit per unit sold (=PROFIT/# of UNITS)
> > I need to add a row that is the average profit for each month. I know
> > how to get the subtotal row to show up, but this justs adds the rows
> > (which is meaningless for me). Even the simple average (e.g. (10 +
> > 20)/2) won't do - I need a weighted average per month (e.g. ALL PROFIT
> > for APRIL/ALL UNITS sold for APRIL). A data example is:
> > for April I sold 10 units of X for a $100 profit and I sold 20 units
> > of Y for a $400 profit
> > As in the table above, the profit per unit is (100/10) $10 for X and
> > (400/20) $20 for Y
> > But the average profit I want is not ($10 + $20) / 2 ($15). It is $500
> > profit / 30 units = ~$17
> >
> > So two questions:
> > How do I add a average row to the matrix?
> > How do I make this a weighted average?
> >
> > Sorry if my explanation is confusing or the answer is basic... new to
> > this and can't find an answer anywhere!

Matrix subtotal problem

Hi,

I have a matrix report like this,

　 Product 1 Product 2 Product 3 subtotal Dept A 　 　 　 　 Dept B 　 　 　 　 … 　 　 　 　 subtotal 　 　 　 　

for some issues, I must add a group filter to filter out some departments that I don't want to show them on this report (ex. Field!dept.value !="A"). Additionally, My users can input the date interval to query by report parameters. When the date interval user inputed only have Dept A data , the report return some error message(I have set the norows property to show "nodata" if there is no data meet the condition). But when I remove the subtotal ,it works and return "nodata" message. I think there's some problem with the subtotal, but I have no idea where to figure it out.

Anyone would give me a help ?!

First, I recommend to install SP1 of SQL Server 2005.

Regarding the filter on the group - it will only apply to the group data but no to the subtotal because the subtotal is not in the scope of the grouping. So if you want to really remove the values for certain groups and a subtotal is present, you should filter directly in the query, at the dataset level, or at the matrix level.

-- Robert

Matrix control

Hi all,

I'm using matrix control as my reporting tools.

But i found one trouble, how can i do my matrix become like this.

Product A

Product B

Customer

Price

Qty

Price

Qty

Customer A

10

10

140

160

Customer B

12

120

120

160

Customer C

10

10

110

140

The price and qty is the static column, and i would like to make the static row also. (show in red color) but i can't.

Please help...

Thanks

Kendy

1.

Add matrix contol

2.

in the row add Customer

3. in the columns add Product

4. Right click on Data and select add column

no you will have two column

add price to the left onw and Qty to the right onw

|||

Hi Idanle,

I have create the matrix control, for the qty and price i using static column.

For static row, i have do it also but the result will be like this

Product A

Product B

Price

Qty

Price

Qty

Customer

Customer A

10

10

140

160

Customer

Customer B

12

120

120

160

Customer

Customer C

10

10

110

140

It will repeat because is static row same as static column where repeated showing out the "Price" and "Qty".

How to make the title "customer" only showing as header.

Thanks

Kendy

|||Can you send me the RDL or upload a print screen of youe design mode|||

Hi Idanle,

I have upload the excel file.

Inside the excel file, the tab name "Defect" is the problem i facing, wherelse the tab name "Output" is what i expected.

http://download.yousendit.com/ED0492A72B6C6C66

Thanks

Kendy

|||

I can't found the problem.

Can you send me your table (data source) and the RDL or the matrix definition?

|||

Hi Idanle,

As u can see from the excel file, the tab defect page.

The "Customer" header will always display as a row detail where show in red color.

But when see the output tab, where the result i wanted. The "Customer" header will remain as header, it wouldn't same like defect tab.

My question is can matrix control done as output tab, if yes how the way to do it.

Thanks

Kendy

|||

Hi,

I see the problem and I'ts seams somthing worng with your matrix setting since this problem should not occur.

This is way I ask for thr RDL and table.

If you can just create your data table in excel I will set for you the RDL

|||

Hi Idanle,

I have upload the table, rdlc and expected output i wanted.

http://download.yousendit.com/CA8B3D95276E0341

Thanks for your help.

Regards

Kendy

|||

The table you sent me is not the same table using your Expected output.

What is the problem with the current output ? it's look fine

|||

Hi Idanle,

Actually, when you look at expected output and current output. The only different is header, in expected output there are header where mark in red color wherelse in current output is without the header.

And i have try do it with static row (just try in one column name Customer), the result i get is like this, please download from the link.

http://download.yousendit.com/BC879C5820984C1A

So can i know how to do it become expected output using matrix control provided by sql server reporting services?

Thanks

Kendy

|||For column headers, what you need to do is put a rectangle in the corner cell. In this rectangle, put your Customer label textbox.

Matrix columns

Hi,
I have a report which is showing monthly sales figures on a matrix. the Rows
are grouped by year and product and the Columns grouped by month. What i
would like to achieve now is to add a column between each month column to
show the % increase between the months.
so that the report looks something like:
Year JAN % diff FEB %diff
MAR ......
2004
Product A 10 100% 5 0%
5 ......
Product B .....
...
2003....
...
regards,You could put a rectangle which has two textboxs in the data cell. One
textbox is for sum of sales and another for percentage of increase. In
column header, do same things to show different title.
Regards,
Henry
"Kazar" <kazar@.kazar.kaz> wrote in message
news:uO%23kdK0lEHA.2880@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a report which is showing monthly sales figures on a matrix. the
> Rows
> are grouped by year and product and the Columns grouped by month. What i
> would like to achieve now is to add a column between each month column to
> show the % increase between the months.
> so that the report looks something like:
> Year JAN % diff FEB %diff
> MAR ......
> 2004
> Product A 10 100% 5 0%
> 5 ......
> Product B .....
> ...
> 2003....
> ...
>
> regards,
>

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