MAX

I have the following code that I created today:
select month(DataPesquisa) as Mes, count(FConceito) as Bom,
isnull(Excelente.Conceito,0) as Excelente,isnull(Regular.Conceito,0) as
Regular,
isnull(Ruim.Conceito,0) as Ruim
from satisfacao x
left outer join (select referencia, count(Fconceito) as Conceito from
satisfacao
where month(Datapesquisa) = '01' and FConceito = '4' and referencia = '2005'
group by referencia) Excelente
on x.referencia = Excelente.referencia
left outer join (select referencia, count(Fconceito) as Conceito from
satisfacao
where month(Datapesquisa) = '01' and FConceito = '2' and referencia = '2005'
group by referencia) Regular
on x.referencia = Regular.referencia
left outer join (select referencia, count(Fconceito) as Conceito from
satisfacao
where month(Datapesquisa) = '01' and FConceito = '1' and referencia = '2005'
group by referencia) Ruim
on x.referencia = Ruim.referencia
where month(Datapesquisa) = '01' and FConceito = '3'and x.referencia =
'2005'
group by x.referencia, month(x.DataPesquisa), Excelente.Conceito,
Regular.Conceito, Ruim.Conceito
Your result is:
Excellent Godd Regulate Bad
-- -- -- -- --
1 3 2 1 0
My question: I want to select the LARGEST value of these results. Does give
there for using the function MAX in that procedure of top or will I have to
do this for the application (programming even)'?I think you can hadle it in the client application
Madhivanan|||Frank Dulk wrote:

> select month(DataPesquisa) as Mes, count(FConceito) as Bom,
> isnull(Excelente.Conceito,0) as Excelente,isnull(Regular.Conceito,0) as
> Regular,
> isnull(Ruim.Conceito,0) as Ruim
> from satisfacao x
> left outer join (select referencia, count(Fconceito) as Conceito from
> satisfacao
> where month(Datapesquisa) = '01' and FConceito = '4' and referencia = '200
5'
> group by referencia) Excelente
> on x.referencia = Excelente.referencia
> left outer join (select referencia, count(Fconceito) as Conceito from
> satisfacao
> where month(Datapesquisa) = '01' and FConceito = '2' and referencia = '200
5'
> group by referencia) Regular
> on x.referencia = Regular.referencia
> left outer join (select referencia, count(Fconceito) as Conceito from
> satisfacao
> where month(Datapesquisa) = '01' and FConceito = '1' and referencia = '200
5'
> group by referencia) Ruim
> on x.referencia = Ruim.referencia
> where month(Datapesquisa) = '01' and FConceito = '3'and x.referencia =
> '2005'
> group by x.referencia, month(x.DataPesquisa), Excelente.Conceito,
> Regular.Conceito, Ruim.Conceito
Instead of several self joins you can rewrite that query to a single
aggregation:
select
month(DataPesquisa) as Mes,
sum(case when FConceito = '4' then 1 else 0) as Excelente,
sum(case when FConceito = '3' then 1 else 0) as Bom,
sum(case when FConceito = '2' then 1 else 0) as Regular,
sum(case when FConceito = '1' then 1 else 0) as Ruim,
from satisfacao
where month(Datapesquisa) = '01' and x.referencia = '2005'
group by x.referencia, month(x.DataPesquisa)

