




















































(For more resources related to this topic, see here.)
John Kirkland is an awesome "accidental" SQL Server DBA for Red Speed Bicycle LLC—a growing bicycle startup based in the United States. The company distributes bikes, bicycle parts, and accessories to various distribution points around the world.
To say that they are performing well financially is an understatement. They are booming! They've been expanding their business to Canada, Australia, France, and the United Kingdom in the last three years.
The company has upgraded their SQL Server 2000 database recently to the latest version of SQL Server 2012. Linda, from the Finance Group, asked John if they can migrate their Microsoft Access Reports into the SQL Server 2012 Reporting Services.
John installed SSRS 2012 in a native mode. He decided to build the reports from the ground up so that the report development process would not interrupt the operation in the Finance Group.
There is only one caveat; John has never authored any reports in SQL Server Reporting Services (SSRS) before.
Let's give John a hand and help him build his reports from the ground up. Then, we'll see more of his SSRS adventures as we follow his journey throughout this article.
Here's the first report requirement for John: a simple table that shows all the sales transactions in their database. Linda wants to see a report with the following data:
We will build our report, and all succeeding reports in this article, using the SQL Server Data Tools (SSDT). SSDT is Visual Studio shell which is an integrated environment used to build SQL Server database objects. You can install SSDT from the SQL Server installation media.
In June 2013, Microsoft released SQL Server Data Tools-Business Intelligence (SSDTBI). SSDTBI is a component that contains templates for SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), and SQL Server Reporting Services (SSRS) for Visual Studio 2012.
SSDTBI replaced Business Intelligence Development Studio (BIDS) from the previous versions of SQL Server.
You have two options in creating your SSRS reports: SSDT or Visual Studio 2012. If you use Visual Studio, you have to install the SSDTBI templates.
Let's create a new solution and name it SSRS2012Blueprints.
For the following exercises, we're using SSRS 2012 in native mode. Also, make a note that we're using the AdventureWorks2012 Sample database all throughout this article unless otherwise indicated. You can download the sample database from CodePlex. Here's the link: http://msftdbprodsamples.codeplex.com/releases/view/55330.
Now, let's define a shared data source and shared dataset for the first report. A shared dataset and data source can be shared among the reports within the project:
If the Solution Explorer window is not visible, access it by navigating to Menu | View | Solution Explorer, or press Ctrl + Alt + L:
For this demonstration, let's use the wizard to create the connection string. As a good practice, I use the wizard for setting up connection strings for my data connections. Aside from convenience, I'm quite confident that I'm getting the right connections that I want.
Clicking on the Edit button next to the connection string box displays the Connection Properties dialog box:
Shared versus embedded data sources and datasets: as a good practice, always use shared data sources and shared datasets where appropriate. One characteristic of a productive development project is using reusable objects as much as possible.
For the connection, one option is to manually specify the connection string as shown:
Data Source=localhost;Initial Catalog=AdventureWorks2012
We may find this option as a convenient way of creating our data connections. But if you're new to the report environment you're currently working on, you may find setting up the connection string manually more cumbersome than setting it up through the wizard.
Always test the connection before saving your data source. After testing, click on the OK buttons on both dialog boxes.
Our next step is to create the shared dataset for the project. Before doing that, let's create a stored procedure named dbo.uspSalesDetails. This is going to be the query for our dataset.
Download the T-SQL codes included in this article if you haven't done so already. We're going to use the T-SQL file named uspSalesDetails_Ch01.sql for this article.
We will use the same stored procedure for this whole article, unless otherwise indicated.
Before we work on the report itself, let's examine our dataset.
In the Solution Explorer window, double-click on the dataset ds_SalesDetailReport.rsd, which displays the Shared Dataset Properties dialog box.
Notice that the fields returned by our stored procedure have been automatically detected by the report designer. You can rename the field as shown:
Ad-hoc Query (Text Query Type) versus Stored Procedure: as a good practice, always use a stored procedure where a query is used. The primary reason for this is that a stored procedure is compiled into a single execution plan. Using stored procedures will also allow you to modify certain elements of your reports without modifying the actual report.
Now, we're almost ready to build our first report. We will create our report by building it from scratch by performing the following steps:
Go ahead if you want to try the wizard but for the purpose of our demonstration, we'll skip the wizard.
Selecting New Item displays the Add New Item dialog box as shown in the following screenshot.
Clicking on the Add button displays the empty report in the report designer. It looks similar to the following screenshot:
You may have noticed that the stored procedure we created for the shared dataset is parameterized. It has the following parameters:
It's a good practice to test all the queries on the database just to make sure we get the datasets that we need. Doing so will eliminate a lot of data quality issues during report execution.
This is also the best time to validate all our data. We want our report consumers to have the correct data that is needed for making critical decisions.
Let's execute the stored procedure in SQL Server Management Studio (SSMS) and take a look at the execution output. We want to make sure that we're getting the results that we want to have on the report.
Now, we add a dataset to our report based on the shared dataset that we had previously created:
We will use this dataset as the underlying data for the table element that we will create to hold our report data.
Let's select that one, then click on OK.
Going back to the Report Data window, you may notice that we now have more objects under the Parameters and Datasets folders.
There are three data regions in SSRS: table, matrix, and list. In SSRS 2012, a fourth data region has been added but you can't see that listed anywhere. It's called tablix.
Tablix is not shown as an option because it is built into those three data regions. What we're doing in the preceding screenshot is essentially dragging data into the underlying tablix data region.
But how can I add my parameters into the report? you may ask.
Well, let's switch to the Preview tab. We should now see our parameters already built into the report because we specified them in our stored procedure.
Our report should look similar to the following screenshot: