Create Business Reports using SSRS

access_time 7 years ago visibility968 comment 0

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’

image

Credentials: using the SQL Server Account.

3) 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:

image

Choose the Table Layout:

image

Choose the Table Style: choose Corporate

Report Name: input SalesPerformanceDashboard

Then you can preview the report in Visual Studio.

image

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

image

Add another Column chart to show sales by products.

image

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

The dashboard looks like the following screenshot:

image

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

info Last modified by Raymond 10 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn or Twitter.

Want to publish your article on Kontext?

Learn more

More from Kontext

Pandas DataFrame Plot - Scatter and Hexbin Chart
visibility 298
thumb_up 0
access_time 10 months ago

 In this article I'm going to show you some examples about plotting scatter and hexbin chart with Pandas DataFrame. I'm using Jupyter Notebook as IDE/code execution environment.  Hexbin chart is a pcolor of a 2-D histogram with hexagonal cell and can be more informative compared ...

Create Business Reports using SSRS
visibility 968
thumb_up 0
access_time 7 years ago

Serial: An Introduction to SQL Server Features 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 ...

Power BI Analytics - Connect to DBMS and Card
visibility 520
thumb_up 0
access_time 3 years ago

Power BI supports connecting to most of the DBMS databases such as SQL Server, Oracle, Teradata, MySQL, DB2, Sybase, Snowflake, Google BigQuery, Impala and etc. This page summarizes the steps to connect to SQL Azure and to create the following part of the sample dashboard of this series ...