SSRS Best Practice I - Limit datasets

In SQL Server Reporting Services you can create datasets. One of the best practices is to keep these datasets to a minimum, preferably just 1. As soon as someone ever has to change the report, he does not have to go through many datasets to look everything up, but can make do with one dataset.

In a report you will often use parameters. Many people will make a separate dataset for every parameter. But this is not needed. With a simple trick you can make a dataset dynamic. Say you have a report with the Internet Sales from the Adventureworks database. You let the user make a choice: ‘Customer’ and ‘Country’. If the user picks nothing, he sees everything. If he picks a customer, the report must show only the customers he picked. If he picks a country, the report must show only the data of that country. The question now is how to solve this simply in a query.

The example below shows how you can solve this in your dataset. Nothing complicated in the report, but handle it at the source:

{geshi xml:lang=“tsql” lines=“false”}
USE AdventureWorksDW
GO

DECLARE @GeselecteerdeKlantID AS INT
DECLARE @GeselecteerdeLandID AS INT

SET @GeselecteerdeKlantID = 0
SET @GeselecteerdeLandID = 2

SELECT  DC.CustomerKey,
DC.FirstName,
DG.GeographyKey,
DG.EnglishCountryRegionName,
SUM(FIS.SalesAmount) AS SalesAmount
FROM    FactInternetSales AS FIS
LEFT OUTER JOIN DimCustomer AS DC ON FIS.CustomerKey = DC.CustomerKey
LEFT OUTER JOIN DimGeography AS DG ON DC.GeographyKey = DG.GeographyKey
WHERE   (
ISNULL(@GeselecteerdeKlantID, 0) = 0
OR DC.CustomerKey = @GeselecteerdeKlantID)
AND (
ISNULL(@GeselecteerdeLandID, 0) = 0
OR DG.GeographyKey = @GeselecteerdeLandID)
GROUP BY DC.CustomerKey,
DC.FirstName,
DG.GeographyKey,
DG.EnglishCountryRegionName{/geshi}