> Excellent Godd Regulate Bad
> -- -- -- -- --
> 1 3 2 1 0
>
> My question: I want to select the LARGEST value of these results. Does giv
e
> there for using the function MAX in that procedure of top or will I have t
o
> do this for the application (programming even)'?
If you want to add a new column with that max value, then the easiest
way is doing it on the client. Else you have to add another case:
select
dt.*,
case
when Excelente > Bom and Excelente > Regular and Excelente > Ruim
then Excelente
when Bom > Regular and Bom > Ruim then Bom
when Regular > Ruim then Regular
else Ruim
end
from
(
select
month(DataPesquisa) as Mes,
sum(case when FConceito = '4' then 1 else 0) as Excelente,
sum(case when FConceito = '3' then 1 else 0) as Bom,
sum(case when FConceito = '2' then 1 else 0) as Regular,
sum(case when FConceito = '1' then 1 else 0) as Ruim,
from satisfacao
where month(Datapesquisa) = '01' and x.referencia = '2005'
group by x.referencia, month(x.DataPesquisa)
) dt
If you just need that max info:
select
month(DataPesquisa) as Mes,
max(cnt)
from
(
select
month(DataPesquisa) as Mes,
FConceito,
count(*) as cnt
from satisfacao
where month(Datapesquisa) = '01' and x.referencia = '2005'
group by x.referencia, month(x.DataPesquisa), FConceito
) dt
all queries untested...
Dieter|||Thank you for the help
I used your code making the necessary fittings and I have new question.
After arranging, Query was like this:
select
dt.*,
case
when Excelente > Bom and Excelente > Regular and Excelente > Ruim
then Excelente
when Bom > Regular and Bom > Ruim then Bom
when Regular > Ruim then Regular
else Ruim
end
as Maior
from (
select month(DataPesquisa) as Mes,
sum (case DConceito When '4' then 1 else 0 End) as Excelente,
sum (case DConceito When '3' then 1 else 0 End) as Bom,
sum (case DConceito When '2' then 1 else 0 End) as Regular,
sum (case DConceito When '1' then 1 else 0 End) as Ruim
from satisfacao x
where month(x.Datapesquisa) = '01' and x.referencia = '2005'
group by x.referencia, month(x.DataPesquisa)
) dt
results it is it:
Mes Excelente Bom Regular Ruim Maior
Now: Does have as I place in Adult's place the name of the field that the
largest value is (Good or Bad)?
"Dieter Noeth" <dnoeth@.gmx.de> escreveu na mensagem
news:Ou28y4vHFHA.2984@.TK2MSFTNGP15.phx.gbl...
> Frank Dulk wrote:
>
'2005'
'2005'
'2005'
> Instead of several self joins you can rewrite that query to a single
> aggregation:
> select
> month(DataPesquisa) as Mes,
> sum(case when FConceito = '4' then 1 else 0) as Excelente,
> sum(case when FConceito = '3' then 1 else 0) as Bom,
> sum(case when FConceito = '2' then 1 else 0) as Regular,
> sum(case when FConceito = '1' then 1 else 0) as Ruim,
> from satisfacao
> where month(Datapesquisa) = '01' and x.referencia = '2005'
> group by x.referencia, month(x.DataPesquisa)
>
give
to
> If you want to add a new column with that max value, then the easiest
> way is doing it on the client. Else you have to add another case:
> select
> dt.*,
> case
> when Excelente > Bom and Excelente > Regular and Excelente > Ruim
> then Excelente
> when Bom > Regular and Bom > Ruim then Bom
> when Regular > Ruim then Regular
> else Ruim
> end
> from
> (
> select
> month(DataPesquisa) as Mes,
> sum(case when FConceito = '4' then 1 else 0) as Excelente,
> sum(case when FConceito = '3' then 1 else 0) as Bom,
> sum(case when FConceito = '2' then 1 else 0) as Regular,
> sum(case when FConceito = '1' then 1 else 0) as Ruim,
> from satisfacao
> where month(Datapesquisa) = '01' and x.referencia = '2005'
> group by x.referencia, month(x.DataPesquisa)
> ) dt
>
> If you just need that max info:
> select
> month(DataPesquisa) as Mes,
> max(cnt)
> from
> (
> select
> month(DataPesquisa) as Mes,
> FConceito,
> count(*) as cnt
> from satisfacao
> where month(Datapesquisa) = '01' and x.referencia = '2005'
> group by x.referencia, month(x.DataPesquisa), FConceito
> ) dt
>
> all queries untested...
> Dieter

Matrix Totals - left formatted

