Autonumber field: matrix

Showing posts with label matrix. Show all posts

Tuesday, March 20, 2012

Background Colour of Matrix cells

Hi All
I have a matrix that goes no bigger than 4 colums and 4 rows big.
I need to have different colours for certain cells within the matrix.
e.g for the first row, i need:
Cell1 = Yellow
Cell2 = Blue
Cell3 = Green
Cell4 = Green
for the 2nd row i need:
Cell1 = Yellow
Cell2 = Blue
Cell3 = Green
Cell4 = Green
for the third row in need:
Cell1 = Red
Cell2 = Yellow
Cell3 = Blue
Cell4 = Blue
and finally for the forth row i need:
Cell1 = Red
Cell2 = Red
Cell3 = Yellow
Cell4 = Yellow
Does anybody know a way setting these to the desired colours?
please help!!!!!!
--
Thanks in advance,
Dave HuntDustpanDave,
According to the "Hitchhiker's Guide to SQL Reporting Services" book, page
433-436, you'll have to use RunningValue and CountDistinct within a Matrix.
They say:
1) Add a Row Gropu called RowColor and give it a grouping expression of "=1"
2) Find the textbox for RowColor and enter the expression
"=RunningValue(Fields!<columngroupname>.Value & Fields!<2ndColumnGroup>.Value
..., CountDistinct, Nothing)"
3) Then enter an expression into the background color for all of the fields
in question: "IIF(RunningValue(Fields!<ColumnGroupName>.Value &
Fields!<2ndColumnGroup>.Value & ..., CountDistinct, Nothing) mod 2, "Color1",
"Color1")
4) The ROWColor Textbox MUST remain visible (you can't hide it), but you can
shrink it's height, set the font to 1 pt, zero out your padding and/or set
the main Color property to the color of your background.
I hope this helps. I haven't done this in a Matrix yet. I've just used the
simpler format for table rows. The book is pretty decent and has helped me a
lot. I'd advise buying a copy, or at least buying a SQL RS book. Don't get
the one published by WROK, though. It's not the best of the available
material.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"DustpanDave" wrote:
> Hi All
> I have a matrix that goes no bigger than 4 colums and 4 rows big.
> I need to have different colours for certain cells within the matrix.
> e.g for the first row, i need:
> Cell1 = Yellow
> Cell2 = Blue
> Cell3 = Green
> Cell4 = Green
> for the 2nd row i need:
> Cell1 = Yellow
> Cell2 = Blue
> Cell3 = Green
> Cell4 = Green
> for the third row in need:
> Cell1 = Red
> Cell2 = Yellow
> Cell3 = Blue
> Cell4 = Blue
> and finally for the forth row i need:
> Cell1 = Red
> Cell2 = Red
> Cell3 = Yellow
> Cell4 = Yellow
> Does anybody know a way setting these to the desired colours?
> please help!!!!!!
> --
> Thanks in advance,
> Dave Hunt|||Oh, I forgot to mention, this is to get alternating rows of color. You'll
have to play with it to get specific cell colors. Also, look up RowNumber()
function in BOL.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"DustpanDave" wrote:
> Hi All
> I have a matrix that goes no bigger than 4 colums and 4 rows big.
> I need to have different colours for certain cells within the matrix.
> e.g for the first row, i need:
> Cell1 = Yellow
> Cell2 = Blue
> Cell3 = Green
> Cell4 = Green
> for the 2nd row i need:
> Cell1 = Yellow
> Cell2 = Blue
> Cell3 = Green
> Cell4 = Green
> for the third row in need:
> Cell1 = Red
> Cell2 = Yellow
> Cell3 = Blue
> Cell4 = Blue
> and finally for the forth row i need:
> Cell1 = Red
> Cell2 = Red
> Cell3 = Yellow
> Cell4 = Yellow
> Does anybody know a way setting these to the desired colours?
> please help!!!!!!
> --
> Thanks in advance,
> Dave Hunt|||Thanks for you help, having a look in the Hitchhikers guide now.
i have tried that but i will give it another go
cheers
dave
--
Thanks in advance,
Dave Hunt
"Catadmin" wrote:
> Oh, I forgot to mention, this is to get alternating rows of color. You'll
> have to play with it to get specific cell colors. Also, look up RowNumber()
> function in BOL.
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the back?
> @.=)
>
> "DustpanDave" wrote:
> > Hi All
> >
> > I have a matrix that goes no bigger than 4 colums and 4 rows big.
> >
> > I need to have different colours for certain cells within the matrix.
> >
> > e.g for the first row, i need:
> >
> > Cell1 = Yellow
> > Cell2 = Blue
> > Cell3 = Green
> > Cell4 = Green
> >
> > for the 2nd row i need:
> > Cell1 = Yellow
> > Cell2 = Blue
> > Cell3 = Green
> > Cell4 = Green
> >
> > for the third row in need:
> > Cell1 = Red
> > Cell2 = Yellow
> > Cell3 = Blue
> > Cell4 = Blue
> >
> > and finally for the forth row i need:
> > Cell1 = Red
> > Cell2 = Red
> > Cell3 = Yellow
> > Cell4 = Yellow
> >
> > Does anybody know a way setting these to the desired colours?
> > please help!!!!!!
> > --
> > Thanks in advance,
> > Dave Hunt|||Perfect, thanks also :-)
This is a problem I've been having and how have it fixed :-)

