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:
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 the 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.
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.
Now if you create a new report the dataset can be used as a source for that report.
Talk to you soon,