You can also check my previous article on SQL Server: Designing Tablix Report in SSRS 2008
To design this report, let’s open Visual studio 2008 Business Intelligence Studio and create a new Report Server project as shown below –
![clip_image001 clip_image001](http://lh6.ggpht.com/_RHQ2YLJe5_U/Ta0ZFn9CWhI/AAAAAAAAAMg/VDz276ue7Sw/clip_image001_thumb.png?imgmax=800)
Once your project is ready, let’s create a data source which will fetch the data from AdventureWorks database. To fetch the data let’s write the following query –
![clip_image002 clip_image002](http://lh3.ggpht.com/_RHQ2YLJe5_U/Ta0ZTDlpvFI/AAAAAAAAAMo/aUirHP5xN5M/clip_image002_thumb%5B1%5D.png?imgmax=800)
SELECT YEAR(SOH.OrderDate) OrderYear,PC.NAME AS CategoryName,PSC.NAME as SubCategoryName,SUM(UnitPrice) TotalPrice, SUM(OrderQty) TotalOrders
FROM Production.ProductCategory PC INNER JOIN Production.ProductSubcategory PSC ON PC.ProductCategoryID=PSC.ProductCategoryID
INNER JOIN Production.Product P ON PSC.ProductSubcategoryID=P.ProductSubcategoryID INNER JOIN Sales.SalesOrderDetail SOD ON P.ProductID=SOD.ProductID
INNER JOIN Sales.SalesOrderHeader SOH ON SOH.SalesOrderID=SOD.SalesOrderID
GROUP BY YEAR(SOH.OrderDate), PC.Name,PSC.Name
ORDER BY OrderYear,CategoryName,SubCategoryName
If you execute this query, you should get a similar the result as shown below –
![clip_image003 clip_image003](http://lh4.ggpht.com/_RHQ2YLJe5_U/Ta0ZURVNdFI/AAAAAAAAAMw/WjoYyhtW5pk/clip_image003_thumb%5B2%5D.png?imgmax=800)
To add a new data source to our report, go to Solution Explorer. Right click ‘Shared Data Sources’ folder and add a ‘New Data Source’. This will show you a shared data source property window. Name the data source as ‘AdventureWorksDS’ and configure the connection to the AdventureWorks database by clicking the ‘Edit’ button as shown below –
![clip_image004 clip_image004](http://lh4.ggpht.com/_RHQ2YLJe5_U/Ta0ZWM9yt5I/AAAAAAAAAM4/cI0gSdCU9LM/clip_image004_thumb%5B1%5D.png?imgmax=800)
Now let’s add a blank report using our solution explorer. To add a blank report, right click the Report and click on ‘Add New Item’ and choose ‘Report’. Name the report as ‘AdventureWorksYearlySalesChartReport’ as shown below –
![clip_image005 clip_image005](http://lh5.ggpht.com/_RHQ2YLJe5_U/Ta0ZXntZ22I/AAAAAAAAANA/qkcZWub1AP4/clip_image005_thumb.png?imgmax=800)
Now go to ‘View’ menu and click on ‘Report Data’. In the ‘Report Data’ window, add a new dataset as shown below –
![clip_image006 clip_image006](http://lh4.ggpht.com/_RHQ2YLJe5_U/Ta0ZZEDmpxI/AAAAAAAAANI/X4TLBX7_RPE/clip_image006_thumb.png?imgmax=800)
This will show you the ‘Dataset Properties’ window. Name the dataset as ‘YearlySalesDS’ and choose the existing data source by clicking a ‘New’ button from ‘Data Source’ option. This displays a ‘Data Source Properties’ window. Choose ‘Use Shared Data Source Reference’ radio button and choose our data source ‘AdventureWorksDS’ which we have created in the above steps. Then paste the query shown above in a ‘Query’ box as shown below –
![clip_image007 clip_image007](http://lh6.ggpht.com/_RHQ2YLJe5_U/Ta0ZaqwqbmI/AAAAAAAAANQ/UIMuN4E2VPA/clip_image007_thumb%5B1%5D.png?imgmax=800)
Now we are ready to design the report. Drag and drop a ‘Chart’ control from tool box on our report. As soon as you drop the chart control on the report, it will ask you to ‘Select Chart Type’. Make a choice of Column chart and click ‘OK’ as shown below –
![clip_image008 clip_image008](http://lh3.ggpht.com/_RHQ2YLJe5_U/Ta0ZcNmJg2I/AAAAAAAAANY/vo1GdzbBz2I/clip_image008_thumb.png?imgmax=800)
Now click on the Chart and you will see the view of the chart with different axis, as shown below –
![clip_image010 clip_image010](http://lh6.ggpht.com/_RHQ2YLJe5_U/Ta0ZdjriWiI/AAAAAAAAANg/zdNt8xpPiy4/clip_image010_thumb%5B2%5D.jpg?imgmax=800)
Now from the ‘Report Data’ Window drag and drop ‘TotalPrice’ on ‘Drop data fields here’ and drag and drop OrderYear on ‘Drop category fields here’. Now your report will look like this –
![clip_image011 clip_image011](http://lh3.ggpht.com/_RHQ2YLJe5_U/Ta0Ze9RSoEI/AAAAAAAAANo/G-WBs9psL5k/clip_image011_thumb.png?imgmax=800)
Now click on the preview report tab and see your report. It will look similar to the following –
![clip_image012 clip_image012](http://lh6.ggpht.com/_RHQ2YLJe5_U/Ta0Zg-CRplI/AAAAAAAAANw/QwdOKARKst4/clip_image012_thumb.png?imgmax=800)
Now let’s modify the report. First of all change the title of the report to ‘Adventure Works Yearly Sales’. Change the ‘Y’ axis title to ‘Total Sales’ and change ‘X’ axis to ‘Order Year’. Now preview your report and it will look like the one shown below –
![clip_image013 clip_image013](http://lh4.ggpht.com/_RHQ2YLJe5_U/Ta0ZiQ-hLRI/AAAAAAAAAN4/nw6Tf2j_1qs/clip_image013_thumb.png?imgmax=800)
Now right click the ‘Total Price’ and click on ‘Add Calculated Series’ as shown below –
![clip_image014 clip_image014](http://lh3.ggpht.com/_RHQ2YLJe5_U/Ta0Zka_kX6I/AAAAAAAAAOA/X6qnPJKo5YA/clip_image014_thumb.png?imgmax=800)
You will see the ‘Calculated Series Properties’ window. From here, choose ‘General’ section. Under formula, choose ‘Exponential Moving Average’ as shown below –
![clip_image015 clip_image015](http://lh4.ggpht.com/_RHQ2YLJe5_U/Ta0Zljho2hI/AAAAAAAAAOI/TH2wq9K-fOM/clip_image015_thumb.png?imgmax=800)
Now preview your report and it will look like the one shown below –
![clip_image016 clip_image016](http://lh5.ggpht.com/_RHQ2YLJe5_U/Ta0Zm71syAI/AAAAAAAAAOQ/N1C7DsJx4yw/clip_image016_thumb.png?imgmax=800)
Now right click the Chart column and choose ‘3D Effects’. This will show you ‘Chart Area Properties’ window. Check the check box ‘Enable 3D’ and click ‘OK’ button by keeping the other properties default. Preview your chart and this is what you see –
![clip_image017 clip_image017](http://lh4.ggpht.com/_RHQ2YLJe5_U/Ta0ZoFni1iI/AAAAAAAAAOY/shpajxMauzI/clip_image017_thumb.png?imgmax=800)
Summary – In this article, we have seen how to design a Chart report using SQL Server Reporting Services 2008.
No comments:
Post a Comment