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?
Posted by Adler at 10:22 PM
Labels: , , , , , , , , , , , , , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)