"On or Before" and "On or After" conditions inside FetchXML Reports

Using the Advanced Find function in CRM 2011 is a common way for developers to generate FetchXML to be used inside reports, specially for CRM Online environments where FetchXML is the only way to query the data.
In this example our goal is to create a report that will show all the appointments with start Date on or after the report execution date.
With Advanced Find we can use the "On or After" condition, as shown in the next figures.



To perform the query is necessary to specify the date, is not possible to define today as value for "On or Before" and "On or After" conditions.
The FetchXML generated is the following:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="appointment">
    <attribute name="subject" />
    <attribute name="scheduledstart" />
    <attribute name="scheduledend" />
    <attribute name="regardingobjectid" />
    <attribute name="activityid" />
    <attribute name="instancetypecode" />
    <order attribute="scheduledstart" descending="false" />
    <order attribute="subject" descending="false" />
    <filter type="and">
      <condition attribute="scheduledstart" operator="on-or-after" value="2013-05-13" />
    </filter>
  </entity>
</fetch>

As we can see the date (15 May 2013) is hard coded, the format is yyyy-MM-dd.

With this FetchXML we can start to create the report, the Design window will look like the next figure:



The Preview window will show the same result as the Advanced Find:



Now we can parameterize the query, we start adding a new parameter with name TodayFetchXML:



The data type is Text and the visibility is Hidden:



We also set a default value for the parameter using this expression:

=Format(Globals!ExecutionTime,"yyyy-MM-dd")

We rely on built-in field Execution Time, formatted as yyyy-MM-dd to be compatible with the query.
Now we can edit the original FetchXML, instead of a fixed date we use the TodayFetchXML parameter.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="appointment">
    <attribute name="subject" />
    <attribute name="scheduledstart" />
    <attribute name="scheduledend" />
    <attribute name="regardingobjectid" />
    <attribute name="activityid" />
    <attribute name="instancetypecode" />
    <order attribute="scheduledstart" descending="false" />
    <order attribute="subject" descending="false" />
    <filter type="and">
      <condition attribute="scheduledstart" operator="on-or-after" value="@TodayFetchXML" />
    </filter>
  </entity>
</fetch>

The report is done and ready to be uploaded to CRM.

It's also possible to create parameters for other days, for yesterday or tomorrow we simply add or remove one day:

// Parameter function for @YesterdayFetchXML
=Format(DateAdd("d",-1,Globals!ExecutionTime),"yyyy-MM-dd")

// Parameter function for @TomorrowFetchXML
=Format(DateAdd("d",1,Globals!ExecutionTime),"yyyy-MM-dd")

If we want to modify the report to show the appointments on or after tomorrow, we just change the parameter from TodayFetchXML to TomorrowFetchXML

<condition attribute="scheduledstart" operator="on-or-after" value="@TomorrowFetchXML" />

With TomorrowFetchXML the report will show accordingly only one appointment: