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

 

How to Link Your SharePoint List to Excel!

How to Link Your SharePoint List to Excel:

SharePoint provides the capability to Link your SharePoint list data to an Excel workbook.  This link is a one way street where data updates made on SharePoint will be reflected in the Excel workbook, but any updates made within Excel will not get pushed through to the SharePoint list.

To Create The Data Connection:

1.    Navigate to your SharePoint list

2.    List Tools

3.    List

4.    Export to Excel

5.    Select Open

6.    In Excel select enable

When you use this method you are not just sending the table of data to Excel, you are actually creating the data connection within the workbook.

You can tell if workbook has a connection by doing the following:

1.    Data Tab on the ribbon

2.    Connections

3.    Connections

4.    There should be something along the lines of Owssvr

You can tell exactly where the connection is going to by inspecting the properties.  Within the properties it will say what the URL is, the list ID, & view ID.

This data will stay stagnant until you refresh the data connection.  You can refresh the data connections within your workbook by:

1.    Data Tab

2.    Connections

3.    Refresh All

Many companies would like to report weekly or monthly status based on the list and use Pivot Tables and Pivot Charts to do so.  By creating the Pivot Tables & Pivot Charts based upon the linked table will allow all of your pivots to be updated when the table is refreshed.

Most users do not know how to refresh the data connection between SharePoint and Excel so I typically like to use VBA on the workbook load to refresh the data or creating a button and using VBA there to Refresh All.  In order to create the button or On Open Code, you will need to enable the developer tools.

How to Enable Developer Tools For Excel (Developer Tab):

1.    Within Excel Select File

2.    Options

3.    Customize Ribbon

4.    Checkmark The Developer Box Under Main Tabs

5.    Select Ok

The Code You Will Need For The Button Refresh:

Button Code (Your button (Name) will replace CommandButton1)

Private Sub CommandButton1_Click()
ActiveWorkbook.RefreshAll
End Sub

&

Workbook Code

Private Sub Workbook_Open()
    ThisWorkbook.RefreshAll
End Sub

To Refresh Automatically On Workbook Open:

1.    Developer Tab

2.    Code

3.    Visual Basic

4.    This Workbook

5.    Drop The Private Sub Workbook_Open() code provided above in and save

To Create A Button To Refresh On Button Click

1.    Developer Tab

2.    Insert

3.    ActiveX controls

4.    Command Button

5.    Double Click Into The Button

6.    Insert the ActiveWorkbook.RefreshAll into the button within the sub procedure

In order for your users to use these “Macros,” you must save the workbook as a Macro Enabled Workbook through Save As.

Enjoy!

Dan