CAML queries, SSRS, and datetime fields
I’ve been using Enesys Software’s product RS Data Extension to query data out of SharePoint 2007 lists in SQL Server (2005) Reporting Services. One quirk with the queries is that they use CAML and the dates must be in a particular format, ISO8601.
I wanted users to use the standard date-picker, but there is no way to format the date inside the query. My work-around is a classic Reporting Services pattern – create an internal parameter that does the formatting for you. The format string for VBScript is “yyyy-MM-ddTHH:mm:ss”.
Below is a screenshot of the Report Parameters dialog. I used the following code to just grab today’s date (no time).
=CDate(CDate(Now()).ToString("MM/dd/yyyy"))
For the second parameter, I create an internal parameter that is dependent on the first. This will prevent it from showing on the report and it will not be filled in until the start date is chosen. Default value:
=CDate(Parameters!StartDate.Value).ToString("yyyy-MM-ddTHH:mm:ss")
I created a test report to show how this works. Here is the report definition:
Below is the report preview: You can now use the parameter in your CAML query.
One other note on CAML date fields. The time is ignored by default. If you want to include the time, you must specify another attribute. Example:
<Geq>
<FieldRef Name="LastModified" />
<Value Type="DateTime" IncludeTimeValue="TRUE">@LastModified!</Value>
</Geq>
I found a helpful post on CAML & DateTime that might be useful for you too.
Hi,
Though this is true that CAML queries require date in ISO format, Enesys RS Data Extension will automatically transform a DateTime report’s parameter into the appropriate ISO format.
Which version of Enesys RS Data Extension are you using?
By the way, just a small typo: a parameter is supposed to be surrounded by @ and ! :
@LastModified!.
Regards,