Max in select clause

I have the following code......
SELECT
a..acct_nbr
,p.pric_uval_amt AS 'Unit Price'
,MAX(pp.pric_asof_dt) AS 'Unit_Price_Date'
FROM
account_t a
LEFT OUTER JOIN price_t p
ON a.ser_id = p.ser_id
GROUP BY
a.acct_nbr ,p.pric_uval_amt
Its purpose is to return an account number from one table, and from the
corresponding price table the latest unit price date and unit price.
Basically the price table
keeps a list of all products associated with an account. Each account can
have multiple products. The price table keeps regularly updated prices for
these products and whatever the date that theprice was updated. What I want
to do is to be able to get the latest (max) unit price date and the
corresponding unit price value.
The code above gives me the latest unit price date (if I take out the
,p.pric_uval_amt AS 'Unit Price' line). If I leave that line in I also get
the prices at all previous dates.
What I tend to get is this......
acct_nbr unit price
unit_price_date
----
0001 90
05/05/2005
0001 98
05/06/2005
0001 91
05/07/2005
0002 43
05/05/2005
0002 45
05/06/2005
When what I want is this.........
acct_nbr unit price
unit_price_date
----
0001 91
05/07/2005
0002 45
05/06/2005
Any idea's ?Hi
Is this the query that you are looking for:
SELECT account_t.acct_nbr, account_t.[unit price], derived.Unit_Price_Date
FROM account_t
INNER JOIN (
SELECT a.acct_nbr,MAX(pp.pric_asof_dt) AS 'Unit_Price_Date'
FROM account_t a
LEFT OUTER JOIN price_t p ON a.ser_id = p.ser_id
GROUP BY a.acct_nbr
) derived
ON derived.Unit_Price_Date = account_t.Unit_Price_Date AND
derived.acct_nbr = account_t.acct_nbr
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"quiglepops" wrote:

> I have the following code......
>
> SELECT
> a..acct_nbr
> ,p.pric_uval_amt AS 'Unit Price'
> ,MAX(pp.pric_asof_dt) AS 'Unit_Price_Date'
> FROM
> account_t a
> LEFT OUTER JOIN price_t p
> ON a.ser_id = p.ser_id
> GROUP BY
> a.acct_nbr ,p.pric_uval_amt
> Its purpose is to return an account number from one table, and from the
> corresponding price table the latest unit price date and unit price.
> Basically the price table
> keeps a list of all products associated with an account. Each account can
> have multiple products. The price table keeps regularly updated prices for
> these products and whatever the date that theprice was updated. What I wan
t
> to do is to be able to get the latest (max) unit price date and the
> corresponding unit price value.
> The code above gives me the latest unit price date (if I take out the
> ,p.pric_uval_amt AS 'Unit Price' line). If I leave that line in I also get
> the prices at all previous dates.
> What I tend to get is this......
> acct_nbr unit price
> unit_price_date
> ----
> 0001 90
> 05/05/2005
> 0001 98
> 05/06/2005
> 0001 91
> 05/07/2005
> 0002 43
> 05/05/2005
> 0002 45
> 05/06/2005
>
> When what I want is this.........
> acct_nbr unit price
> unit_price_date
> ----
> 0001 91
> 05/07/2005
> 0002 45
> 05/06/2005
>
> Any idea's ?
>
>|||Try,
Try,
SELECT
a..acct_nbr
,p.pric_uval_amt AS 'Unit Price'
,p.pric_asof_dt AS 'Unit_Price_Date'
FROM
account_t as a
LEFT OUTER JOIN
price_t as p
ON a.ser_id = p.ser_id
where
p.pric_asof_dt = (select max(p1.pric_asof_dt) from price_t as p1 where
p1.ser_id = a.ser_id)
AMB
"quiglepops" wrote:

> I have the following code......
>
> SELECT
> a..acct_nbr
> ,p.pric_uval_amt AS 'Unit Price'
> ,MAX(pp.pric_asof_dt) AS 'Unit_Price_Date'
> FROM
> account_t a
> LEFT OUTER JOIN price_t p
> ON a.ser_id = p.ser_id
> GROUP BY
> a.acct_nbr ,p.pric_uval_amt
> Its purpose is to return an account number from one table, and from the
> corresponding price table the latest unit price date and unit price.
> Basically the price table
> keeps a list of all products associated with an account. Each account can
> have multiple products. The price table keeps regularly updated prices for
> these products and whatever the date that theprice was updated. What I wan
t
> to do is to be able to get the latest (max) unit price date and the
> corresponding unit price value.
> The code above gives me the latest unit price date (if I take out the
> ,p.pric_uval_amt AS 'Unit Price' line). If I leave that line in I also get
> the prices at all previous dates.
> What I tend to get is this......
> acct_nbr unit price
> unit_price_date
> ----
> 0001 90
> 05/05/2005
> 0001 98
> 05/06/2005
> 0001 91
> 05/07/2005
> 0002 43
> 05/05/2005
> 0002 45
> 05/06/2005
>
> When what I want is this.........
> acct_nbr unit price
> unit_price_date
> ----
> 0001 91
> 05/07/2005
> 0002 45
> 05/06/2005
>
> Any idea's ?
>
>|||Thanks everyone for helping.
Alejandro, used your solution worked well. Thanks a lot.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:B65F1A34-7B16-46BB-B371-6143E989ACC5@.microsoft.com...
> Try,
> Try,
> SELECT
> a..acct_nbr
> ,p.pric_uval_amt AS 'Unit Price'
> ,p.pric_asof_dt AS 'Unit_Price_Date'
> FROM
> account_t as a
> LEFT OUTER JOIN
> price_t as p
> ON a.ser_id = p.ser_id
> where
> p.pric_asof_dt = (select max(p1.pric_asof_dt) from price_t as p1 where
> p1.ser_id = a.ser_id)
>
> AMB
>
> "quiglepops" wrote:
>
can
for
want
get

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

Matriz SQL

