Archive for March, 2014

Passing “LIKE” Parameters To Fetch XML in SSRS Reports

Today I encountered a situation where I needed to do a LIKE lookup in the Account entity in Dynamics CRM 2013 in SQL Server Reporting Services.

I have a simple Fetch XML Query:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
     <entity name="account">
          <attribute name="name" alias="Name" />
          <filter type="and">
               <condition attribute="name" operator="like" value="@AccountName" />
          </filter>
     </entity>
</fetch>

The problem here is that I need to add “%”s to the condition to search for multiple items (i.e. the LIKE) … if I want to find any instances where the name has “aca” (to find any academy, academics, etc.) … my condition needs to be:

<condition attribute="name" operator="like" value="%" + @AccountName + "%" />

But that in itself doesn’t work … how can I get the “%”‘s into the query … don’t worry, there is a way.

1) Add a parameter to the SSRS Report as you normally would

parameter

2) Create a dataset and add a custom parameter to the dataset

– Name it whatever you would like, but set it to the value of an expression. ┬áMake the expression look like this

="%" & Parameters!AccountName.Value & "%"

dataset

3) Use the “LIKE” parameter just created in your Fetch XML

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
     <entity name="account">
          <attribute name="name" alias="Name" />
          <filter type="and">
               <condition attribute="name" operator="like" value="@AccountNameLike" />
          </filter>
     </entity>
</fetch>

 

At this point, test your work, and you should be able to use LIKE parameters in Fetch XML in SQL Server Reports Service Reports.

Advertisements

Leave a comment