Share

Querying Report Definitions

Have you ever been in the process of replacing or modifying a field and wondered what reports will be impacted? It is possible to run a query that lists out reports that contain certain text in their definition using the attribute BodyText which has the definitions for both Report Wizard Reports and SSRS RDL file reports.

For example, say that my entity new_asset has a field called new_value. We are planning on splitting out cei_value into two fields for different methods of measuring the value of the asset and want to review reports that may need to be updated.

SELECT Name, Description
FROM FilteredReport
WHERE BodyText LIKE '%new[_]asset%' AND BodyText LIKE '%new[_]value%'
ORDER BY Name

I can run the query to get a list of reports that contain the text "new_asset" and "new_value" in their definition. Of course this might include reports that reference the cei_value attribute of another entity as long as they reference the cei_asset or even reports that contained "cei_value" somewhere in the comments but it would give us a good starting point. For CRM online, I'd use the equivalent fetch along with a tool like Tanguy's XrmToolbox FetchXml Tester.

<fetch version="1.0"   output-format="xml-platform" mapping="logical"   distinct="false">
  <entity   name="report">
    <attribute   name="name" />
    <attribute   name="description" />
    <order attribute="name"   descending="false" />
    <filter   type="and">
      <condition   attribute="bodytext" operator="like"   value="%new[_]asset%" />
      <condition   attribute="bodytext" operator="like"   value="%new[_]value%" />
    </filter>
  </entity>
</fetch>