Solved

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

Posted on 2016-08-26
7
364 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 18

Expert Comment

by:Walter Curtis
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 18

Expert Comment

by:Walter Curtis
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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 18

Expert Comment

by:Walter Curtis
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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 lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

751 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