Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Reportex
Regular Visitor

Report on Report Server Folder Structure

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?

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Reportex
Regular Visitor

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?

 

 

Anonymous
Not applicable

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...

Anonymous
Not applicable

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;
jcollinson2001
Resolver IV
Resolver IV

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.

v-qiuyu-msft
Community Support
Community Support

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors