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%?
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment