(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:
- Sales Order ID
- Product Name
- Unit Price
- 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:
- 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:
- 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.
- 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.
- 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.
- 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:
- 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.
- 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.
- Choose the Report template (default report template) in the template window. Name the report SalesDetailsReport.rdl.
- 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:
- 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:
- 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.
- Indicate that we want to use a shared dataset.
- 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.
- 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.
- 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.
- 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:
Creating a radio button parameter
Like I said, all the parameters shown in the previous screenshot are prebuilt by way of our stored procedure. That's including the radio button parameter at the top of our report.
How to do this? Let's take a look at our stored procedure.
You might have noticed that the first parameter is called @IsOnline and is of T-SQL type BIT or Boolean (TRUE or FALSE).
This parameter is checked against a column in the underlying table named OnlineOrderFlag, which holds a value of either 0 or 1 (BIT); 1 returns TRUE. We're passing this parameter in the WHERE clause as shown in the following expression:
Select … From … Where OnlineOrderFlag = @IsOnline
So, let's run our report with some values. Supply the following data into their respective parameter container:
IsOnline: False Category: Accessories Subcategory: Helmets Product: Sport-100 Helmet, Blue
Basically, what we want to see in this report execution are all the items sold in-store only, excluding all the online purchases.
We should get something like the following screenshot:
Making a parameter optional
Another common requirement on parameterized reports is the ability to exclude one or more parameters at runtime. For example, we want to see all the sold items online under the accessories category without going further down to subcategory and product.
In this example, let's make subcategory and product optional. We need to modify our stored procedure first, as done in the following expression:
Select … From … Where Subcategory = COALESCE(@Subcategory, Subcategory) AND Product = COALESCE(@Product, Product)
Please take a note that the fields may have different names in the code included with the article. The previous code is for clarity purpose only. Modify the codes accordingly.
Basically, what we're trying to do here is we're testing the @Subcategory and @Product parameters for null values. The COALESCE() function returns the first nonnull in the argument.
We can also use the ISNULL() function, which works like this: ISNULL (check_expression, replacement_value).
Don't confuse the ISNULL() function with the IS NULL operator. They're two different things.
You might be debating which one to use, COALESCE() or ISNULL().
The difference between the two functions are widely documented and debated on various forums. Let it suffice to say that ISNULL() is not an ANSI standard, and thus, proprietary only to T-SQL.
Let's go back to our report.
After we modified the stored procedure to test null values, we then modify the subcategory and product parameters in our report:
- In the Report Data window, let's expand the Parameters folder and right-click on the Subcategory parameter then select Parameter Properties:
Clicking on the Parameter properties displays, you guessed it right, the Report Parameter Properties dialog box.
- Go to the General tab and then select the Allow null value checkbox, then click on OK.
- Repeat the same on the Product parameter and we're all set. Now, let's run our report with the optional parameters.
- Let's try the following values:
IsOnline: True (we want to know what items are sold online only, not including in-store) Category: Clothing Subcategory: NULL Product: NULL
We would come up with something like the following screenshot:
Populating a drop-down parameter with a dataset
Normally, we don't want to type in values in the parameter field. So, we want to populate that field with the available data from our backend table.
Another reason is we want to give the report consumers only the available values from our dataset.
For example, with the category parameter, we may want to provide all the category values from our dataset. We don't want to leave them guessing what categories are out there to choose from.
To populate our @Category parameter, we need to create a new dataset for our report.
Since we might only need this for this particular report, we'll create a report dataset (in contrast to a shared dataset).
- Right-click on the Datasets folder in the Report Data window, and select Add Dataset. The Dataset Properties dialog box then displays our options to create a dataset.
- Let's name our dataset dd_Categories and select the option Use a dataset embedded in my report.
- Let's skip the data source, we'll come back to that in a second. On the Query type, select Text then type:
SELECT Name FROM Production.ProductCategory.
Our Dataset Properties should look like the following screenshot:
- Let's go back to the data source. Click on the New button next to the data source field, which displays the Data Source Properties window. Let's name our source DS_Categories, and reference our project's data source as shown in the following screenshot:
We may also want to select the option to Use single transaction when processing the queries. We don't need to specify new credentials for this source because we're inheriting all the credentials from our referenced data source. Click on OK on both dialog boxes when done.
- Now, we right-click on the @Category parameter in the Report Data window, and select Parameter Properties.
- On the General tab, select Allow multiple values.
- Switch to the Available Values tab. Select Get values from a query and specify the dataset name, value field, and name field according to the dataset we just created. Click on OK when done:
Our report Category parameter should now show the drop-down list of categories:
Creating a multivalued parameter
You might have noticed that the category parameter at this point only allows us to select one category at a time. We may want to select two or more Categories.
Let's set the Category parameter to accept multiple values by selecting Allow multiple values in its properties:
The next thing we need to do is create a table-valued function that returns an array of categories. What the function does is simply collect the selected categories from our report and pass it to our stored procedure. We'll do two more things to complete our multivalued parameter.
Table-valued functions are user-defined functions that returns Table datatype. For more information about Table-valued functions, please go to this link http://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx.
First, let's create the table-valued function and name it MultiValueParamSplit. Please find the script named MultiValueParamSplit_Ch01.sql in the code folder for this article.
Next, let's modify our stored procedure dbo.uspSalesDetails to make use of our function like this:
Select … From… Where Category IN (Select [Category] From dbo.MultiValueParamSplit(@Category, ','))
Now, our report should allow us to select multiple categories to pass on our Category parameter:
Creating a cascading parameter
Now, let's set up a cascading parameter between Category and Subcategory. We want to only show subcategories that belong to a particular category. So, if we select Bikes on our Category parameter, we want to see only the subcategories that belong to bikes, that is, mountain bikes, road bikes, and touring bikes.
Let's create a stored procedure named dbo.uspSubcategory. Run the script called uspSubcategory_Ch01.sql included in this article.
After we create the dbo.uspSubcategory stored procedure, let's create a new dataset out of that procedure. Let's name it ds_Subcategory:
The uspSubcategory stored procedure accepts a Category variable. So, under the hood, what we're trying to set up is to pass the value of the category field to the parameter of the Subcategory.
By default, the report designer detects all the parameters in the dataset (in this case, a stored procedure). The Parameter tab of the Dataset Properties dialog shows all the parameters detected by the report designer.
After setting up the properties for our new dataset, let's set up the @Subcategory parameter to use the same dataset:
We should now see that our Category parameter cascades to the Subcategory parameter:
Please make note that we only needed to pass a @Category parameter to our Subcategory stored procedure without doing any other setup or process on the Report Designer/Manager side, because, both the @Category and @Subcategory Report Parameters are on the same scope, which is this report (SalesdetailsReport.rdl).
Creating a cascading multivalued parameter
We may have noticed that the cascading functionality between the Category and Subcategory works only when we have a single category. If we select multiple Categories, we want to get all the Subcategories (multivalued) under those Categories.
We might think that setting up the @Subcategory parameter to "allow multiple values" would do the trick. But that won't work:
I've seen people handle this requirement by using custom codes that are processed on the report server.
It's a good practice that anything that can be processed on the SQL Server database should be processed on the database.
With the use of the table-valued function (MultiValueParamSplit) we created previously, we can set the @Subcategory parameter to accept multiple values without modifying our report. This is the primary reason why we should prefer using stored procedures for our datasets.
Let's modify the dataset behind the Subcategory parameter by modifying the dbo.uspSubcategory stored procedure.
We only need to change the WHERE clause from:
Select… From… WHERE Category = @Category
A clause that uses our MultiValueParameterSplit function:
Select… From… WHERE Category IN (SELECT [Category] FROM dbo.MultiValueParamSplit(@Category, ',')
We should now see a cascading effect between the multivalued Category and Subcategory parameters:
To make use of these cascading subcategories, we still need to modify the underlying dataset of our report which is the tblSalesReport to accept the multivalued subcategory parameter. To do that, let's modify the stored procedure ds_SalesDetailsReport.
Let's change the WHERE clause of the @Subcategory parameter from:
Select… From… Where Subcategory = @Subcategory to Select… From… Where Subcategory IN (Select [Category] FROM [dbo].[MultiValueParamSplit](@Subcategory, '',''))
Sales details report in action!
Here's what our report, the sales details report, looks like in action:
In this article, we learned how to build a report from the ground up. We've also learned different tricks on how to implement parameters. If you've followed all the exercises, you should now have a strong grasp on how to use parameters in different scenarios.
Resources for Article:
- Overview of SQL Server Reporting Services 2012 Architecture, Features, and Tools [Article]
- SQL Server 2008 R2: Multiserver Management Using Utility Explorer [Article]
- Team Foundation Server 2012 [Article]