Matrix SubTotals

I have subtotal at the bottom of the matrix. I am using Distinct Count in the expression field of matrix. But when I run the report, The Subtotal is giving the sum of all not the Distict counts. I can see the properties of Subtotal row. But I can not find the expression Property for that. Please help me anyone know it or had this issue.

I appreciate your help.

Thanks in advance

Rams

Hi ramsk

The subtotal expression in matrix report cannot be directley accesed.
You need to use custom code if you wish to manipulate the data in a different
way. This is because the expression used in the Matrix subtotal is the same as that
of the detail rows by default.

Custom code can be written using vb.NET or c# by going to report properties
and selecting the code tab. Write the function to accept integer values(or whichever data type you use)
and the call it from the detail cell within the matrix using the following expression:

=Code.<insert function name here without sharp bracets>(<field name here without sharp brackets>)

EX: =Code.GetFields(Fields!Net_Invoice.Value)

Now you can do a count on the values using vb/c#
Using this method even the most complex calculations can be done.

Gerhard Davids

|||

Thank you Gerhard Davids. I don't know vb.NET/C# coding. I will try to do that.

Thanks,

Ramsk

|||

Actually,

You can access the subtotal for Matrix report. Click on the green triangle of your matrix totals and you can write your expression there.

Hammer

|||

Hi Hammer,

I'm sorry to have to inform you that this is in fact
not possible. There has been much contravercy
about this and it has been causing many people
headaches including me.

Thus we have been using this very tedious and
redundant workaround.If you follow this link you
see in the newsgroup discussion that clearly
states: it's not possible at this time.

Also if you search around a bit you will get the
same result. Even with CTP2 this functionality
is not available.

Just thaught I'd let you know as not to get some
peoples hopes up.

Gerhard Davids

Matrix Report Textbox Expression

=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%?

Ive tried both .10 and 10 for the value, and still the same thing

Because you're doing a sum, it's doing it for the entire dataset. If this expression is in a grouping e.g. table, matrix, list etc... then try changing the expresison to

=IIF( Fields!Percentile.Value >= .10, "Yellow", "White")

Effectively evalueated for every row in the group.

|||WOW, I've been searching for an answer for this for a long time, I really appreciate your time.|||You're welcome.

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

Matrix Report and Expression on field

I have a matrix report that has two columns, and one of the colums has the following expression for background color:

=IIF( Fields!Percentile.Value >= .10, "Yellow", "White")

Basically if the percent is greater than 10 highlight the field, for some reason i have some fields that dont show up yellow, see below:

http://duhaas.googlepages.com/percent.Jpg

your colour expression needs to wrap the field reference with the same aggregate function as the textbox value expression.

So, if you are using: =sum(Fields!Percentile.Value) as your value then change the colour expression to

=IIF( SUM(Fields!Percentile.Value) >= .10, "Yellow", "White")

this is because when no aggregate function is specified the the first value returned is used by default to evaluate the expression.

|||YOU DA MAN

Matrix Question

