POWER BI – DRILLTHROUGH FROM ONE REPORT TO ANOTHER REPORT FOR INCREASED SECURITY (CROSS-REPORT DRILL THROUGH)

APPLIES TO: ✓ Power BI Desktop ✓ Power BI service

The traditional drill through do not allow to drill through from one report to another report. It is only work in a single report. It is page to page drill through.

Cross report drill through allows you to drill through from one report to another report.

At first, I didn’t think this was going to be that great of a feature but then I realized this actually serves a big purpose. It’ll allow you to kind of set up a little bit more security around your reporting.

For example: Let’s say as a Manager, I want to be able to see the detailed data but I don’t want my employees to see this kind of level of details. I only want them to see the summary report. We can now set up drill through from the summary to details report but not actually give them access to the detail report. So, it’s kind of serving that purpose of keeping things secure but not having to create multiple reports for your different users that have different access. This is different than row-level security because row-level security is going to allow you to restrict the actual data that’s shown to them not the actual report that’s shown. So, we’re going to restrict what they can see based on a report level.

CROSS REPORT DRILL THROUGH

I have one page in one report let’s call it my source report. Source report is a report that will actually use the visual that you’ll use to invoke the drill to the other report. The other report that you land on we’re going to refer to that as our target. That’s actually going to be the page that you land on after the drill is initiated from your source report. So, two separate reports I’m going to go from one page of my source report to another page in target report.

Scenario: I have one report that contains everything I need from a pretty high level and I saw another report that contains some other information I need. So, I’m going to show you how can I link them up. All of this is possible using the Cross Report Drill Through.

Step 1. Open your TARGET REPORT, on your target report open up the VISUALIZATIONS PANE and you’ll see a little toggle for CROSS REPORT, switch it to ON.

 

The next thing you have to do is you have to identify the column that you’re going to use to connect those two reports. One of the main requirements is that those you need to have those columns and they need to be named the same and they need to exist in both reports but in addition to that the table names the tables that contains those columns those names must be the same also. The table and the column names must be exact same across both of those reports.

Step 2. After you identify that column you just drag it down to the drill through section.

Step 3. Then all you need to do is go to FILE → OPTIONS AND SETTINGS → OPTIONS and then what you going to see there. There’s two sections GLOBAL and CURRENT FILE in CURRENT FILE look for REPORT SETTINGS and you’re going to see a property called CROSS REPORT DRILL THROUGH and there’s going to be a property ALLOW VISUALS IN THIS REPORT TO USE DRILLTHROUGH TARGETS FROM OTHER REPORTS (✓Check it) you have to click that and then click OK.

Note: Do this process for both the reports (Source & Target)

Save the report and then publish it. If you forget to do this in Power BI desktop that’s okay, I’m going to show to do it in the service also.

Go to your workspace where you published these reports, if you forgot to set that property in your Power BI desktop. Once you go to your report network space, you’ll see a little gear on either one of those reports. You need to remember this property to be set for both the target and the source center target however you want to line them up.

You click on that gear (SETTINGS) you scroll to the bottom if it doesn’t appear, and you’ll see CROSS REPORT DRILL THROUGH make sure it’s gold (TOGGLED ON) and repeat that for both reports.

Just need to make sure that table and column from your target report exists in the source report and ensure that you can get the context of that value when you pass it in other words. I have a table that contain order number so when I right click, it’s going to grab that order number pass it to the other report and filter that page by that order number.

Step 4. Go to your SOURCE REPORT. What you want to do is finally you know the order that you want to drill through RIGHT CLICK it → SELECT DRILLTHROUGH and what you going to see is the name of the page with the actual report name in brackets. So, I’m going to go from my source page over to THE CUSTOMER DETAILS PAGE IN THE TARGET REPORT.

Step 5. Now what’s going to happen is, it takes that order number and passes it to another report in the same workspace with keeping all filters and also filter the report for that order number that you select while initiating the drill through.

Important points to note:

Both these report source and target must be in the same workspace.
The table and the column names must be exact same across both of those reports.
If you set Keep all filters to ON when you set up the target page, filter context from the source visual can include the following:

Report, page, and visual level filters that affect the source visual.
Cross-filter and cross-highlighting that affect the source visual.
Slicers and sync-slicers on the page.
URL parameters.