Properly “Percentagizing” Percentages from Project Server into Excel Pivot Tables

The problem:

 

When you pull a field with a % value into Excel with a report, there are a number of problems you can encounter with the formatting.  I’ve found most of these tricks in various places online, but I couldn’t find one comprehensive article that included all these ideas (plus a couple hair-brained schemes of my own) so I thought I’d put one together.

 

The PCT value in Project Server’s SQL database can only be two digits long.  When you pull it into your Pivot Table, it shows up fine, but without the % sign.  If you tell it that the field is a % format, it adds two zeroes, making 50% into 5000%, for example.

 

There are some nifty ways to fix this.

 

  1. Custom Number Format

 

I like to change the number format by right clicking on a value in the column, clicking on “Value Field Settings” and then “Number Format”.  Then go to the “Custom” Category and put the following little dude into the Type box:

 

[=0]0;#”%”

 

This will stick in your pivot table and make your percentages happy and useful.

 

However…

 

There are some instances where this doesn’t work.  If you are using a custom field and not placing the item in the “Values” area of the pivot table, for example, you may not have the option to change the number format.

 

In that case, you can cosmetically fix it in the query by adding a string to the select statement. 

 

  1. Fix it in the query

 

I’m using the Task % Complete field in this example:

 

CONVERT(nvarchar(50), MSP_EpmTask_UserView.TaskPercentCompleted)+’%’ as PercentComplete

 

Of course, this is also not ideal, because now it’s a text field and can’t be used for a value filter or calculation or sorted by largest/smallest, but at least it’s only one step, and it works if you just need it to LOOK like a percentage.

 

If you really need it to remain a value, you can use both the query and also do an “after effect” in Excel.

 

  1. Fix it in the query AND the number format

 

First, you have to multiply by 100 (otherwise 50% will become 100%) and then divide by 10000.  I know that sounds janky, but it works and doesn’t seem to slow down the query.

 

MSP_EpmTask_UserView.TaskPercentCompleted*100/10000

 

Then you can make the Number Value of that a % and it will work as normal.

 

So there you go – some methods to work around the fact that Excel wants your percentages to be decimals less than 1 whilst (that word looks really out of place here, but I’m keeping it anyway) Project Server wants it to be a two-digit number under 100.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s