Hi, I have the following problem
In a DB exist this inf:
(I can use many tables... and/or many columns)
-->
1 2 3 4
5 6 7 8
9 a b c
d e f g
<--
In need write a select sentence that move in "circle" all info, for
example
5 1 2 3
9 a 6 4
d b 7 8
e f g cDoes this help:
http://spaces.msn.com/drsql/Blog/cns!80677FB08B3162E4!908.entry
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Alejandro" <jalejandro0211@.gmail.com> wrote in message
news:1148327031.695609.65500@.g10g2000cwb.googlegroups.com...
> Hi, I have the following problem
> In a DB exist this inf:
> (I can use many tables... and/or many columns)
> -->
> 1 2 3 4
> 5 6 7 8
> 9 a b c
> d e f g
> <--
> In need write a select sentence that move in "circle" all info, for
> example
> 5 1 2 3
> 9 a 6 4
> d b 7 8
> e f g c
>|||What is your table structure and how are you getting this output to begin
with?
Post DDL and an explanation of how the original data is generated/selected.
http://www.aspfaq.com/etiquette.asp?id=5006
Also, this sounds like a class assignment. If so, what sort of class is it?
"Alejandro" <jalejandro0211@.gmail.com> wrote in message
news:1148327031.695609.65500@.g10g2000cwb.googlegroups.com...
> Hi, I have the following problem
> In a DB exist this inf:
> (I can use many tables... and/or many columns)
> -->
> 1 2 3 4
> 5 6 7 8
> 9 a b c
> d e f g
> <--
> In need write a select sentence that move in "circle" all info, for
> example
> 5 1 2 3
> 9 a 6 4
> d b 7 8
> e f g c
>|||the DB has not been created by now, the table structure can be anyone,
4 tables, 1 table/ 1 columns, 1 table 16 columns, etc.
Not class assignment.|||Can you explain the situation/application for this logic? It will certainly
help in determining a valid approach. Also, I am very curious as to how
this might be useful in a real world situation.
Anyway, if your matrix will always be 4x4, you can try this table setup. It
involves two tables, one storing the data with its original location in the
matrix (as columns and rows) and a second storing the matrix shift (original
cell and new cell). We use a case statement with an agregate function to
produce the matrix, and we join to the matrix shift table to determine the
new location. To shift the cells more than once we could join to the matrix
shift table as many times as we need to rotate the matrix values. We can
permanently update the values at each position in the matrix if needed.
Lastly, we could change the Value stored in the Data table to a FK pointing
to another table with as many columns as we need. This would still really
only work a column at a time, but you could use any column you wanted in the
matrix.
On SQL 2005, you could probably use the pivot/unpivot functions to
accomplish this.
/*
Create our table/view structure which has data in rows and columns (matrix
format)
*/
CREATE TABLE #tmpData (ColNum integer, RowNum Integer, Value varchar(10)
primary key(ColNum,RowNum))
go
INSERT #tmpData(ColNum,RowNum,Value) VALUES (1,1,'A');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (2,1,'B');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (3,1,'C');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (4,1,'D');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (1,2,'E');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (2,2,'F');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (3,2,'G');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (4,2,'H');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (1,3,'I');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (2,3,'J');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (3,3,'K');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (4,3,'L');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (1,4,'M');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (2,4,'N');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (3,4,'O');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (4,4,'P');
go
/*
Display data in the matrix format
*/
select
max(case when ColNum=1 then Value end) as Col1
,max(case when ColNum=2 then Value end) as Col2
,max(case when ColNum=3 then Value end) as Col3
,max(case when ColNum=4 then Value end) as Col4
from #tmpData
group by RowNum;
/*
Create our matrix mapping, showing how the matrix cells will move
*/
Create table #tmpMatrixShift
(ColNum1 integer,RowNum1 integer,ColNum2 integer,RowNum2 integer);
go
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(1,1,2,1);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(2,1,3,1);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(3,1,4,1);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(4,1,4,2);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(4,2,4,3);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(4,3,4,4);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(4,4,3,4);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(3,4,2,4);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(2,4,1,4);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(1,4,1,3);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(1,3,1,2);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(1,2,1,1);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(2,2,3,2);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(3,2,3,3);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(2,3,2,2);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(3,3,2,3);
go
/*
Join our data table to the matrix mapping table in order to get the new cell
locations
Display data in the matrix format
*/
select
max(case when b.ColNum2=1 then a.Value end) as Col1
,max(case when b.ColNum2=2 then a.Value end) as Col2
,max(case when b.ColNum2=3 then a.Value end) as Col3
,max(case when b.ColNum2=4 then a.Value end) as Col4
from #tmpData a
inner join #tmpMatrixShift b
on a.colnum = b.colnum1
and a.rownum = b.rownum1
group by b.RowNum2;
DROP TABLE #tmpData;
DROP TABLE #tmpMatrixShift;
"Alejandro" <jalejandro0211@.gmail.com> wrote in message
news:1148328455.125472.120050@.j55g2000cwa.googlegroups.com...
> the DB has not been created by now, the table structure can be anyone,
> 4 tables, 1 table/ 1 columns, 1 table 16 columns, etc.
> Not class assignment.
>|||I have a program in the office (very very old) don't have objects of
this program, but this take a sentence select from .ini to execute,
this program transform a values returned (contability) and calculate
some, I can modify that select to --select transformed tables...
=BF?...
Well I do this
CREATE TABLE "MATRIX" ( COL1 VARCHAR(10) NOT NULL ,
COL2 VARCHAR(10) NOT NULL ,
COL3 VARCHAR(10) NOT NULL ,
COL4 VARCHAR(10) NOT NULL );
insert into matrix values ('1','2','3','4');
insert into matrix values ('5','6','7','8');
insert into matrix values ('9','10','11','12');
insert into matrix values ('13','14','15','16');
now?|||Have you tried the approach I posted? As long as you are fixed at 4 columns
and 4 rows in the matrix, I believe it should do what you want. However,
some of the more math-intensive folks may be able to come up with an
algorithm that is more effective.
"Alejandro" <jalejandro0211@.gmail.com> wrote in message
news:1148333584.761203.102270@.j73g2000cwa.googlegroups.com...
I have a program in the office (very very old) don't have objects of
this program, but this take a sentence select from .ini to execute,
this program transform a values returned (contability) and calculate
some, I can modify that select to --select transformed tables...
?...
Well I do this
CREATE TABLE "MATRIX" ( COL1 VARCHAR(10) NOT NULL ,
COL2 VARCHAR(10) NOT NULL ,
COL3 VARCHAR(10) NOT NULL ,
COL4 VARCHAR(10) NOT NULL );
insert into matrix values ('1','2','3','4');
insert into matrix values ('5','6','7','8');
insert into matrix values ('9','10','11','12');
insert into matrix values ('13','14','15','16');
now?|||The link I posted shows a pretty interesting method as well... But it looks
like the OP just wants the answer handed to him without investing any of his
own thought.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%232JeHYpfGHA.4864@.TK2MSFTNGP05.phx.gbl...
> Have you tried the approach I posted? As long as you are fixed at 4
> columns
> and 4 rows in the matrix, I believe it should do what you want. However,
> some of the more math-intensive folks may be able to come up with an
> algorithm that is more effective.
> "Alejandro" <jalejandro0211@.gmail.com> wrote in message
> news:1148333584.761203.102270@.j73g2000cwa.googlegroups.com...
> I have a program in the office (very very old) don't have objects of
> this program, but this take a sentence select from .ini to execute,
> this program transform a values returned (contability) and calculate
> some, I can modify that select to --select transformed tables...
> ?...
> Well I do this
> CREATE TABLE "MATRIX" ( COL1 VARCHAR(10) NOT NULL ,
> COL2 VARCHAR(10) NOT NULL ,
> COL3 VARCHAR(10) NOT NULL ,
> COL4 VARCHAR(10) NOT NULL );
> insert into matrix values ('1','2','3','4');
> insert into matrix values ('5','6','7','8');
> insert into matrix values ('9','10','11','12');
> insert into matrix values ('13','14','15','16');
> now?
>|||Your link looked more like a cross tab solution, flipping the columns and
rows. I tried to apply it to this situation, but couldn't think of how to
do it, since the cells are being rotated rather than flipped. I am still
trying to think of a valid application for this sort of thing...
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23$qSMGqfGHA.1456@.TK2MSFTNGP04.phx.gbl...
> The link I posted shows a pretty interesting method as well... But it
looks
> like the OP just wants the answer handed to him without investing any of
his
> own thought.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:%232JeHYpfGHA.4864@.TK2MSFTNGP05.phx.gbl...
However,
>

