Reporting Services 2012 Charts:
Reporting Services 2012 provides an extensive array of charting capabilities. Reporting Services 2012, now, includes numbers of different chart types Column, Line, Shape, Bar, Area, Range, Scatter, Polar.
Reporting Services Chart Data Regions:
Because charts contain data, they are considered, in Reporting Services, to be a data region. A chart data region operates just like any other data region and uses groups, query parameters, and filters much the same way as a table, list, or matrix.
The chart data of Reporting Services is organized into three main areas: values, category groups and series groups.
- Generally Category groups are distributed along the x-axis or horizontal axis and value are shown along y-axis.
- From the two column value one must be a numeric data type.
- A chart, like any other data region, is attached to one particular dataset.
- You can group data within each axis.
- At minimum, a chart should have one aggregated field for the value and one grouped field for the category.
Step-1: Creating the Basic Chart Report:
To create a project, Open BIDS (SQL Server Business Intelligence Development Studio) and choose ‘File, New, Project’ which opens the New Project dialog box.
Choose the Report Server Project template; enter a project name, “SSRSChartChapter-1” and Click OK. BIDS Report Designer opens.
Create a shared data source, in the Solution Explorer pane, right-click the ‘Shared Data Sources ‘folder and select ‘Add New Data Source’ from the context menu. The ‘Shared Data Source Properties’ dialog box displays.
Enter a descriptive name for the new datasource here we took “DSNorthwind”, the correct provider here we took “Microsoft SQL Server”, and then click the ‘Edit…’ button to create the data source connection string. The ‘Connection Properties’ dialog box appears which is used to create a connection string. Enter proper credentials.
We took Server name as 127.0.0.1 represents ‘localhost’ and SQL Server authentication and check the checkbox ‘Save my Password’ and in option ‘select or enter Database name’ we selected ‘Northwind’ now test the connection with button Test Connection and then click on OK.
The control will return to ‘Shared Data Source Properties’ windows , now the connection string will show some values as given on the screen
Data Source=127.0.0.1;Initial Catalog=Northwind
Click on ok , a Shared Data source with name ‘DSNorthwind.rds’ will be created as shown
Step-2: Add a New Report to the project “SSRSChartChapter-1”.
In Solution Explorer, right-click the Reports folder and select ‘Add, New Item’ , please Do not choose the “Add New Report” as this option will start the Report Wizard which does not support the chart data region as shown below.
This will display the ‘Add New Item’ dialog box. Under ‘Templates’, Select the ‘Report’ and enter a descriptive report name. Click ‘Add’.
A report data region will be created as shown.
Step-3: To add a chart to a report:
Before adding a chart we have to set the Data Source properties of the manually created report “Chapter-1-Creating Basic Chart Using BIDS”. For that go to View>Report Data from the main menu bars as shown in the image.
A Report Data Properties windows will appear right click on ‘Add Data Source’ form the context menu
Choose option “use shared Data Source reference” option and choose the Shared Data Source “DSNorthwind” created in Step-1 and click OK button. Name the Data Source as ‘DSNorthwindShared’ and select Query Type as text and paste the following query to Query windows as shown and click ok button.
select d.CategoryName ,year(OrderDate) as OrderYear,sum(Quantity) as TotQtyOrd
from Orders a inner join [Order Details] b on a.OrderID=b.OrderID inner join products c
on b.ProductID=c.ProductID inner join Categories d on c.CategoryID=d.CategoryID
group by d.CategoryName ,year(OrderDate)
From the Toolbox pane on the left-side of the Designer, drag a “Chart” tool onto the Report Designer as shown.
Once you drop the chart onto the design surface, the “Select Chart Type” dialog box appears.
Choose the any chart form column chart, we had chosen “3D-Clustered Calendar” and click OK Button, the following screen will appear with default Chart.
Now resize the chart by selecting its outer boundaries and dragging to the page for increasing the size. Now open already Created DataSet by clicking on main menu View>Report Data. A following screen will appear showing DataSets and chart in Design view now click on middle of the Chart a “Chart Data” Properties is appeared as shown.
Now, we need to specify what data or values you want to analyze in the chart. To do that, select the chart in the report designer; this will make the Chart Data dialog box appear. Click on the tiny “+” sign for adding values in the Values box and for adding category in the Category box.
In our example add “TotQtyOrd” to first box and “CategoryName” in the next box i.e Category Groups and change the Horizontal ‘Axis Title’ to “Product Categories” and Vertical ‘Axis Title’ to “Quantity Ordered” and the ‘Chart Title’ to “Chart Showing Category wise Product Ordered” and change the Font Properties of all these as desired and click on Preview button.
The above Chart will appear , but not showing the exact Quantity Ordered so again go to the design view and right click on can Chart’s Bar and choose “Show Data Lablel”.
Now run the report again the following screen will appears showing Chart .
Step-4: Further Change can be done also to the existing chart and one more column value from DataSet i.e “OrderYear” can be included. To the activity just create another copy of “Chapter-1-Creating Basic Chart Using BIDS.rdl” and do the changes in that copy only as shown.
And again past the copied clipboard for that right click on “SSRSChartChapter-1” project as shown and click on paste another copy of “Chapter-1-Creating Basic Chart Using BIDS.rdl” will be created with name “copy of Chapter-1-Creating Basic Chart Using BIDS.rdl”, rename this file to “Chapter-1-Creating Basic Chart Using BIDS-Detail.rdl” and start working on this file.
Now open the chart “Chapter-1-Creating Basic Chart Using BIDS-Detail.rdl” in the design view and click on the middle of the chart to bring “Chart Data” Properties window. Now remove the “CategoryName” from “Category Group” of “Chart Data” and Drag “CategoryName” and “OrderYear” from DataSet “DSNorthwindShared” and drop to “Series Group” and “Category Group” respectively as shown.
As now on x-axis “OrderYear” value will come with “CategoryName” increase the width of the Chart to maximum extent to accommodate all Product Categories and Yearwise Order comparison and change the Header label of the chart to “Chart Showing Category and Year wise Product Ordered” and for making difference change the Background color of the chart by selecting the chart and selecting Background Color form the toolbar as shown in the image. and preview the chart.
Preview the chart, this completes. “Chapter-1-Creating Basic Chart Using BIDS-Detail.rdl”
Deployment of Chart and calling it from web application is similar to that of Reports.