Find out usage of cubes in reports

Ralf Zerfass

Hi,

I want to find out, in which application studio reports the cube ABC is referenced, if possible both, in cell formulas as well as in list definitions.

Is there a standard feature to do this in Version 11 or does anybody have quick solution with a SQL-Query on the repository tables?

Thanks for any tipps,

Ralf

MikeLewis

This is Version 11 SQL Code:

Use HotelCore_Repository

Declare @ReportUniqueName nvarchar(100)='RHRBudgetingMainForm' -- Must be UNIQUE Name
Declare @XMLDoc XML
Declare @DocHandle int

select
@XMLDoc=XMLContext
from coReportH R
Where R.Name=@ReportUniqueName
and R.StampVersion= ( Select Max(R2.StampVersion)
from coReportH R2
Where R2.Name=R.Name
)

EXEC sp_xml_preparedocument @DocHandle output, @XMLDoc

Select Distinct
@ReportUniqueName as ReportName,
CubeUName
From OpenXML(@DocHandle,'/ReportContext/ReportObjects/ReportObject/List/ServerList',2)
With ( CubeUName nvarchar(100)
)

EXEC sp_xml_removedocument @DocHandle