Passing DateTime SSRS parameters filters to SharePoint GetListItems CAML query issue

Question

I'm having some issues passing report parameters to a CAML query that's behind my reporting connecting to a SharePoint list. I have a parameter Date (datetime type).

I used =Parameters!Date.Value in my query but by default <CalendarDate> takes the current date as default date and it returns the result for the current date i.e Todays Date. However if I used actual hardcoded dates like 2012-11-03T00:42:34Z the report works fine.

So I'm not sure what I'm missing, My query is as follows

<Query>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
<Parameters>
<Parameter Name="listName">
<DefaultValue>Bookings</DefaultValue>
</Parameter>

<Parameter Name="query" Type="xml">
<DefaultValue>
<Query>
<Where>
<DateRangesOverlap>
<FieldRef Name="EventDate"/>
<FieldRef Name="EndDate"/>
<FieldRef Name="RecurrenceID"/>
<Value Type="DateTime">
<Today/>
</Value>
</DateRangesOverlap>
</Where>
</Query>
</DefaultValue>
</Parameter>

<Parameter Name="queryOptions" Type="xml">
<DefaultValue>
<QueryOptions>
<ExpandRecurrence>TRUE</ExpandRecurrence>
<CalendarDate>=Parameters!Date.Value</CalendarDate>
</QueryOptions>
</DefaultValue>
</Parameter>

<Parameter Name="ViewFields" Type="xml">
<DefaultValue>
<ViewFields>
    <FieldRef Name="Title" />
    <FieldRef Name="EventDate" />
    <FieldRef Name="EndDate" />
    <FieldRef Name="Room" />
    <FieldRef Name="Hosted_x0020_By" />
    <FieldRef Name="External_x0020_Attendees" />
    <FieldRef Name="Catering_x0020_Requirements" />
    <FieldRef Name="Fixed_x0020_Facilities" />
    <FieldRef Name="Other_x0020_Facilities" />
    <FieldRef Name="Company" />
    <FieldRef Name="Layout" />
</ViewFields>
</DefaultValue>
</Parameter>

</Parameters>
</Method>
<ElementPath IgnoreNamespaces="True">*</ElementPath>
</Query>

I tried different formatting options as

=CDate(Parameters!Date.Value).ToString("yyyy-MM-ddTHH:mm:ssZ")

but still its not working, its jus returning results for current date, even if i dont put any value in .

If i put than it returns for the current day. i think somehow the parameter value is not at all getting passed in the query. Kindly Help

what do you see if you put the =CDate(Parameters!Date.Value).ToString("yyyy-MM-ddTHH:mm:ssZ") in a TextBox when you run the report? — Frank Goortani
The value comes proper in the textbox, but the query is not taking the input value,am i doing something wrong while passing parameter? The query just takes the default date as today and getting executed. — Ishan

Ishan Ishan · Accepted Answer · 2012-11-10T08:46:50

Here is another post to the same question which is the solution to this.

Passing DateTime SSRS parameters filters to SharePoint GetListItems CAML query issue

1 Answers