Forum Discussion
Get SQL Server Data from Power BI Report Server's Rest API
Hi, alexyin1053
Your clarification has helped me better understand your needs. You expect to get a list of users with access to each Power BI report. This is available through the API. I wrote a python code:
key api : Power BI Reports - Get Power BI Report Policies - REST API (Power bi report) | Microsoft Learn
key api: Power BI Reports - Get Power BI Reports - REST API (Power bi report) | Microsoft Learn
import requests from requests_ntlm import HttpNtlmAuth url = "http://Yourserver/Reports/api/v2.0/PowerBIReports" headers = { "accept": "application/json, text/plain, */*", "accept-encoding": "gzip, deflate", "accept-language": "en-US,en;q=0.9", "connection": "keep-alive", "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/132.0.0.0 Safari/537.36 Edg/132.0.0.0" } # replace your username and pwd username = "" password = "" response = requests.get(url, headers=headers, auth=HttpNtlmAuth(username, password)) print(response.status_code) reportlist = response.json()["value"] newlist = [] newdict = {} for item in reportlist: newdict = {"id": item["Id"],"ReportName":item["Name"]} newlist.append(newdict) for i in range(len(newlist)): url2 = f"http://YourServer/Reports/api/v2.0/PowerBIReports({newlist[i]["id"]})/Policies" response1 = requests.get(url2, headers=headers, auth=HttpNtlmAuth(username, password)) policies = response1.json()["Policies"] newlist[i]["Policies"] = policies print(newlist)You should be able to get a dictionary like this (which contains a list of user information accessible for each report):
You can then use this dictionary for analysis or import into Power BI.
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Thanks for the reply, and sorry I didn't described our goal very clearly.
Our goal is to display the information on another service website. The information is about all the dashboards currently on the ReportServer, along with the people who have browsing permissions.
SSMS Query SQL code:
SELECT DISTINCT U.UserName, C.Name as ObjectName, C.Path,
C.Description, MU.UserName As LatestModifiedByUserName
FROM [ReportServer].[dbo].[Users] U
join [ReportServer].[dbo].[PolicyUserRole] PUR on U.UserID = PUR.UserID
join [ReportServer].[dbo].[Policies] P on P.PolicyID = PUR.PolicyID
join [ReportServer].[dbo].[Catalog] C on C.PolicyID = P.PolicyID
join [ReportServer].[dbo].[Users] MU on C.ModifiedByID = MU.UserID
Where C.Type = 13 --Filter Type="PBIReport“;
Ideally, we would like to retrieve the upper information directly through the API of the AP Server so that the data is always up to date.
Besides, we also have access to the SQL database, so querying directly to the SQL Server is also a viable option.
Let me know if more information is needed!
Thanks!
Best Regards,
Alex
Hi, alexyin1053
Your clarification has helped me better understand your needs. You expect to get a list of users with access to each Power BI report. This is available through the API. I wrote a python code:
key api : Power BI Reports - Get Power BI Report Policies - REST API (Power bi report) | Microsoft Learn
key api: Power BI Reports - Get Power BI Reports - REST API (Power bi report) | Microsoft Learn
import requests
from requests_ntlm import HttpNtlmAuth
url = "http://Yourserver/Reports/api/v2.0/PowerBIReports"
headers = {
"accept": "application/json, text/plain, */*",
"accept-encoding": "gzip, deflate",
"accept-language": "en-US,en;q=0.9",
"connection": "keep-alive",
"user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/132.0.0.0 Safari/537.36 Edg/132.0.0.0"
}
# replace your username and pwd
username = ""
password = ""
response = requests.get(url, headers=headers, auth=HttpNtlmAuth(username, password))
print(response.status_code)
reportlist = response.json()["value"]
newlist = []
newdict = {}
for item in reportlist:
newdict = {"id": item["Id"],"ReportName":item["Name"]}
newlist.append(newdict)
for i in range(len(newlist)):
url2 = f"http://YourServer/Reports/api/v2.0/PowerBIReports({newlist[i]["id"]})/Policies"
response1 = requests.get(url2, headers=headers, auth=HttpNtlmAuth(username, password))
policies = response1.json()["Policies"]
newlist[i]["Policies"] = policies
print(newlist)You should be able to get a dictionary like this (which contains a list of user information accessible for each report):
You can then use this dictionary for analysis or import into Power BI.
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous
This do solved our problem and also letting us know the limitations of PBIRS' API!
Thanks a lot!
Best Regards,
Alex