I have successfully created a matrix consisting of payroll dates as column
headers, with task types as my rows and hours/task/day the detail data. The
columns total perfectly, but the totals only display to the RIGHT of all the
data. We display our totals FIRST, then the detail data. Can I do this?
total hrs day 1 day 2
task 1 16 8 8Yes. Click on the little green triangle in the (row/column) heading to get
the subtotal properties and look at the properties window. There is a
"Position" property which is set to "After" by default. You can set it to
"Before", which gives you the effect you want.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"JeanSA" <JeanSA@.discussions.microsoft.com> wrote in message
news:C4A8ABDC-B776-4CE1-9B4B-1DDC70DD6B8A@.microsoft.com...
>I have successfully created a matrix consisting of payroll dates as column
> headers, with task types as my rows and hours/task/day the detail data.
> The
> columns total perfectly, but the totals only display to the RIGHT of all
> the
> data. We display our totals FIRST, then the detail data. Can I do this?
> total hrs day 1 day 2
> task 1 16 8 8|||Thank you. Wasn't sure what "Position" meant. I appreciate your quick
response.
"Robert Bruckner [MSFT]" wrote:
> Yes. Click on the little green triangle in the (row/column) heading to get
> the subtotal properties and look at the properties window. There is a
> "Position" property which is set to "After" by default. You can set it to
> "Before", which gives you the effect you want.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "JeanSA" <JeanSA@.discussions.microsoft.com> wrote in message
> news:C4A8ABDC-B776-4CE1-9B4B-1DDC70DD6B8A@.microsoft.com...
> >I have successfully created a matrix consisting of payroll dates as column
> > headers, with task types as my rows and hours/task/day the detail data.
> > The
> > columns total perfectly, but the totals only display to the RIGHT of all
> > the
> > data. We display our totals FIRST, then the detail data. Can I do this?
> > total hrs day 1 day 2
> > task 1 16 8 8
>
>

Matrix subtotals only getting first value

Hi
I have created a matrix with subtotal on both the Row and Column. However, the total only seems to be considering the first value it encounters. Has anybody any ideas why this should be the case?
sample output:
Col1 Col2 Col3 Total
Row1 1.0 1.0 1.0
Row2 1.0 2.0 1.0
Total 1.0 1.0 1.0 1.0
*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=eb765bbec307481280b9ce093a602227
*****************************************Solved it, my grouping was wrong.
Opened up another heap of problems, but that is another story ...
*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5193
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=6f19700c9cb74267842c83847b306254
*****************************************|||How did you resolve? I'm having a similar issue with a matrix. I've one
group for the row, one group for the column and 4 entries for the data. I've
switched the row and column group, eliminated entries for the data and still
have the result of the 1st instance of return data for the subtotal.
Thanks!
Michelle
"Jan Bodey via SQLMonster.com" wrote:
> Solved it, my grouping was wrong.
> Opened up another heap of problems, but that is another story ...
> *****************************************
> * A copy of the whole thread can be found at:
> * http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5193
> *
> * Report spam or abuse by clicking the following URL:
> * http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=6f19700c9cb74267842c83847b306254
> *****************************************
>

Matrix Subtotals

I have created a matrix and I am trying to add a percentage subtotal on the
following. I have this Fiscal Year (FY) and Last Fiscal Year (LFY) data. Then
I added in a subtotal by right clicking on the column group for Male/Female.
Know I need to add and additional field that calculates the Total % .
M F Total Total %
Age FY LFY FY LFY FY LFY FY LFY
0 10 5 1 5 11 10 .33 .46
1-4 4 2 12 3 16 5 .47 .23
5-9 3 1 4 6 7 7 .21 .32
Total 17 8 17 14 34 22This is a multi-part message in MIME format.
--=_NextPart_000_003D_01C61F7D.57B99130
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
You'll probably need to check for scope in your cell expression, to make =the right calculation based on "where" you are in your matrix.
Use the following guideline:
=3DIif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", ="In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of =ColumnGroup1", "In Subtotal of entire matrix"))
Then, to calculate the percent, in the right scope, use this:
Fields!Name.Value / First(Fields!Amount.Value, "MatrixColumnGroupName") =- assuming that your first row is a total.
If it's not, you might be able to use SUM if you add the Matrix column =group name =3D Fields!Name.Value / SUM(Fields!Amount.Value, ="MatrixColumnGroupName")
Kaisa M. Lindahl
"Asim" <Asim@.discussions.microsoft.com> wrote in message =news:2BAED4A1-4E68-4B13-A0E0-A76A8B8D9BC3@.microsoft.com...
>I have created a matrix and I am trying to add a percentage subtotal on =the > following. I have this Fiscal Year (FY) and Last Fiscal Year (LFY) =data. Then > I added in a subtotal by right clicking on the column group for =Male/Female. > Know I need to add and additional field that calculates the Total % .
> > M F Total =Total %
> Age FY LFY FY LFY FY LFY FY LFY
> 0 10 5 1 5 11 10 .33 = .46
> 1-4 4 2 12 3 16 5 .47 = .23
> 5-9 3 1 4 6 7 7 .21 = .32
> Total 17 8 17 14 34 22
--=_NextPart_000_003D_01C61F7D.57B99130
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You'll probably need to check for scope in your cell =expression, to make the right calculation based on "where" you are in =your matrix.
Use the following guideline:
=3DIif(InScope("ColumnGroup1"), =iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In =Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))
Then, to calculate the percent, in the right scope, =use this:
Fields!Name.Value / First(Fields!Amount.Value, ="MatrixColumnGroupName") - assuming that your first row is a total.
If it's not, you might be able to use SUM if you add =the Matrix column group name =3D Fields!Name.Value / =SUM(Fields!Amount.Value, "MatrixColumnGroupName")
Kaisa M. Lindahl
"Asim" =wrote in message news:2BAED4A1-4E68-4B13-A0E0-A76A8B8D9BC3@.microsoft.com...>I have created a matrix and =I am trying to add a percentage subtotal on the > following. I have this =Fiscal Year (FY) and Last Fiscal Year (LFY) data. Then > I added in a =subtotal by right clicking on the column group for Male/Female. > Know =I need to add and additional field that calculates the Total % .> >  =; M F = Total Total %> Age &nbs=p; FY LFY FY LFY FY LFY FY =LFY> 0 = 10 5 =1 5 11 10 .33 =.46> 1-4 &nbs=p; 4 2 12 3 =16 5 =.47 .23> 5-9 &nbs=p; 3 1 =4 6 7 =7 .21 .32> Total &n=bsp; 17 8 =17 14 34 22

---

--=_NextPart_000_003D_01C61F7D.57B99130--|||Kaisa,
You seem to understand this InScope function so well and yet I take your
suggestion as you say "you have to check for scope in your cell expression".
The cell expression only *allows* one expression. In my detail cell I have
something like Sum(Fields!CriticalCount.value) and I can see how to put that
into the InScope but I want to do something else if I am in the subtotal and
I have questions about that. You are getting close to providing the answer
here when you say "then to calculate the percent in the right scope..." but
*where* would you put this calculation in the *one* cell expression that is
there? I have messed with this for a long time and almost got it working
with ONE column and ONE row ... but still couldnt figure out how to create a
subtotal expression different ( I tried using some thing like
SUM(ReportItems!tbCritCount.value) for the subtotal expression and of course
got errors on the aggregate and using ReportItems! ) So how can I reference
the values I want? lets say I want to check the max value in a column at the
subtotal level? OK ... if I cant do that ... then lets say I want to SUM the
values of an expression that I have in the detail cell of the column. I am
just not getting this and I am really really trying to ... :-(
"Kaisa M. Lindahl" wrote:
> You'll probably need to check for scope in your cell expression, to make the right calculation based on "where" you are in your matrix.
> Use the following guideline:
> =Iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))
> Then, to calculate the percent, in the right scope, use this:
> Fields!Name.Value / First(Fields!Amount.Value, "MatrixColumnGroupName") - assuming that your
> first row is a total.
> If it's not, you might be able to use SUM if you add the Matrix column group
> name = Fields!Name.Value / SUM(Fields!Amount.Value, "MatrixColumnGroupName")
> Kaisa M. Lindahl
> "Asim" <Asim@.discussions.microsoft.com> wrote in message news:2BAED4A1-4E68-4B13-A0E0-A76A8B8D9BC3@.microsoft.com...
> >I have created a matrix and I am trying to add a percentage subtotal on the
> > following. I have this Fiscal Year (FY) and Last Fiscal Year (LFY) data. Then
> > I added in a subtotal by right clicking on the column group for Male/Female.
> > Know I need to add and additional field that calculates the Total % .
> >
> > M F Total Total %
> > Age FY LFY FY LFY FY LFY FY LFY
> > 0 10 5 1 5 11 10 .33 .46
> > 1-4 4 2 12 3 16 5 .47 .23
> > 5-9 3 1 4 6 7 7 .21 .32
> > Total 17 8 17 14 34 22sql

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

I've created a matrix report that uses the "Jump to URL" option of the action property to allow my report to drill down to a website using query strings.

For example, the action property looks something like this:
="www.mywebsite.com/page.htm?company=" + Fields!Company.Value + "&status=" + Fields!Status.Value

This works nicely for all the cells in the matrix, but it doesn't work for the subtotals. For example if I click on the subtotal for company "ABC" for all statuses, I would like my query string to link as follows:
="www.mywebsite.com/page.htm?company=" + Fields!Company.Value + "&status="
where no status is specified.

Any ideas or suggestions?
ThanksI did a little more searching and found my question has already been answered here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=24371&SiteID=1

Matrix Reports Spawn Extra Page - Enclosing in List fixes this.

We really like using the Matrix reports, but we were finding that every Matrix report that we created would spawn an extraneous blank page. We tried putting the matrix in a rectangle, which works well for positioning other items on reports, but this had no effect on the problem.

Then we tried placing the matrix in a list with the list group details set to "=Nothing". It worked great - no more extra pages. Looked and didn't see this tip mentioned elsewhere so thought it might be worth sharing.

This usually happens because you have too many columns in your matrix, and it is spreading onto a second page horizontally.

Several other ways to fix this are: have less columns; make the columns and the fonts within them smaller; decrease the size of the margins on the report page; and alter the report page dimensions so that it is rendered in landscape mode.

Matrix report export to Excel

I created a report based on matrix.
I set the can grow property of all fields to false.
In the report the fields are behaving as expected - the size is fixed.
When I'm exporting to excel the fields are growing according to the text size.
(I have other report based on table and the can grow property controls the
fields alos when exporting to excel).
How can I control the can grow?
Thak'sHello,
It seems that you're running into a limitation in Excel. When there are
merged cells in a row, Excel cannot apply it's equivalent of CanGrow on the
row. You're likely running into merged cells due to the layout of your
report. If items in the report don't line up with each other vertically,
SRS are forced to span items across multiple cells in order to preserve
your layout.
I have forwarded your feedback to the product team. In the meantime, I also
encourage you submit via the link below
http://lab.msdn.microsoft.com/productfeedback/default.aspx
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.
=====================================================Please note that the newsgroups are staffed weekdays with a goal to provide
ONE BUSINESS DAY RESPONSE to all posts.
If this response time does not meet your needs, please contact CSS for more
immediate assistance:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone#faq607

matrix problem!

I created a matrix and everything looks good except that when I do a preview
the first page is ok but the second to the last pages my matrix shifts a bit
to the right. Anyone has an idea what's causing this? I can't seem to
reproduce this in my other report so I'm wondering what's up with this?
Thanks!Does this mean that no one has an answer to my question? I just want to know
if this is a bug or something I'm doing wrong?
"Laura" wrote:
> I created a matrix and everything looks good except that when I do a preview
> the first page is ok but the second to the last pages my matrix shifts a bit
> to the right. Anyone has an idea what's causing this? I can't seem to
> reproduce this in my other report so I'm wondering what's up with this?
> Thanks!

Matrix grouped by Day Part and Date

I am wondering has anybody ever created a Matrix in a report grouped by Day Part (10-2 Morn, 2-6 Aft, 6-10 Eve etc) and Date?

I would like to see a report with an output such as

13/02/2006 14/02/2006 Total
Morn Aft Eve Morn Aft Eve
Mr A 2 4 5 2 6 2 21
Miss B 8 8 1 1 4 5 27

I have a DB table which records the datetime each time a viewer changes TV channel. The report is a summary by user of each channel change By Day Part with Each Date

I can do this easily enough by just grouping on Date but now require another level of detail.Ok keep date as your first category, but group day part as a subcategory. You may need to represent the day part numerically, ie morn =1, aft = 2, eve = 3, then you will be able to srt in ascending order the dispalay morn aft eve. I hope this helps, i came across something similar the other day.|||I don't think you can have Subcategories in a Matrix, the only similar thing I've tried was to add a second grouping to the Column Grouping. The output of this does not sub group the data in Day parts it simply displays the Date with the first Day Part appended, however it doesn't sub group by Day Part

Matrix Duplicates

