I was recently asked by a customer if you could build a Shared Dataset using Report Builder 3.0. More specifically, can you build a Shared Dataset that combines multiple tables from the same database? To be honest, I wasn’t sure initially because I never attempted to. However, after a little testing I found out that it was absolutely possible. In this post I will demonstrate how to accomplish this. I will be using the ContosoSchoolsDW database.

NOTE:

Similar to creating a Shared Dataset using SQL Server Data Tools (SSDT), you must first create a Shared Data Source.

Watch the video if you prefer:

Step-By-Step

1. Open Report Builder.
2. Select New Dataset from the list of choices.
3. Then either select a previously used data source or click the Browse other data sources… hyperlink and locate the data source of choice.
4. After you have selected a Data Source click the button in the bottom right labeled Create.

   

5. On the left of the screen you will see the Database view. You can build a dataset based on Tables, Stored Procedures or Table-values Functions. For now expand the folder labeled Tables.

   NOTE: Before I proceed understand that Foreign Keys are essential to using the Query Designer to create the query for the dataset. While you can write a query from scratch by clicking the button labeled Edit as Text, you must be proficient with corresponding data sources programming language no matter if it is TSQL, PLSQL, etc…. In addition, you can also manually add relationships by expanding the relationships section, which is located between the Selected fields and Applied filters section, but this requires an intimate understanding of the underlying Database schema. Since I know that the appropriate Foreign Keys exists in the selected data source using the Query Designer will be sufficient.

   

6. Expand Dim_School and select School_Name from the list of available columns.
7. Expand Dim_Time and select Year_Name from the list of available columns.
8. Expand Fact_Student_Absence and select Tardy from the list of available columns.

   

   NOTE: If you want to see the TSQL that the designer is generate click the Edit as Text button in the ribbon. Also, if you would like to aggregate the data you can do so by clicking in the column next to the desired field and select the aggregation type from the list of available values.

9. Click the filter icon located midway down the window on the right.
10. Click in the Field name column and expand the Fact_Student_Absence table.
11. Select Tardy from the list of available columns.
12. Click in the Operate column and select is not from the list of available values.
13. Click in the Value column and select null from the list of available values.
14. Click the save button at the top of the window and the Save As Dataset window will open.
15. Enter StudentAbsence.rsd in the textbox labeled Name.
16. Browse to a location where you have been granted permissions to save datasets and click the button labeled OK.

Now if you create a new report the dataset can be used as a source for that report.

Talk to you soon,

Patrick LeBlanc