SharePoint Date & Time Fields: Pivot Table Issues

Hello Everyone,

This issue is hard to explain, so look at the picture since a picture is worth 1,000 words!

Every single item with a different time captured in SharePoint is read in as its own entity, which makes it extremely tedious when trying to run a report for a certain day, week, month, quarter, etc.

The issue is actually extremely easy to resolve and actually is caused by how your pivot table is set up, rather than how SharePoint operates or your connection.  This is how most users would try and set up the pivot table since it makes logical sense.

This pivot table set up is actually the cause of the issue.  Instead of putting the Date & Time Field (in my case Start Time) in the Report Filter, put the field into the row labels section as the image below illustrates.

This will give you a result similar to the following:

 

Setting the Pivot Table up in this fashion will give you a plethora of filters that resolve the issue of the appended time.

These Date Filters will ignore the time and give you the desired date rangers that you were trying to accomplish through the Report Filter.

 

There is an issue with pulling only one day’s worth of data.  For example, if I used the Equals Filter and tried putting in 6/10/2013, the pivot table would not display any data.  In order to get a particular day’s information, use the Between filter and set the end date to the next day.

There is not a day in-between 6/10/2013 & 6/11/2013, so the pivot table will only show me the results for 6/10/2013.

I hope this solves some headaches!

Dan