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
Create SSRS project ‘SSRS-Sample’
Create Shared Data Source ‘SalesDS’
https://api.kontext.tech/resource/15d42c0c-40dd-50a1-be4a-e2d58b9cb5cd
Credentials: using the SQL Server Account.
- 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
- 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>.