using SSRS to generate a report based on a Calendar in Sharepoint 2013

Question

We use the calendar in SP2013 to collect information when a user is away on holiday and when there will be any visitors. It works fine as a calendar but now struggling to generate a report in SSRS to show all the days when on holiday.

Example; this is what calendar looks like in SP2013

I am struggling to show all the consecutive days from Friday 03/02/2017 until 17/02/2017.
In this case, the Reports is just pulling the start date =Fields!Start_Time.Value

As you can see, from the result above, it does not show all the consecutive days but only the start time. Yes, you may say "...well you specified to show from the start time" but my question is here how to modify it so it shows HOLIDAY on all the dates relative to the start & end date.

Any ideas / suggestions?

The SSRS Query Type is as following:

<RSSharePointList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ListName>Staff Movements</ListName>
  <ViewFields>
    <FieldRef Name="Title" />
    <FieldRef Name="Location" />
    <FieldRef Name="EventDate" />
    <FieldRef Name="EndDate" />
    <FieldRef Name="Category" />
    <FieldRef Name="User" />
    <FieldRef Name="Department" />
    <FieldRef Name="Position" />
    <FieldRef Name="Visitors_x0020_Name" />
    <FieldRef Name="Visitors_x0020_Business" />
    <FieldRef Name="Visitors_x0020_purpose" />
    <FieldRef Name="Visitors_x0020_Visiting" />
    <FieldRef Name="Created" />
  </ViewFields>
</RSSharePointList>

Answer 1

I think you want to retrieve the information from Calendar which are mainly recurrent events to show in the reports.

If you specify the calendar start date and end date (2 weeks). It actually preset only one record in the list If you retrieve directly from the list you can only see one item coming into a form or a report.

There is a field call “RecurrenceData” which hold the complete information of that event in xml format.

You just have a look into this to understand https://fatalfrenchy.wordpress.com/2010/07/16/sharepoint-recurrence-data-schema/

So you have to build a logic to display one event into multiple records in the report and do filter for it.

You have to add the VB logic to convert that xml into a multi data in Report Properties under “Code” section and you can also reference property to solve your problem.

Please see the example in this link https://technet.microsoft.com/en-us/library/dd255294(v=sql.100).aspx

Please let me know if my solution suits for you