Fiscal Date Query Operators in FetchXML

The following example shows a FetchXML expression that finds all orders fulfilled in the last fiscal period, according to the organization’s fiscal year settings. For example, if the organization uses fiscal months, the query returns orders fulfilled in the last fiscal month. If the organization uses fiscal quarters, the query returns orders fulfilled in the last fiscal quarter. If the organization uses fiscal semesters, orders fulfilled in the last fiscal semester are returned.

<fetch>
<entity name=’order’>
<attribute name=’name’/>
<filter type=’and’>
<condition attribute=’datefulfilled’
           operator=’last-fiscal-period’/>
</filter>
</entity>
</fetch>

The following example shows a FetchXML expression that finds all accounts created in FY08.

<fetch>
<entity name=’account’>
<attribute name=’name’/>
<filter type=’and’>
<condition attribute=’createdon’ operator=’in-fiscal-year’ value=’2008’/>
</filter>
</entity>
</fetch>

The following example shows a FetchXML expression that finds all opportunities with an estimated close date in the next three fiscal years, based on the organization’s fiscal year settings. The value for x is specified in the value attribute of the condition tag.

<fetch>
<entity name=’opportunity’>
<attribute name=’name’/>
<filter type=’and’>
<condition attribute=’estimatedclosedate’
           operator=’next-x-fiscal-years’
           value=’3’/>
</filter>
</entity>
</fetch>

The following example shows a FetchXML expression that finds all orders fulfilled in period three of any fiscal year, according to the organization’s fiscal year settings. The fiscal period value is specified in the value attribute of the condition tag. If the organization uses fiscal months, the query returns results from M3. If the organization uses fiscal quarters, the query returns results from Q3. If the organization uses fiscal semesters, no results are returned; there are only two semesters, and the value supplied is therefore out-of-range.

<fetch>
<entity name=’order’>
<attribute name=’name’/>
<filter type=’and’>
<condition attribute=’datefulfilled’
           operator=’in-fiscal-period’
           value=’3’/>
</filter>
</entity>
</fetch>

The following example shows a FetchXML expression that finds all orders fulfilled in period three of fiscal year 2008, according to the organization’s fiscal year settings. If the organization uses fiscal months, the query returns results from M3. If the organization uses fiscal quarters, the query returns results from Q3. If the organization uses fiscal semesters, no results are returned; there are only two semesters, and the value supplied is therefore out-of-range.

<fetch>
<entity name=’order’>
<attribute name=’name’/>
<filter type=’and’>
<condition attribute=’datefulfilled’ operator=’in-fiscal-period-and-year’>
<value>3</value>
<value>2008</value>
</condition>
</filter>
</entity>
</fetch>

The following example shows a FetchXML aggregation expression that sums the total amount of orders fulfilled and groups the result by fiscal semester and fiscal year.

<fetch aggregate=’true’>
<entity name=’order’>
<attribute name=’totalamount’ aggregate=’sum’ alias=’total’/>
<attribute name=’datefulfilled’ groupby=’true’ dategrouping=’fiscal-period’/>
</entity>
</fetch>

Concepts

Other Resources