Chapter-1-Creating Basic Chart Using SQL Server Business Intelligence Development Studio

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.

Chart Basics

The chart data of Reporting Services is organized into three main areas: values, category groups and series groups.

  1. Generally Category groups are distributed along the x-axis or horizontal axis and value are shown along y-axis.
  2. From the two column value one must be a numeric data type.
  3. A chart, like any other data region, is attached to one particular dataset.
  4. You can group data within each axis.
  5. At minimum, a chart should have one aggregated field for the value and one grouped field for the category.



Download Full Tutorial for Chapter-1-Creating Basic Chart Report in PDF format



Download Source Code for Chapter-1-Creating Basic Chart Report

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.

Create New Report Server Project
Create New Report Server Project

Choose the Report Server Project template; enter a project name, “SSRSChartChapter-1” and Click OK. BIDS Report Designer opens.

Choose the Report Server Project template
Choose the Report Server Project template

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.

Create a shared data source
Create a shared data source

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.

Provide Connection properties
Provide Connection properties

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

shared data source properties

Click on ok , a Shared Data source with name ‘DSNorthwind.rds’ will be created as shown

created shared data source

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’.

Select Report

A report data region will be created as shown.

Report data region
Report data region

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.

set the Data Source properties

A Report Data Properties windows will appear right click on ‘Add Data Source’ form the context menu

add data source

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)

Choose data source and create query

From the Toolbox pane on the left-side of the Designer, drag a “Chart” tool onto the Report Designer as shown.

chart type

Once you drop the chart onto the design surface, the “Select Chart Type” dialog box appears.

Select Chart Type

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.

chosen 3D-Clustered Calendar
Choose 3D-Clustered Calendar


 



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.

Chart Data Properties

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.

Chart Showing Category wise Product Ordered
Chart Showing Category wise Product Ordered

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”.

Chart Showing Category wise Product Ordered
Chart Showing Category wise Product Ordered

Now run the report again the following screen will appears showing Chart .

Report preview

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.

create copy of basic ssrs chart

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.

paste basic chart using ssrs

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.

series group and category group

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.

change the Background color

Preview the chart, this completes. “Chapter-1-Creating Basic Chart Using BIDS-Detail.rdl”

report preview

Deployment of Chart and calling it from web application is similar to that of Reports.



3 thoughts on “Chapter-1-Creating Basic Chart Using SQL Server Business Intelligence Development Studio

  1. HI,
    Nice article i love your all article.
    I have one urgent requirement to implement background color in Axis level
    E.g.
    I have a Bar char graph in this chart i need to implement/Set background color of X-Axis And Y-Axis plot.
    I search but no luck please help or suggest accordingly.

    • Dear Vinay Ji, thanks for liking our pages on the post “CREATING BASIC CHART” , Please clarify more on your requirement if possible send us some dummy image on you requirement so that we can work on it and provide you the exact solution.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

fourteen + 4 =