I'd like to be able to produce a chart of, let's say, stock level (y axis) by date (x axis) for 2004 (series 1) and the same for 2005 (series 2). However, I just don't know how to do it. I guess I need to overlay one chart with another or something but just can't think my way around the problem. Trouble is, if I do a straight chart, the x axis will start at Jan 1st 2004 and end at December 31st 2005, but I want to be able to compare year on year.
Does anyone understand my question or am I just jabbering rubbish! !
Year on year comparison chart
Hot Topic
- cannot login to SQLExpress 2005 help please!!
- Raw File Destination Create Always error
- Simulate ROW_NUMBER () in SQL 2000
- help for starting SQL Express Manager 2005
- Microsoft SQL Server, Error: 233
- Errors when attempting to use DataReader destination
- How to receive files in FTP task
- case statement problem
- Local Application Launch
- import from Access to SQL, not knowing the table format
SQL Server
- Restoring a primary log shipping database - will it ship ?
- Diagramming Error?
- BUG: Integration Services Project-can't click on "save Copy of Package As..."
- xml to relational tables
- Error Calling SQLCLR UDF
- Using CASE statement within GROUP BY
- SQL DataReader Mischief ! HELP !
- Dimensions with complex hierarchies ????
- sql 2005 SP1 Setup Failed
- What SQL query can be used to determine the primary key of a given table?
Year on year comparison chart
Arpan Biswas
I've been having some thoughts about how to do it and have come up with a couple of ideas. I could use a string concatenation of the month followed by the year (e.g. 0217 for 17th Feb) and then use that to group on the x axis. Another idea was to return from the query the number of days since the 1st Jan in that year (using "DATEDIFF(DAY, '01-01-' + CAST(YEAR(se_date) AS CHAR),se_date)" and then group on that.
If you have anything more elegant than my ugly suggestions I would be delighted to know.
Thanks,
Tim
Routb
You have to make dimensions and stock cube. Then you'll could answear all your questions simplier and faster.
I hope it helps you.
Graham Byrne
You can perform a year over year comparison within the same chart. For instance, you would have a category grouping just based on the month, e.g. =Month(Fields!OrderDate.Value), and a series grouping based on the year, e.g. =Year(Fields!OrderDate.Value).
Here is a sample (you need to copy & paste it from the posting and save it as .rdl file):
< 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>
<Chart Name="Sales">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style />
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<Color>Brown</Color>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Pastel</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitPrice.Value * Fields!Quantity.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BorderWidth>
<Default>6pt</Default>
</BorderWidth>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Marker />
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>100</PointWidth>
<Type>Line</Type>
<Title>
<Caption>Sales</Caption>
<Style>
<FontSize>14pt</FontSize>
<FontWeight>700</FontWeight>
</Style>
</Title>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="Sales_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Month(Fields!OrderDate.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Month(Fields!OrderDate.Value)</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=MonthName(Month(Fields!OrderDate.Value))</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>6.125in</Height>
<SeriesGroupings>
<SeriesGrouping>
<DynamicSeries>
<Grouping Name="Sales_SeriesGroup1">
<GroupExpressions>
<GroupExpression>=Year(Fields!OrderDate.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
<Label />
</DynamicSeries>
</SeriesGrouping>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Sales</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundGradientEndColor>White</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<MinorTickMarks>Outside</MinorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>6.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>da5964d0-11a7-4e51-9b22-cc4fa55fdd7a</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT [Order Details].UnitPrice, [Order Details].Quantity, Orders.OrderDate
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>bc811835-2302-4f9e-9c89-a99d4d3f5fd2</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>