How to Install PowerPivot & Connect to your first SharePoint Lists & Libraries

PowerPivot as a great BI tool offered by Microsoft which can handle millions and millions of records with ease and supports table relationships.  I have been asked why PowerPivot just does not come OOB with Excel and that is because of the particular Hardware & Software requirements which are needed.  If you are not sure what the requirements are, read this MSDN article which outlines exactly what you need.

http://msdn.microsoft.com/en-us/library/ee210640.aspx

This post assumes that you have the requirements and are ready to install PowerPivot

To Install PowerPivot for Excel 2010

1.    http://www.microsoft.com/en-us/download/details.aspx?id=29074

2.    Select download

3.    Select the download for your current system

a.    X86 is for a 32 bit system & x64 is for a 64 bit system

4.    Run the installer after the download has completed

5.    Open Excel 2010

6.    Select File Tab

7.    Select Options

8.    Select Add-Ins

9.    Next to manage select COM Add-ins from the combo box

10. Select Go

11. Check the box next to PowerPivot for Excel and select OK

You should now have a tab on the ribbon for PowerPivot!

Enable PowerPivot for Excel 2013

 

1. Go to File →Options → Add-Ins.

2. In the Manage box, click COM Add-ins> Go.

3. Check the Microsoft Office PowerPivot for Excel 2013 box, and then click OK. If you have other versions of the PowerPivot add-in installed, those versions are also listed in the COM Add-ins list. Be sure to select the PowerPivot add-in for Excel 2013.

You should now have a tab on the ribbon for PowerPivot!

Connecting To Your First SharePoint List

1.    Open Excel

2.    Select the PowerPivot Tab

3.    Click PowerPivot Window

4.    On the Home Tab

a.    Select From Data Feeds

5.    You will be prompted with the Table Import Wizard

6.    Friendly Connection Name can be anything your heart desires, it is just the display name of the connection

7.    Data Feed URL is what actually enables you to connect to the list

a.    Input your site URL i.e http://domain/sitename

8.    At the end of your site URL you will need to have:

9.    /_vti_bin/ListData.svc in order to connect to the list

10. The final URL should look like this http://domain/sitename/_vti_bin/ListData.svc

11. Select Next

a.    If the connection was successful, you should see a list of your lists and libraries

12. Check the box next to the lists / libraries you would like to create reports on and select Finish

From this step on you can create your Metrics using Pivot Tables & Charts.  There are many great tutorials online regarding how to create the relationships amongst lists & how to use PowerPivot effectively, but I may release an article in the future.

Cheers!

Dan

 

List Validation Formula: Force Business Rules For Time Sheets

Hello!

I have received a few questions regarding scenarios where a Super User has been tasked with creating a simple time sheet form such as:

Employee Name: People & Groups

Start Time: Date & Time
End Time: Date & Time
Description: Multiple Lines of Plain Text
 

This form in SharePoint would take about 30 seconds to create, but how do you enforce the business rules which makes it usable?

Business Requirements:

·         Start Time date must equal the End Time date

·         End Time (hours & minutes) must be after the Start Time (hour & minutes)

Well the good news is I have created the formula to do exactly the above!

In order to create this validation:

1.    Navigate to your list

2.    Select List Tools

3.    Select List

4.    Select List Settings

5.    Select Validation Settings

6.    Use this formula

a.    =AND(DATE(YEAR([End Time]),MONTH([End Time]),DAY([End Time]))=DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time])),TIME(HOUR([Start Time]),MINUTE([Start Time]),SECOND([Start Time]))<TIME(HOUR([End Time]),MINUTE([End Time]),SECOND([End Time])))

7.    Set your User Message (message the user receives when they break the rules!)

8.    Complete

This formula will force the above business requirements and just swap out Start Time & End Time with whatever your Start & End field are called.

Best Regards,

Dan

 

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 Enable Check In / Check Out & Versioning

Hello Everyone,

Sorry for the delay in posts, but I have been struggling with the type of content I want this blog to provide.  After my train ride to work this morning I was approached by gentlemen with some SharePoint questions since he saw that I was reading a book for an upcoming certification exam.

His question was simple in that he wanted to know how to enable Check in / Checkout, how versioning worked, how to turn it on, and its benefits.  I realized that these posts don’t have to be the most technical as long as I am able to help people.

I will first explain how to activate the features & then the ideology behind them.

How to Activate Check In / Check Out

1.    Navigate to the library you would like the check-in / checkout activated

2.    Select Library under Library Tools

3.    Select Library Settings

4.    Select Versioning Settings

5.    Select the Yes Radio Button next to “Require Check Out”

6.    Select Ok

a.    You now have the Check In / Check Out Feature!

 

How to Activate Versioning

1.    Navigate to the library or list you would like to have versioning

2.    Select Library under Library Tools or List under List Tools

3.    Select Library Settings / List Settings

4.    Select Versioning Settings

5.    Select “create a version each time you edit an item in this library / list

6.    Input the number of versions (explained later in the post)

7.    Select Ok

a.    You now have the Versioning Feature!

Explanation:

From this point on in the article I will be discussing some of the benefits and caveats of activating these features. 

 

Pros & Cons of Check in / Check Out

Pros:

·         Avoiding other users modify your files

·         Preventing multiple users edit the same file at the same time

Cons

·         Users have to remember to check-out documents before editing and check-in after finishing

·         If a user forgot to check-in a file, other users cannot modify that file but they still can view it. Only admin can check-in the file if necessary

·         For documents just uploaded, their state is checking out, the user has to check-in them. Otherwise, other users cannot see them

Versioning

Versioning is a great tool, but versioning has the capability of eating up allot of disk space due to the way files are saved.  In 2010 an entire file is saved for each version of the document.  To put it in perspective, if you have one file with 10 versions you essentially have used the space of ten files.  When you multiply this out by 10 files with 10 versions each, you now have taken up the space of 100 files.  There are a plethora of benefits though with versioning such as major and minor version, version history, and being able to review past versions.  Check with your SharePoint administrator on how many versions they would recommend based on your infrastructure.

Microsoft has addressed this space issue in 2013 with “Shredded Storage,” which can be a misleading name.  Shredded Storage is a huge benefit of 2013 since rather than saving the entire file as a new version, it will only save the incremental changes.

2010

2 versions of a file at 4 megabytes = 8 megabytes used

2013 with Shredded Storage

2 versions of a file at 4 megabytes with an updated paragraph = 4.00001 megabytes used.

 

Note:

Shredded storage only affects Microsoft Product Documents and will not work for documents such as PDF etc, these will be versioned as they were in 2010.

 

I hope this clears up any confusion on this topic and thanks again for reading!

 

Dan