PowerBI has come a long way when it comes to reporting besides the standard dashboards. However there are still many practical uses for simple and plain Reporting Services Reports(SSRS). For instance, detail reports are very suitable for SSRS. In a relative standard datawarehouse environment there will be cubes present. These cubes are mainly for, according to best practices, aggregations and data high over. And that is exactly what it is good at. When you want to see more detailed data to explain these aggregates, you want to drill down, in most cases, to your Datawarehouse/Datamart(s).
This integration can easily be done by implementing web url’s in your PowerBI, or cube environment, based on what the end user has selected. In this blog I will demonstrate how this can be done. For this example I’m using: a simple dataset, a tabular cube, a PowerBI report and a SSRS report. The dataset contains data about super heroes. What can i say, I’m a fan, so bare with me on this one 😊.
So here is my PowerBI report. It contains some graphs, calculations, filters and a simple table. the table shows the publishers of the super heroes from which the Gender is unknown.
With bookmarks I control which graph I want to see on this report. Later I will write a blog about bookmarks and how to use them as well. In this blog and in the example that I’m showing you, I created an SSRS report that shows all of the heroes where the gender is unknown. This report can be filtered by publisher. The SSRS reports looks like this:
So the goal is to link these two together. In the PowerBI report users must have the possibility to click on a link that will open the SSRS report with the filters already set. Let’s start by creating a measure in PowerBI. When you are connecting to a tabular cube(Direct query), I suggest that you create the measure in the cube. For now, we will create the measure in PowerBI. Define the measure as shown below:
First of, the only parameter I want to give to the SSRS report is the publisher. So we start by creating a variable with the selected publisher. Then I define my base URL for the reportserver. If you want to show the URL only when there are values, you can use an IF statement to check if there are values as I have done. Finally I take my base URL and concatenate it with the selected publisher value. When the measure is created we have to change the data categorie of this measure to “Web-URL”.
Now the measure can be used in a visual. This can be done in two ways. You can use the measure and set it in the background on another column with conditional formatting. Or you can select the measure in your visual as a seperate column. In my dashboard I did both.
So, when you want to use the measure on the background you select your visual and choose the format tab. Then choose conditional formatting and switch on “Web-URL”. A window will appear, that is where you select your measure. The second option is that you select the measure in your visual as a seperate column. At first you will see the URL itself. But if you want to change that to a URL icon you can. Again, select your visual and go to the format tab. Select values and switch on the “URL-Icon”. It wil look like this:
And there you have it! I can now click in my PowerBI dashboard on a measure to open a SSRS report, filtered by the selected publisher. For example, if I click on the “1” for “DC Comics” I get the following SSRS report back:
So, That’s all folks! If you have any questions, comments/remarks, feel free to contact me.