(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:

• Date
• Sales Order ID
• Category
• Subcategory
• Product Name
• Unit Price
• Quantity
• Line Total

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.

Defining a data source for the project

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:

1. Right-click on the Shared Data Sources folder under the SSRS2012Bueprints solution in the Solution Explorer window, as shown in the following illustration.

   If the Solution Explorer window is not visible, access it by navigating to Menu | View | Solution Explorer, or press Ctrl + Alt + L:

   

2. Select Add New Data Source which displays the Shared Data Source Properties window. Let’s name our data source DS_SSRS2012Blueprint.

   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.

   

3. Another option for setting the connection is through the Connection Properties dialog box, as shown in the next screenshot.

   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.

Defining the dataset for the project

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.

1. Right-click on the Shared Datasets folder in Solution Explorer, just like we did when we created the data source. That displays the Shared Datasets Properties dialog.
2. Let’s name our dataset ds_SalesDetailReport. We use the query type stored procedure, and select or type uspSalesDetails on the Select or enter stored procedure name drop-down combo box. Click on OK when you’re done:

   

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.

Creating the report file

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:

1. Going back to the Solution Explorer window, right-click on the Reports folder. Please take note that selecting the Add New Report option will initialize Report Wizard. Use the wizard to build simple tabular or matrix reports.

   Go ahead if you want to try the wizard but for the purpose of our demonstration, we’ll skip the wizard.

2. Select Add, instead of Add New Report, then select New Item:

   

   Selecting New Item displays the Add New Item dialog box as shown in the following screenshot.

3. Choose the Report template (default report template) in the template window. Name the report SalesDetailsReport.rdl.
4. Click on the Add button to add the report to our project:

   

   Clicking on the Add button displays the empty report in the report designer. It looks similar to the following screenshot:

   

Creating a parameterized report

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:

1. Right-click on the Datasets folder in the Report Data window. If it’s not open, you can open it by navigating to Menu | View | Report Data, or press Ctrl + Alt + D:

   

2. Selecting Add Dataset displays the Dataset Properties. Let’s name our report dataset tblSalesReport.

   We will use this dataset as the underlying data for the table element that we will create to hold our report data.

3. Indicate that we want to use a shared dataset.
4. A list of the project shared datasets is displayed. We only have one at this point, which is the ds_SalesDetailsReport.

   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.

   

5. Switch to the Toolbox window. If you don’t see it, then go to Menu | View | Toolbox, or press Ctrl + Alt + X. Double-click or drag a table to the empty surface of the designer.
6. Let’s add more columns to the table to accommodate all eight dataset fields. Click on the table, then right-click on the bar on the last column and select Insert Column | Right.
7. To add data to the report, let’s drag each element from the dataset to their own cell at the table data region.

   

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: