As I’ve said before, the cardinal rule of well-performing reports is to use SQL Server for your processing, and not SSRS. Instead, save SSRS for presenting and distributing data. (And here’s why.)
Once you’ve done that, you need to next understand the best way to use datasets.
First, what is a dataset?
You can think of a dataset in two pieces:
- Some kind of SQL code (a SELECT statement or a stored procedure)
- Additional logic performed to the results of that SQL Code once the code is returned to SSRS.
Now let’s look at specific types of datasets and some scenarios.
Multiple SSRS Datasets for Parameters
Many reports require more than one query. To illustrate, let’s take a simple example I’ve built over AdventureWorks. This simple report gives us sales and allows us to select those sales based on customer and/or item.
Now, it would be theoretically possible to just have the user key in the appropriate item number. But that’s not going to make anyone happy (especially if they’ll eventually to want to select multiple items or customers). So, this report has two additional data sets: one to provide values for the customer and one to provide values for the item.
Let’s take a look at the report:
We have five datasets, but they’re not all the same.
The first dataset (called “DataSet1”), is used in the table and provides the bulk of the report output.
The other four datasets—“Territories,” “Categories,” “FiscalYears,” “FiscalPeriods,” and “Quarters”—are only used for parameter selection.
Shared Datasets vs. Embedded Datasets
Continuing with this example, only DataSet1 is unique to this report.
You can see this by right clicking on the dataset and choosing properties:
Now, we can see that the DataSet1 is “embedded in the report.” This means that this particular set of “query plus extras” can only be used by this report.
The other four datasets use the shared dataset option. That means that the query was defined BEFORE we created the report.
If we look on the right side of Visual Studio, we can see all the shared datasets that I’ve created for this particular project. I’m using most, but not all of them in this report.
When to Share Datasets
When I work in development, I almost only use shared datasets for parameters. If I have complicated logic that might power multiple reports, I’ll create a view, stored procedure or even simple datamart rather than create a complex dataset (as discussed in my post “SSRS Consultant Tip: Stored Procedures vs. Views for SSRS Reports“).
To keep things clear, I generally name datasets as I have here: “DataSet1” for the dataset unique the report and then real names (e.g. “Products,” “Territories,” “FiscalYears”) for shared datasets.
Multiple Datasets in One Report Beyond Parameters
In the example above, I only have one main dataset. But sometimes I’ll use other datasets in one report.
For example, for a NAV customer, I created a report to show all invoices that have been selected for payment. The user then asked to see all vendors with a credit balance who were not receiving checks at that time. In other words, every time the user asks for step one they also want to see step two.
Therefore, I created another dataset and another table in the report. But essentially, they’re two reports in one because they’re always requested together.
A No-No: Combining Data from Multiple Datasets With the Look Up Function
SSRS has the ability to combine data from multiple data sets. Going back to my cardinal rule about using SSRS only for presenting data and reporting, I don’t think this is a good idea.
Yes, it’s possible to link each dataset in the report to a different data source and therefore a different database (which I once did, combining Unix data and AS400 data in one report. Oh, the horror!). But the performance suffers.
I intend to write more posts on this topic as this is a fairly common scenario for users using multiple software packages.