I have a matrix created that is displaying duplicate rows, the underlying
data is fine, not sure what's going on . It looks something like this.
Label Job#1 Job#2
Shoes 10 15
Shirts 50 45
Pants 25
Pants 40
I can't understand why "pants" is displaying twice. It seems to be
happening randomly in the matrix.Most likely, in one case the label field has a contents like "Pants", and in
the other case there is some whitespace at the end, such as "Pants ".
Try changing the grouping expression to e.g. =Trim(Fields!Label.Value)
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
news:0BA709D4-88AF-4126-943D-829CDD76B869@.microsoft.com...
>I have a matrix created that is displaying duplicate rows, the underlying
> data is fine, not sure what's going on . It looks something like this.
> Label Job#1 Job#2
> Shoes 10 15
> Shirts 50 45
> Pants 25
> Pants 40
> I can't understand why "pants" is displaying twice. It seems to be
> happening randomly in the matrix.|||I tried this and now I'm getting a blank label for the duplicate row, but
it's still there. I did a Len(Label) for these rows in SQL and they are the
exact same length. Quite strange.
Label Job#1 Job#2
Shoes 10 15
Shirts 50 45
Pants 25
40
"Robert Bruckner [MSFT]" wrote:
> Most likely, in one case the label field has a contents like "Pants", and in
> the other case there is some whitespace at the end, such as "Pants ".
> Try changing the grouping expression to e.g. =Trim(Fields!Label.Value)
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
> news:0BA709D4-88AF-4126-943D-829CDD76B869@.microsoft.com...
> >I have a matrix created that is displaying duplicate rows, the underlying
> > data is fine, not sure what's going on . It looks something like this.
> >
> > Label Job#1 Job#2
> > Shoes 10 15
> > Shirts 50 45
> > Pants 25
> > Pants 40
> >
> > I can't understand why "pants" is displaying twice. It seems to be
> > happening randomly in the matrix.
>
>|||I realized it's now blank becuase the 'hide duplicates' toggle was on for the
group in the matrix; however, it doesn't explain why it recognizes the row as
a duplicate, but doesn't just display the data together in one row.
"Robert Bruckner [MSFT]" wrote:
> Most likely, in one case the label field has a contents like "Pants", and in
> the other case there is some whitespace at the end, such as "Pants ".
> Try changing the grouping expression to e.g. =Trim(Fields!Label.Value)
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
> news:0BA709D4-88AF-4126-943D-829CDD76B869@.microsoft.com...
> >I have a matrix created that is displaying duplicate rows, the underlying
> > data is fine, not sure what's going on . It looks something like this.
> >
> > Label Job#1 Job#2
> > Shoes 10 15
> > Shirts 50 45
> > Pants 25
> > Pants 40
> >
> > I can't understand why "pants" is displaying twice. It seems to be
> > happening randomly in the matrix.
>
>|||There may be something else in the query or the report design that results
in that behavior. Can you post a small report (e.g. based on Northwind data)
that reproduces the issue you are experiencing?
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
news:46268052-7531-4F0C-A2BE-FE27AAA8E7A3@.microsoft.com...
>I realized it's now blank becuase the 'hide duplicates' toggle was on for
>the
> group in the matrix; however, it doesn't explain why it recognizes the row
> as
> a duplicate, but doesn't just display the data together in one row.
> "Robert Bruckner [MSFT]" wrote:
>> Most likely, in one case the label field has a contents like "Pants", and
>> in
>> the other case there is some whitespace at the end, such as "Pants ".
>> Try changing the grouping expression to e.g. =Trim(Fields!Label.Value)
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "FL Jim" <FLJim@.discussions.microsoft.com> wrote in message
>> news:0BA709D4-88AF-4126-943D-829CDD76B869@.microsoft.com...
>> >I have a matrix created that is displaying duplicate rows, the
>> >underlying
>> > data is fine, not sure what's going on . It looks something like
>> > this.
>> >
>> > Label Job#1 Job#2
>> > Shoes 10 15
>> > Shirts 50 45
>> > Pants 25
>> > Pants 40
>> >
>> > I can't understand why "pants" is displaying twice. It seems to be
>> > happening randomly in the matrix.
>>

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.

Materializing the cubes, absence of CREATE CUBE

Hi all,

(1) Does making a cube through Cube Wizard in Visual Studio imply materialiing the view
in a database?

I created a cube in AdventureWorksDW sample database. There is no error after I
deployed the project. Still the size of data files and transaction-log files of database
remain the same.

(2) Is the cube materialized? Can we materialize cubes?

(3) Shall Microsoft introduce operation CREATE CUBE in the MDX? I saw only
ALTER CUBE, CREATE SUBCUBE, and DROP SUBCUBE in online book.

Thank you,

Bernaridho

1) No

2) Yes, you can take the cubes offline

3) http://msdn2.microsoft.com/en-us/library/ms145581.aspx