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.
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.
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.
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.
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.
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).
Hi Brett –
Great write up! I’m curious to know what your thoughts are on using Power BI datasets for paginated reports versus going the stored procedure route. Seems like there wouldn’t be as much flexibility using the PBI dataset with the example you provided using the date scenario – would that be a correct assumption?
Thanks Tim –
My general experience and guidance to clients has been that it’s not easy to build intermediate to advanced paginated reports against Tabular models or Power BI datasets. Power BI Report Builder is an MDX client and thus doesn’t naturally cooperate with authored DAX queries such as when you’re configuring multi-value report parameters. For this reason, in my projects we’ve often leveraged relational data warehouse objects in SQL Server, Azure SQL, Synapse SQL, or other supported relational database for paginated reports, particularly for complex or custom paginated reports containing logic you wouldn’t want to build into your analytical model. There’s also the advantage that SQL is much more generally known than DAX – clients/companies don’t want consultants to leave them with DAX queries they can’t easily support. My view could (and will) change if/when Microsoft improves Power BI Report Builder support for the Tabular/Power BI engine but I’m not aware of any major improvements coming at this time. I should write about this topic further in an upcoming blog.
How about an approach that uses both the Tabular model and SQL procs as data sources? Or is it best to just use one or the other and not a combination of both?
Sorry for the late reply, Brett. I went down the path of creating a tabular model and then realized I can’t use a report parameter value to dynamically select a column in the model and pass that to the dataset DAX query. But it doesn’t seem like it’s technically possible to change the DAX query for the paginated dataset by selecting a value that represents the ‘table'[column] in a report parameter. I wish there was a way to call an object from a report parameter in the DAX query…