Report navigation is, in my opinion, the essence of true data exploration and discovery. It lets business users see and understand important information in different forms; in summary and in detail, within context. SSRS and Power BI are truly a dynamic duo for reporting and data discovery. I wanted to post a follow up to these two articles because they are apparently very popular topics. Posted two days apart on my blog, they received 2,744 and 3,356 views. Woo!
Nine years ago we released the Reporting Services Recipes Book from Wrox Press, which was a very popular collection of design patterns and techniques, often referenced frequently by Microsoft leaders at industry conferences. In the book, we showcased different ways to move between SSRS reports by passing parameters and dynamic URLs to create interactive reporting solutions. I am working with my former co-author and a team of talented BI and report design experts on the second edition of the the Recipe book that will include not only updated examples of SSRS patterns for on-prem report solutions but we are including new patterns for Power BI Report Server and Paginated reports in the Power BI service.
Today we have Power BI in addition to SSRS (now called “Paginated Reports”). These tools are different and often serve distinctly different purposes for creating reporting solutions. But, using Power BI and SSRS/Paginated Reports together opens doors to create incredibly rich integrated report and data discovery solutions.
I recently published two blog posts to share some of my work-in-progress for the recipe book: Drillthrough from Power BI to an SSRS Paginated Report, and: Drillthrough from Paginated Report to Power BI. Both of these posts demonstrate navigation from one of the report tools to the other, essentially allowing users to work with these two capable tools in a full circle. As the newer and more modern data analysis tool, Power BI fills and important gap but it is not a replacement for the type of multi-page grouped and repeated style of reporting that SSRS is optimized for. Likewise, Power BI excels as an interactive tool for data discovery and self-service analysis. SSRS and Power BI were borne from the same platform and and have common architectures but also many differences. Used together, the realm of possibilities is expansive.
It is important to understand that the techniques used to navigate and pass filter context between these report tools is limited to the capabilities of web query string parameterization. In my experience, this is rarely a show-stopper but I do occasionally encounter folks pushing the limits – both practically and technically.
Both SSRS and Power BI have their own drillthrough capabilities using internal mechanisms that do not rely on the web browser and report server to handle web URLs. However, moving between different report types relies and web URLS. Although powerful and flexible, there are some limitations. Each web browser has it’s own maximum URL length limit, ranging from 2048 to 2083 or so characters. The server-hosted web services also have their own limits. For simplicity, let’s call the limit about 2000 characters, which will include the base domain and folder path URL, and the query string directives, parameters and values. Keep all this within the ~2000 character limit and you’re good. Also be mindful that all this is visible in the browser’s address bar. Requests and responses sent to the Power BI service are encrypted so there is little risk of exposure outside the session. Your on-prem report server connections can also be protected using certificate-based encryption. Just be mindful about what you pass in sight of the user and web browser after the stream is decrypted on the client.
The report navigation recipes posted here on my blog are early drafts. I welcome your feedback, questions and ideas for more report recipes. Sample files and other downloadable content that are referenced in the recipes will be available when the book is published – and no, that date has not been set, but I will make an announcement when we know. Thank you in advanced for your questions and comments.
7 thoughts on “Drillthrough Navigation Between Power BI and SSRS Paginated Reports”
sir, i want to create a paginated report and in that i have to create table of contents, and when extracted in pdf/excel a user while clicking on any table of content topic should skip diectly to that content..is it possible in power bi report builder,,email me at firstname.lastname@example.org
Hello Swati, I prefer to keep interactions here in my blog so that others can see them.
The feature you are looking for is called a document map but they work only when the report is viewed in the web browser. Reports rendered to other formats such as PDF and Excel do not produce navigation links.
I am just hit the project requirements to export a wide table with a lot of text columns from the Power BI report.
It looks like paginated reports are the solution, but the question is in the number of filters (>10) with multiple choice which could (and will) be applied by a user before export. With such a set of parameters, we will hit the URL length limit very soon.
Do you know any workarounds to pass a LOT of parameter values to a paginated report in Power BI?
That’s a tough problem to solve, Maxim. The only work-around I have come up with is to write-back to a table and store the filtering values. It’s certainly not ideal and I wouldn’t call it a best practice but it might be the only option you have.
Thanks for the answer, Paul. I think writeback is not suitable for my case. Although, maybe PowerApps will help here (just a small button to save the filter values somewhere and start the export process) … In any case, you gave me an idea to sleep with, thanks!
Have you been able to navigate from SSRS relational queries to Olap MDX reports (either in PowerBI or SSRS). We are trying to use Power BI on premise but dont want to lose the ability to drill from olap to ssrs detail like we can do using Perfornance Point Services and SSAS Report Actions.
Thanks in advance!
Hi Greg, I’ve written about how to drillthrough to an SSRS report using an MDX dataset with parameters, but I don’t have an example handy but there are examples in the 2016 book and a bit here: https://sqlserverbi.blog/2011/05/04/designing-reports-with-custom-mdx-queries-part-3/. The trick is to concatenate the parameter values into MDX member references. Neither SSRS nor Power BI support cube actions like Excel or PerformancePoint and I doubt that we will see that capability any time soon.
The good news is that you can do the same kind of thing in both SSRS and Power BI as a drillthrough target by using parameters.