Monday, September 7, 2015

AX 2012 – Have a list of the SSRS Reports to a specific Layer using Model tables



Hi Guys


Below a quick code to have the list of SSRS Reports created or modified to a specific Layer or Model.


Run this against the Model DB

By Layer will be :


use ModelDb


SELECT a.name, a.origin, a.axid, a.parentid, b.modelid, c.LayerId, d.name, e.name


  FROM ModelElement a


  join ModelElementData b


  on a.ElementHandle = b.ElementHandle


  join model c


  on b.ModelId = c.id


  join layer d


  on d.Id = c.LayerId


  join ModelManifest e


  on e.ModelId = b.modelid


  where a.elementType = 85


  and   c.LayerId <> 0


  and   d.name = 'VAR'





By Model will be :



use ModelDb


SELECT a.name, a.origin, a.axid, a.parentid, b.modelid, c.LayerId, d.name, e.name


  FROM ModelElement a


  join ModelElementData b


  on a.ElementHandle = b.ElementHandle


  join model c


  on b.ModelId = c.id


  join layer d


  on d.Id = c.LayerId


  join ModelManifest e


  on e.ModelId = b.modelid


  where a.elementType = 85


  and   c.LayerId <> 0


  and   e.name = 'ModelName'



Enjoy!

No comments: