Matrix Totals Problem - SSRS 2005

I wasn't able to get my totals working accurately in a table footer using SUM, so I though hmm, maybe try matrix...well, still same type of problems...a bit different but still, my totals are not totalling the columns!

Screen Shot:

http://www.photopizzaz.biz/matrix_totals_problem.jpg

I don't know what else to do, I give up at this point and need help from someone here who has already gone through these headaches!

I don't understand why BOL doesn't address more complicated issues such as this...they barely tell you anything there.

I believe you need to preface your column expressions with sum: ie: the expression under customer number would be: sum(fields!customernumber.Value)sql

matrix sum

hello

I have a count (bookings.id) as nobookings in my query, which returns a count of all specific classes for a member. In the matrix i have sum(fields!nobookings.value) which adds the bookings by class type (1 tennis, 2 golf, 3 cricket). Now i need the total of all classes by pmember (6 in this case). Can srs do this?

I assume you have a row or column grouping on the class type in the matrix, and then you get the sum of nobookings in the cells under each class type. If this is what you currently have in the matrix, just add subtotal (right-click on the row or column header, and click on subtotal) to that row or column, and you should get the total or all classes in the cells under the subtotal.|||I have the same problem, but whenever I add subtotals, the total figure is just the first value in a cell and not the total of all values. What am I doing wrong? Thanks!|||Check on the aggregate function in the expression for the cell textbox, and make sure you are using Sum() instead of First().|||I have the same problem as the subtotal only displays the first value of the row. But I could not find where is the place to change the expression. All the values below the 'Total' header in layout is greyed out and it drived me crazy. Could you please advice? Thanks a lot.|||

You dont directley edit the subtotal expression.

Add the Sum() to the row\column details expression,
as the subtotal uses the expression from R/C detail
as it's own.

Hope I'm being clear enough

G

|||Thanks you so much. I think I finally got it.|||Regarding about the issue of subtotal.I am encountering a problem,wherein,when i use the aggregate sum in the details expression,the value changed from the actual value in my database?Can you have any suggestions about it?Thanks!!

matrix sum

hello

I have a count (bookings.id) as nobookings in my query, which returns a count of all specific classes for a member. In the matrix i have sum(fields!nobookings.value) which adds the bookings by class type (1 tennis, 2 golf, 3 cricket). Now i need the total of all classes by pmember (6 in this case). Can srs do this?

I assume you have a row or column grouping on the class type in the matrix, and then you get the sum of nobookings in the cells under each class type. If this is what you currently have in the matrix, just add subtotal (right-click on the row or column header, and click on subtotal) to that row or column, and you should get the total or all classes in the cells under the subtotal.|||I have the same problem, but whenever I add subtotals, the total figure is just the first value in a cell and not the total of all values. What am I doing wrong? Thanks!|||Check on the aggregate function in the expression for the cell textbox, and make sure you are using Sum() instead of First().|||I have the same problem as the subtotal only displays the first value of the row. But I could not find where is the place to change the expression. All the values below the 'Total' header in layout is greyed out and it drived me crazy. Could you please advice? Thanks a lot.|||

You dont directley edit the subtotal expression.

Add the Sum() to the row\column details expression,
as the subtotal uses the expression from R/C detail
as it's own.

Hope I'm being clear enough

G

|||Thanks you so much. I think I finally got it.|||Regarding about the issue of subtotal.I am encountering a problem,wherein,when i use the aggregate sum in the details expression,the value changed from the actual value in my database?Can you have any suggestions about it?Thanks!!

matrix sum

hello

I have a count (bookings.id) as nobookings in my query, which returns a count of all specific classes for a member. In the matrix i have sum(fields!nobookings.value) which adds the bookings by class type (1 tennis, 2 golf, 3 cricket). Now i need the total of all classes by pmember (6 in this case). Can srs do this?

