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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.