In a matrix with multiple groups on an axis, which has sub totals, how do
you determine in an expression where a particular data cell is?
For example:
----
| -2003 | +2004 |
Total |
----
| Q1 | Q2 | Q3 | Q4 | |
|
----
Item 1 | x1 | x2 | x3 | x4 | x5 |
x6 |
----
Item 2 | x7 | x8 | x9 | x10 | x11 |
x12 |
----
x1 = Item 1 for 2003-Q1
x2 = Item 1 for 2003-Q2
x3 = Item 1 for 2003-Q3
x4 = Item 1 for 2003-Q4
x5 = Item 1 for 2004
x6 = Item 1 for (2003 + 2004)
x7 = Item 2 for 2003-Q1
x8 = Item 2 for 2003-Q2
x9 = Item 2 for 2003-Q3
x10 = Item 2 for 2003-Q4
x11 = Item 2 for 2004
x12 = Item 2 for (2003 + 2004)
For instance:
At cell position x4, how do I know that I am in group 2 (Quarters), at cell
position x5, how do I know that I am in group 1 (years), and at cell
position x6, how do I know that I am in group 1 (years) sub total'Did you look at the InScope function? It will allow you to distinguish
between cells in subtotals and cells in the groupings. More information on
InScope is available at:
http://msdn.microsoft.com/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul Allan" <paul_allan@.maxqtech.com> wrote in message
news:O%23pHpENnEHA.3172@.TK2MSFTNGP10.phx.gbl...
> In a matrix with multiple groups on an axis, which has sub totals, how do
> you determine in an expression where a particular data cell is?
> For example:
> ----
> | -2003 | +2004
|
> Total |
> ----
> | Q1 | Q2 | Q3 | Q4 | |
> |
> ----
> Item 1 | x1 | x2 | x3 | x4 | x5 |
> x6 |
> ----
> Item 2 | x7 | x8 | x9 | x10 | x11 |
> x12 |
> ----
> x1 = Item 1 for 2003-Q1
> x2 = Item 1 for 2003-Q2
> x3 = Item 1 for 2003-Q3
> x4 = Item 1 for 2003-Q4
> x5 = Item 1 for 2004
> x6 = Item 1 for (2003 + 2004)
> x7 = Item 2 for 2003-Q1
> x8 = Item 2 for 2003-Q2
> x9 = Item 2 for 2003-Q3
> x10 = Item 2 for 2003-Q4
> x11 = Item 2 for 2004
> x12 = Item 2 for (2003 + 2004)
> For instance:
> At cell position x4, how do I know that I am in group 2 (Quarters), at
cell
> position x5, how do I know that I am in group 1 (years), and at cell
> position x6, how do I know that I am in group 1 (years) sub total'
>

Matrix Expression Problem

Have a matrix with three columns depicting the following table:
# of Results # of Results
% of Results
within 5 working days > 5 working days >5
working days
January 33 1
February 24 16
etc
The two # of Results colums have a count(Fields!January.Value) in the data
box.
I now need to get the % column done. I am having difficulty in doing this.
Any help would be greatly appreciated.You could add a subtotal column. In the matrix cell, you then need to take
into account the subtotal scope and use a different expression to calculate
the percentage. Something similar to this:
=iif(InScope("MatrixColumnGroupingName"), Count(Fields!Cases.Value),
Sum(iif( /* condition for the first column group instance */, 1, 0)) /
Sum(iif( /* condition for the second column group instance */, 1, 0)))
The comments in the expression above need to be replaced with the matrix
column grouping expression (basically identify the data rows that contribute
to the first grouping column vs. the second grouping column). For more
information about the usage of the InScope(...) function, please check the
MSDN documentation: http://msdn2.microsoft.com/en-us/library/ms226955
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
news:A5123C30-6A02-4480-A75E-8D3D6FD9AD43@.microsoft.com...
> Have a matrix with three columns depicting the following table:
> # of Results # of Results
> % of Results
> within 5 working days > 5 working days
> >5
> working days
> January 33 1
> February 24 16
> etc
> The two # of Results colums have a count(Fields!January.Value) in the data
> box.
> I now need to get the % column done. I am having difficulty in doing
> this.
> Any help would be greatly appreciated.
>|||BTW: in your case it seems like you don't really need a matrix. You can use
a table with a table grouping based on the month. Then inside the table
columns, you use expressions like shown below to determine the actual
counts:
=Sum(iif( /* condition */, 1, 0))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:ucv9B0uwFHA.720@.TK2MSFTNGP15.phx.gbl...
> You could add a subtotal column. In the matrix cell, you then need to take
> into account the subtotal scope and use a different expression to
> calculate the percentage. Something similar to this:
> =iif(InScope("MatrixColumnGroupingName"), Count(Fields!Cases.Value),
> Sum(iif( /* condition for the first column group instance */, 1, 0)) /
> Sum(iif( /* condition for the second column group instance */, 1, 0)))
> The comments in the expression above need to be replaced with the matrix
> column grouping expression (basically identify the data rows that
> contribute to the first grouping column vs. the second grouping column).
> For more information about the usage of the InScope(...) function, please
> check the MSDN documentation:
> http://msdn2.microsoft.com/en-us/library/ms226955
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
> news:A5123C30-6A02-4480-A75E-8D3D6FD9AD43@.microsoft.com...
>> Have a matrix with three columns depicting the following table:
>> # of Results # of Results
>> % of Results
>> within 5 working days > 5 working days >5
>> working days
>> January 33 1
>> February 24 16
>> etc
>> The two # of Results colums have a count(Fields!January.Value) in the
>> data
>> box.
>> I now need to get the % column done. I am having difficulty in doing
>> this.
>> Any help would be greatly appreciated.
>>
>|||Thanks for the point in the right direction Robert.
Still not sure how to get the percentage column. Below is my code for the
report thus far. Probably crude but hey im still learning this stuff.
SELECT
PMI.LaboratorySentToOranisationId as LabId,
O.Name as LabName,
TTLI.Name as TestType,
LTD.DateRecieved as DateReceived,
LTD.TestCompletedDate as CompleteDate,
/*# of Results within 5 working days*/
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '01') then 1
Else 0
End) as January,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '02') then 1
Else 0
End) as February,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '03') then 1
Else 0
End) as March,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '04') then 1
Else 0
End) as April,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '05') then 1
Else 0
End) as May,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '06') then 1
Else 0
End) as June,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '07') then 1
Else 0
End) as July,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '08') then 1
Else 0
End) as August,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '09') then 1
Else 0
End) as September,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '10') then 1
Else 0
End) as October,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '11') then 1
Else 0
End) as November,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '12') then 1
Else 0
End) as December,
/*# of Results >5 Working Days*/
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '01') then 1
Else 0
End) as Jan1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '02') then 1
Else 0
End) as Feb1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '03') then 1
Else 0
End) as Mar1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '04') then 1
Else 0
End) as Apr1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '05') then 1
Else 0
End) as May1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '06') then 1
Else 0
End) as Jun1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '07') then 1
Else 0
End) as Jul1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '08') then 1
Else 0
End) as Aug1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '09') then 1
Else 0
End) as Sep1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '10') then 1
Else 0
End) as Oct1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '11') then 1
Else 0
End) as Nov1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '12') then 1
Else 0
End) as Dec1
FROM
PostMortemInspection PMI
INNER JOIN LabTestRequest LTR on LTR.PostMortemInspectionId = PMI.Id
INNER JOIN Organisation O on PMI.LaboratorySentToOranisationId = O.Id
AND (O.EndDate is NULL)
INNER JOIN TestTypeLookUpItem TTLI on LTR.TestTypeLookUpItemId = TTLI.Id and
(TTLI.Id = '13')
INNER JOIN LabTestDetail LTD on LTR.Id = LTD.LabTestRequestId
--where (ltd.DateRecieved is NOT NULL)
As you can see im using Case statements for each column of data. In the
field of the table im using =SUM(Fields!January.Value). So getting the % is
hard. What I was trying to do was take (column B * 100) / column A.
Something along these lines: =SUM(Fields!Jul1.Value * 100) /
Fields!July.Value. However im getting Infinity displayed.
Any more help would be greatly appreciated.
Cheers
"Robert Bruckner [MSFT]" wrote:
> BTW: in your case it seems like you don't really need a matrix. You can use
> a table with a table grouping based on the month. Then inside the table
> columns, you use expressions like shown below to determine the actual
> counts:
> =Sum(iif( /* condition */, 1, 0))
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:ucv9B0uwFHA.720@.TK2MSFTNGP15.phx.gbl...
> > You could add a subtotal column. In the matrix cell, you then need to take
> > into account the subtotal scope and use a different expression to
> > calculate the percentage. Something similar to this:
> >
> > =iif(InScope("MatrixColumnGroupingName"), Count(Fields!Cases.Value),
> > Sum(iif( /* condition for the first column group instance */, 1, 0)) /
> > Sum(iif( /* condition for the second column group instance */, 1, 0)))
> >
> > The comments in the expression above need to be replaced with the matrix
> > column grouping expression (basically identify the data rows that
> > contribute to the first grouping column vs. the second grouping column).
> > For more information about the usage of the InScope(...) function, please
> > check the MSDN documentation:
> > http://msdn2.microsoft.com/en-us/library/ms226955
> >
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> >
> > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
> > news:A5123C30-6A02-4480-A75E-8D3D6FD9AD43@.microsoft.com...
> >> Have a matrix with three columns depicting the following table:
> >>
> >> # of Results # of Results
> >> % of Results
> >> within 5 working days > 5 working days >5
> >> working days
> >>
> >> January 33 1
> >> February 24 16
> >>
> >> etc
> >>
> >> The two # of Results colums have a count(Fields!January.Value) in the
> >> data
> >> box.
> >> I now need to get the % column done. I am having difficulty in doing
> >> this.
> >> Any help would be greatly appreciated.
> >>
> >>
> >
> >
>
>

Matrix Conditional Formatting

I have a matrix and want to put a border on only one column. I have
this expression in the right and left borderstyle property:
=iif(First(Fields!desc.Value)="Sent Census","Solid","None")
So if the value column = "Sent Census" then I want the column to have a
left and right border. My problem is that if the data cell has a 0 in it, the
border doesn't appear. I know why, I'm just looking for a work around to
have the whole column bordered even if a 0 is placed in the data cell.
Thanks!Seems like as soon as I post a question, I figure out the answer:
=iif(ReportItems("desc").Value="Sent Census","Solid","None")
You reference the textbox, not the data. doh.
"Sharon" wrote:
> I have a matrix and want to put a border on only one column. I have
> this expression in the right and left borderstyle property:
> =iif(First(Fields!desc.Value)="Sent Census","Solid","None")
> So if the value column = "Sent Census" then I want the column to have a
> left and right border. My problem is that if the data cell has a 0 in it, the
> border doesn't appear. I know why, I'm just looking for a work around to
> have the whole column bordered even if a 0 is placed in the data cell.
> Thanks!
>

Matrix calculation for percent

How do I write expression to calculate a value to show percentage of the
subtotal value in a Matrix?
I have already tried =Fields!name.value/sum(Fields!name.value) and this
does not work
Example:
Matrix has Coulmn group Matrix_col and a Row group Matrix_Row and I would
like to show the percentage of the subtotal for Matrix_Row in a column next
to Matrix Col.
Matrix_Col Percent
Matrix_Row
value 1 %25
value 2 %50
value 1 %25
Total 4Create a column, and set your expression to 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
"Curtis" <gilbertson.curtis@.leg.wa.gov> wrote in message
news:uB5u4igFGHA.1028@.TK2MSFTNGP11.phx.gbl...
> How do I write expression to calculate a value to show percentage of the
> subtotal value in a Matrix?
> I have already tried =Fields!name.value/sum(Fields!name.value) and this
> does not work
> Example:
> Matrix has Coulmn group Matrix_col and a Row group Matrix_Row and I would
> like to show the percentage of the subtotal for Matrix_Row in a column
> next
> to Matrix Col.
> Matrix_Col Percent
> Matrix_Row
> value 1 %25
> value 2 %50
> value 1 %25
>
> Total 4
>|||Thank you very much for your answer, I've been looking for a solution for
months.
"Kaisa M. Lindahl" wrote:
> Create a column, and set your expression to 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
> "Curtis" <gilbertson.curtis@.leg.wa.gov> wrote in message
> news:uB5u4igFGHA.1028@.TK2MSFTNGP11.phx.gbl...
> > How do I write expression to calculate a value to show percentage of the
> > subtotal value in a Matrix?
> >
> > I have already tried =Fields!name.value/sum(Fields!name.value) and this
> > does not work
> >
> > Example:
> > Matrix has Coulmn group Matrix_col and a Row group Matrix_Row and I would
> > like to show the percentage of the subtotal for Matrix_Row in a column
> > next
> > to Matrix Col.
> >
> > Matrix_Col Percent
> >
> > Matrix_Row
> >
> > value 1 %25
> >
> > value 2 %50
> >
> > value 1 %25
> >
> >
> >
> > Total 4
> >
> >
>
>