I assume you have a row or column grouping on the class type in the matrix, and then you get the sum of nobookings in the cells under each class type. If this is what you currently have in the matrix, just add subtotal (right-click on the row or column header, and click on subtotal) to that row or column, and you should get the total or all classes in the cells under the subtotal.|||I have the same problem, but whenever I add subtotals, the total figure is just the first value in a cell and not the total of all values. What am I doing wrong? Thanks!|||Check on the aggregate function in the expression for the cell textbox, and make sure you are using Sum() instead of First().|||I have the same problem as the subtotal only displays the first value of the row. But I could not find where is the place to change the expression. All the values below the 'Total' header in layout is greyed out and it drived me crazy. Could you please advice? Thanks a lot.|||

You dont directley edit the subtotal expression.

Add the Sum() to the row\column details expression,
as the subtotal uses the expression from R/C detail
as it's own.

Hope I'm being clear enough

G

|||Thanks you so much. I think I finally got it.

matrix sum

hello

I have a count (bookings.id) as nobookings in my query, which returns a count of all specific classes for a member. In the matrix i have sum(fields!nobookings.value) which adds the bookings by class type (1 tennis, 2 golf, 3 cricket). Now i need the total of all classes by pmember (6 in this case). Can srs do this?

I assume you have a row or column grouping on the class type in the matrix, and then you get the sum of nobookings in the cells under each class type. If this is what you currently have in the matrix, just add subtotal (right-click on the row or column header, and click on subtotal) to that row or column, and you should get the total or all classes in the cells under the subtotal.|||I have the same problem, but whenever I add subtotals, the total figure is just the first value in a cell and not the total of all values. What am I doing wrong? Thanks!|||Check on the aggregate function in the expression for the cell textbox, and make sure you are using Sum() instead of First().|||I have the same problem as the subtotal only displays the first value of the row. But I could not find where is the place to change the expression. All the values below the 'Total' header in layout is greyed out and it drived me crazy. Could you please advice? Thanks a lot.|||

You dont directley edit the subtotal expression.

Add the Sum() to the row\column details expression,
as the subtotal uses the expression from R/C detail
as it's own.

Hope I'm being clear enough

G

|||Thanks you so much. I think I finally got it.

Matrix SubTotals

