Using DAX With Reporting Services

David Stelfox gives us an example of using DAX when connecting SQL Server Reporting Services to a SQL Server Analysis Services Tabular model:

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.

It’s a good post and a good example.  The only quibble I have is in the motivating paragraph; Power BI and SQL Server Reporting Services have different end goals—Power BI isn’t (and I think never will be) a pixel-perfect report building product; it’s meant to be a dashboarding technology.  That quibble aside, the example is well worth checking out.

Related Posts

Sorting When Your Measure Is Not In The Visual

Kasper de Jonge shows us different ways of sorting a visual by some unrelated measure: So lets start with the simple one, I want to sort a chart on a measure not part of the visual. Let’s take this visual: Now instead of sorting by OrderQuantity I want to sort by the ListPrice. The trick […]

Read More

Reporting Services And Availability Groups

Thomas Rushton has a good rant worked up: Yes, you may have an availability group – well done – and you may have installed SSRS on both servers. But you’ve only set up the reporting application to point to one of those? And you’ve given the link https://<<Listener_Name>>/reports out to the users? Head/desk. I told you at […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Categories

July 2018
MTWTFSS
« Jun  
 1
2345678
9101112131415
16171819202122
23242526272829
3031