Matrix - Totals

What does the following error mean?
[rsNonAggregateInMatrixCell] The Value expression for the textbox â'textbox2â'
references a field outside an aggregate function. Value expressions in
matrix cells should be aggregates, to allow for subtotaling.
Build complete -- 0 errors, 1 warningsResolved
"Terry" wrote:
> What does the following error mean?
> [rsNonAggregateInMatrixCell] The Value expression for the textbox â'textbox2â'
> references a field outside an aggregate function. Value expressions in
> matrix cells should be aggregates, to allow for subtotaling.
> Build complete -- 0 errors, 1 warnings|||Figured it out. For others knowledge, you need to add the First() around the
value, like this for BorderStyle:
=iif(First(Fields!desc.Value)="Sent","Solid","None")
"Sharon" wrote:
> How was this resolved?
>
> "Terry" wrote:
> > Resolved
> >
> > "Terry" wrote:
> >
> > > What does the following error mean?
> > >
> > > [rsNonAggregateInMatrixCell] The Value expression for the textbox â'textbox2â'
> > > references a field outside an aggregate function. Value expressions in
> > > matrix cells should be aggregates, to allow for subtotaling.
> > > Build complete -- 0 errors, 1 warnings|||How was this resolved?
"Terry" wrote:
> Resolved
> "Terry" wrote:
> > What does the following error mean?
> >
> > [rsNonAggregateInMatrixCell] The Value expression for the textbox â'textbox2â'
> > references a field outside an aggregate function. Value expressions in
> > matrix cells should be aggregates, to allow for subtotaling.
> > Build complete -- 0 errors, 1 warnings

Matrix - Calculate percentage column

How do I write expression to calculate a value to show percentage of the
subtotal value in a Matrix? example:
Matrix has Coulmn group Matrix_col and a Row group Matrix_Row and I would
like to show the percentage of the subtotal for Matrix_Row in a column next
to Matrix Col.
Matrix_Col Percent
Matrix_Row
value 1 %25
value 2 %50
value 1 %25
Total 4I would try this in the third column
Field!Name.Value/ SUM(Field!Name.Value)
I'd also give this third column a percentage format
Med bouchenafa
"Curtis" <gilbertson.curtis@.leg.wa.gov> a écrit dans le message de news:
%23tJnBVXFGHA.2320@.TK2MSFTNGP11.phx.gbl...
> How do I write expression to calculate a value to show percentage of the
> subtotal value in a Matrix? example:
> Matrix has Coulmn group Matrix_col and a Row group Matrix_Row and I would
> like to show the percentage of the subtotal for Matrix_Row in a column
> next to Matrix Col.
>
> Matrix_Col Percent
> Matrix_Row
> value 1 %25
> value 2 %50
> value 1 %25
>
> Total 4
>|||Med, Thanks for your reply. I have tried this already and it does not work.
I tried it in the third column as well and it still does not work. Any
other ideas?
"Med Bouchenafa" <com.hotmail@.bouchenafa> wrote in message
news:uuhpEWfFGHA.516@.TK2MSFTNGP15.phx.gbl...
>I would try this in the third column
> Field!Name.Value/ SUM(Field!Name.Value)
> I'd also give this third column a percentage format
> Med bouchenafa
>
> "Curtis" <gilbertson.curtis@.leg.wa.gov> a écrit dans le message de news:
> %23tJnBVXFGHA.2320@.TK2MSFTNGP11.phx.gbl...
>> How do I write expression to calculate a value to show percentage of the
>> subtotal value in a Matrix? example:
>> Matrix has Coulmn group Matrix_col and a Row group Matrix_Row and I
>> would like to show the percentage of the subtotal for Matrix_Row in a
>> column next to Matrix Col.
>>
>> Matrix_Col Percent
>> Matrix_Row
>> value 1 %25
>> value 2 %50
>> value 1 %25
>>
>> Total 4
>>
>