3

I'm building a report using SSRS, for a report ran within a SharePoint site (integrated mode). I'm using the "SharePoint List" as the data source (not a web service). My dataset retrieves events from a SharePoint Calendar. I'm using the query builder to add the following CAML query, which works as expected in other environments (such as using the SharePoint web services or the object model).

Here is the whole XML:

<RSSharePointList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 <ListName>Calendar</ListName>
 <ViewFields>
   <FieldRef Name="Title" />
   <FieldRef Name="Location" />
   <FieldRef Name="EventDate" />
   <FieldRef Name="EndDate" />
   <FieldRef Name="Description" />
   <FieldRef Name="fAllDayEvent" />
   <FieldRef Name="fRecurrence" />
   <FieldRef Name="RecurrenceData" />
   <FieldRef Name="Category" />
   <FieldRef Name="ID" />
   <FieldRef Name="Modified" />
   <FieldRef Name="Created" />
   <FieldRef Name="Author" />
   <FieldRef Name="Editor" />
 </ViewFields>
 <Query>
   <Where>
       <DateRangesOverlap>
           <FieldRef Name="EventDate" />
           <FieldRef Name="EndDate" />
           <FieldRef Name="RecurrenceID" />
           <Value Type="DateTime"><Today/></Value>
       </DateRangesOverlap>
   </Where>
   <OrderBy>
        <FieldRef Name="EventDate" />
   </OrderBy>
   <QueryOptions>
     <RecurrencePatternXMLVersion>v3</RecurrencePatternXMLVersion>
     <ExpandRecurrence>TRUE</ExpandRecurrence>
    <CalendarDate><Today/></CalendarDate>
    <DateInUtc>FALSE</DateInUtc>
   </QueryOptions>
 </Query>
</RSSharePointList>

The problem with this query is that it retrieves all recurrent events that were set with a Start Date in the past and where the End Date is in the future, even if none of the recurrent event serie happen Today. I researched thoroughly and tested how to make this work within an SSRS report and haven't been able to make it work.

I would be open to other suggestions. I'm using SharePoint 2013.

  • wow, finally somebody else who is experiencing same issue! I am using SQL Report Builder to generate a report from a sharepoint calendar - issue is that it shows the dates but not able to show weekdays MON - FRI on weekly basis .... how far did you get with this? – Bucki Dec 22 '16 at 7:03
  • @Bucki I ended up using a web service. When creating the data source, select connection type "XML" and add a reference to the old XML web service, e.g. /_vti_bin/Lists.asmx. Then, when you create your the dataset enter your query as text. Below is an example: – Wal Dec 22 '16 at 21:33
  • <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>My Calendar</DefaultValue> </Parameter> <Parameter Name="query" Type="xml"> <DefaultValue> <Query> <Where> <DateRangesOverlap> <FieldRef Name="EventDate"/> <FieldRef Name="EndDate"/> <FieldRef Name="RecurrenceID"/> <Value IncludeTimeValue="FALSE" Type="DateTime"> <Today /> </Value> </DateRangesOverlap> </Where> </Query> – Wal Dec 22 '16 at 21:36
  • </DefaultValue> </Parameter> <Parameter Name="orderBy" Type="xml"> <OrderBy> <FieldRef Name="EventDate" /> </OrderBy> </Parameter> <Parameter Name="queryOptions" Type="xml"> <DefaultValue> <QueryOptions> <RecurrencePatternXMLVersion>v3</RecurrencePatternXMLVersion> <ExpandRecurrence>TRUE</ExpandRecurrence> <DateInUtc>FALSE</DateInUtc> </QueryOptions> </DefaultValue> </Parameter> </Parameters> </Method> <ElementPath IgnoreNamespaces="True">*</ElementPath> </Query> – Wal Dec 22 '16 at 21:37
  • It has its own shortfalls (for instance, with All Day events) but you can iron those out combining data sources. – Wal Dec 22 '16 at 21:38
2

In QueryOptions, if the CalendarDate is set, it needs the date to be a string in the format: yyyy-mm-ddThh:mm:ssZ

Remove <CalendarDate><Today/></CalendarDate> from <QueryOptions>. If you are interested in events happening "Today" only, you don't need to set CalendarDate property.

See Recurring Calendar Events for more information about how CalendarDate options are honored in DateRangeOverlap quries.

  • Thanks for your reply. I read your blog before posting this :) I removed <CalendarDate> from QueryOptions, but the recurrent events with start dates in the past and end dates in the future are still showing up, even if the series doesn't have any instance on Today. – Wal Oct 11 '16 at 19:37
  • @Wal, that is interesting. Ideally only today's events should have shown up. – Nadeem Yousuf Oct 12 '16 at 2:56
  • @NadeemYousuf please suggest how to make the SSRS showing the weekdays Monday to Friday on weekly basis (where the Monday ist first day of each week) and then the relevant entries updates onto the week days. At the moment the report shows all entries in a single row ... – Bucki Dec 22 '16 at 7:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.