Is it possible to set the background color on the Total (Row and/or Column) of a Matrix. I'm talking the entire Row and/or column not just the cell that reads "Total". I would like to have my subtotal rows and columns to be gray.Yes, It is possible!
In the textbox that has the "Total", you'll see, in the right upermost corner, a "green flag"; click and in the properties window set the background color to gray.
-
Telmo Moreira
Showing posts with label color. Show all posts
Showing posts with label color. Show all posts
Friday, March 23, 2012
Monday, March 19, 2012
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%?
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%?
Matrix report 4th row group subtotal row color
I'm dealing w/ SSRS 2005.
I have my main matrix report which has five row groups.
What I'd like to do is have the subtotal at the 4th level have a coloring for the whole row at run-time...so the user can follow from left to right what the 4th level subtotal actually is (the report can get fairly wide).
At design time, you don't even see the rows to the right of the subtotal, you just see the subtotal box.
Thanks!
got it...click on the little green triangle in the upper right corner, and set the background property.Friday, March 9, 2012
Matrix Background Color Change
Hi All.
Is there anyway to change matrix type reoports rows to change background
colors rows alternate row to red or else white in color. Please help me. It
works fine in on table type.
Thanks!
ReddyThis is Chris Webbs' description on how to do it
http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx
Kaisa M. Lindahl Lervik
"Reddy" <Reddy@.discussions.microsoft.com> wrote in message
news:9805F625-7622-4C6D-9D6D-30CA7E99E601@.microsoft.com...
> Hi All.
> Is there anyway to change matrix type reoports rows to change background
> colors rows alternate row to red or else white in color. Please help me.
> It
> works fine in on table type.
> Thanks!
> Reddy
Is there anyway to change matrix type reoports rows to change background
colors rows alternate row to red or else white in color. Please help me. It
works fine in on table type.
Thanks!
ReddyThis is Chris Webbs' description on how to do it
http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx
Kaisa M. Lindahl Lervik
"Reddy" <Reddy@.discussions.microsoft.com> wrote in message
news:9805F625-7622-4C6D-9D6D-30CA7E99E601@.microsoft.com...
> Hi All.
> Is there anyway to change matrix type reoports rows to change background
> colors rows alternate row to red or else white in color. Please help me.
> It
> works fine in on table type.
> Thanks!
> Reddy
matrix apperaance
Anyone have any idea how to get a matrix to display a different background
color for every other row?Chris Hay's has a post on this.
http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx
Steve MunLeeuw
"Brian L" <BrianL@.discussions.microsoft.com> wrote in message
news:EC0A1662-1AA5-446A-A82F-53AE6D614106@.microsoft.com...
> Anyone have any idea how to get a matrix to display a different background
> color for every other row?
color for every other row?Chris Hay's has a post on this.
http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx
Steve MunLeeuw
"Brian L" <BrianL@.discussions.microsoft.com> wrote in message
news:EC0A1662-1AA5-446A-A82F-53AE6D614106@.microsoft.com...
> Anyone have any idea how to get a matrix to display a different background
> color for every other row?
Matrix - Subtotal Conditional Formatting - How do you do it?
My subtotal column's background color is dark gray and the text is bold
when the row group is collapsed, but I don't see this in other similar
matrix reports without a row group. This report was inherited from a
previous developer.
How did they do it? Even if I set the subtotal cell's colors to be
different from the entire column, they synchronize. I want to do this,
but can't figure out whether it's a setting or not.
Thanks!
MIkeThe subtotal heading should have a little green triangle on it. If you click
on that triangle, the VS properties window should show style settings that
specifically apply to subtotals row/columns.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bassist695" <Michael.EJ.Reynolds@.gmail.com> wrote in message
news:1128365078.625735.314000@.g49g2000cwa.googlegroups.com...
> My subtotal column's background color is dark gray and the text is bold
> when the row group is collapsed, but I don't see this in other similar
> matrix reports without a row group. This report was inherited from a
> previous developer.
> How did they do it? Even if I set the subtotal cell's colors to be
> different from the entire column, they synchronize. I want to do this,
> but can't figure out whether it's a setting or not.
> Thanks!
> MIke
>
when the row group is collapsed, but I don't see this in other similar
matrix reports without a row group. This report was inherited from a
previous developer.
How did they do it? Even if I set the subtotal cell's colors to be
different from the entire column, they synchronize. I want to do this,
but can't figure out whether it's a setting or not.
Thanks!
MIkeThe subtotal heading should have a little green triangle on it. If you click
on that triangle, the VS properties window should show style settings that
specifically apply to subtotals row/columns.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bassist695" <Michael.EJ.Reynolds@.gmail.com> wrote in message
news:1128365078.625735.314000@.g49g2000cwa.googlegroups.com...
> My subtotal column's background color is dark gray and the text is bold
> when the row group is collapsed, but I don't see this in other similar
> matrix reports without a row group. This report was inherited from a
> previous developer.
> How did they do it? Even if I set the subtotal cell's colors to be
> different from the entire column, they synchronize. I want to do this,
> but can't figure out whether it's a setting or not.
> Thanks!
> MIke
>
Wednesday, March 7, 2012
matrix - change color based on data trend up or down
I have a dataset with customer, date and count columns. Then I have a matrix
based on that information as follows:
Oct 2007 Sep 2007 Aug 2007
Customer A 123 432 959
Customer B 984 394 345
Customer C 2459 2335 234
I would like to have the color of the count red if the number is lower for
that customer for the next date (i.e. 123) and green if the number is higher
for the next date (i.e. 984).
Any idea how I do this?
StephanieOn Oct 1, 9:49 am, Stephanie <Stepha...@.discussions.microsoft.com>
wrote:
> I have a dataset with customer, date and count columns. Then I have a matrix
> based on that information as follows:
> Oct 2007 Sep 2007 Aug 2007
> Customer A 123 432 959
> Customer B 984 394 345
> Customer C 2459 2335 234
> I would like to have the color of the count red if the number is lower for
> that customer for the next date (i.e. 123) and green if the number is higher
> for the next date (i.e. 984).
> Any idea how I do this?
> Stephanie
You might be able to use 'iif' in conjunction with the 'Previous'
function to set the background color. I'm not sure of the exact
syntax, but this might lead you in the right direction.
=iif(Previous(Fields!Count.Value) < Fields!Count.Value, "Green",
"Red")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
based on that information as follows:
Oct 2007 Sep 2007 Aug 2007
Customer A 123 432 959
Customer B 984 394 345
Customer C 2459 2335 234
I would like to have the color of the count red if the number is lower for
that customer for the next date (i.e. 123) and green if the number is higher
for the next date (i.e. 984).
Any idea how I do this?
StephanieOn Oct 1, 9:49 am, Stephanie <Stepha...@.discussions.microsoft.com>
wrote:
> I have a dataset with customer, date and count columns. Then I have a matrix
> based on that information as follows:
> Oct 2007 Sep 2007 Aug 2007
> Customer A 123 432 959
> Customer B 984 394 345
> Customer C 2459 2335 234
> I would like to have the color of the count red if the number is lower for
> that customer for the next date (i.e. 123) and green if the number is higher
> for the next date (i.e. 984).
> Any idea how I do this?
> Stephanie
You might be able to use 'iif' in conjunction with the 'Previous'
function to set the background color. I'm not sure of the exact
syntax, but this might lead you in the right direction.
=iif(Previous(Fields!Count.Value) < Fields!Count.Value, "Green",
"Red")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
Subscribe to:
Posts (Atom)