Forum Discussion
Resolver IURLs for Other Reports
I need someone cleverer than me to explain what's happening here and if there's any way to solve it.
We're looking at creating a report that links to a bunch of other reports based on data coming out of the RestAPI. My idea was to build URLs by concatenating the generic parts of a report URL with the workspace and report IDs of other reports. This works, to an extent, but I'm confused by the security checks I'm getting. I'm an admin, so have access to everything within the tenant.
I've been playing around and am getting different results for seemingly arbitrary reasons:
- If I build a URL within Notepad and copy it into the address bar, the report opens fine;
- If I open a report and swap the workspace and report IDs in the address bar for those of another report, it sets off a security check and tells me I need to request access - even if it's my report;
- If I create a custom column concatenating data together to form a URL for each report in the table, and map a URL to a button, it sets off the security check - again, even if it's my report;
- If I create a custom column in which every entry is the same URL for a single report, but I copy and pasted the URL into the column builder, then map it to a button the report opens fine.
Can someone explain what's happening? I know that the page the report opens on can change the "ReportSection?" of a URL, but that doesn't seem to be causing the problem.
FIXED IT!!!!!!! It was a stupid error in formatting, but only on the report not in the underlying data. It was also a stupid error on my part for not being consistent.
So, basically what was happening is that all data was being pulled through from the RestAPI correctly and the concatenation was working fine. BUT, when the data was pulling through from PowerQuery to the report the ReportID column was changing all the letters in the ID to uppercase. This meant that the urls on the report were visually different from those in PowerQuery. For example, in PQ the report ID looked like this:
1a23bc4d-56gh...
But when it pulled through to the actual report it looked like this:
1A23BC4D-56GH...
This meant that when PBI tried to open the report it had a spasm because the report ID wasn't quite right, despite having all the same letters in it.
My error when trying to understand it was that, when testing what the hell was happening, I was pulling the urls from different places. So, when testing the Sharepoint side, I was pulling the urls from the report (I'd loaded them into a matrix) so they wouldn't work, but when I was comparing them (as in the examples I gave in my reply above) I was pulling them from PQ, which looked correct. I've literally only just noticed in the last 10 minutes that the case for report ID was wrong on the report.
Why is it happening? I have no idea. But, I've fixed it by simply adding in a 'Transform>lowercase' step on both the ReportID column and the custom url column within PQ. Nothing changes in PQ, but it pulls through to the report correctly now.
Thanks christinepayton for replying. Had you not mentioned that it worked for you and maybe the url structure was incorrect I wouldn't have spent this morning looking back at it.
4 Replies
- christinepayton
Most Valuable Professional
Could you maybe post an example of your concatenated link or the formula you're using? I did exactly what you're referring to in the past with the PBI API data and it did not do this to me, so it may be an issue with your URL structure. Though keep in mind that admins do not have automatic access to everything in the tenant, they still have to be on the workspace to view things (this is true for most areas of M365, like SharePoint admins don't have access to all SharePoint sites by default unless they add themselves to it).
Mat42christinepayton thanks for the reply.
So, as an example, this is the anonymised (simply replaced all the numbers with a 1) url structure from a report if I open it through the service (I know you wouldn't be able to open the report anyway, but I anonymised it so I don't get murdered by our IT):
and here is the same url (anonymised in the same way as above) as created by me within PowerQuery:
The formula when creating the url column is simply a concatenation formula:
"https://app.powerbi.com/groups/"&[WorkspaceId]&"/reports/"&[ReportId]&"/ReportSection?experience=power-bi"
Is there a function I should be adding to it or a suffix so that it recognises the user? As I said, I have access to all of the reports I'm testing this with and some of them I created, but I wind up with a security check message then the permission box below. It happens whether I open from desktop or a published report in service.
There is obviously something I'm not doing correctly because I tested it this morning
with Sharepoint. I opened the above report, copied the url from the address bar (the first anonymised option above), then mapped it to a button on a basic Sharepoint page. Clicking the button, opened the report. But, if I copied the url I built (the second anonymised option above), which to my eyes looks identical, and mapped that to the button, I get the permission window.
- Mat42
FIXED IT!!!!!!! It was a stupid error in formatting, but only on the report not in the underlying data. It was also a stupid error on my part for not being consistent.
So, basically what was happening is that all data was being pulled through from the RestAPI correctly and the concatenation was working fine. BUT, when the data was pulling through from PowerQuery to the report the ReportID column was changing all the letters in the ID to uppercase. This meant that the urls on the report were visually different from those in PowerQuery. For example, in PQ the report ID looked like this:
1a23bc4d-56gh...
But when it pulled through to the actual report it looked like this:
1A23BC4D-56GH...
This meant that when PBI tried to open the report it had a spasm because the report ID wasn't quite right, despite having all the same letters in it.
My error when trying to understand it was that, when testing what the hell was happening, I was pulling the urls from different places. So, when testing the Sharepoint side, I was pulling the urls from the report (I'd loaded them into a matrix) so they wouldn't work, but when I was comparing them (as in the examples I gave in my reply above) I was pulling them from PQ, which looked correct. I've literally only just noticed in the last 10 minutes that the case for report ID was wrong on the report.
Why is it happening? I have no idea. But, I've fixed it by simply adding in a 'Transform>lowercase' step on both the ReportID column and the custom url column within PQ. Nothing changes in PQ, but it pulls through to the report correctly now.
Thanks christinepayton for replying. Had you not mentioned that it worked for you and maybe the url structure was incorrect I wouldn't have spent this morning looking back at it.
- christinepayton
Oh gosh now that you mention it, I did run into that exact same thing when trying to relate tables on the report ID, with the case not matching in various places - totally forgot about that!
