locked
CRM 2011: How do a SSRS Report using parametrized FetchXML with a LIKE operator RRS feed

  • Question

  • I need to use a LIKE operator in a Parametrized FetchXML.  I want use the parameter so that the user running the report can change this as needed through the SSRS UI

    It works when I use just LIKE operator (with the percent signs)

     

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
    <entity name="account">
    <attribute name="name" />
    <attribute name="primarycontactid" />
    <attribute name="telephone1" />
    <attribute name="accountid" />
    <order attribute="name" descending="false" />
    <filter type="and">
    <condition attribute="bds_tripday" operator="like" value="%A%" />
    </filter>
    </entity>
    </fetch>

     

    It kinda works when I use the just the Parameter (it doesn't find wildcard matches)

     

    <filter type="and">
    <condition attribute="bds_tripday" operator="like" value="@myParameter" />
    </filter>

     

    It doesn't work at all if use both (no records are found when @parameter is set to 'A')

     

    <filter type="and">
    <condition attribute="bds_tripday" operator="like" value="%@myParameter%" />
    </filter>

     

    I can get this to work if don't apply the filter to FetchXML  and instead filter the resultset in SSRS (I'm not a big fan of this because of the amount of data that has to be passed back and forth)

    Thanks in advance for any help or ideas


    Wednesday, May 4, 2011 6:54 PM

Answers

  • Go into the properties of the dataset and click on the Parameters "tab".  Enter the following for your parameter value: ="%" & Parameters!myParameter.Value & "%"

    Then use the standard value for a parameter in the condition value:

    <filter type="and">
       <condition attribute="bds_tripday" operator="like" value="@myParameter" />
    </filter>

    Hope this helps!

    Rich



    Friday, May 13, 2011 8:00 PM

All replies

  • Go into the properties of the dataset and click on the Parameters "tab".  Enter the following for your parameter value: ="%" & Parameters!myParameter.Value & "%"

    Then use the standard value for a parameter in the condition value:

    <filter type="and">
       <condition attribute="bds_tripday" operator="like" value="@myParameter" />
    </filter>

    Hope this helps!

    Rich



    Friday, May 13, 2011 8:00 PM
  • Thanks!!
    Friday, May 13, 2011 8:54 PM