Get Started with Chart Development in Reporting Services 2008

Ref: Michael Garrett

With Reporting Services 2008, it is now much easier to produce charts from data in SQL Server, and the variety of charts seems almost limitless. All you need to get started  is a quick step-by-step guide  that tells you the basics and gets you past the stage of creating the first chart. Well, here it is.

One of the greatest rewards of developing any type of report is to transform the vast amounts of business data into useful  information that can support commercial decision-making; producing such  reports as the  performance of an internal process, percentage of an employees’ contribution to overall product sales, or a department’s budget compared to other departments.  It can become a real challenge for the developer of a report to present this vast amount of information properly, because the correct interpretation of data by the user is as important as the data itself.  
One solution is to present the data in an aggregated format so that business users can more easily and quickly digest this information. The chart is a tool designed specifically for the presentation of aggregated data. If it is done properly, it is possible for the  user to quickly grasp the information available as they dash off to their next meeting, without needing to scroll through, and assimilate,  a huge list of tabular results.

Reporting Services 2008 Charts:

Reporting Services 2008 provides an extensive array of charting capabilities which have been greatly improved, and are much more intuitive than before. Reporting Services 2008, now, includes 8 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 parlance, 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. Charts operate just like any other data region. Nonetheless, charts  contain some unique characteristics that separate them from the other data regions.
In this article, you will be creating a basic column chart using data from the AdventureWorks data warehouse database, AdventureWorksDW2008R2. The chart will present AdventureWorks total sales grouped by year and region.

Chart Basics

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

• Category groups are distributed along the x-axis or horizontal axis.
• Values are shown along the y-axis or vertical axis.
• Y-axis values 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.
• Charts cannot be created using the Report Project Wizard. The Report Project Wizard does not support chart or free-form data regions.

Creating the Basic Chart Report:

To create a project, Open BIDS and choose ‘File, New, Project’ which opens the New Project dialog box.

When Reporting Services is installed, a new project type is added that is called ‘Business Intelligence Projects’ (located in the ‘Project types’ pane in the upper-left hand corner). This type includes templates for SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS) and SQL Server Reporting Services (SSRS). The Reporting Services templates are as follows:

• Report Server Project – produces a blank project.
• Report Server Project Wizard – very similar to Report Server Project template except it automatically starts the Report Wizard.
• Report Model Project – creates a metadata description of a database which is used for ad-hoc reporting.

Choose the Report Server Project template, enter a project name, “AdventureWorksReporting” and Click OK. 

BIDS Report Designer opens, displaying the Start Page in the center of the interface. Notice, also, in Solution Explorer, that two folders are created called ‘Shared Data Sources and Reports’.
Your first step in creating any reporting project should be to create a data source. A Reporting Services data source stores information about the connection that is used to access databases or other resources that return data. Reporting Services supports two types of data sources: shared and embedded (also called private). A shared data source is stored independently from the report and can be used in many reports.  An embedded or private data source can be used only within one report so its use is limited to one report. For this reason, it is best to use shared data sources whenever possible.
To 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,  the correct provider, 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 your proper credentials.

Add a Report to the project:

In Solution Explorer, right-click the Reports folder and select ‘Add, New Item’ (note: Do not choose the first item from the context menu, “Add New Report”. This option will start the Report Wizard which does not support the chart data region).

This will display the ‘Add New Item’ dialog box.
Under ‘Templates’, Select ‘Report’ and enter a descriptive report name. Click ‘Add’.

This opens the report in the Report Designer.

Note: Report Layout
Before creating any reports, you need to be aware of how SSRS report layout works. This will help you to build reports that will render and paginate more consistently  across the different SSRS rendering extensions. Rendering is the process of generating a document or image from data. Reporting Services 2008 supports seven different rendering extensions: CSV, Excel, HTML, Image, PDF, Word and XML

The rendering Extension that you use will affect the way that pagination works. Pagination, as it relates to Reporting Services, is the process of dividing information or content into discrete pages. For example, Image, PDF, and Word formats let you precisely control where the pages break, while Excel and HTML do not. To control pagination, you need to know how a report fits onto a page. When you create a report, two design elements are automatically created; Report and Body. Report, the outer most element,  has a property called ‘Page Size’. By default, ‘Page Size’ is set to 8.5  by 11 inches with each margins set to 1in. The Body element is located inside the Report element;  all report content fits onto. The Body element. It has a property called Size which, by default, is set to 6.5 by 2 inches. You can set the Body Size properties interactively by resizing the design surface in the Report Designer using your mouse or by setting the Width and Height property values in the Body’s Properties pane.  If you do expand the Body element’s Width and Height larger than the Report elements Page Size, the Page Size property remains the same. This could spoil the rendering of  the report.

 Apply the following formula to calculate the report page width:      Report Width = Body Width + Report Left Margin + Report Right Margin

