Create Business Reports using SSRS

Create Business Reports using SSRS

Raymond Tang Raymond Tang 0 1487 0.37 index 6/8/2014

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’

https://api.kontext.tech/resource/15d42c0c-40dd-50a1-be4a-e2d58b9cb5cd

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:

https://api.kontext.tech/resource/90a742c9-fb20-582e-aa0b-ffedf384d047

Choose the Table Layout:

https://api.kontext.tech/resource/c6470010-acd1-5899-b975-bf692b4e8978

Choose the Table Style: choose Corporate

Report Name: input SalesPerformanceDashboard

Then you can preview the report in Visual Studio.

https://api.kontext.tech/resource/083de5be-65cf-5299-8401-e86c043f6864

  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.

https://api.kontext.tech/resource/a982a7c6-0d58-548f-a196-69524b858e20

Add another Column chart to show sales by products.

https://api.kontext.tech/resource/9aacbc8c-2fbb-5aa6-92a8-9b4fb4c40d10

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

The dashboard looks like the following screenshot:

https://api.kontext.tech/resource/dfc73641-2249-5f9e-a7a6-9021fc03967a

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

plot

Join the Discussion

View or add your thoughts below

Comments