Thursday, February 16, 2012

Averages in Matrix report

Greetings,
I have a report that works just great:
Jan Feb Mar Apr
Facility1 80 78 65 90
Facility2 85 79 90 49
It tells you a calculated score for each facility for each month. It's
exactly what they asked for. Until I showed it to them. Now they want
more info.
So I added another row to sort by - Region
Jan Feb Mar
region1 facility1 80 78 65
facility2 85 79 90
region2 facility3 81 65 82
facility4 84 90 71
And that works great. The problem is, they want a YTD col, and these
are scores. I can't just add them up, I have to either average them,
or calc a YTD score for each facility and put it out to the right after
the last months column.
Jan Feb Mar YTD
region1 facility1 80 78 65 74.33
facility2 85 79 90 84.66
region2 facility3 81 65 82 76
facility4 84 90 71 81.66
The second problem is, I'd like to be able to see an average for
Regions. Is it possible?
Jan Feb Mar YTD
region1 facility1 80 78 65 74.33
facility2 85 79 90 84.66
region average 82.5 78.5 77.5 79.49
region2 facility3 81 65 82 76
facility4 84 90 71 81.66
region average 82.5 77.5 76.5 78.83
here's my RDL.
<?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">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>Brown</BackgroundColor>
<BorderWidth>
<Bottom>3pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>18pt</FontSize>
<TextAlign>Center</TextAlign>
<Color>White</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.33in</Height>
<Width>5in</Width>
<CanGrow>true</CanGrow>
<Value>Monthly Scorecard</Value>
</Textbox>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N0</Format>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=(Sum(Fields!MonthTotal.Value) & " %
")</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.24in</Height>
</MatrixRow>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N0</Format>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!month.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.24in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>0.96875in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>Cypress_APP</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_month">
<GroupExpressions>
<GroupExpression>=Fields!month.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!month.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<ReportItems>
<Textbox Name="month">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>=iif(RunningValue(Fields!PortfolioName.Value &
Fields!name.Value, Countdistinct, Nothing) mod 2, "LightBlue",
"White")</BackgroundColor>
<FontSize>9pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>month</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=choose(Fields!month.Value,
"Jan Score",
"Feb Score",
"Mar Score",
"Apr Score",
"May Score",
"Jun Score",
"July Score",
"Aug Score",
"Sept Score",
"Oct Score",
"Nov Score",
"Dec Score"
)</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.24in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>4.90625in</Width>
<Top>0.33in</Top>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_RowGroup2">
<GroupExpressions>
<GroupExpression>=Fields!PortfolioName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>=iif(RunningValue(Fields!PortfolioName.Value &
Fields!name.Value, Countdistinct, Nothing) mod 2, "LightBlue",
"White")</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!PortfolioName.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>0.875in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_RowGroup3">
<GroupExpressions>
<GroupExpression>=Fields!name.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>=iif(RunningValue(Fields!PortfolioName.Value &
Fields!name.Value, Countdistinct, Nothing) mod 2, "LightBlue",
"White")</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!name.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>3in</Width>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping Name="RowColor">
<GroupExpressions>
<GroupExpression>=1</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>=iif(RunningValue(Fields!PortfolioName.Value &
Fields!name.Value, Countdistinct, Nothing) mod 2, "LightBlue",
"White")</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=RunningValue(Fields!PortfolioName.Value &
Fields!name.Value, Countdistinct, Nothing)</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>0.03125in</Width>
</RowGrouping>
<RowGrouping>
<Width>0.03125in</Width>
<StaticRows>
<StaticRow>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>=iif(RunningValue(Fields!PortfolioName.Value &
Fields!name.Value, Countdistinct, Nothing) mod 2, "LightBlue",
"White")</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Month Total</Value>
</Textbox>
</ReportItems>
</StaticRow>
<StaticRow>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>=iif(RunningValue(Fields!PortfolioName.Value &
Fields!name.Value, Countdistinct, Nothing) mod 2, "LightBlue",
"White")</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontSize>9pt</FontSize>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</StaticRow>
</StaticRows>
</RowGrouping>
</RowGroupings>
</Matrix>
</ReportItems>
<Style />
<Height>1.05in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Cypress_APP">
<rd:DataSourceID>ed3b9625-deb2-4de7-b3be-35b5117506fa</rd:DataSourceID>
<DataSourceReference>Cypress_APP</DataSourceReference>
</DataSource>
</DataSources>
<Width>7.375in</Width>
<DataSets>
<DataSet Name="Cypress_APP">
<Fields>
<Field Name="name">
<DataField>Name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PortfolioName">
<DataField>PortfolioName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="month">
<DataField>Month</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="measureID">
<DataField>MeasureID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="MonthTotal">
<DataField>MonthTotal</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Cypress_APP</DataSourceName>
<CommandText>SELECT e.Name, Portfolios.Name AS
PortfolioName, sc.Month, sc.MeasureID, dbo.udfn_M1vsM2(CASE WHEN
sc.[MeasureID] IN (26)
THEN (CASE sc.[Measure1] WHEN 0 THEN 0 ELSE
sc.[Measure2] / sc.[Measure1] END) ELSE sc.[Measure1] END, CASE WHEN
sc.[MeasureID] IN (26)
THEN sc.[Measure4] ELSE sc.[Measure2] END,
m.BusinessRule, m.partialScore, m.Score) AS MonthTotal
FROM ScoreCard sc INNER JOIN
Enterprise e ON sc.EnterpriseID = e.EnterpriseID
INNER JOIN
Measure m ON sc.MeasureID = m.MeasureID INNER
JOIN
Enterprise_View ev ON e.EnterpriseID = ev.EnterpriseID INNER JOIN
(SELECT EnterpriseID, [Name]
FROM Enterprise
WHERE EnterpriseTypeID = 2) Portfolios
ON Portfolios.EnterpriseID = ev.ParentID
WHERE (sc.Year = YEAR(@.date1))
GROUP BY Portfolios.Name, e.Name, sc.Month, sc.MeasureID, m.MeasureID,
sc.Measure1, sc.Measure2, sc.Measure4, m.BusinessRule, m.partialScore,
m.Score
ORDER BY sc.Month, e.Name, m.MeasureID</CommandText>
<QueryParameters>
<QueryParameter Name="@.date1">
<Value>=Parameters!date1.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>1577349c-ff67-4564-9cb1-afa782e635fe</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<ReportParameters>
<ReportParameter Name="date1">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=DateAdd(DateInterval.Day, (DatePart(DateInterval.Day,
Now)) * -1, Now)</Value>
</Values>
</DefaultValue>
<Prompt>date1</Prompt>
</ReportParameter>
</ReportParameters>
<Language>en-US</Language>
</Report>> And that works great. The problem is, they want a YTD col, and these
> are scores. I can't just add them up, I have to either average them,
> or calc a YTD score for each facility and put it out to the right after
> the last months column.
> Jan Feb Mar YTD
> region1 facility1 80 78 65 74.33
> facility2 85 79 90 84.66
> region2 facility3 81 65 82 76
> facility4 84 90 71 81.66
No problem at all. I would create the average based upon the values coming
back from your query. Depending on how your report is set up, you will need
to place logic in your code to account for the number of months. If a month
has a zero score, don't include it in your average calculation...or
something like that.
> The second problem is, I'd like to be able to see an average for
> Regions. Is it possible?
> Jan Feb Mar YTD
> region1 facility1 80 78 65 74.33
> facility2 85 79 90 84.66
> region average 82.5 78.5 77.5 79.49
> region2 facility3 81 65 82 76
> facility4 84 90 71 81.66
> region average 82.5 77.5 76.5 78.83
Depending on how you have your report set up, there are several ways to do
this. You can add up the actual report objects (textbox1, textbox2, etc) and
get your average that way (check out the RunningValue() function), or you
could write your own function in the code block that maintains the average
value per region as the report renders.|||G wrote:
> No problem at all. I would create the average based upon the values coming
> back from your query. Depending on how your report is set up, you will need
> to place logic in your code to account for the number of months. If a month
> has a zero score, don't include it in your average calculation...or
> something like that.
Yes, but how do I show it. When I click the subtotal button, it puts a
total bar out there. If I put a formula in there, it puts the forumula
in the header of the report, not on the subtotal line like I want it.
> > The second problem is, I'd like to be able to see an average for
> > Regions. Is it possible?
> >
> > Jan Feb Mar YTD
> > region1 facility1 80 78 65 74.33
> > facility2 85 79 90 84.66
> > region average 82.5 78.5 77.5 79.49
> >
> > region2 facility3 81 65 82 76
> > facility4 84 90 71 81.66
> > region average 82.5 77.5 76.5 78.83
> Depending on how you have your report set up, there are several ways to do
> this. You can add up the actual report objects (textbox1, textbox2, etc) and
> get your average that way (check out the RunningValue() function), or you
> could write your own function in the code block that maintains the average
> value per region as the report renders.
Once I have the value, how do I insert it? as another rowgroup?

Averages in Matrix

I am new to Reporting Services on SQL Server 2005 and I need help. I have a report that I have create below using matrices.

Qtr 1
1 2 3 4 5 6 7 8 9 10 11 12 13 Total 2004 80 60 70 95 70 85 60 90 110 55 90 90 50 1005 2005 5 5 10 20 5 5 5 5 5 10 75

The columns represent each week in a quarter and the row represents the year the week is in and the details is the number of new accounts created during that particular week of the year. I have couple of questions.

How do I get and average for the number of account created per week per quarter?

How do I create a percentage of the change of accounts per week per year?

Qtr 1
1 2 3 4 5 6 7 8 9 10 11 12 13 Total AVG 2004 80 60 70 95 70 85 60 90 110 55 90 90 50 1005 77.31 2005 5 0 5 10 20 5 0 5 5 5 5 0 10 75 5.77 -93.8% -100.0% -92.9% -89.5% -71.4% -94.1% -100.0% -94.4% -95.5% -90.9% -94.4% -100.0% -80.0%

Thanks in Advance.

You can use the InScope function to distinguish subtotal cells from other cells. Please check the MSDN documentation about the InScope function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp

With InScope you can determine the current scope of a matrix cell (e.g. in subtotal or not). You would use an IIF-expression to set the cell expression based on the InScope return values. Note: a matrix cell is "in scope" of column and row groupings, so you need at least two InScope function calls in the case where you have one dynamic row and one dynamic column grouping. E.g.

=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))

Replace "In Cell" with =Sum(Fields!Amount.Value)
Replace "In Subtotal..." with =Avg(Fields!Amount.Value)

However, note that since the subtotal cells share the same cell definition as the group instance cells, adding two "subtotals" (one for "Total", the other for "AVG") at the same level is not supported. One way of solving this is to add a rectangle into the matrix cell and use two textboxes to show the total and the average. Then use conditional visibility on the average textbox to only have it visible for subtotals.

-- Robert

|||

Hi Robert,

Can you pls let us know How can we create a rectangle in matrix?.It will be a great help to us if you can send the detail.Currently we are hanging on this issue.We not only need average,we need Count and total also .The Detail is involved expression to convert from minutes(from stored procedure) to Hours:min.When we are trying to Give sum on this field,it is unable to find sum.it's giving last value or first value

We need Multiple sub totals also.

Any help is highly appreciated

Thank you Robert,
Raj Deep.A

|||

we need all the three aggregations at the end of the Report as three rows

Thank you Robert,
Raj Deep.A

|||

i try the method that you mention above but when i try to put it at expression it give me some error. for example:

i declare a column group name as "matrix1_columngroup1"

=iif(InScope(matrix1_columngroup1), .....) but the system return "unrecogized identifier" .

can you please help me on this. thanks.

