[TIP] Converting Date/Time Parameters to Text for FetchXML Queries in SSRS Reports

Introduction: When working with SQL Server Reporting Services (SSRS) in conjunction with FetchXML queries, you might encounter a scenario where date/time parameters cannot be used directly. This article outlines a practical solution to convert date/time parameters to text type, enabling their use in FetchXML queries within SSRS reports.

Scenario: In a typical SSRS report, you may have two date/time parameters: Parameters!StartDate.Value and Parameters!EndDate.Value. However, when attempting to use these parameters within a FetchXML query, you may run into issues as FetchXML might not support date/time type parameters directly.

Solution: To resolve this, create two additional text type parameters named StartDateText and EndDateText.

Set the default & available values of these parameters to convert the date/time parameters to text format using the Format function in SSRS.

Below are the steps to achieve this:

1. 1. Create Text Type Parameters:

   • Navigate to the report parameters section in SSRS.
   • Create two new text type parameters named StartDateText and EndDateText.

2. 2. Set Default Values:

   • For StartDateText, set the default value expression as: =Format(Parameters!StartDate.Value, "MM/dd/yyyy")
   • For EndDateText, set the default value expression as: =Format(Parameters!EndDate.Value, "MM/dd/yyyy")
   • 

   • 
     
     
     

3. 3. Set Available Values:

   • For both StartDateText and EndDateText, set the available values expression to the same expressions used for default values, and leave the Label field empty.
   • 
     
   • 
     
     
     

4. 4. Hide Text Type Parameters:

   • Mark the StartDateText and EndDateText parameters as hidden so they won't be visible to the report users.

5. 5. Modify FetchXML Query:

   • Update the FetchXML query to utilize the StartDateText and EndDateText parameters in the filter conditions instead of the original date/time parameters.

Example FetchXML Query Modification:

FetchXML filters part: 
<filter type='and'>
  <condition attribute='createdon' operator='on-or-after' value="@StartDateText" />
  <condition attribute='createdon' operator='on-or-before' value="@EndDateText" />
</filter>

Conclusion:

By creating additional text type parameters and formatting the date/time parameters as text, you can seamlessly use these parameters within FetchXML queries in SSRS reports. This solution ensures that your SSRS reports can interact with FetchXML queries effectively while handling date/time parameters.