Dynamic Datasets Part I

The purpose of this post is to introduce a method of making the datasets embedded in paginated reports more dynamic. Specifically, multiple queries reflecting distinct logic are embedded in a single dataset query (or stored procedure) and a report parameter is exposed to the user to determine which of three distinct queries to execute.

Use Cases

If you have any experience with paginated reports or SQL Server Reporting Services (SSRS) reports (.rdl files) you’re familiar with report parameters as a primary method for making a report somewhat ‘dynamic’. Though they’re certainly not as naturally interactive as the reports developed in Power BI Desktop, a set of report parameters representing common or highly valued dimensions can provide some level of self-service flexibility for users of the report.

However, in the real world we’re often confronted with business needs or ‘requirements’ that don’t neatly align with a few parameter dropdowns representing individual columns in a source data warehouse. In some cases, the users or business want to have the option for the report to execute completely different logic involving different tables or views, joins, calculations, etc. For example, in a recent project there were a number of custom lists of products, customers, and stores stored within the data warehouse and the business wanted the option to filter the report data for any one of these specific lists or not filter the report by any of these lists. Certain elements of the method described in this blog post were applicable to this project.

Report Requirements

For this report project, the business wants to be able view sales data across common dimensions but to be able to easily toggle between order date, ship date, and due date. In other words, if the user selects order date, the report should return sales data reflecting the sales order date and any date parameters (ie Year, Month) should also reflect the order date.

Outside of the date requirement, it’s a very basic report:

In the report above, the user has made selections on four parameters, three of which support multiple values. The Date Type parameter, which only accepts one value, is what makes this report a bit more dynamic than most paginated reports. Switching between Order Date, Ship Date, and Due Date would return different results based on these dates.

Report Design

There’s plenty of documentation and other blog posts on exposing a set of values in a parameter based on a query or a stored procedure so I won’t go into any details for the Customer Country, Calendar Year, or Product Category parameters. For this example, the label and values for the Date Type parameter are entered within the report:

The label values are exposed to the user in the report interface prompt and their associated value(s) is what’s passed to the query or procedure.

Query and Procedure

Like most paginated reports, at the heart of this report is either the T-SQL query or the stored procedure which passes the parameter values defined by the user into one (or multiple) queries. Though this example is not that complex, the query and procedure are long enough that I’ve uploaded the SQL files to my Github repository for your download if interested.

In general, a stored procedure is recommended over embedding the SQL query in the dataset. The stored procedure provides greater manageability and for the flavors of SQL Server which cache stored procedure query plans, performance can be benefited as well. Sometimes, however, the stored procedure isn’t an option for the report development team or isn’t an option early on in the project so I’ve uploaded both SQL files.

Essential Logic

Both the stored procedure or ‘proc’ and the query use three IF conditions to conditionally execute one of three T-SQL queries. The only difference between each of the three T-SQL queries is the join between the sale fact view and the date dimension view on order date, shipping date, and due date, respectively.

--Order Date Scenario
IF @DateScenario = 1
SELECT....
FROM
[BI].[vFact_InternetSales] AS F
INNER JOIN [BI].[vDim_FinDate] AS D ON 
	F.[Order Date Key] = D.[Date Key]

You may notice the IN operator used for the parameters in the SQL query and a different approach in the procedure. The IN operator (intuitively) supports multiple parameter values for the SQL query. For the stored procedure, a contained subquery with a STRING_SPLIT function is used:

P.[Product Category] IN (SELECT value FROM STRING_SPLIT(@ProductCat,','))

Far Beyond Computers

I wanted to share a short but true story. Last Tuesday night I wasn’t feeling well. I had a number of concerning symptoms, including a cough, sneezing, and most concerning, shortness of breath. Without going into great detail, at 7 PM I had all these symptoms to the point I was seriously considering seeking immediate medical attention.

Somehow, I fell asleep at 7 PM and I recall seeing a number of behaviors that I regret as well as some biblical scriptures. I woke up at 9 PM and there tears in my eyes but my breathing was completely back to normal, I wasn’t coughing, I wasn’t sneezing, my fever was gone, and my chest felt normal. I haven’t experienced these symptoms since 7 pm that night.

I’ve been an atheist for approximately 20 years but from this experience forward I believe in God – I believe in Jesus Christ. Whatever your circumstances, I hope this short story gives you hope and, if applicable, you also consider reconsider your faith.

Wrapping Up

Although this was a simplistic example, if you weren’t aware of this technique I hope you consider some variation of this approach for your applicable scenarios. Your reports don’t necessarily need to contain one query – they can contain many which, albeit returning the same fields, can vary greatly and thus extend the flexibility of your paginated reports.

In a Part II follow up to this blog post, I’ll share something similar when the source database is a Power BI dataset or an Analysis Services model (hence DAX queries).