Solved

SharePoint Data Export: get recurring events individual or actual duration of recurring events.

Posted on 2016-08-26
7
101 Views
Last Modified: 2016-09-03
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
0
Comment
Question by:jiriki
  • 4
  • 3
7 Comments
 
LVL 14

Expert Comment

by:SneekCo
ID: 41774209
Wow - that's quit a system... let's look at what you are trying to do..

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

From a database design point of view this could be accomplished in this fashion. Each meeting as described above could have a common identifier, call it a unique meeting ID. In other words, let's say we have a team staff meeting every Tuesday for one hour for five weeks. All of those meeting would share a common meeting ID of "123". The duration can continue to use the field duration, which may have to be calculated in some way, but the goal is to have information you can query and use. So a query would be -  (in plain English) - Give the sum of the value of the duration field for each meeting with an ID of "123". The result would be "5". If you add a three hour meeting with the ID of "123" and run the query again, the value would be "8",  I think you get the picture....

That is the basic design I would recommend... all of the other text is great info, but you may need to go back to the drawing board and don't think too deep, don't complicate things.... (which, honestly, I am afraid you may have...)

Hope that helps...
0
 

Author Comment

by:jiriki
ID: 41774824
You've summed up my requirement, likely better than me, but actually accessing said data seems to be the trick I've failing at. i.e. to calculate the request "Give the sum of the value of the duration field for each meeting with an ID of '123'", I have to have specific data.

From standard views I can directly reference the Start and End Timestamp, Event ID and the Boolean status if Recurrence is set for an event.   From the Start and End times, I can get the duration of a single event (assuming it is less than 24 hours, which for my purposes will be a given).  The start date reflects the time of the first occurrence and then End date reflects the end time of the last occurrence, thus filter out time only, End minus First gives me single event duration in hours/minutes.

What I can't seem to query is the info required to 'calculate' the number of recurring events... i.e. First Tuesday of every Week or repeat 50 times, or worse "no end date" (which I've not delved into how this affects the End Time, but this should be an error condition anyway and corrected as there should be no infinite recurrences in our calendars).

So I found this article "Accessing SharePoint Lists with Visual Basic for Applications" which seems to set the stage.  Referencing back to the TechNet article which uses the SPQuery object to define the CAML query in pre-defined variable within that object (literally "Query"), I need to find a similar method to replicate the 'SPQuery.Query' within the VBA SOAP call... at least that's my thought process.

It seems the crux for me is exactly how to integrate CAML query into a SOAP call via VBA to recover the recurrence data...
0
 
LVL 14

Expert Comment

by:SneekCo
ID: 41774838
(This is just a quick tip, my brain is still processing the rest)

There is a really good way to work with SP web services available called "SPServices".  

http://spservices.codeplex.com/

Hate to point you in a different direction, but in the long  run this may be more efficient than using VBA and SOAP.

Just a thought..
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Accepted Solution

by:
jiriki earned 0 total points
ID: 41775092
OK, think I've solved myself and had to get bits and pieces from all over.

So the CAML query has to go into the SOAP "envelope" in the struction of <Envelope><body><function_call><listName><listView><query><Query>

... the key seems to be the <query><Query></Query></query> double-up which I pulled from this post.

So as an example, below code responsds with cal events for the current month with recurring events already extrapolated.  I'm guessing (and will start playing with) changing the query would allow you to get the non-extrapolated recurring event and have a value for the Recurrence raw data.

Hope this helps someone in the future!

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ExampleCAML_SOAP_QUERY_FOR_SP_CAL()
'
' My bastardization of the following to try and pull recurring event information from a sharepoint calendar
 'QUERY IDEA PULLED FROM http://social.technet.microsoft.com/wiki/contents/articles/32133.sharepoint-2013-retrieve-recurrence-data-programmatically-from-a-calendar-list.aspx
 'BUILDING SOAP CALL AND VB SYNTAX PULLED FROM http://depressedpress.com/2014/04/05/accessing-sharepoint-lists-with-visual-basic-for-applications/
 'InputBoxDK is a Override function for std InputBox to obfuscate typed in data with '*' FROM https://www.experts-exchange.com/questions/26818311/Password-Mask-in-INPUT-BOX-for-VBA.html
 'IsNotBlank is a custom function to handle null, empty, "", etc

' Returned Attribute.Name possibilities from this specific CAML query.  The Query is specific (see above technet link) with
' recurring events already extrapolated for the given 'month'
'    ows_Title
'    ows_EventDate
'    ows_EventDate
'    ows_fRecurrence
'    ows__ModerationStatus
'    ows__Level
'    ows_UserID
'    ows_ID
'    ows_UniqueId
'    ows_owshiddenversion
'    ows_FSObjType
'    ows_Created
'    ows_Category
'    ows_PermMask
'    ows_Modified
'    ows_FileRef
'    ows_fAllDayEvent
'    ows_MetaInfo

Function ExampleCAML_SOAP_QUERY_FOR_SP_CAL() as string
		Const cSHAREPOINT_SITE As String = "https://<URL to your SITE>/"
		Const cCALENDAR_SITE As String = "https://<URL to yoru SITE>/<Your Calendar>/"
		Const cLIST_SVC_SUFFIX = "_vti_bin/Lists.asmx"
		Public Const cXML_AUTH_FAIL = "401"
		Public Const cXML_BAD_REQUEST = "400"
		Public Const cXML_OK_REQUEST = "200"
	
	    ' Set credentials
	    Dim sUser As String, _
	        sPassword As String, _
	        sErr As String, _
	        sLine As String, _
	        sTab As String, _
	        sIDList As String, _
	        sCAML As String
			  
		 Dim iCurID As Integer
		 
		 Dim oRequest As Object
		 Dim oReturnedRow
		 Dim oAttribute
			  
	    Dim sSOAPURL_List As String, _
	        sSOAPListName As String, _
	        sSOAPViewName As String, _
	        sSOAPAction As String, _
	        sSOAPEnvelope_Pre As String, _
	        sSOAPEnvelope_Pst As String, _
	        sSOAPMessage As String
	        
	    sTab = "     "
	        
	    sUser = InputBox("Enter user ID", "User Name")
	    
	    sPassword = InputBoxDK("Enter Password", "Paswword")
	        
	    ' Set SOAP/Webservice Parameters	    
	    sSOAPURL_List = cCALENDAR_SITE & cLIST_SVC_SUFFIX
	    sSOAPListName = cLIBRARY_GUID
	    sSOAPViewName = cLIBRARY_ALLEVENTS_VIEW
	     
	    ' SOAP Action URL    
	    sSOAPAction = "http://schemas.microsoft.com/sharepoint/soap/GetListItems"
	
	    ' SOAP Envelope       
	    sSOAPEnvelope_Pre = "<?xml version=""1.0"" encoding=""utf-8""?>" & _
	        "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">" & _
	        "<soap:Body>"
	        
	    sSOAPEnvelope_Pst = "</soap:Body></soap:Envelope>"
	    
	    'QUERY IDEA PULLED FORM http://social.technet.microsoft.com/wiki/contents/articles/32133.sharepoint-2013-retrieve-recurrence-data-programmatically-from-a-calendar-list.aspx
	    sCAML = "<query> " & _
	                "<Query> " & _
	                    "<Where> " & _
	                        "<DateRangesOverlap> " & _
	                            "<FieldRef Name='EventDate' /> " & _
	                            "<FieldRef Name='EndDate' /> " & _
	                            "<FieldRef Name='RecurrenceID' /> " & _
	                            "<Value Type='DateTime'><Month /></Value> " & _
	                        "</DateRangesOverlap> " & _
	                    "</Where> " & _
	                    "<OrderBy> " & _
	                        "<FieldRef Name='EventDate' Ascending='True' /> " & _
	                    "</OrderBy> " & _
	                "</Query> " & _
	            "</query> " & _
	            "<viewFields> " & _
	                "<ViewFields> " & _
	                    "<FieldRef Name='Title' /> " & _
	                    "<FieldRef Name='EventDate' /> " & _
	                    "<FieldRef Name='EndDate' /> " & _
	                    "<FieldRef Name='fRecurrence' /> " & _
	                    "<FieldRef Name='RecurrenceData' /> " & _
	                    "<FieldRef Name='Location' /> " & _
	                "</ViewFields> " & _
	            "</viewFields>"
	
		 ' Complete the packet
	    sSOAPMessage = sSOAPEnvelope_Pre & _
	                    " <GetListItems xmlns=""http://schemas.microsoft.com/sharepoint/soap/"">" & _
	                    " <listName>" & sSOAPListName & "</listName>" & _
	                    " <viewName>" & sSOAPViewName & "</viewName>" & _
	                    sCAML & _
	                    " </GetListItems>" & sSOAPEnvelope_Pst
	
	        
	    ' Create HTTP Object	    
	    Set oRequest = CreateObject("MSXML2.ServerXMLHTTP.6.0")
	        
	    ' Call the service to get the List
	    oRequest.Open "POST", sSOAPURL_List, False, sUser, sPassword
	    oRequest.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
	    oRequest.setRequestHeader "SOAPAction", sSOAPAction
	    oRequest.Send (sSOAPMessage)
	    
	    'DESTROY CRENDTIALS
	    sUser = "NOPE"
	    sUser = ""
	    sPassword = "NOPE"
	    sPassword = ""
	    
	    'CHECK RESPONSE OF CALL
	    Select Case oRequest.Status
	        Case cXML_AUTH_FAIL
	            sErr = "RESPONSE: " & cXML_AUTH_FAIL & " - Authentication failed.  Bad ID/PWD or incorrect permissions."
	            GoTo Err_Proc
	        Case cXML_BAD_REQUEST
	            sErr = "RESPONSE: " & cXML_BAD_REQUEST & " - Bad Request.  LIkely SOAP QUERY bad in some way."
	            GoTo Err_Proc
	        Case cXML_OK_REQUEST
	            'PROCESS RESPONSE
	            For Each oReturnedRow In oRequest.responseXML.getElementsByTagName("z:row")
	                ' Get the Current ID
	                iCurID = oReturnedRow.getAttribute("ows_ID")
	                
	                For Each oAttribute In oReturnedRow.Attributes
	                    sLine = sLine & sTab & oAttribute.Name & " | " & oAttribute.NodeValue & vbCrLf
	                Next
	                
	                 
	                sIDList = sIDList & iCurID & "--------------" & vbCrLf & sLine & vbCrLf & vbCrLf
	            Next
	            
	            iCurID = 0

	        Case Else
	            sErr = "RESPONSE: " & oRequest.Status & " - Unknown Failure"
	            GoTo Err_Proc
	    End Select

		ExampleCAML_SOAP_QUERY_FOR_SP_CAL() = sIDList
	    
