Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi
Is it possible for Power BI to report on itself?
I want to set up a PBI file that reports on the folder structures I have in my Report Server environments so that I can do comparisons between Dev/UAT/Prod etc.
Is it possible?
Solved! Go to Solution.
Hi @Reportex,
You can try to utilize Power BI report server rest API:
https://docs.microsoft.com/en-us/power-bi/report-server/rest-api
https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0#/
Best Regards,
Qiuyun Yu
Thanks for the answers, all very useful.
Used the rest-api call in the end using an odata connection to get the catalog items and developed from there:
http://<myhost>/reports/api/v2.0/CatalogItems
The documentation also mentions being able to retrieve the definition of the report
"Using the REST API, you can, for example, navigate the folder hierarchy, discover the contents of a folder, or download a report definition. "
But haven't found out a way of doing that yet - any ideas?
You need https://app.swaggerhub.com/apis/microsoft-rs/pbirs/2.0#/CatalogItems/GetCatalogItem
You pass in the GUID (ID) of the item. It returns the RDL content. Can't say I've used it directly, as you can tell I'm pretty old school and tend to go kick the database until it gives me what I want.
We use the REST API via PowerShell and the ReportingServicesTools library Micorosft Provide.
Thanks stpnet, not quite the report definition I was after, but I'll keep digging.
I'm old school too, but trying to get access to the db at ClientCo would take forever...
You can query the backend DB for each instance.
I'm pretty sure there are some sync tools out there. Though not sure if they give you whats different or just align everything using some clever magic pixies.
A query like this gives you what's in the catalog. The underlying GUIDS will be different on the different servers but the overall elements (folders/reports/datasets) etc should be the same.
SELECT r.ItemID ,r.Path ,r.Name ,r.ParentID ,CASE r.Type WHEN 1 THEN 'Folder' WHEN 2 THEN 'SSRS Report' WHEN 3 THEN 'Branding Content' WHEN 5 THEN 'Data Source' WHEN 8 THEN 'KPI Dataset' WHEN 11 THEN 'KPI Card' WHEN 13 THEN 'PBIX Report' END AS ContentType --,r.Property ,r.Description ,r.Hidden -- 0 or 1 --,r.CreatedByID ,c.UserName AS CreatedBy ,r.CreationDate ,m.UserName AS ModifiedBy ,r.ModifiedDate --,r.MimeType --only useful for things like images etc. --,r.SnapshotLimit --,r.Parameter -- xml can be used to figure out params on SSRS reports --,r.PolicyID --,r.PolicyRoot --,r.ExecutionFlag --,r.ExecutionTime --,r.SubType --,r.ComponentID ,r.ContentSize FROM dbo.Catalog r LEFT OUTER JOIN dbo.Users c ON r.CreatedByID = c.UserID LEFT OUTER JOIN dbo.Users m ON r.ModifiedByID = m.UserID WHERE LEFT(PATH,37) <> '/68f0607b-9378-4bbb-9e70-4da3d7d66838' -- ignore branding items AND LEFT(PATH,14) <> '/Users Folders' -- ignore user folders ORDER BY r.PATH;
Another option would be to connect directly to the ReportServer database.
We have a report on our server which queries the ReportServer database execution log, so we can see how many people are running the reports, seems to work very well.
We've connected the model in our report to Active Directory in order to get full user details from the logon name stored in the execution log. Our AD is structured by division/department, so we can see which departments are making use of the server. It's really useful!
Hey I know this post is old, but how did you connect to AD? Is there a general table that you can query and connect to? I see UserName column in the ExecutionLogStorage table, but not sure where companies generally store AD information. I don't believe we use Azure, but I could be wrong... pretty sure it is on Prem.
Hi @Reportex,
You can try to utilize Power BI report server rest API:
https://docs.microsoft.com/en-us/power-bi/report-server/rest-api
https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0#/
Best Regards,
Qiuyun Yu
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.