Tools like Power BI have changed reporting allowing power users to leverage tabular cubes to present information quicker and without the (perceived) need for developers. However, experience tells us many users still want data in tables with a myriad of formatting and display rules. Power BI is not quite there yet in terms of providing all this functionality in the same way that SSRS is. For me, SSRS’s great value and, at the same time its curse, is the sheer amount of customisation a developer can do. I have found that almost anything a business user demands in terms of formatting and display is possible.
But you have invested your time and money in a tabular SSAS model which plays nicely with Power BI but your users want SSRS reports so how to get to your data – using DAX, of course. Using EVALUATE, SUMMARIZECOLUMNS and SELECTCOLUMNS you can return data from a tabular model in a tabular format ready to be read as a dataset in SSRS.
If you had the following data in your tabular model:
ProductName | ProductCategory | Country | Sales |
Surly Steamroller | Complete bike | France | £46,575 |
Genesis Day One | Frame | France | £47,987 |
Genesis Day One | Frame | France | £47,987 |
Genesis Vapour 20 | Complete bike | France | £24,867 |
Genesis Vapour 20 | Complete bike | United Kingdom | £21,856 |
Genesis Day One | Complete bike | United Kingdom | £47,875 |
Surly Steamroller | Complete bike | United Kingdom | £27,969 |
Surly Steamroller | Complete bike | United States | £46,575 |
Genesis Day One | Frame | United States | £47,987 |
Genesis Day One | Complete bike | United States | £47,987 |
A pattern for returning data for SSRS could be:
DEFINE VAR FilterCountry = @country EVALUATE SELECTCOLUMNS ( SUMMARIZECOLUMNS ( 'Product'[ProductName], 'ProductCategory'[ProductCategory], 'Geography'[Country], FILTER ( VALUES ( 'Geography'[Country] ), ( OR ( ( FilterCountry = "All" ), PATHCONTAINS ( FilterCountry, 'Geography'[Country] ) ) ) ), "Sales", [Sales] ), "Product Name", [ProductName], "Product Category", [ProductCategory], "Sales", [Sales], "Estimated VAT", [Sales] * 0.2 )
To step through this…
- The DEFINE part declare a variable which will take a value from a SSRS parameter
- SUMMARIZECOLUMNS will return a CROSS JOIN of all the dimension columns entered, by passing in a measure or fact column as the last part of the block (here [Sales]) it will return only the cmbinations of dimension where there is a value for measure or fact item – particularly useful if you are dealing with date table stretching into the future.
- FILTER will filter the SUMMARIZECOLUMNS based on the SSRS parameter value. By adding the OR and PATHCONTAINS sections you can handle multi-value parameters in SSRS. This is a good blog post explaining this approach.
- SELECTCOLUMNS allows you to both provide friendly names for any dimensions (this is better handled in the tabular model but the amount of times it isn’t) and also perform calculations within your DAX query.
If your SSRS parameter was set to France and United Kingdom, the results would be:
Product Name | Product Category | Country | Sales | Estimated VAT |
Surly Steamroller | Complete bike | France | £46,575 | £931.50 |
Genesis Day One | Frame | France | £47,987 | £959.74 |
Genesis Day One | Frame | France | £47,987 | £959.74 |
Genesis Vapour 20 | Complete bike | France | £24,867 | £497.34 |
Genesis Vapour 20 | Complete bike | United Kingdom | £21,856 | £437.12 |
Genesis Day One | Complete bike | United Kingdom | £47,875 | £957.50 |
Surly Steamroller | Complete bike | United Kingdom | £27,969 | £559.38 |
If you set up a connection to your tabular cube in SSRS, you can paste in your DAX code, configure your parameters and you are good to go!
Introduction to Data Wrangler in Microsoft Fabric
What is Data Wrangler? A key selling point of Microsoft Fabric is the Data Science
Jul
Autogen Power BI Model in Tabular Editor
In the realm of business intelligence, Power BI has emerged as a powerful tool for
Jul
Microsoft Healthcare Accelerator for Fabric
Microsoft released the Healthcare Data Solutions in Microsoft Fabric in Q1 2024. It was introduced
Jul
Unlock the Power of Colour: Make Your Power BI Reports Pop
Colour is a powerful visual tool that can enhance the appeal and readability of your
Jul
Python vs. PySpark: Navigating Data Analytics in Databricks – Part 2
Part 2: Exploring Advanced Functionalities in Databricks Welcome back to our Databricks journey! In this
May
GPT-4 with Vision vs Custom Vision in Anomaly Detection
Businesses today are generating data at an unprecedented rate. Automated processing of data is essential
May
Exploring DALL·E Capabilities
What is DALL·E? DALL·E is text-to-image generation system developed by OpenAI using deep learning methodologies.
May
Using Copilot Studio to Develop a HR Policy Bot
The next addition to Microsoft’s generative AI and large language model tools is Microsoft Copilot
Apr