Exit_Proc:
    Set oRequest = Nothing
    Set oReturnedRow = Nothing
    Exit Function
    
Err_Proc:
    If IsNotBlank(sErr) Then
        MsgBox sErr, vbExclamation, "SharePoint Response Error"
    Else
        MsgBox Err.Number & " - " & Err.Description
    End If
    GoTo Exit_Proc
End Function

Open in new window

0
 
LVL 14

Expert Comment

by:SneekCo
ID: 41775184
Fantastic, give yourself a big pat on the back!!

Have a good one...
0
 

Author Comment

by:jiriki
ID: 41775642
Additional note.  If you want to pull a custom column, simply call it as an attribute with the "ows_" prefixed.  Not sure how to manage this if you have a space in the name as the CAML will not accept spaces in the attribute name, so there is either a way to escape it or 'convert it'

e.g.
varforyour_value = oReturnedRow.getAttribute("ows_YOURCOLUMNNAMEHERE")

Open in new window


My understanding that it is also best practices to reference the column in the Field view section of the CAML so...

"<ViewFields> " & _
                 ...
                 "<FieldRef Name='YOURCOLUMNNAMEHERE' /> " &_
                 ...
</ViewFields> " & _

Open in new window


With regards to limit your results to a specific timeframe, it can get complicated, especially if you get exploded recurring events.  SP always spills over either side of the results, so you basically (again from what I've gathered), parse your results to filter out dates that are outside of your needs.  SP does not include  an actual 'date range' equivalent that I can find and you can run up against a limit to results either technical or forced by the SP Configuration.  Other posters state that basically  say for 1 Year, you need to query each month of the year, prune out results t the month specified, and repeat for each year you want.  *FUN!*
0
 

Author Closing Comment

by:jiriki
ID: 41782786
I was able to formulate a solution on my own. Posted solution.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now