Retrieve List of SSAS 2005 KPI Names using MDX?

I this actually possible? All my research to date suggests that it is not. I know it can be done using XMLA or AMO but these are not available from Reporting Services right?

My goal is to retrieve a list of KPI Names to Reporting Services. These names will then be used as the allowed values list of a paremeter for a KPI report. I previously managed to do it for calculated measures using EXCEPT([Measures].AllMembers, [Measures].Members).

I can currently think of three options, none of which I like!

1) Create SQL CLR Proc and use AMO to retrieve KPI Names and return result set

2) Create SQL CLR Proc and use XMLA to retrieve KPI Names and return result set

3) Periodically run some app which uses one of the above methods to populate a "Current Set of KPIs" table

Please, somebody tell me there is another way :)

Eventually I worked out a way to do it. I found that there is a "OLEDb Schema GUID" for KPIs in SSAS. I wrote a SQL CLR Procedure to connect to SSAS via OLEDB but I afterwards realised SQL Server 2005's OPENROWSET would probably have done the trick too. Anyway, the code I used in SQL CLR is:

Code Snippet

// Open the Analysis Server connection

DataTable dt = new DataTable();

SqlMetaData[] metaData;

using (OleDbConnection cnn = new OleDbConnection(cnn_str))

{

cnn.Open();

// Execute the XMLA Schema request, convert rows to SqlDataRecord for sending to the Pipe.

Guid guid = new Guid("{2AE44109-ED3D-4842-B16F-B694D1CB0E3F}"); // The GUID for MDSCHEMA_KPIS

dt = cnn.GetOleDbSchemaTable(guid, null);

}

Yay, I now have a way to list KPIs in Reporting Services.|||

The ASSP project (a .NET stored proc project for SSAS) has a way to do just what you're looking for:

CALL ASSP.Discover("MDSCHEMA_KPIS")

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=XmlaDiscover&referringTitle=Home

|||Thankyou kindly furmangg, this is excellent. And to think, I ended up writing a CLR SP using OleDb to query SSAS...

I can't believe I overlooked the ASSP project, it is full of so much useful stuff.