Matrix with totals at the top and a empty row

Hi,
I would like to have the following,
field 1 field 2 field 3
---
empty row
total 1000 1000 1000
empty row
field 4 2000 2000 2000
field 5 8000 8000 8000
Any advice on how to do that?Set Subtotal position to "Before" and use top and bottom padding to add
space around subtotal
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Reg" <reg@.dsl.za.org> wrote in message
news:eB47v2amEHA.2504@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I would like to have the following,
> field 1 field 2 field 3
> ---
> empty row
> total 1000 1000 1000
> empty row
> field 4 2000 2000 2000
> field 5 8000 8000 8000
> Any advice on how to do that?
>|||Thanks this works.
Now just one more problem. If we use the example below, on the column
grouping(Contains Months) have an iif to check if the date is before a
parameter passed to the report. I then use either one or the other field
from the database depending on whether it is true or false.
When I then add the total it would give me the wrong value, not really sure
which value it is giving me, but it always one value from the column and not
a calculated value.
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:ev416MhmEHA.2504@.TK2MSFTNGP14.phx.gbl...
> Set Subtotal position to "Before" and use top and bottom padding to add
> space around subtotal
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Reg" <reg@.dsl.za.org> wrote in message
> news:eB47v2amEHA.2504@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > I would like to have the following,
> >
> > field 1 field 2 field 3
> > ---
> > empty row
> > total 1000 1000 1000
> > empty row
> > field 4 2000 2000 2000
> > field 5 8000 8000 8000
> >
> > Any advice on how to do that?
> >
> >
>|||Would you like to create simple report that exhibits this problem and e-mail
it to me?
Thanks,
Lev
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Reg" <reg@.dsl.za.org> wrote in message
news:%23NDgWQimEHA.3876@.TK2MSFTNGP15.phx.gbl...
> Thanks this works.
> Now just one more problem. If we use the example below, on the column
> grouping(Contains Months) have an iif to check if the date is before a
> parameter passed to the report. I then use either one or the other field
> from the database depending on whether it is true or false.
> When I then add the total it would give me the wrong value, not really
> sure
> which value it is giving me, but it always one value from the column and
> not
> a calculated value.
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:ev416MhmEHA.2504@.TK2MSFTNGP14.phx.gbl...
>> Set Subtotal position to "Before" and use top and bottom padding to add
>> space around subtotal
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "Reg" <reg@.dsl.za.org> wrote in message
>> news:eB47v2amEHA.2504@.TK2MSFTNGP14.phx.gbl...
>> > Hi,
>> >
>> > I would like to have the following,
>> >
>> > field 1 field 2 field 3
>> > ---
>> > empty row
>> > total 1000 1000 1000
>> > empty row
>> > field 4 2000 2000 2000
>> > field 5 8000 8000 8000
>> >
>> > Any advice on how to do that?
>> >
>> >
>>
>

Matrix title

I have the following report using RS2000

(1) In my page header I have a textbox(title of the report).

(2) In my report body, I have a matrix report that can extend horizontally.

My issue is that I don't know how to make sure that my page header title (1) stretches as far as the last matrix row (2).

Any help? Has anyone ever come across this issue and is there a solution to it?

Thanks,

YADGOR2000

Two options:

1. You can add an outmost status column (or a dynamic column with a constant group expression) in the matrix. Put the title in the column header and set the TextAlign to center.

2. Add a table with two header rows and one column, and drop the matrix inside the second row of the table. Put the title in the first table row, and set the TextAlign to center.

|||

I am not sure I can understand your response. Can you give me step by step break down for either option 1 or 2.

Thank you

yadgor2000

Matrix title

I have the following report using RS2000

(1) In my page header I have a textbox(title of the report).

(2) In my report body, I have a matrix report that can extend horizontally.

My issue is that I don't know how to make sure that my page header title (1) stretches as far as the last matrix row (2).

Any help? Has anyone ever come across this issue and is there a solution to it?

Thanks,

YADGOR2000

Two options:

1. You can add an outmost status column (or a dynamic column with a constant group expression) in the matrix. Put the title in the column header and set the TextAlign to center.

2. Add a table with two header rows and one column, and drop the matrix inside the second row of the table. Put the title in the first table row, and set the TextAlign to center.

|||

I am not sure I can understand your response. Can you give me step by step break down for either option 1 or 2.

Thank you

yadgor2000

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 label issue (SSRS 2005)

Hello,

We have a matrix that includes two row groups with subtotals for each group, like the following:

<table width="80%">

<tr><td align="center">Unit</td><td align="center">Room</td><td align="center">Data</td></tr>

<tr><td>Unit 1</td><td>Rm 34A</td><td>6</td></tr>

<tr><td>&nbsp;</td><td>&nbsp;</td><td>4</td></tr>

<tr><td>&nbsp;</td><td>Rm 34A Total</td><td>10</td></tr>

<tr><td>&nbsp;</td><td>Rm 50</td><td>7</td></tr>

<tr><td>&nbsp;</td><td>Rm 34A Total</td><td>7</td></tr>

<tr><td>Unit 1 Total</td><td>&nbsp;</td><td>17</td></tr>

</table>

The issue is that the second group's subtotal label doesn't display the correct name, as in the example. It's always "Rm 34A" or whichever room is returned first in the dataset, even if the subtotal is actually for Rm 35. The labels for the first group display correctly, and the issue is only a label problem - the subtotal data seems to be fine. For the subtotal label, we have =Fields!Subgroup.Value + " Total" as the expression. Anybody have any suggestions? Thanks,

RLG

RLGow wrote:

Hello,

We have a matrix that includes two row groups with subtotals for each group, like the following:

<table width="80%">

<tr><td align="center">Unit</td><td align="center">Room</td><td align="center">Data</td></tr>

<tr><td>Unit 1</td><td>Rm 34A</td><td>6</td></tr>

<tr><td>&nbsp;</td><td>&nbsp;</td><td>4</td></tr>

<tr><td>&nbsp;</td><td>Rm 34A Total</td><td>10</td></tr>

<tr><td>&nbsp;</td><td>Rm 50</td><td>7</td></tr>

<tr><td>&nbsp;</td><td>Rm 34A Total</td><td>7</td></tr>

<tr><td>Unit 1 Total</td><td>&nbsp;</td><td>17</td></tr>

</table>

The issue is that the second group's subtotal label doesn't display the correct name, as in the example. It's always "Rm 34A" or whichever room is returned first in the dataset, even if the subtotal is actually for Rm 35. The labels for the first group display correctly, and the issue is only a label problem - the subtotal data seems to be fine. For the subtotal label, we have =Fields!Subgroup.Value + " Total" as the expression. Anybody have any suggestions? Thanks,

RLG

Try

=Fields!Subgroup.Value & " Total"

|||

Hi,

I also have the same problem.

However, I have discovered that this behaviour appears then you have column-subtotals and row-subtotal.

If I remove the subtotal for rows this problem seems to disappear.

Instead I get another problems with empty labels in my subtotal text-fields. There are no NULL or empty strings in used columns in my recordset!

If you have any kind of solution or work around please let me know.

Regards, Jonas

Matrix Report Columns Grand Total

Hi There i have a Report That Display's data in following format

Areas

Code | Descirption | Emirates | GCC | ASIAN | OTHER ARABS | so on..
1 What ever 3 0 2 1
2 ABC 0 0 1 0
-
Total 3 0 3 1

The Areas are Matric Columns and are dynamically generated .

i dont want sub total of each row , but i want Grand total at the end of the all columns generated by matrix

I have no clue how do to it

Kind Regards

Just Right-click the column header and choose subtotal.

|||No this is what i dont want

this will add total below each row which i dont want i want the grand totals after the last row|||

OK, we seem to be talking cross purposes and your explanation is not clear enough.

If you want an additional column at the end the has the total value for all areas then right-click the column group and choose subtotal.

If you want an additional row at the very bottom that has the total for all the code/description row category, then right-click on the code group i.e. outermost group, and choose subtotal.

|||
let me explain again here is the report's result few row

R






African American Asian Emirates European GCC Not Stated Oceanian(Australia-Newzealand-Fiji Islands) Other Arab Countries TOTAL 1120 Services activities related to extraction of oil and gas, except surveying services ????? ??????? ??????? ???????? ????? ?????? ???????? ????? ????? 20610000 20610000 20610000 1320 Mining of non-ferrous metal ores, except uranium and thorium ores ????? ?????? (?????) ??????? ??? ???????? (???????) (???????? ?????? ?????????? ?????????) 150000 150000 150000 1410 Quarrying for extraction of stones, sands, and clay ??????? ??????? ???????? ??????? ??????? ?????? 0 0

Total Row 0 African Colum total 0 15000 ( asian) rest of totals

the regions name that you see are actually column of matrix report and i want total in last row for each column
i have added total as column but i also want grand total row at the end

means horizontal total of each row
and vertical total of each column

i hope i got it right this time

|||

Fine. Then doing what I said in my previous reply should work for you. Have you tried it?

Just to be totally explicit, right-click the textbox in your matrix that has the "code" and not the one that has the "description" and select the subtotal option.

|||worked thanks a lot

Matrix Report Background Color

I have the following applied as an expression to the background color
on a cell
=IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow",
"White")
I only want the ones above 10% to be yellow, otherwise white.
Any ideas anyone on why the entire range of cells are yellow and not
just the one above 10%?Is percentile an integer or a float. If float test is >= .10
"duhaas@.gmail.com" wrote:
> I have the following applied as an expression to the background color
> on a cell
> =IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow",
> "White")
> I only want the ones above 10% to be yellow, otherwise white.
> Any ideas anyone on why the entire range of cells are yellow and not
> just the one above 10%?
>|||here is the query being called:
SELECT s.babr, s.SicDescription, SUM(s.AccountMV) AS mv,
SUM(s.AccountMV) / q.ps AS Percentile
FROM SnapsRaw s INNER JOIN
(SELECT babr, SUM(accountmv) ps
FROM snapsraw
WHERE monthend = '08/01/2006'
GROUP BY babr) q ON q.babr = s.babr
WHERE s.MonthEnd = '08/01/2006'
GROUP BY s.Babr, s.SicDescription, q.ps, q.babr
percentile is the number being displayed, changing it to .10 didnt seem
to help
On Nov 10, 4:12 pm, William <Will...@.discussions.microsoft.com> wrote:
> Is percentile an integer or a float. If float test is >= .10
> "duh...@.gmail.com" wrote:
> > I have the following applied as an expression to the background color
> > on a cell
> > =IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow",
> > "White")
> > I only want the ones above 10% to be yellow, otherwise white.
> > Any ideas anyone on why the entire range of cells are yellow and not
> > just the one above 10%?|||Unless AccountMV & q.ps are both integers, Percentile is a decimal number so
your test should use the decimal as in:
IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= .10, "Yellow", "White")
"duhaas@.gmail.com" wrote:
> here is the query being called:
> SELECT s.babr, s.SicDescription, SUM(s.AccountMV) AS mv,
> SUM(s.AccountMV) / q.ps AS Percentile
> FROM SnapsRaw s INNER JOIN
> (SELECT babr, SUM(accountmv) ps
> FROM snapsraw
> WHERE monthend = '08/01/2006'
> GROUP BY babr) q ON q.babr = s.babr
> WHERE s.MonthEnd = '08/01/2006'
> GROUP BY s.Babr, s.SicDescription, q.ps, q.babr
> percentile is the number being displayed, changing it to .10 didnt seem
> to help
>
> On Nov 10, 4:12 pm, William <Will...@.discussions.microsoft.com> wrote:
> > Is percentile an integer or a float. If float test is >= .10
> >
> > "duh...@.gmail.com" wrote:
> > > I have the following applied as an expression to the background color
> > > on a cell
> >
> > > =IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow",
> > > "White")
> >
> > > I only want the ones above 10% to be yellow, otherwise white.
> >
> > > Any ideas anyone on why the entire range of cells are yellow and not
> > > just the one above 10%?
>|||William
Appreciate all the help, I have made the change, and its still
highlighting the entire range of cells instead of just those above 10%,
here is the code for the report, its textbox2 that im playing with:
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<Body>
<ReportItems>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>7pt</FontSize>
<TextAlign>Left</TextAlign>
<Color>Blue</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>SIC ID/ Industry</Value>
</Textbox>
</ReportItems>
</Corner>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>1pt</PaddingLeft>
<Format>g</Format>
<BackgroundColor>=IIF(Sum(Fields!Percentile.Value, "RegionalSnaps")
>= .10, "Yellow", "White")</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>7pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>1pt</PaddingBottom>
<PaddingTop>1pt</PaddingTop>
<PaddingRight>1pt</PaddingRight>
</Style>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=sum(Fields!Percentile.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.15in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>0.625in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>RegionalSnaps</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_babr">
<GroupExpressions>
<GroupExpression>=Fields!babr.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!babr.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<ReportItems>
<Textbox Name="babr">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>7pt</FontSize>
<TextAlign>Center</TextAlign>
<Color>Blue</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>babr</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!babr.Value</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.15in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_SicDescription">
<GroupExpressions>
<GroupExpression>=Fields!SicDescription.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!SicDescription.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<ReportItems>
<Textbox Name="SicDescription">
<Style>
<PaddingLeft>1pt</PaddingLeft>
<BorderWidth>
<Right>1pt</Right>
</BorderWidth>
<BorderColor>
<Right>Black</Right>
</BorderColor>
<BorderStyle>
<Right>Solid</Right>
</BorderStyle>
<FontSize>7pt</FontSize>
<TextAlign>Left</TextAlign>
<PaddingBottom>1pt</PaddingBottom>
<PaddingTop>1pt</PaddingTop>
<PaddingRight>1pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>SicDescription</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!SicDescription.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1.75in</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
</ReportItems>
<Style />
<Height>0.3in</Height>
</Body>
<DataSources>
<DataSource Name="RegionalSnaps">
<rd:DataSourceID>74b2d458-9f2d-4e29-8375-2ed584778c1c</rd:DataSourceID>
<DataSourceReference>RegionalSnaps</DataSourceReference>
</DataSource>
</DataSources>
<Code />
<Width>2.5in</Width>
<DataSets>
<DataSet Name="RegionalSnaps">
<Fields>
<Field Name="babr">
<DataField>babr</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="SicDescription">
<DataField>SicDescription</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="mv">
<DataField>mv</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Percentile">
<DataField>Percentile</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>RegionalSnaps</DataSourceName>
<CommandText>SELECT s.babr, s.SicDescription,
SUM(s.AccountMV) AS mv, SUM(s.AccountMV) / q.ps AS Percentile
FROM SnapsRaw s INNER JOIN
(SELECT babr, SUM(accountmv) ps
FROM snapsraw
WHERE monthend = '08/01/2006'
GROUP BY babr) q ON q.babr = s.babr
WHERE s.MonthEnd = '08/01/2006'
GROUP BY s.Babr, s.SicDescription, q.ps, q.babr</CommandText>
</Query>
</DataSet>
</DataSets>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>f4c1c559-ee3a-43cb-9c9d-27f23c7b8cc8</rd:ReportID>
<Language>en-US</Language>
</Report>
On Nov 15, 8:35 am, William <Will...@.discussions.microsoft.com> wrote:
> Unless AccountMV & q.ps are both integers, Percentile is a decimal number so
> your test should use the decimal as in:
> IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= .10, "Yellow", "White")"duh...@.gmail.com" wrote:
> > here is the query being called:
> > SELECT s.babr, s.SicDescription, SUM(s.AccountMV) AS mv,
> > SUM(s.AccountMV) / q.ps AS Percentile
> > FROM SnapsRaw s INNER JOIN
> > (SELECT babr, SUM(accountmv) ps
> > FROM snapsraw
> > WHERE monthend = '08/01/2006'
> > GROUP BY babr) q ON q.babr = s.babr
> > WHERE s.MonthEnd = '08/01/2006'
> > GROUP BY s.Babr, s.SicDescription, q.ps, q.babr
> > percentile is the number being displayed, changing it to .10 didnt seem
> > to help
> > On Nov 10, 4:12 pm, William <Will...@.discussions.microsoft.com> wrote:
> > > Is percentile an integer or a float. If float test is >= .10
> > > "duh...@.gmail.com" wrote:
> > > > I have the following applied as an expression to the background color
> > > > on a cell
> > > > =IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow",
> > > > "White")
> > > > I only want the ones above 10% to be yellow, otherwise white.
> > > > Any ideas anyone on why the entire range of cells are yellow and not
> > > > just the one above 10%?|||I took your IIF statement and applied it to a cell in a report I have and a
value of .111 came back with yellow background. So I would go back to the
format of the value coming in and verify that you have a number with decimals
coming in. Also verify the scope of the IIF test if you have multiple data
sources with the same field names.
"duhaas@.gmail.com" wrote:
> William
> Appreciate all the help, I have made the change, and its still
> highlighting the entire range of cells instead of just those above 10%,
> here is the code for the report, its textbox2 that im playing with:
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <Body>
> <ReportItems>
> <Matrix Name="matrix1">
> <Corner>
> <ReportItems>
> <Textbox Name="textbox3">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextDecoration>Underline</TextDecoration>
> <FontSize>7pt</FontSize>
> <TextAlign>Left</TextAlign>
> <Color>Blue</Color>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>textbox3</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>SIC ID/ Industry</Value>
> </Textbox>
> </ReportItems>
> </Corner>
> <Style />
> <MatrixRows>
> <MatrixRow>
> <MatrixCells>
> <MatrixCell>
> <ReportItems>
> <Textbox Name="textbox2">
> <Style>
> <PaddingLeft>1pt</PaddingLeft>
> <Format>g</Format>
> <BackgroundColor>=IIF(Sum(Fields!Percentile.Value, "RegionalSnaps")
> >= .10, "Yellow", "White")</BackgroundColor>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <FontSize>7pt</FontSize>
> <TextAlign>Center</TextAlign>
> <PaddingBottom>1pt</PaddingBottom>
> <PaddingTop>1pt</PaddingTop>
> <PaddingRight>1pt</PaddingRight>
> </Style>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=sum(Fields!Percentile.Value)</Value>
> </Textbox>
> </ReportItems>
> </MatrixCell>
> </MatrixCells>
> <Height>0.15in</Height>
> </MatrixRow>
> </MatrixRows>
> <MatrixColumns>
> <MatrixColumn>
> <Width>0.625in</Width>
> </MatrixColumn>
> </MatrixColumns>
> <DataSetName>RegionalSnaps</DataSetName>
> <ColumnGroupings>
> <ColumnGrouping>
> <DynamicColumns>
> <Grouping Name="matrix1_babr">
> <GroupExpressions>
> <GroupExpression>=Fields!babr.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields!babr.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <ReportItems>
> <Textbox Name="babr">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BorderWidth>
> <Bottom>1pt</Bottom>
> </BorderWidth>
> <BorderColor>
> <Bottom>Black</Bottom>
> </BorderColor>
> <BorderStyle>
> <Bottom>Solid</Bottom>
> </BorderStyle>
> <FontSize>7pt</FontSize>
> <TextAlign>Center</TextAlign>
> <Color>Blue</Color>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>babr</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!babr.Value</Value>
> </Textbox>
> </ReportItems>
> </DynamicColumns>
> <Height>0.15in</Height>
> </ColumnGrouping>
> </ColumnGroupings>
> <Left>0.125in</Left>
> <RowGroupings>
> <RowGrouping>
> <DynamicRows>
> <Grouping Name="matrix1_SicDescription">
> <GroupExpressions>
> <GroupExpression>=Fields!SicDescription.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields!SicDescription.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <ReportItems>
> <Textbox Name="SicDescription">
> <Style>
> <PaddingLeft>1pt</PaddingLeft>
> <BorderWidth>
> <Right>1pt</Right>
> </BorderWidth>
> <BorderColor>
> <Right>Black</Right>
> </BorderColor>
> <BorderStyle>
> <Right>Solid</Right>
> </BorderStyle>
> <FontSize>7pt</FontSize>
> <TextAlign>Left</TextAlign>
> <PaddingBottom>1pt</PaddingBottom>
> <PaddingTop>1pt</PaddingTop>
> <PaddingRight>1pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>SicDescription</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!SicDescription.Value</Value>
> </Textbox>
> </ReportItems>
> </DynamicRows>
> <Width>1.75in</Width>
> </RowGrouping>
> </RowGroupings>
> </Matrix>
> </ReportItems>
> <Style />
> <Height>0.3in</Height>
> </Body>
> <DataSources>
> <DataSource Name="RegionalSnaps">
> <rd:DataSourceID>74b2d458-9f2d-4e29-8375-2ed584778c1c</rd:DataSourceID>
> <DataSourceReference>RegionalSnaps</DataSourceReference>
> </DataSource>
> </DataSources>
> <Code />
> <Width>2.5in</Width>
> <DataSets>
> <DataSet Name="RegionalSnaps">
> <Fields>
> <Field Name="babr">
> <DataField>babr</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="SicDescription">
> <DataField>SicDescription</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="mv">
> <DataField>mv</DataField>
> <rd:TypeName>System.Decimal</rd:TypeName>
> </Field>
> <Field Name="Percentile">
> <DataField>Percentile</DataField>
> <rd:TypeName>System.Decimal</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>RegionalSnaps</DataSourceName>
> <CommandText>SELECT s.babr, s.SicDescription,
> SUM(s.AccountMV) AS mv, SUM(s.AccountMV) / q.ps AS Percentile
> FROM SnapsRaw s INNER JOIN
> (SELECT babr, SUM(accountmv) ps
> FROM snapsraw
> WHERE monthend = '08/01/2006'
> GROUP BY babr) q ON q.babr = s.babr
> WHERE s.MonthEnd = '08/01/2006'
> GROUP BY s.Babr, s.SicDescription, q.ps, q.babr</CommandText>
> </Query>
> </DataSet>
> </DataSets>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>f4c1c559-ee3a-43cb-9c9d-27f23c7b8cc8</rd:ReportID>
> <Language>en-US</Language>
> </Report>
> On Nov 15, 8:35 am, William <Will...@.discussions.microsoft.com> wrote:
> > Unless AccountMV & q.ps are both integers, Percentile is a decimal number so
> > your test should use the decimal as in:
> >
> > IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= .10, "Yellow", "White")"duh...@.gmail.com" wrote:
> > > here is the query being called:
> >
> > > SELECT s.babr, s.SicDescription, SUM(s.AccountMV) AS mv,
> > > SUM(s.AccountMV) / q.ps AS Percentile
> > > FROM SnapsRaw s INNER JOIN
> > > (SELECT babr, SUM(accountmv) ps
> > > FROM snapsraw
> > > WHERE monthend = '08/01/2006'
> > > GROUP BY babr) q ON q.babr = s.babr
> > > WHERE s.MonthEnd = '08/01/2006'
> > > GROUP BY s.Babr, s.SicDescription, q.ps, q.babr
> >
> > > percentile is the number being displayed, changing it to .10 didnt seem
> > > to help
> >
> > > On Nov 10, 4:12 pm, William <Will...@.discussions.microsoft.com> wrote:
> > > > Is percentile an integer or a float. If float test is >= .10
> >
> > > > "duh...@.gmail.com" wrote:
> > > > > I have the following applied as an expression to the background color
> > > > > on a cell
> >
> > > > > =IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow",
> > > > > "White")
> >
> > > > > I only want the ones above 10% to be yellow, otherwise white.
> >
> > > > > Any ideas anyone on why the entire range of cells are yellow and not
> > > > > just the one above 10%?
>|||Thanks again for your feedback, in running the query standalone the
results appear like this:
STATE GROUP
MV PERCENT
KC Engineering & Management Services 336044936.72 0.0556
STL Wholesale Trade-Nondurable Goods 221851565.42 0.1078
The accountmv field is a datatype money, just dont understand what the
deal is.
On Nov 15, 9:15 am, William <Will...@.discussions.microsoft.com> wrote:
> I took your IIF statement and applied it to a cell in a report I have and a
> value of .111 came back with yellow background. So I would go back to the
> format of the value coming in and verify that you have a number with decimals
> coming in. Also verify the scope of the IIF test if you have multiple data
> sources with the same field names."duh...@.gmail.com" wrote:
> > William
> > Appreciate all the help, I have made the change, and its still
> > highlighting the entire range of cells instead of just those above 10%,
> > here is the code for the report, its textbox2 that im playing with:
> > <?xml version="1.0" encoding="utf-8"?>
> > <Report
> > xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini..."
> > xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > <Body>
> > <ReportItems>
> > <Matrix Name="matrix1">
> > <Corner>
> > <ReportItems>
> > <Textbox Name="textbox3">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <TextDecoration>Underline</TextDecoration>
> > <FontSize>7pt</FontSize>
> > <TextAlign>Left</TextAlign>
> > <Color>Blue</Color>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>3</ZIndex>
> > <rd:DefaultName>textbox3</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>SIC ID/ Industry</Value>
> > </Textbox>
> > </ReportItems>
> > </Corner>
> > <Style />
> > <MatrixRows>
> > <MatrixRow>
> > <MatrixCells>
> > <MatrixCell>
> > <ReportItems>
> > <Textbox Name="textbox2">
> > <Style>
> > <PaddingLeft>1pt</PaddingLeft>
> > <Format>g</Format>
> > <BackgroundColor>=IIF(Sum(Fields!Percentile.Value, "RegionalSnaps")
> > >= .10, "Yellow", "White")</BackgroundColor>
> > <BorderStyle>
> > <Default>Solid</Default>
> > </BorderStyle>
> > <FontSize>7pt</FontSize>
> > <TextAlign>Center</TextAlign>
> > <PaddingBottom>1pt</PaddingBottom>
> > <PaddingTop>1pt</PaddingTop>
> > <PaddingRight>1pt</PaddingRight>
> > </Style>
> > <rd:DefaultName>textbox2</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=sum(Fields!Percentile.Value)</Value>
> > </Textbox>
> > </ReportItems>
> > </MatrixCell>
> > </MatrixCells>
> > <Height>0.15in</Height>
> > </MatrixRow>
> > </MatrixRows>
> > <MatrixColumns>
> > <MatrixColumn>
> > <Width>0.625in</Width>
> > </MatrixColumn>
> > </MatrixColumns>
> > <DataSetName>RegionalSnaps</DataSetName>
> > <ColumnGroupings>
> > <ColumnGrouping>
> > <DynamicColumns>
> > <Grouping Name="matrix1_babr">
> > <GroupExpressions>
> > <GroupExpression>=Fields!babr.Value</GroupExpression>
> > </GroupExpressions>
> > </Grouping>
> > <Sorting>
> > <SortBy>
> > <SortExpression>=Fields!babr.Value</SortExpression>
> > <Direction>Ascending</Direction>
> > </SortBy>
> > </Sorting>
> > <ReportItems>
> > <Textbox Name="babr">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BorderWidth>
> > <Bottom>1pt</Bottom>
> > </BorderWidth>
> > <BorderColor>
> > <Bottom>Black</Bottom>
> > </BorderColor>
> > <BorderStyle>
> > <Bottom>Solid</Bottom>
> > </BorderStyle>
> > <FontSize>7pt</FontSize>
> > <TextAlign>Center</TextAlign>
> > <Color>Blue</Color>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>2</ZIndex>
> > <rd:DefaultName>babr</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!babr.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </DynamicColumns>
> > <Height>0.15in</Height>
> > </ColumnGrouping>
> > </ColumnGroupings>
> > <Left>0.125in</Left>
> > <RowGroupings>
> > <RowGrouping>
> > <DynamicRows>
> > <Grouping Name="matrix1_SicDescription">
> > <GroupExpressions>
> > <GroupExpression>=Fields!SicDescription.Value</GroupExpression>
> > </GroupExpressions>
> > </Grouping>
> > <Sorting>
> > <SortBy>
> > <SortExpression>=Fields!SicDescription.Value</SortExpression>
> > <Direction>Ascending</Direction>
> > </SortBy>
> > </Sorting>
> > <ReportItems>
> > <Textbox Name="SicDescription">
> > <Style>
> > <PaddingLeft>1pt</PaddingLeft>
> > <BorderWidth>
> > <Right>1pt</Right>
> > </BorderWidth>
> > <BorderColor>
> > <Right>Black</Right>
> > </BorderColor>
> > <BorderStyle>
> > <Right>Solid</Right>
> > </BorderStyle>
> > <FontSize>7pt</FontSize>
> > <TextAlign>Left</TextAlign>
> > <PaddingBottom>1pt</PaddingBottom>
> > <PaddingTop>1pt</PaddingTop>
> > <PaddingRight>1pt</PaddingRight>
> > </Style>
> > <ZIndex>1</ZIndex>
> > <rd:DefaultName>SicDescription</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!SicDescription.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </DynamicRows>
> > <Width>1.75in</Width>
> > </RowGrouping>
> > </RowGroupings>
> > </Matrix>
> > </ReportItems>
> > <Style />
> > <Height>0.3in</Height>
> > </Body>
> > <DataSources>
> > <DataSource Name="RegionalSnaps">
> > <rd:DataSourceID>74b2d458-9f2d-4e29-8375-2ed584778c1c</rd:DataSourceID>
> > <DataSourceReference>RegionalSnaps</DataSourceReference>
> > </DataSource>
> > </DataSources>
> > <Code />
> > <Width>2.5in</Width>
> > <DataSets>
> > <DataSet Name="RegionalSnaps">
> > <Fields>
> > <Field Name="babr">
> > <DataField>babr</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="SicDescription">
> > <DataField>SicDescription</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="mv">
> > <DataField>mv</DataField>
> > <rd:TypeName>System.Decimal</rd:TypeName>
> > </Field>
> > <Field Name="Percentile">
> > <DataField>Percentile</DataField>
> > <rd:TypeName>System.Decimal</rd:TypeName>
> > </Field>
> > </Fields>
> > <Query>
> > <DataSourceName>RegionalSnaps</DataSourceName>
> > <CommandText>SELECT s.babr, s.SicDescription,
> > SUM(s.AccountMV) AS mv, SUM(s.AccountMV) / q.ps AS Percentile
> > FROM SnapsRaw s INNER JOIN
> > (SELECT babr, SUM(accountmv) ps
> > FROM snapsraw
> > WHERE monthend = '08/01/2006'
> > GROUP BY babr) q ON q.babr = s.babr
> > WHERE s.MonthEnd = '08/01/2006'
> > GROUP BY s.Babr, s.SicDescription, q.ps, q.babr</CommandText>
> > </Query>
> > </DataSet>
> > </DataSets>
> > <rd:SnapToGrid>true</rd:SnapToGrid>
> > <rd:DrawGrid>true</rd:DrawGrid>
> > <rd:ReportID>f4c1c559-ee3a-43cb-9c9d-27f23c7b8cc8</rd:ReportID>
> > <Language>en-US</Language>
> > </Report>
> > On Nov 15, 8:35 am, William <Will...@.discussions.microsoft.com> wrote:
> > > Unless AccountMV & q.ps are both integers, Percentile is a decimal number so
> > > your test should use the decimal as in:
> > > IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= .10, "Yellow", "White")"duh...@.gmail.com" wrote:
> > > > here is the query being called:
> > > > SELECT s.babr, s.SicDescription, SUM(s.AccountMV) AS mv,
> > > > SUM(s.AccountMV) / q.ps AS Percentile
> > > > FROM SnapsRaw s INNER JOIN
> > > > (SELECT babr, SUM(accountmv) ps
> > > > FROM snapsraw
> > > > WHERE monthend = '08/01/2006'
> > > > GROUP BY babr) q ON q.babr = s.babr
> > > > WHERE s.MonthEnd = '08/01/2006'
> > > > GROUP BY s.Babr, s.SicDescription, q.ps, q.babr
> > > > percentile is the number being displayed, changing it to .10 didnt seem
> > > > to help
> > > > On Nov 10, 4:12 pm, William <Will...@.discussions.microsoft.com> wrote:
> > > > > Is percentile an integer or a float. If float test is >= .10
> > > > > "duh...@.gmail.com" wrote:
> > > > > > I have the following applied as an expression to the background color
> > > > > > on a cell
> > > > > > =IIF( Sum(Fields!Percentile.Value, "RegionalSnaps") >= 10, "Yellow",
> > > > > > "White")
> > > > > > I only want the ones above 10% to be yellow, otherwise white.
> > > > > > Any ideas anyone on why the entire range of cells are yellow and not
> > > > > > just the one above 10%?