Start off, I'm a novice with SharePoint, we have v2010, I am admin of my site, have SharePoint designer available to me but it is not supported by our central IT that runs the server and I want to use as little programming as possible (I'm not a coder).
So ultimately, I'm am trying to export SharePoint Calendar data so that I can produce various reports, etc in an automated fashion. One of the bits of data I'm wanting to analyze is actual duration of a recurring event... i.e. if a recurring event is 1hr, each Tuesday for a period that contains 5 Tuesdays, the value I'm looking for is 5.0
I understand that SP stores a recurring event as a single event with 'meta-data' that describes the recurrence from which it extrapolates those recurring events on various Views.
So far, I've been able generate the .iqy file for the All Events view, load it into Excel set to auto-refresh on open, add columns that calculate total time, generate various charts/pivots from said data, then have Excel export that data as HTML on close/save. All automated and get the best of both worlds... SharePoint interface and Excel's pivot/charting. Additionally I have custom Columns that pull data and, through linking with Access, run VBA/SQL code to fill in other columns with non-sharepoint data (e.g. I take the Created By, trim out the domain\ in a calc. field in sharepoint, then populate a supervisor field based on that calculated column with Access. I run the VBA function via scheduled task on a nightly basis so the supervisor field gets populated routinely. Then in Excel I can filter/group by the supervisor field).
However, the All Events view only has one record for each recurring event with the start date being the date of the first recurrence and end date of the last... this means for the above example, 5 Tuesdays of 1hr instead of being 5hrs turns into ~800+hrs.
So I see that I can create a new view using the Standard View, with Expanded Recurring Events
, and in the browser of that view it looks great... I see ALL instances of the recurring event starting with today and can click next or previous to see all existing... however, when I export via the iqy, I only get a very small snippet... about 16 rows with no apparent pattern (i.e. I have a calendar from 2009 - 2016, the iqy results in Excel shows 1 recurring event from 2012, a few from 2014 some recurring some not, and the remaining from various months in 2016... and there are way more than 16 recurring events... roughly 200 that extrapolate to ~400+ events).
My guess is that the expanded View displayed in browser is processed from the data in realtime somehow which does not occur in the iqy process AND some kind of filter that limits the output. In looking at the .aspx code for that view page in designer, I do not see how that works and that's about my limit of my knowledge with designer.
I looked at the built-in Graphing and analysis capabilities of SharePoint, but they are very limited, require lots of customization, etc... just doesn't hold a candle to what I can do and how fast I can do it in excel. Extremely frustrated that the actual total duration of a recurring event is that hard to get at.
So my next thought is that if I can access the recurrence data (I see the recurrence ID, column), then I can try and calculate that myself... i.e. filter out the recurring events (using the existing boolean Recurrence column), subtract any 'deleted items' from a recurrence (i.e. they deleted a specific occurrence in a recurring event, sharepoint creates a standalone event with 'DELETED:" prepended to the Title, get the recurrence data and calculate the actual duration myself and factor that back into the non-recurring data... but I cannot seem to figure out how I can get at this data, let alone interpret it if I could.
I understand that you can possible export recurrence meta data to iCal or .cvs through Outlook, but I do not see a good way to automate that. (example post
). I also see a TechNet article
on using CAML to pull such data. I kinda get the gist of CAML being an XML/XSLT'ized version of SQL WHERE conditions, but not quit getting how I would execute that from Excel or Access to get at the data (i.e. embed that into my current workflow). I'll keep reading on that front to see if I can see anything that triggers info I can see within the SP panels or SP Designer.
Feel like it shouldn't be this difficult to automate all this, but damn! Any help on a way to get at the actual total duration of a given recurring event that I could work into my above workflow (or even a possible way to streamline/cut out/augment the above work flow... say with a calculated field that could get me the actual duration, would be awesome. TIA