The data cell contains '=iif( Fields!dt.Value <
#11/1/2004#,"*",Sum(Fields!MyValue.Value))' which is causing the subtotals
(row & column) to show an * whenever the row or column shows an *.
How do I get the subtotal to ignore the *'s and subtotal on just those cells
that contain a value.I discovered that the subtotals cell is using the Fields!dt.Value from the
first matrix column, and since the columns are sorted oldest dt to newest the
dt.Value is < the date indicated.
Can someone think of a way I can fix this other the changing the column order?
"Harolds" wrote:
> The data cell contains '=iif( Fields!dt.Value <
> #11/1/2004#,"*",Sum(Fields!MyValue.Value))' which is causing the subtotals
> (row & column) to show an * whenever the row or column shows an *.
> How do I get the subtotal to ignore the *'s and subtotal on just those cells
> that contain a value.|||I changed my iif to =iif(last(Fields!dt.Value)... and this fixed my problem.
"Harolds" wrote:
> The data cell contains '=iif( Fields!dt.Value <
> #11/1/2004#,"*",Sum(Fields!MyValue.Value))' which is causing the subtotals
> (row & column) to show an * whenever the row or column shows an *.
> How do I get the subtotal to ignore the *'s and subtotal on just those cells
> that contain a value.

Matrix SubTotal

Hi,
Brief Explanation:
I need 2 fields (say, A and B) in the Matrix Data Region with their SubTotals shown (sum(A), sum(B)). Now, in the matrix Data Region, values for only A should be displayed but both for subtotals both (sum(A), sum(B)) should be displayed.
Detail Explanation, If required:
I am working with a matrix and require 2 fields to be shown in the Data Region against 1 row and 1 column groups. To display 2 fields in the Data Region, i kept a rectangle in the Data Region and placed 2 textboxes inside it with each displaying it's own respective field values.
Now when i add a subtotal to the report, it displays 2 rows for subtotals for each of the Data fields. So far so good.
My requirement is to suppress One of the Data Fields in the Data Region section and LET only it's the Subtotal get displayed. To achieve this, when i keep one of the textboxes hidden, the subtotal also gets hidden.
How can i achieve this ? Even a workwround suggested would be appreciated.
Crystal does this by allowing to access subtotals fields seperately than the data section fields which RS does not.
ThanksYou could conditionally hide the textbox based on the InScope function (e.g.
<Hidden>=InScope("RowGroup") and InScope("ColumnGroup")</Hidden>)
However, that won't collapse out the space occupied by the hidden textbox.
The size of the subtotal cell (in the current version) must be the same as
the size of a detail cell. If you're willing to let your subtotals be a
little cramped, you could do a variation of this with three textboxes. Two
small textboxes (one for A and one for B) which take up half of the
rectangle each and one large textbox (for just A) which takes up the entire
rectangle (overlapping with the other textboxes). You could then hide the
small textboxes when in the detail cell and hide the large textbox when in a
subtotal cell.
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"push" <push@.discussions.microsoft.com> wrote in message
news:E185ABCD-EE32-4E81-A735-BCBA236262C8@.microsoft.com...
> Hi,
> Brief Explanation:
> I need 2 fields (say, A and B) in the Matrix Data Region with their
SubTotals shown (sum(A), sum(B)). Now, in the matrix Data Region, values for
only A should be displayed but both for subtotals both (sum(A), sum(B))
should be displayed.
> Detail Explanation, If required:
> I am working with a matrix and require 2 fields to be shown in the Data
Region against 1 row and 1 column groups. To display 2 fields in the Data
Region, i kept a rectangle in the Data Region and placed 2 textboxes inside
it with each displaying it's own respective field values.
> Now when i add a subtotal to the report, it displays 2 rows for subtotals
for each of the Data fields. So far so good.
> My requirement is to suppress One of the Data Fields in the Data Region
section and LET only it's the Subtotal get displayed. To achieve this, when
i keep one of the textboxes hidden, the subtotal also gets hidden.
> How can i achieve this ? Even a workwround suggested would be appreciated.
> Crystal does this by allowing to access subtotals fields seperately than
the data section fields which RS does not.
> Thanks
>|||This worked in the preview. Thanks. Now there is another issue. When i give a border for the Subtotals fields (A, B) (from Right Clink of that green corner), while previewing it says "Object Reference not set to an instance of an Object".
How can i place a border on the subtotals as a whole (i.e. for both A and B) ? Also, if, possible, how can i conditionally place the border for only one of the subtotals (i.e. only for subtotal A)?
Thanks,
"Chris Hays [MSFT]" wrote:
> You could conditionally hide the textbox based on the InScope function (e.g.
> <Hidden>=InScope("RowGroup") and InScope("ColumnGroup")</Hidden>)
> However, that won't collapse out the space occupied by the hidden textbox.
> The size of the subtotal cell (in the current version) must be the same as
> the size of a detail cell. If you're willing to let your subtotals be a
> little cramped, you could do a variation of this with three textboxes. Two
> small textboxes (one for A and one for B) which take up half of the
> rectangle each and one large textbox (for just A) which takes up the entire
> rectangle (overlapping with the other textboxes). You could then hide the
> small textboxes when in the detail cell and hide the large textbox when in a
> subtotal cell.
> --
> My employer's lawyers require me to say:
> "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> "push" <push@.discussions.microsoft.com> wrote in message
> news:E185ABCD-EE32-4E81-A735-BCBA236262C8@.microsoft.com...
> > Hi,
> >
> > Brief Explanation:
> > I need 2 fields (say, A and B) in the Matrix Data Region with their
> SubTotals shown (sum(A), sum(B)). Now, in the matrix Data Region, values for
> only A should be displayed but both for subtotals both (sum(A), sum(B))
> should be displayed.
> >
> > Detail Explanation, If required:
> >
> > I am working with a matrix and require 2 fields to be shown in the Data
> Region against 1 row and 1 column groups. To display 2 fields in the Data
> Region, i kept a rectangle in the Data Region and placed 2 textboxes inside
> it with each displaying it's own respective field values.
> > Now when i add a subtotal to the report, it displays 2 rows for subtotals
> for each of the Data fields. So far so good.
> > My requirement is to suppress One of the Data Fields in the Data Region
> section and LET only it's the Subtotal get displayed. To achieve this, when
> i keep one of the textboxes hidden, the subtotal also gets hidden.
> > How can i achieve this ? Even a workwround suggested would be appreciated.
> > Crystal does this by allowing to access subtotals fields seperately than
> the data section fields which RS does not.
> >
> > Thanks
> >
>
>|||You're setting the border properties the right way. I don't know why you're
getting that error. (I tried the same thing here and it works, so I'd need
your report and the associated data file to try to reproduce the problem
here). A workaround in the mean time: Just set the borders on one or both
of the small textboxes directly. Since they'll be hidden unless you're in a
subtotal cell, you don't actually need to use the subtotal-specific style
properties (the green-triangle ones).
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"push" <push@.discussions.microsoft.com> wrote in message
news:027F0F5F-1971-4DEF-B118-7615AF3C988B@.microsoft.com...
> This worked in the preview. Thanks. Now there is another issue. When i
give a border for the Subtotals fields (A, B) (from Right Clink of that
green corner), while previewing it says "Object Reference not set to an
instance of an Object".
> How can i place a border on the subtotals as a whole (i.e. for both A and
B) ? Also, if, possible, how can i conditionally place the border for only
one of the subtotals (i.e. only for subtotal A)?
> Thanks,
>
> "Chris Hays [MSFT]" wrote:
> > You could conditionally hide the textbox based on the InScope function
(e.g.
> > <Hidden>=InScope("RowGroup") and InScope("ColumnGroup")</Hidden>)
> > However, that won't collapse out the space occupied by the hidden
textbox.
> > The size of the subtotal cell (in the current version) must be the same
as
> > the size of a detail cell. If you're willing to let your subtotals be a
> > little cramped, you could do a variation of this with three textboxes.
Two
> > small textboxes (one for A and one for B) which take up half of the
> > rectangle each and one large textbox (for just A) which takes up the
entire
> > rectangle (overlapping with the other textboxes). You could then hide
the
> > small textboxes when in the detail cell and hide the large textbox when
in a
> > subtotal cell.
> >
> > --
> > My employer's lawyers require me to say:
> > "This posting is provided 'AS IS' with no warranties, and confers no
> > rights."
> >
> > "push" <push@.discussions.microsoft.com> wrote in message
> > news:E185ABCD-EE32-4E81-A735-BCBA236262C8@.microsoft.com...
> > > Hi,
> > >
> > > Brief Explanation:
> > > I need 2 fields (say, A and B) in the Matrix Data Region with their
> > SubTotals shown (sum(A), sum(B)). Now, in the matrix Data Region, values
for
> > only A should be displayed but both for subtotals both (sum(A), sum(B))
> > should be displayed.
> > >
> > > Detail Explanation, If required:
> > >
> > > I am working with a matrix and require 2 fields to be shown in the
Data
> > Region against 1 row and 1 column groups. To display 2 fields in the
Data
> > Region, i kept a rectangle in the Data Region and placed 2 textboxes
inside
> > it with each displaying it's own respective field values.
> > > Now when i add a subtotal to the report, it displays 2 rows for
subtotals
> > for each of the Data fields. So far so good.
> > > My requirement is to suppress One of the Data Fields in the Data
Region
> > section and LET only it's the Subtotal get displayed. To achieve this,
when
> > i keep one of the textboxes hidden, the subtotal also gets hidden.
> > > How can i achieve this ? Even a workwround suggested would be
appreciated.
> > > Crystal does this by allowing to access subtotals fields seperately
than
> > the data section fields which RS does not.
> > >
> > > Thanks
> > >
> >
> >
> >

Matrix Row Counts

I have a matrix which sums sales to a customer by Month/Year.
Rows - Customer Name, Sum(NetSales)
Columns - Month/Year
What I want to capture is the total number of rows in the matrix.
For example, I am passing the report 144 records from my query; however
there are only 35 distinct customers.
Is there a function or property of the matrix that will give me this
information? I am currently doing a SELECT DISTICT on customer no in
another query to get this result.Using the CountDistinct Aggregate would do the job so you would not
need the second query.

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 Groups and Sums

I have what I thought was a pretty simple report, but have encountered two
issues.
I have three groups in this report.
1. When using sum on my detail row I am getting what appears to be a running
total in one of the columns. When I don't use sum then I get either the first
or last value for a detail record.
2. When using groups, the results are not what I would expect. It seems
almost impossible to get the grouping I would like. If I want to total on the
first group and then the second group and then a final total for all the
groups I can't. Am I missing something as this seems like it should be a
trivial effort.
If anybody can help I would appreciate it.
--
DCDUse Fields!fieldname.value (sounds like you are getting sum, first, and
last)... do not use a function at all..
Look at the sum documentation,, there is an additional parameter which
allows you to set the scope, Use a group name there and see if that helps...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Darryl" <ddillman@.tmsteam.com> wrote in message
news:F1127546-8532-4873-A11B-B6A7D3B8897E@.microsoft.com...
>I have what I thought was a pretty simple report, but have encountered two
> issues.
> I have three groups in this report.
> 1. When using sum on my detail row I am getting what appears to be a
> running
> total in one of the columns. When I don't use sum then I get either the
> first
> or last value for a detail record.
> 2. When using groups, the results are not what I would expect. It seems
> almost impossible to get the grouping I would like. If I want to total on
> the
> first group and then the second group and then a final total for all the
> groups I can't. Am I missing something as this seems like it should be a
> trivial effort.
> If anybody can help I would appreciate it.
> --
> DCD

Matrix grouping

My fiscal year starts from April. How can I group with fiscal year like this?

Select items,sum(sales), date from tableA

2007

2006

4

5

6

7

8

9

10

11

12

1

2

3

4

5

6

7

8

9

10

11

12

1

2

3

1

Books

10

20

0

0

0

0

0

0

0

20

50

0

25

10

10

0

0

5

0

25

15

10

10

20

2

Panel

10

10

10

20

20

10

10

20

10

10

10

10

20

20

20

20

30

30

10

10

10

30

30

30

3

Frame

Try to add your fiscal year at your Time dimension.

Helped?

Regards

|||

Is it possible user date field group to like this in matrix?

2005-4-1 to 2006-3-31

2006-4-1 to 2007-3-31

4

4

1

Books

10

25

2

Panel

10

20

3

Frame

6

6

|||

Dear Friend,

the both columns is based in the date parameter of your report, correct?

You only need 2 periods? 1 year ago and 2 years ago from parameter date, correct?!

Regards!

|||

Hi PedroCGD

The columns is based in the date parameter. I wants to do 5 year periods. Can you help me?

|||

Yes I'll help you, but only in a few hours when I arrive home!!

You'll get it! Dont panic! :-)

regards!

|||

palm99,

Can I try resolve your problem or you already resolved?

Regards

|||

Hi PedroCGD

I am waiting your help.

Matrix grouping

My fiscal year starts from April. How can I group with fiscal year like this?

Select items,sum(sales), date from tableA

2007

2006

4

5

6

7

8

9

10

11

12

1

2

3

4

5

6

7

8

9

10

11

12

1

2

3

1

Books

10

20

0

0

0

0

0

0

0

20

50

0

25

10

10

0

0

5

0

25

15

10

10

20

2

Panel

10

10

10

20

20

10

10

20

10

10

10

10

20

20

20

20

30

30

10

10

10

30

30

30

3

Frame

Try to add your fiscal year at your Time dimension.

Helped?

Regards

|||

Is it possible user date field group to like this in matrix?

2005-4-1 to 2006-3-31

2006-4-1 to 2007-3-31

4

4

1

Books

10

25

2

Panel

10

20

3

Frame

6

6

|||

Dear Friend,

the both columns is based in the date parameter of your report, correct?

You only need 2 periods? 1 year ago and 2 years ago from parameter date, correct?!

Regards!

|||

Hi PedroCGD

The columns is based in the date parameter. I wants to do 5 year periods. Can you help me?

|||

Yes I'll help you, but only in a few hours when I arrive home!!

You'll get it! Dont panic! :-)

regards!

|||

palm99,

Can I try resolve your problem or you already resolved?

Regards

|||

Hi PedroCGD

I am waiting your help.