Hi everyone,
Just wondering how to do a 12 mth moving average in a matrix or graph (both would be great!).
Does anyone have a custom function for this, or is there an easier way?
thanks,
99
I'm not sure if you are going to be using an Analysis Services data source, but if so there is a pre-built template you can use for calculating moving averages.
Just open up your Analysis Services project. Then open the cube and select the "Calculations" tab. Now, in the "Calculation Tools" frame on the left, select the "Templates" tab. Expand "Time Series" and select "Moving Average".
Once you get the moving average calculation defined in the Analysis Services, you will be able to add this measure to your matrix or graph.
|||
Hi Joel,
thanks for the reply :-)
I am using a BI project with report project getting data straight from SQL server - no ETL or cubes.
Can't seem to find any templates for the report projects. Any other ideas, or is the analysis services the only option?
cheers,
99
|||
There is an upcoming MSDN article (July/August timeframe) where I will include e.g. a detailed sample and explain how to implement moving averages in RS 2005 with the builtin charts and a custom code function.
I can probably post my sample already next week on this forum (part of it was already shown at TechEd 2006 in Boston last week).
-- Robert
|||
Fantastic!!!
But can you do it in 2000, which is what i'm still using?
If not, then i have a business case to upgrade.
Ta,
99
|||
RS 2000 charts do not support the RunningValue() function - so you won't be able to use my sample on RS 2000.
-- Robert
|||
thanks Robert!
While I'm here, is this also the case for cumulative graphs?
99
|||
Yes, moving and rolling average in charts both need the RunningValue function, which is only supported in RS 2005 charts.
The RunningValue function in general is already supported in list, table and matrix starting with RS 2000.
-- Robert
|||
I attached the RS 2005 sample at the bottom of this posting.
The sample will need some careful study. Quick description of the sample:
* the report runs against the AdventureWorks2005 sample database that comes with SQL Server 2005
* it shows sales data for employees
* the report is parameterized - you select the employee and you get to see a chart with the sales data for that particular employee over a timespan of several years. You will get one column per month ("Sales"). In addition there is a calculated value ("Avg. Sales 3 months") over the previous 3 months showing a moving average over that timespan as a line chart.
The actual moving average calculation is performed in the custom code section of the report. If you want to calculate the moving average over a longer period of time, you just need to modify the queueLength variable.
The sample will be explained in more detail in an upcoming MSDN article.
-- Robert
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="AdventureWorks2005">
<DataSourceReference>AdventureWorks</DataSourceReference>
<rd:DataSourceID>9170a9c9-ef0c-4fe4-88a9-f7414f39857a</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="SalesPersonID">
<DataType>String</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>DataSet1</DataSetName>
<ValueField>SalesPersonID</ValueField>
</DataSetReference>
</DefaultValue>
<Prompt>Sales Person</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>DataSet1</DataSetName>
<ValueField>SalesPersonID</ValueField>
<LabelField>FullName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Chart Name="chart1">
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>TopCenter</Position>
<Layout>Table</Layout>
</Legend>
<Subtype>Plain</Subtype>
<Title />
<Height>5.5in</Height>
<CategoryAxis>
<Axis>
<Title />
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<PointWidth>0</PointWidth>
<Left>0.125in</Left>
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<DataSetName>AdventureWorksSales</DataSetName>
<SeriesGroupings>
<SeriesGrouping>
<DynamicSeries>
<Grouping Name="SalesPerson">
<GroupExpressions>
<GroupExpression>=Fields!SalesPersonID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Fields!FullName.Value</Label>
</DynamicSeries>
</SeriesGrouping>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Sales</Label>
</StaticMember>
<StaticMember>
<Label>Avg. Sales (3 months)</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Top>0.125in</Top>
<PlotArea>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
<Type>Column</Type>
<Width>6.625in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="Year">
<GroupExpressions>
<GroupExpression>=Year(Fields!OrderDate.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
<Label />
</DynamicCategories>
</CategoryGrouping>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="Month">
<GroupExpressions>
<GroupExpression>=Month(Fields!OrderDate.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Format(Fields!OrderDate.Value,"MMM")</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!TotalDue.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BackgroundColor>RosyBrown</BackgroundColor>
<BackgroundGradientEndColor>Firebrick</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
</Style>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=RunningValue(Code.CumulativeQueue(Fields!TotalDue.Value), Sum, "SalesPerson")</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BorderWidth>
<Default>6pt</Default>
</BorderWidth>
<BorderColor>
<Default>Gold</Default>
</BorderColor>
</Style>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
<PlotType>Line</PlotType>
</ChartSeries>
</ChartData>
<Style>
<BackgroundColor>LightSteelBlue</BackgroundColor>
<BackgroundGradientType>HorizontalCenter</BackgroundGradientType>
</Style>
</Chart>
</ReportItems>
<Height>5.75in</Height>
</Body>
<rd:ReportID>cd326200-c624-4180-ad24-bb8ffb49c2d1</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="AdventureWorksSales">
<Query>
<CommandText>SELECT Sales.SalesPerson.SalesPersonID, Sales.SalesPerson.Bonus, Sales.SalesPerson.CommissionPct, Sales.SalesPerson.SalesYTD,
Sales.SalesPerson.SalesLastYear, HumanResources.Employee.ManagerID, HumanResources.Employee.Title, Person.Contact.FirstName,
Person.Contact.MiddleName, Person.Contact.LastName, HumanResources.Employee.EmployeeID, Person.Contact.ContactID,
HumanResources.Employee.ContactID AS Expr3, SUM(Sales.SalesOrderHeader.TotalDue) AS TotalDue, Sales.SalesOrderHeader.OrderDate
FROM Sales.SalesOrderHeader INNER JOIN
Sales.SalesPerson INNER JOIN
HumanResources.Employee ON Sales.SalesPerson.SalesPersonID = HumanResources.Employee.EmployeeID INNER JOIN
Person.Contact ON HumanResources.Employee.ContactID = Person.Contact.ContactID ON
Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.SalesPersonID
GROUP BY Sales.SalesPerson.SalesPersonID, Sales.SalesPerson.Bonus, Sales.SalesPerson.CommissionPct, Sales.SalesPerson.SalesYTD,
Sales.SalesPerson.SalesLastYear, HumanResources.Employee.ManagerID, HumanResources.Employee.Title, Person.Contact.FirstName,
Person.Contact.MiddleName, Person.Contact.LastName, HumanResources.Employee.EmployeeID, Person.Contact.ContactID,
HumanResources.Employee.ContactID, Sales.SalesOrderHeader.OrderDate
HAVING (Sales.SalesPerson.SalesPersonID IN (@.SalesPersonID))</CommandText>
<QueryParameters>
<QueryParameter Name="@.SalesPersonID">
<Value>=Parameters!SalesPersonID.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>AdventureWorks2005</DataSourceName>
</Query>
<Fields>
<Field Name="SalesPersonID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>SalesPersonID</DataField>
</Field>
<Field Name="Bonus">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>Bonus</DataField>
</Field>
<Field Name="CommissionPct">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>CommissionPct</DataField>
</Field>
<Field Name="SalesYTD">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>SalesYTD</DataField>
</Field>
<Field Name="SalesLastYear">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>SalesLastYear</DataField>
</Field>
<Field Name="ManagerID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ManagerID</DataField>
</Field>
<Field Name="Title">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Title</DataField>
</Field>
<Field Name="FirstName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>FirstName</DataField>
</Field>
<Field Name="MiddleName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>MiddleName</DataField>
</Field>
<Field Name="LastName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>LastName</DataField>
</Field>
<Field Name="EmployeeID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>EmployeeID</DataField>
</Field>
<Field Name="ContactID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ContactID</DataField>
</Field>
<Field Name="Expr3">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>Expr3</DataField>
</Field>
<Field Name="TotalDue">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>TotalDue</DataField>
</Field>
<Field Name="OrderDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>OrderDate</DataField>
</Field>
<Field Name="FullName">
<Value>=Fields!FirstName.Value & " " & Fields!LastName.Value</Value>
</Field>
</Fields>
</DataSet>
<DataSet Name="DataSet1">
<Query>
<CommandText>SELECT DISTINCT Sales.SalesPerson.SalesPersonID, Person.Contact.FirstName, Person.Contact.LastName
FROM Person.Contact INNER JOIN
HumanResources.Employee ON Person.Contact.ContactID = HumanResources.Employee.ContactID INNER JOIN
Sales.SalesPerson ON HumanResources.Employee.EmployeeID = Sales.SalesPerson.SalesPersonID
WHERE (Sales.SalesPerson.SalesYTD > 0) OR
(Sales.SalesPerson.SalesLastYear > 0)</CommandText>
<DataSourceName>AdventureWorks2005</DataSourceName>
</Query>
<Fields>
<Field Name="SalesPersonID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>SalesPersonID</DataField>
</Field>
<Field Name="FirstName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>FirstName</DataField>
</Field>
<Field Name="LastName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>LastName</DataField>
</Field>
<Field Name="FullName">
<Value>=Fields!FirstName.Value & " " & Fields!LastName.Value</Value>
</Field>
</Fields>
</DataSet>
</DataSets>
<Author>Robert M. Bruckner, Microsoft</Author>
<Code> Private queueLength As Integer = 3
Private queueSum As Double = 0
Private queueFull As Boolean = False
Private queue As New System.Collections.Generic.Queue(Of Double)
Public Function CumulativeQueue(ByVal currentValue As Double) As Object
Dim removedValue As Double = 0
If queue.Count >= queueLength Then
removedValue = queue.Dequeue()
End If
queueSum += currentValue
queueSum -= removedValue
queue.Enqueue(currentValue)
If queue.Count < queueLength Then
Return Nothing
ElseIf queue.Count = queueLength And queueFull = False Then
queueFull = True
Return queueSum / queueLength
Else
Return (currentValue - removedValue) / queueLength
End If
End Function</Code>
<Width>6.875in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
