Reporting Services

Here I’ve created a simple report using SQL Server Reporting Services. I used SQL Server 2008 with 2008 Integration services installed, as well as BIDS (Business Intelligence Development Studio) with Visual Studio 2008.

Create a database and run the Test Database Script to create and fill the test tables, as well as create a stored procedure to use as the data source for the report.

Create a report:

1) Open BIDS and ‘File => ‘New’ => ‘Project’.
2) In ‘Project types’ select ‘Business Intelligence Projects’ and ‘Report Server Project’. ‘OK’ to create project.

Create a data source:
3) In Solution Explorer, right click on ‘Shared Data Sources’ and select ‘Add New Data Source’.
4) In the ‘General’ dialog, name the source and select type ‘Microsoft SQL Server’.
5) Next to connection string, click ‘Edit’ and point to the server and the text database.
6) On the ‘Credentials’ tab, make sure ‘Use Windows Authentication’ is selected and click ‘OK’.

Create a data set, which is a model of the query results:

7) In Solution Explorer, right click on ‘Shared Data Sets’ and select ‘Add New Dataset’.
8) On the ‘Query’ tab, name the data set and select the data source created earlier.
9) In ‘Query Type’, select ‘Stored Procedure’, then select the ‘GetSales’ procedure.
Here you can change column names, supply default procedure parameters, etc, but we are going to use the defaults.
Click ‘OK’ to create the data set.

Create a new report:
10) In Solution Explorer, right click on ‘Reports’ and select ‘Add New Report’.
11) Select the data source created earlier and click ‘Next’.
12) In ‘Design The Query’, use “exec dbo.[GetSales] ‘2012-12-31’, ‘2013-01-03′” in the query string field.
13) Select the ‘Tabular’ type.
14) In ‘Design The Table’, select all of the available fields in ‘Details’.
15) Select the ‘Slate’ style.
16) Name the report and finish.
17) View the results in the ‘Preview’ tab.

Now that the report has been created, we’ll need to publish the report to Reporting Services.

Publish Reports:
1) select the Project, right click and select Properties. Fill in ‘TargetReportUrl’ attribute with the URL of the installed Reporting services, usually in the form http://ServerName/ReportServer, where ‘ServerName’ is the name of your actual server.
2) Make sure the correct version of the host SQL Server is selected in ‘TargetServerVersion’.
3) On the menu bar, Select ‘Build’ and then ‘Deploy Project’

Now that the report has been deployed, we can run it.

Run Reports:
1) Navigate to report services URL (http://ServerName/ReportServer) in your web browser.
2) Click on the project name, and on the next page click on the link for the report.

Report Templates:
You can create a template to be used for all reports. For my installation (Visual Studio 2008 and SQL Server 2008), update the file at:
C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject\Report.rdl

Advertisements

One Response to Reporting Services

  1. […] while back I posted a tutorial on creating and deploying a report using SSRS. I wanted to go back to this example, see if anything had changed in SQL Server 2014 and […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: