topic Re: How to Query Report folder structure of PowerBI report server and see all the hierarchy of folders ? in Report Server

How to Query Report folder structure of PowerBI report server and see all the hierarchy of folders ?

Anonymous — Wed, 08 Sep 2021 18:34:15 GMT

How to Query Report folder structure of PowerBI report server and see all the hierarchy of folders ?

I would like to query PowerBI report server and want to see how folders have been organized .I want the nested structure so that I can make decision in re-designing the folders based on my Business line and sub departments.

I m technical user and not want to see what data is present in the reports but just the structure and objects on report server .

Re: How to Query Report folder structure of PowerBI report server and see all the hierarchy of folders ?

blynchdata — Wed, 08 Sep 2021 21:42:57 GMT

You could try starting with something like this SQL and modify it from here. You'll need access to the SQL Server and the ReportServer database.

USE [ReportServer]
GO

SELECT CATALOG.NAME
,CATALOG.[Path]
,DataSource.NAME datasource
,CATALOG.[Description]
,Created.UserName AS CreatedByUser
,CATALOG.[CreationDate]
,Modified.UserName AS ModifiedByUser
,CATALOG.[ModifiedDate]
FROM [dbo].[Catalog]
LEFT JOIN (
SELECT [UserID]
,[UserName]
FROM [dbo].[Users]
) AS Created ON CATALOG.CreatedByID = Created.UserID
LEFT JOIN (
SELECT [UserID]
,[UserName]
FROM [dbo].[Users]
) AS Modified ON CATALOG.ModifiedByID = Modified.UserID
JOIN DataSource ON CATALOG.ItemID = DataSource.ItemID
JOIN CATALOG cat1 ON DataSource.Link = cat1.ItemID
WHERE CATALOG.[Type] = 2
ORDER BY [Path]
,NAME

Re: How to Query Report folder structure of PowerBI report server and see all the hierarchy of folders ?

d_gosbell — Thu, 09 Sep 2021 00:46:46 GMT

You can also do this in a Power BI Report using an OData connector and connecting to the following endpoint

http(s)://<yourserver>/reports/api/v2.0/CatalogItems

This has a report path column which you can split on / characters to create a hierarchy

Re: How to Query Report folder structure of PowerBI report server and see all the hierarchy of folders ?

Anonymous — Thu, 09 Sep 2021 00:01:02 GMT

Got a error via OData connector using windows auth

Details: "Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (404) Not Found. (Not Found)
OData Version: 4, Error: The remote server returned an error: (404) Not Found. (Not Found)
OData Version: 3, Error: The remote server returned an error: (404) Not Found. (Not Found)"

Re: How to Query Report folder structure of PowerBI report server and see all the hierarchy of folders ?

d_gosbell — Thu, 09 Sep 2021 00:48:11 GMT

Anonymous wrote:

Got a error via OData connector using windows auth

Sorry, there should be a /reports (or whatever you have configured for your portal url) in front of the /api so the endpoint should be as follows

http(s)://<yourserver>/reports/api/v2.0/CatalogItems