Business Intelligence Reporting Services Project: Design, Deployment and Subscription

CHAPTER 1: CREATING A REPORT SERVER PROJECT

Objectives:

This chapter aims to enable you to:

1. Create a Report Server Project

2. Design a Server-side table report 

3. Apply formatting to the report

4. Add calculation and data grouping to the report

Introduction

In this module, you will learn how to create a basic table report using the Report Designer of the SQL Server Data Tools.  The data to be presented on the report will be retrieved from the AdventureWorks database, which can be downloaded from Microsoft’s website.

The workarounds include creating a report project, creating a connection to the database, writing the query to retrieve the data, adding a table on the report, specifying the columns to be shown, grouping, calculating and previewing the report

.For you to proceed, make sure that your system has the following installed:

1. SQL Server Database Engine

2.  SQL Server Reporting Services (native mode)

3.  AdventureWorks database     

4.  SQL Server Data Tools with the SQL Server Reporting Services     

Create a Report Server Project

1. From the Start menu of Windows, click Microsoft SQL Server 2012 then SQL Server Data Tools
2. In SQL Server Data Tools, click File, New, Project

.

3. From the Installed Templates, click Reporting Services under Business Intelligence

4. Click Report Server Project

.

5. In the Name box, type SampleReportProject

6. Click OK

Create a Report File in the Report Server Project

1. Right-click Reports from the Solution Explorer

2. Click Add > New Item… from the context menu

.

3. Click Report from the list

4. Type InternetSales.rdl in the Name box

5. Click Add

.

Setup a Data Source

After creating a Report Server Project with one report definition file, the next task is to setup a data source that will allow you to pull the required data from the AdventureWorks database.

Create a Data Source

1. From the Report Data window, right-click Data Sources > Add Data Source...

.

2.  In the Name box, type dsAdventureWorks

3.  Click the Edit… button

4.  Click the Server Name dropdown list and select your SQL Server

.

5. Select AdventureWorks as your database

.

6. Click Test Connection.  If everything went well, you should be able to read this message:

.

7.  Click OK.

8.  Switch to Credentials page, make sure that Use Windows Authentication (integrated security) is selected.

9.  Click OK

Setup a Data Set

1. From the Report Data window, right-click Data Set…

.

2.  Type dsetAdventureWorks in the Name box

3.  Select Use a dataset embedded in my report

4.  Select your data source dsAdventureWorks from the list

.

5. Click Query Designer… The following window opens.

.

6.  Click the button with +  to add the table(s) that you need in the report

 7.  Select SalesOrderHeader table from the list

 8.  Select the fields [SalesOrderID, OrderDate, OnlineOrderFlag, Subtotal, TaxAmt, Freight, TotalDue]

 9.  Under the Filter column of the OnlineOrderFlag field, type 1.  This means that only those rows whose value of OnlineOrderFlag is True will be selected.

 10. Uncheck the Output option of the OnlineOrderFlag.  This means that we don’t want to view this column.  We just used it for filtering.

.

11.  Click OK. You will return to the previous screen.

 12.  Click OK again.  This is how your Report Window should look like.

.

Adding a Table to the Report

To display the records in a list format, a table is required.

1.  Double-click Table from the Toolbox.  A table will be created on the report.

.

2. Drag the table on your desired location on the report

.

3.  Insert a column by right-clicking the table’s last column > Insert Column > Right.  Repeat this to add 3 more columns

.

4.  Click the Data cell of the table’s first column and select SalesOrderID from the list.  Repeat this step to show the OrderDate in the second column, Subtotal in the third column, Freight in the fourth column and TaxAmt in the fifth column.  Leave the sixth column blank

.

5. Click the Preview tab to see how your report initially looks

.

Apply Some Formatting to the Report

Order Date column includes the time which is not actually required while Sub Total, Freight and Tax Amt columns include 4 decimal points where most of the time, only 2 are required.

1.  Switch to the design view by clicking the Design tab

2.   Right-click [OrderDate] field > Text Box Properties…

3.  Click Number > Date > 31/01/2000

.

4.  Click OK.

5.  Right-click [SubTotal] field > Text Box Properties…

6.  Click Number > Number. By default, decimal places is set to 2.

7.  Tick Use 1000 Separator (,) by clicking it.

8.  Click OK.

9.  Repeat from 5 to 8 to do the same for [Freight] and [TaxAmt].

10.  Select the header row by clicking the row header button

.

11.  Apply Bold, Center and change the font size to 11pt

.

12.  Click the Preview tab to see how the data look in the report after formatting.

As shown in the following screenshot, the report looks better now than it was.

.

Adding Calculation and Data Grouping

Add calculation:

The last column of the table is intended for the Total which is the sum of SubTotal, Freight and TaxAmt columns.

1.       Right-click the empty cell of the last column > Expression…The Expression editor shows.

2.       In the Category, click Fields(dsetAdventureWorks)

.

3.  Double-click SubTotal in the list of Values.  The field will be shown in the Set expression for: Value box

.

4. Click inside the Set expression for: Value box and type +

 5.  Double-click Freight in the list of Values.  The expression is updated as shown.

.

6. Repeat the steps to include TaxAmt.

.

7.  Type Total as the column header for this calculated field

.

8.  Format the calculated field <<Expr>> similar to that of Subtotal’s.

9. Click the Preview tab.  Notice that the totals have been easily calculated and shown.

.

Add Data Grouping

 Let us group the records according to the OrderDate column.

 1.  Switch to Design view

2.  Right-click (Details) > Add Group > Parent Group. The Tablix Group box shows

.

3.  From the Group by box, select [OrderDate]

4.  Tick Add group footer

.

5.  Click OK

6.  Right-click the Order Date column header > Delete Columns

.

7.  Format the new [OrderDate] column Date > 31/01/2000

8.  Preview the report.  

Notice that records are now grouped according to OrderDate.

.

Add a Sub-total for each Order Date and a Grand Total

1.       Right-click the [SubTotal] cell > Add Total

.

2.  Preview the report

.

3.  Switch to Design view

4.  Right-click [Sum(SubTotal)] field > Add Total 

Notice that a new [Sum(SubTotal)] has been created under it labelled with “Total”

.

5.   Preview the report

6.  Go to last page of the report and scroll down until you will see the last row which is the Total

.

CHAPTER 2. DEPLOYING THE SERVER-SIDE REPORT (RDL) TO THE REPORT SERVER

Objectives:

This chapter enables you to:

1.  Launch the Report Server’s interface

2.  Configure the Report Server

3.   Deploy a report to the Report Server

Introduction

Just like a web page, a report can also be deployed to and access from the Report Server.  This is made possible through the SQL Server’s Reporting Services.  This chapter will walk you through Report Server configuration and report deployment.

Opening the Report Services Configuration Manager

1. Start the Reporting Services Configuration Manager from the Configuration Tools folder of Microsoft SQL Server 2012

.

2. Click Connect

.

3. Click Report Manager URL tab and click the URL of the Report Server. This should open the Report Server web interface.  Note: Internet Explorer browser is highly recommended for this.

.

If you've seen the following screen, it means that the Report Server is running and ready to host report files.

.

Configuring the Reporting Services

If the Report Server is running, do the following configurations:

Report Manager Folder Settings

1. In Report Manager, on the Home page, click Folder Settings.

2. In the Folder Settings page, click Security.

.

3. Click New Role Assignment.

.

Note: Replace HPi5\i5 with your own HostName\UserName

4. In the Group or user name field, type your Windows user account in this format:

     <domain>\<user> 

5. Tick the Role(s) to be assigned to the user. 

6. Click OK

.

Report Manager Site Settings

1.  Open your browser with administrative privileges and browse to report manager, http://<server name>/reports

2. Click Site Settings in the upper corner of the Home page.

Note: If you do not see the Site Settings option, close and reopen your browser and browse to report manager with administrative privileges.

.

3. Click Security

.

4. Click New Role Assignment

5.  In the Group or user name field, type your Windows user account in this format: <domain>\<user>.

6.  Select System Administrator.

.

7.  Click OK.

8.  Close Report Manager.

9.  Re-open Report Manager in Internet Explorer, without using Run as administrator.

Deploying the Report to Report Server

1.  Open the Report Project to be deployed using the SQL Server Data Tools

2. From the Solution Explorer, Right-click the Report Project and click Properties