You need to verify that the result does not exceed the Report’s Page Size.Width value, otherwise, you will encounter problems when viewing reports in different formats.

To add a chart to a report:

From the Toolbox pane on the left-side of the Designer, drag a chart report item onto the Report Designer. Once you drop the chart onto the design surface, the “Select Chart Type” dialog box appears.

Choose the first chart from the Column chart type. Click OK.

Next, the Data Source Properties dialog box appears. Enter a descriptive name for the data source, choose the “Use shared data source reference” radio button and select the shared data source created previously from the drop-down menu. Click ‘Next’.

This will bring you to the Query Designer dialog box. Keep Text selected in the ‘Command type’ drop-down menu and enter the following query into the Query text box:

SELECT  d.CalendarYear AS Year,

            s.SalesTerritoryCountry AS Region,

        CAST(SUM(f.SalesAmount) AS DECIMAL(18,2)) AS SalesAmount

FROM    dbo.FactInternetSales AS f

        INNER JOIN dbo.DimDate AS d ON f.OrderDateKey = d.DateKey

        INNER JOIN dbo.DimSalesTerritory as s

        ON

        f.SalesTerritoryKey = s.SalesTerritoryAlternateKey

GROUP BY d.CalendarYear,s.SalesTerritoryCountry

To verify the results of the query, click the exclamation point button on the toolbar.

Your results should look similar.
Click Finish, which will take you to the Report’s ‘Report Designer’ interface.

Notice the item directly below the ‘data source ‘ labeled ‘AdventureWorksDataSource’in the Report Data Pane on the left-side of the Designer. This item is called a dataset. A dataset is the means by which reports access data. In Reporting Services, reports never directly reference data sources; reports use datasets instead. Datasets do not contain the actual data, just the query syntax needed to retrieve the data. The fields or the columns in the dataset are listed below the datasets. These fields are what you will use to add data to the chart.
By default, the size of a  chart is 3in wide and height of 2in. To expand the chart’s size, click the chart in the Report Designer and put your cursor over the circle in the upper-left hand corner of the highlighted chart. Once the cursor displays two arrows along both ends, hold down the left click and expand the chart to the desired size (you can also change the width and height of the chart in the Charts Properties pane).
A chart isn’t much use without some data. To add data fields to the chart, click the middle of the chart in the Report Designer.  

The chart data drop down fields will be displayed along the top, bottom, and right side of the highlighted chart. These are the areas into which you will add the dataset fields. With the Report Data pane still open, drag the Year field from the dataset to the Drop category fields here (bottom) drop zone and drag the ‘SalesAmount’ field to the ‘Drop data fields here’ (top) drop zone. Now preview the results by clicking the ‘Preview’ tab in the Report Designer.

The charts is not going to win any design contests but even at this stage the chart contains usable information.
Now, go back to the Design tab and drag the Region Field to the ‘Drop series fields here’ drop zone. This will further subdivide the ‘SalesAmount by Region’ and preview the chart again.

To enhance the chart’s look and feel, you will at least  need to

• remove the x-axis title
• update the y- axis and chart titles
• format the y-axis values as currency
• move the legend beneath the chart to allow for more horizontal space for the columns

To remove the x-axis title, go back to the Design tab. Click in the area directly below the chart on the label titled, “Axis Title” and press Delete. Next, to update the y-axis title, click on the vertically-aligned y-axis label titled, “Axis Title”. Once selected, change the label from “Axis Title” to “Sales Amount”. To update the Chart Title, click on the phrase, “Chart Title”, and enter “Sales Amount by Year/Region”.
To format the y-axis data value as currency, right-click on the y-axis and select ‘Axis Properties…’
 
This displays the ‘Value Axis Properties’ dialog box.
From the ‘Value Axis Properties’ dialog box, select the ‘Number’ tab, select ‘Currency’  from the ‘Category’ List, set the Decimal places as 0 and select the ‘Use 1000 separator(,)’ check box. Then  Click OK.

Finally, in order to move the legend below the chart, right-click the legend and select Legend Properties. From the General tab, change the Layout drop down menu from Auto table to Row and change the legend position button to the lower-right corner button. Click OK.

Preview the Report.

Now build and deploy the chart to Report Manager.

Business Uses:

Once the report is deployed you can export the report to Excel. The chart is rendered as a.png file format inside the Excel Worksheet allowing you to expand the size of the chart without losing much, if any, image quality.

Additionally, business users can copy the image directly from Excel and paste it into a PowerPoint slide, or any other publishing application.

Wrap Up:

I hope I have shown you how easy it is to develop charts in Reporting Services 2008. This article gives step-by-step instructions on the basics of chart development so as to get you up and running. Much more could be written about different aspects of this powerful data region, but once you have broken the barrier of the first report it is much easier to  explore, for yourself ,  the almost endless charting possibilities that are available in Reporting Services 2008.