Friday, June 05, 2015

AX2012 - Listing reports which run against AX SSAS Cubes

Just to share a quick tip. If you need to know which SSRS reports in AX gather data from AX OLAP cubes, you can run the query below in SSMS against the report server database.
      
 SELECT a.Path, a.Name as FullName,
 SUBSTRING(a.Name, 1, CHARINDEX('.', a.Name)-1) as ReportName,
 SUBSTRING(a.Name, CHARINDEX('.', a.Name)+1, 1000) as DesignName,
 a.Parameter, b.Name, b.Extension
 FROM Catalog a
 JOIN DataSource b
 ON a.ItemID = b.ItemID
 WHERE b.Name = 'DynamicsAXOLAP' 
 Order by a.Name, b.Name, a.Path          

The result is like this:

There are report name, design name and an XML presentation of the Parameters. The ReportName column contains the name of the reports, which match the nodes under \SSRS Reports\Reports in AOT.

This posting is provided "AS IS" with no warranties, and confers no rights.