sara

|||

You need to use double quotes around the group name. You should be passing as string i.e.

=iif(InScope("matrix1_columngroup1"), .....)

|||somebody explain me step by step how to work with the InScope function, and if it's possible with SSRS 2000. I have less experience with Reporting Services. I alreay have the groups and all stuff. Thanks|||

Even I am facing same problem. Can someone please explain step by step. Thanks in advance.

Averages in Matrix

I am new to Reporting Services on SQL Server 2005 and I need help. I have a report that I have create below using matrices.

Qtr 1

Total

2004

110

1005

2005

How do I get and average for the number of account created per week per quarter?

How do I create a percentage of the change of accounts per week per year?

Qtr 1

Total

AVG

2004

110

1005

77.31

2005

5.77

-93.8%

-100.0%

-92.9%

-89.5%

-71.4%

-94.1%

-100.0%

-94.4%

-95.5%

-90.9%

-94.4%

-100.0%

-80.0%

Thanks in Advance.

=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))

Replace "In Cell" with =Sum(Fields!Amount.Value)
Replace "In Subtotal..." with =Avg(Fields!Amount.Value)

-- Robert

|||

Hi Robert,

Can you pls let us know How can we create a rectangle in matrix?.It

will be a great help to us if you can send the detail.Currently we are

hanging on this issue.We not only need average,we need Count and total

