locked
CRM 2015 - SSRS Report cannot set date in pre filter. RRS feed

  • Question

  • I have a specific requirement from a customer that the pre-filter of an SSRS report should contain an "on-or-after" and an "on-or-before" clause.  Effectively similar to a last 7 days filter but the customer want to specify the dates as they find that much easier to understand.

    I can make it work by hard coding the default value thus:

    ="<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'><entity name='new_meeting'><all-attributes/>" + 
    "<filter type='and'>" +
    "<condition attribute='scheduledstart' operator='on-or-after' value='2015-11-10' />" +
    "<condition attribute='scheduledstart' operator='on-or-before' value='2015-11-17' />" +
    "<condition attribute='statuscode' operator='eq' value='2' /></filter>" + 
    "</entity></fetch>"

    However if I inject a date formula into the pre filter default value I get blank values when I deploy and run the report.

    ="<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'><entity name='new_meeting'><all-attributes/>" + 
    "<filter type='and'>" +
    "<condition attribute='scheduledstart' operator='on-or-after' value='" + Format(DateAdd(DateInterval.Day, -7, Today()),"yyyy-MM-dd") + "' />" +
    "<condition attribute='scheduledstart' operator='on-or-before' value='" + Format(Today(),"yyyy-MM-dd") + "' />" +
    "<condition attribute='statuscode' operator='eq' value='2' /></filter>" + 
    "</entity></fetch>"

    Am I doing something very wrong here?  Is this something which should work?

    My basic issue is that I want to use an end date which is the date on which the report is run and then work backwards a number of days to create the default query window.

    Tuesday, November 17, 2015 4:05 PM

Answers

  • Hi Gordon,

        I am able to get this to work by parameterizing the date values you want to use in your report. If you modify your FetchXML query like the following example, and add parameters (see screenshots), the report will work as expected:

    <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="createdon" operator="on-or-after" value="@StartDate" />
          <condition attribute="createdon" operator="on-or-before" value="@EndDate" />
        </filter>
      </entity>
    </fetch>

    Hope that helps!

    John

    Wednesday, November 18, 2015 3:55 AM

All replies

  • Hi Gordon,

        I am able to get this to work by parameterizing the date values you want to use in your report. If you modify your FetchXML query like the following example, and add parameters (see screenshots), the report will work as expected:

    <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="createdon" operator="on-or-after" value="@StartDate" />
          <condition attribute="createdon" operator="on-or-before" value="@EndDate" />
        </filter>
      </entity>
    </fetch>

    Hope that helps!

    John

    Wednesday, November 18, 2015 3:55 AM
  • Yes, that was my original solution and I have reverted back.

    It just seemed strange that the pre-filter can take a formula to concatenate a string but seems to completely ignore my date values.  Presumably this is something to do with the order that the report is processed.  Thought I might have been doing something wrong. 

    It is a shame that I can't use the pre-filter.

    Thursday, November 19, 2015 10:38 AM