Kontext Platform

Create Business Reports using SSRS

2014-06-08 plot

Serial: An Introduction to SQL Server Features

Case Scenario

After the data is loaded into the database, reports can be built using SSRS (SQL Server Reporting Service).

The general requirements for in this scenario are:

  • Sales performance in different areas
  • Sales amount for different products
  • Reports can be subscribed and exported to different formats (Excel, PDF, Word, CSV..)
  • Reports are accessible through browsers (IE, Chrome, Firefox…)

Create SSRS Report

  1. Create SSRS project ‘SSRS-Sample’

  2. Create Shared Data Source ‘SalesDS’

/project/allsqlserver/resources/15D42C0C-40DD-50A1-BE4A-E2D58B9CB5CD.webp

Credentials: using the SQL Server Account.

  1. Use Report Wizard to create one new report ‘SalesPerformanceDashboard’

Select the Data Source: choose the shared data source ‘SalesDS’

Design the Query: use the following query

SELECT        Areas.AreaName, Products.ProductName, Products.Color, SUM(Sales.Amount) AS Amount
FROM            Areas INNER JOIN
                         Sales ON Areas.AreaId = Sales.AreaId INNER JOIN
                         Products ON Sales.ProductId = Products.ProductId
Group BY   Areas.AreaName, Products.ProductName, Products.Color

Select the Report Type: choose tabular

Design the Table:

/project/allsqlserver/resources/90A742C9-FB20-582E-AA0B-FFEDF384D047.webp

Choose the Table Layout:

/project/allsqlserver/resources/C6470010-ACD1-5899-B975-BF692B4E8978.webp

Choose the Table Style: choose Corporate

Report Name: input SalesPerformanceDashboard

Then you can preview the report in Visual Studio.

/project/allsqlserver/resources/083DE5BE-65CF-5299-8401-E86C043F6864.webp

  1. Add charts into the report.

Add chart form Toolbox into the report design area and name is as ‘Area Sales’. Select the report type Pie.

/project/allsqlserver/resources/A982A7C6-0D58-548F-A196-69524B858E20.webp

Add another Column chart to show sales by products.

/project/allsqlserver/resources/9AACBC8C-2FBB-5AA6-92A8-9B4FB4C40D10.webp

Add the third Pie chart to show sales by Color preferences.

The dashboard looks like the following screenshot:

/project/allsqlserver/resources/DFC73641-2249-5F9E-A7A6-9021FC03967A.webp

SSRS Subscriptions

SSRS supports reporting subscriptions which deliver reports through Email, Shared Folder, SharePoint… It can be configured with schedules and data-driven events.

Find out more at <http://msdn.microsoft.com/en-us/library/ms159762.aspx>.