also .The Detail is involved expression to convert from minutes(from

stored procedure) to Hours:min.When we are trying to Give sum on this field,it is unable to find sum.it's giving last value or first value

We need Multiple sub totals also.

Any help is highly appreciated

Thank you Robert,

Raj Deep.A

|||

we need all the three aggregations at the end of the Report as three rows

Thank you Robert,

Raj Deep.A

|||

can you please help me on this. thanks.

sara

|||

You need to use double quotes around the group name. You should be passing as string i.e.

=iif(InScope("matrix1_columngroup1"), .....)

Averages in Matrix

I am new to Reporting Services on SQL Server 2005 and I need help. I have a report that I have create below using matrices.

Qtr 1

Total

2004

110

1005

2005

How do I get and average for the number of account created per week per quarter?

How do I create a percentage of the change of accounts per week per year?

Qtr 1

Total

AVG

2004

110

1005

77.31

2005

5.77

-93.8%

-100.0%

-92.9%

-89.5%

-71.4%

-94.1%

-100.0%

-94.4%

-95.5%

-90.9%

-94.4%

-100.0%

-80.0%

Thanks in Advance.

=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))

Replace "In Cell" with =Sum(Fields!Amount.Value)
Replace "In Subtotal..." with =Avg(Fields!Amount.Value)

-- Robert

|||

Hi Robert,

Can you pls let us know How can we create a rectangle in matrix?.It

will be a great help to us if you can send the detail.Currently we are

hanging on this issue.We not only need average,we need Count and total

also .The Detail is involved expression to convert from minutes(from

stored procedure) to Hours:min.When we are trying to Give sum on this field,it is unable to find sum.it's giving last value or first value

We need Multiple sub totals also.

Any help is highly appreciated

Thank you Robert,

Raj Deep.A

|||

we need all the three aggregations at the end of the Report as three rows

Thank you Robert,

Raj Deep.A

|||

can you please help me on this. thanks.

sara

|||

You need to use double quotes around the group name. You should be passing as string i.e.

=iif(InScope("matrix1_columngroup1"), .....)

Tuesday, March 20, 2012

Background Colour of Matrix cells

Thursday, February 16, 2012

Averages in Matrix report

Averages in Matrix

Averages in Matrix

Averages in Matrix

Autonumber field

Blog Archive

About Me