June 7, 2011 at 11:46 am
SSRS has a sample report called "Sales Trend 2008" that shows a year to year growth. The report is grouped by year then quarter, with a total for each year. That total is then used to calculate the Year to Year growth. The years are shown in ascending order (2003, 2004, 2005). With a little tweaking of the code I was able to sort the years descending and have the %Growth calculate properly.
My problem is trying to port this same example to one of my reports. I need to show MTD and YTD as columns, so the YTD is not being totaled by the report - it is a number pulled from SQL. My problem is that the %Growth calculates based on MTD + YTD for each year. I need the %Growth to be based on YTD only. I also adjusted the report function code to calculate growth as defined by my finance guy:
(CurrentYear - PreviousYear) / PreviousYear
I pasted a sample RDL for a very basic report like I am trying to do below. The datasource is Adventureworks but you don't actually need the AdventureWorks DB. The SQL statement is not pulling from any tables, it is hard-coded to display data like I need for demonstration purposes.
Any ideas on how to calculate %Growth on YTD only and not MTD + YTD ?
Thanks
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition">
<DataSources>
<DataSource Name="AdventureWorks">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>Data Source=(local);Initial Catalog=AdventureWorks</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
<rd:DataSourceID>c1e8b68d-4f36-4d8e-aed7-3a5aa4dd5c34</rd:DataSourceID>
<rd:SecurityType>Windows</rd:SecurityType>
</DataSource>
</DataSources>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="Section">
<DataField>Section</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Period">
<DataField>Period</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Year">
<DataField>Year</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Product">
<DataField>Product</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Qty">
<DataField>Qty</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>AdventureWorks</DataSourceName>
<CommandText>--Finished
--MTD 2010
Select 'Finished' as 'Section', 'MTD' as 'Period'
,2010 as 'Year'
,'ABC' as 'Product'
,5 as 'Qty'
UNION ALL
Select 'Finished' as 'Section', 'MTD' as 'Period'
,2010 as 'Year'
,'DEF' as 'Product'
,10 as 'Qty'
UNION ALL
Select 'Finished' as 'Section', 'MTD' as 'Period'
,2010 as 'Year'
,'GHI' as 'Product'
,20 as 'Qty'
UNION ALL
--YTD 2010
Select 'Finished' as 'Section', 'YTD' as 'Period'
,2010 as 'Year'
,'ABC' as 'Product'
,150 as 'Qty'
UNION ALL
Select 'Finished' as 'Section', 'YTD' as 'Period'
,2010 as 'Year'
,'DEF' as 'Product'
,125 as 'Qty'
UNION ALL
Select 'Finished' as 'Section', 'YTD' as 'Period'
,2010 as 'Year'
,'GHI' as 'Product'
,200 as 'Qty'
UNION ALL
--MTD 2011
Select 'Finished' as 'Section', 'MTD' as 'Period'
,2011 as 'Year'
,'ABC' as 'Product'
,15 as 'Qty'
UNION ALL
Select 'Finished' as 'Section', 'MTD' as 'Period'
,2011 as 'Year'
,'DEF' as 'Product'
,7 as 'Qty'
UNION ALL
Select 'Finished' as 'Section', 'MTD' as 'Period'
,2011 as 'Year'
,'GHI' as 'Product'
,32 as 'Qty'
UNION ALL
--YTD 2011
Select 'Finished' as 'Section', 'YTD' as 'Period'
,2011 as 'Year'
,'ABC' as 'Product'
,120 as 'Qty'
UNION ALL
Select 'Finished' as 'Section', 'YTD' as 'Period'
,2011 as 'Year'
,'DEF' as 'Product'
,105 as 'Qty'
UNION ALL
Select 'Finished' as 'Section', 'YTD' as 'Period'
,2011 as 'Year'
,'GHI' as 'Product'
,210 as 'Qty'
UNION ALL
--InProcess
--MTD 2011
Select 'InProcess' as 'Section', 'MTD' as 'Period'
,2011 as 'Year'
,'ABC' as 'Product'
,3 as 'Qty'
UNION ALL
Select 'InProcess' as 'Section', 'MTD' as 'Period'
,2011 as 'Year'
,'DEF' as 'Product'
,1 as 'Qty'
UNION ALL
Select 'InProcess' as 'Section', 'MTD' as 'Period'
,2011 as 'Year'
,'GHI' as 'Product'
,2 as 'Qty'
UNION ALL
--YTD 2011
Select 'InProcess' as 'Section', 'YTD' as 'Period'
,2011 as 'Year'
,'ABC' as 'Product'
,12 as 'Qty'
UNION ALL
Select 'InProcess' as 'Section', 'YTD' as 'Period'
,2011 as 'Year'
,'DEF' as 'Product'
,10 as 'Qty'
UNION ALL
Select 'InProcess' as 'Section', 'YTD' as 'Period'
,2011 as 'Year'
,'GHI' as 'Product'
,21 as 'Qty'</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<Body>
<ReportItems>
<Tablix Name="matrix1">
<TablixCorner>
<TablixCornerRows>
<TablixCornerRow>
<TablixCornerCell>
<CellContents>
<Textbox Name="textbox3">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value />
<Style>
<FontFamily>Tahoma</FontFamily>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>textbox3</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
<ColSpan>2</ColSpan>
<RowSpan>2</RowSpan>
</CellContents>
</TablixCornerCell>
<TablixCornerCell />
</TablixCornerRow>
<TablixCornerRow>
<TablixCornerCell />
<TablixCornerCell />
</TablixCornerRow>
</TablixCornerRows>
</TablixCorner>
<TablixBody>
<TablixColumns>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
</TablixColumns>
<TablixRows>
<TablixRow>
<Height>0.21in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="textbox2">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Sum(Fields!Qty.Value)</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>textbox2</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
<DataElementOutput>Output</DataElementOutput>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Textbox6">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Code.GetGrowthValue(Sum(Fields!Qty.Value),Previous(Sum(Fields!Qty.Value), "matrix1_Year"))</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<Format>P0</Format>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox6</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
<DataElementOutput>Output</DataElementOutput>
</TablixCell>
</TablixCells>
</TablixRow>
</TablixRows>
</TablixBody>
<TablixColumnHierarchy>
<TablixMembers>
<TablixMember>
<Group Name="matrix1_Year">
<GroupExpressions>
<GroupExpression>=Fields!Year.Value</GroupExpression>
</GroupExpressions>
</Group>
<SortExpressions>
<SortExpression>
<Value>=Fields!Year.Value</Value>
<Direction>Descending</Direction>
</SortExpression>
</SortExpressions>
<TablixHeader>
<Size>0.21in</Size>
<CellContents>
<Textbox Name="Year">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!Year.Value</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontWeight>Bold</FontWeight>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>Year</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>#6e9eca</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixHeader>
<TablixMembers>
<TablixMember>
<Group Name="matrix1_Period">
<GroupExpressions>
<GroupExpression>=Fields!Period.Value</GroupExpression>
</GroupExpressions>
</Group>
<SortExpressions>
<SortExpression>
<Value>=Fields!Period.Value</Value>
</SortExpression>
</SortExpressions>
<TablixHeader>
<Size>0.21in</Size>
<CellContents>
<Textbox Name="Period">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!Period.Value</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontWeight>Bold</FontWeight>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>Period</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>SlateGray</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixHeader>
<DataElementOutput>Output</DataElementOutput>
<KeepTogether>true</KeepTogether>
</TablixMember>
<TablixMember>
<TablixHeader>
<Size>0.21in</Size>
<CellContents>
<Textbox Name="Textbox5">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>% Growth</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontWeight>Bold</FontWeight>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox5</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>SlateGray</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixHeader>
<Visibility>
<Hidden>=Iif(Fields!Year.Value = 2011,True,False)</Hidden>
</Visibility>
</TablixMember>
</TablixMembers>
<DataElementOutput>Output</DataElementOutput>
</TablixMember>
</TablixMembers>
</TablixColumnHierarchy>
<TablixRowHierarchy>
<TablixMembers>
<TablixMember>
<Group Name="matrix1_Section">
<GroupExpressions>
<GroupExpression>=Fields!Section.Value</GroupExpression>
</GroupExpressions>
</Group>
<SortExpressions>
<SortExpression>
<Value>=Fields!Section.Value</Value>
</SortExpression>
</SortExpressions>
<TablixHeader>
<Size>1in</Size>
<CellContents>
<Textbox Name="Section">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!Section.Value</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontWeight>Bold</FontWeight>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Section</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>#6e9eca</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixHeader>
<TablixMembers>
<TablixMember>
<Group Name="matrix1_Product">
<GroupExpressions>
<GroupExpression>=Fields!Product.Value</GroupExpression>
</GroupExpressions>
</Group>
<SortExpressions>
<SortExpression>
<Value>=Fields!Product.Value</Value>
</SortExpression>
</SortExpressions>
<TablixHeader>
<Size>1in</Size>
<CellContents>
<Textbox Name="Product">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!Product.Value</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontWeight>Bold</FontWeight>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Product</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>SlateGray</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixHeader>
<DataElementOutput>Output</DataElementOutput>
<KeepTogether>true</KeepTogether>
</TablixMember>
</TablixMembers>
<DataElementOutput>Output</DataElementOutput>
</TablixMember>
</TablixMembers>
</TablixRowHierarchy>
<RepeatColumnHeaders>true</RepeatColumnHeaders>
<RepeatRowHeaders>true</RepeatRowHeaders>
<DataSetName>DataSet1</DataSetName>
<Top>0.36in</Top>
<Height>0.63in</Height>
<Width>4in</Width>
<Style />
</Tablix>
<Textbox Name="textbox1">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Test Year Growth</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>20pt</FontSize>
<FontWeight>Bold</FontWeight>
<Color>SteelBlue</Color>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.36in</Height>
<Width>5in</Width>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</ReportItems>
<Height>0.99in</Height>
<Style />
</Body>
<Code>Public Function GetGrowthValue(ByVal PreviousValue, ByVal CurrentValue) As Object
If IsNothing(PreviousValue) OR IsNothing(CurrentValue) Then
Return Nothing
Else if PreviousValue = 0 OR CurrentValue = 0 Then
Return Nothing
Else
Return (CurrentValue - PreviousValue) / PreviousValue
End If
End Function</Code>
<Width>5in</Width>
<Page>
<LeftMargin>1in</LeftMargin>
<RightMargin>1in</RightMargin>
<TopMargin>1in</TopMargin>
<BottomMargin>1in</BottomMargin>
<Style />
</Page>
<Language>en-US</Language>
<ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
<rd:ReportID>735c1e36-abe6-459e-9911-12a0110b60fe</rd:ReportID>
<rd:ReportUnitType>Inch</rd:ReportUnitType>
</Report>
June 10, 2011 at 3:13 pm
Here's what finally worked in case anyone else needs it:
This is the Expr for the %Growth textbox
=Code.GetGrowthValue(Sum(iif(Fields!Period.Value="YTD",Cdbl(Fields!Qty.Value),Cdbl(0))),
Previous(Sum(iif(Fields!Period.Value="YTD",Cdbl(Fields!Qty.Value),Cdbl(0))), "matrix1_Year"))
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply