Report on Report Server Folder Structure

[Reportex]

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?

[Reportex]

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]

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.

[Reportex]

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]

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]

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!

[djanszentql]

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 · Accepted 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