3.  In the Property Pages (as shown below), specify the TargetServerURL (address of the Report Server). In this example, http://localhost/reportserver is used because the Report Server is running in the local computer.  Otherwise, “localhost” should be replaced by the actual host name or IP address.

.

4. Click OK

5. From the Solution Explorer, right-click the shared data source used by the report and click Deploy

.

You should be able to read a message in the Output window similar to what is shown below

6.  Right-click the report file (.rdl) from the Solution Explorer, and click Deploy.  Similar message should be shown.

.

7.  Switch to the Web Browser where Report Server interface is shown and click Home at the upper right corner of the screen

You should be able to see two (2) folders added as shown above.  This means that the data source and report files were successfully deployed

8. Click the Report Project (e.g. Report Project 3) folder. You should see the report file that is stored inside. 

.

9. Click the Report file to see the output.

.

.

You've just deployed to and opened the report from the Report Server.  The chapter will cover the creation of Data-driven subscription.

CHAPTER 3: SETTING-UP A DATA-DRIVEN REPORT SUBSCRIPTION

Objectives

This chapter will enable you to configure the Report Server to achieve the following:

1. Setup a Data-driven Subscription

2. Generate and Send the Report of various File Types to the Desired User based on a Schedule

Introduction

After deployment, the report file is now being hosted by the Report Server.  Such report can now be delivered automatically to the target recipients through File Share or e-Mail based on a schedule or timing.   This job will be executed by another SQL Service known as the SQL Server Agent. 

Configure the Data Source

1. Click the Data Source folder to view the report file stored inside

2. Click Manage from the context menu of the data source file

.

3. Configure the data source as shown below:

.

a. Select the option “Credentials stored in the report server"

b. Type the User Name (e.g. hpi5\i5  where hpi5 is the domain and i5 is the user name)

c. Type the password of the user

d. Tick “Use as Windows Credentials when connecting to the data source”

e. Click Test Connection button.  You should be able to read the message “Connection created successfully.”

Starting the SQL Server Agent

This service must be started before creating a Data-driven subscription.  Subscription is actually a job that will be executed by the SQL Server Agent based on a schedule.  

From the SQL Server Management Studio, you should see a similar figure as shown below if the SQL Server Agent is running.

.

To start the service if it is stopped:

1. Log-in to the desired Database Engine through the SQL Server Management Studio

2.  Right-click SQL Server Agent

3.  Click Start

4.  Confirm your action by clicking Yes from the message box

Creating a Data Source (table) for the Recipients of the Report

1.  In the SQL Server database used by the report, create a table with the following structure and data:

.

2.  Enter the data as shown below

.

Notice that under the Format column, PDFs are used.  This means that the user will receive the report in PDF format.  If desired, Excel and Word can also be used as formats.

Creating a Data-driven Subscription

1.  From the context menu of the report file, click Manage

.

2. Click New Data-driven Subscription

.

Step 1

a.  Type a description for the subscription (e.g. Sales Report)

b.  Specify how recipients are notified (this is the delivery method). Choose “Windows File Share”.  This method will generate a file (e.g. PDF) from the report that can be accessed by the target users.

c.  Click Next

.

Step 2

a.  Type the Connection string (AdventureWorks2012 is the name of the database)

b.  Type the user name and password

c.  Tick “Use as Windows……….”

d.  Click Next

.

Step 3

a.  Write the SQL statement to get the required recipients’ data from the Subscribers table

b.  Click Validate.  If your SQL statement is valid, you should be able to see the message “Query validated successfully".  If not, check what went wrong in your query (e.g. syntax, table name)

c.  Click Next

.

Step 4

.

.

Step 5

Just click Next 

.

Step 6

a.  Select “On a schedule created for this subscription”

b.  Click Next

.

Step 7

Note: Step 7 cannot be completed if the SQL Server Agent is not running

a.  Create the schedule by specifying the day(s) and start time.  The schedule below means that the report processing will be done every Sunday, Wednesday and Friday at 3PM.

.

b. Click Finish.  You should be able to see the following screenshot:

.

After the processing done by the Report Server, you should see the report files generated and saved in the shared folder.  The number of report files should be the same with the number of records in the Subscribers table.

.