My first Power Map tour!!

A while ago I wrote a blog post about Power BI tools. At this time I had no Excel 2013 available so I played only with Power BI desktop and Power BI App which don't require Excel 2013. But to give a first overview of the Power BI tool, I share with you this simple but clear picture from Devin Knight's website, a fellow SharePoint MVP.

I was able to build really nice reports without Excel 2013 and my post has been read by many folks, one of the most successful post I wrote! But unfortunately I was not able to play around with Power Map: indeed it is not available with Power BI App, even though you can create a map report but without all the nice features provided by Power Map. 

But you know what? I finally managed to get Excel 2013! The first thing I did was to install all the Power BI addons and start playing with Power Map. So let me share with you my first little Power Map tour. Please be indulgent with me, that's a first shot! But to give you the envy to go through this post, here is what you'll be able to achieve:

Set up Excel 2013

First step is to download the addons and activate them in Excel. Once downloaded, you can go to the COM Add-ins dialog box and check all required items.

Figure 1: add the Power BI tools to Excel 2013

You can see that you have now extra tabs in the ribbon. Moreover if you navigate to the "Insert" tab, you'll see the Power View and Power Map groups.

Connect to the data source

I won't go in details on how to retrieve the data from Project Server / Online. It has been documented in many blogs particularly using oData feeds, which is pretty straight forward. Once done, you'll get for example your Project oData feed data in your Excel sheet.

Figure 2: oData feed in the Excel sheet

Add the data to Power Map

Once you open Power Map, you'll get it open as a new application but strangely, no data inside. It doesn't include automatically the data from the current datasheet.

Figure 3: Power Map first opening with no data

Then go back to Excel, select your table and add it to Power Map.

Figure 4: add your data to Power Map

Here is what you get.

Figure 5: oData feed added to Power Map

Start with Power Map

Basically here is how Power Map works:

• The highest entity is a tour which you can export only as a video when you have finished
• In a tour, you have different scenes which can be seen as a slide in a PowerPoint document
• In a scene, you can defined layers, which correspond to dataset you'll display on the map
• Each layer has some properties such as location, data, category, chart type, scene settings etc...

Note that obviously you must have a custom field related to the location in order to have relevant layers, such as a location project enterprise custom field. In my case, you can see below in PWA/Project Center that I have a "location" project level ECF associated with a lookup table.

Figure 6: location project enterprise custom fields in PWA

Add the data

First thing, I rename the default tour name and default layer name.

Figure 7: layer renamed

Then pick up your location custom field. If the location values are automatically recognized, you'll see the corresponding pointers on the map.

Figure 8: location added to the layer

Then it is time to add the data in order to create your scene. In my example, I choose the project duration and actual duration. You can see that I can choose between 5 chart types.

Figure 9: data added to the layer

Format the scene

Ok, start to look good, isn't it?? If you move your mouse cursor over the bar, you'll see the selected data like you can do with Power View. Now it is time to do some formatting choosing a theme and showing labels.

Figure 10: formatting the scene

Format the layer

As said before, you can now configure the layer. For example you can change the shape of the bars, the opacity, the height or thickness and the color.

Figure 11: formatting the layer

It might look like this (note the buttons in the map group of the ribbon).

Figure 12: layer formatted

Configure the scene

The scene can be configured in order to record the final video. You'll be able to set the effect, effect time and speed.

Figure 13: configure the scene

Improve your tour

Once you have understood those principles, you can easily create more complex tours, adding more scenes. In this second scene, I added pie charts with the project duration per enterprise project types.

Figure 14: second pie chart scene

I can also add a text box to give a description or any meaningful comments.

Figure 15: text box added

Finally you can add 2D chart. It will add a chart with the data of the active map.

Figure 16: 2D chart added

Export your tour

Here is in my own point of view one of the limitations. I found no other way for exporting my map than exporting a picture or creating a video. Meaning that I can't just have my map as a report embedded in an Excel sheet and which I could expose in a SharePoint context such as a PWA PDP. I do hope that since I'm still learning the Power BI tools, there is something that I missed about the Power Map export... As far as I understood, the closest thing available to Power Map would be PowerView's Bing maps integration, which is part of SSRS and also requires PowerPivot. 

If someone can bring some light on this particular topic, I'd be glad to here!

Here is my scene exported as a picture:

Figure 17: scene exported as a picture

Just to be sure that I'm correctly linked to my PWA tenant data, I take 2 UK projects and mark all activities as completed to see if I can see immediatly the impacts on my Power Map tour.

Figure 18: refresh oData feed from Power Map

I can see that the UK data are correctly updated.

Figure 19: UK data updated

If I select the UK projects' actual duration (blue stack), the same data is highlighted on the map.

Figure 20: data highlighted

You can also export your entire tour as a video, even with a soundtrack:

Figure 21: exporting the entire tour as a video

Unfortunately, here is where my laptop crashes time after time. Encoding the tour as a video is quite resource consuming and apparently too much for my computer. I'll make some more tries and hopefully I'll post the video soon.

Looking ahead, a final comment would be that with Excel 2016, all Power BI addons (Power View, Pwoer Query, Power Pivot, Power Map) will be natively shipped with Excel and no more accessible as addons. It will make the usage of those addons more intuitive.

MS Project: how to get more than the 10 default local custom fields?

Hi,
Those days, I've been playing around with the Enterprise Global (as you can also see in another recent post), quite a while!! Recently a user who was working with MS Project standalone (meaning not connected to Project Server or Project Online) had the following concern. He needed to use more than the 10 cost custom fields. Indeed if you go to the project tab and click on "custom fields", you'll see that you are limited to 10 cost fields. 

Figure 1: cost custom fields in MS Project

The first workaround is obviously to use text custom fields and/or number custom fields. But there are some limitations:

• the number custom fields cannot be displayed as cost with the currency symbol,
• the text custom fields cannot be rolled up unless you write some formula or do some VBA.

So how to have more than 10 cost custom fields? You need to have in your professional network someone who has access either to a Project Server or Project Online instance. Indeed you have no other limitations than performance for enterprise custom fields. This means that you can create almost as many cost custom fields as you need. But this is actually not as simpler as it might be. Indeed if you check out the enterprise global and open the organizer on the "fields" tab, you'll see that you cannot select either the global.mpt or the checked-out enterprise global. Thus you cannot copy items into the local project arghh..

So here are the steps: 

1- Create the extra cost enterprise custom field(s) on a Project Server / Online tenant

This is the first step, quite straight forward. Connect to your instance, go to server settings and create the extra enterprise cost custom field(s). Be sure to configure the rollup at summary task level as needed, for example with a sum.

Figure 2: extra cost enterprise custom field created from the server settings

2- Save the global items locally

Open MS Project Pro connected to the given PWA instance and save a blank new project locally. Be sure to save in the local file all enterprise custom fields and global items.

Figure 3: project saved locally with the global items

You can now close MS Project.

3- Copy the local extra field into the global

Now the issue is that you'll have ALL enterprise custom fields in your local file. This might not be suitable since the initial need comes from a user who is working in a standalone mode which is more than likely not your enterprise context. Thus he probably doesn't want to be polluted by all your enterprise metadata. 

Logically this last operation should be done by the initial user which has the need since it will copy the extra cost field(s) in his global. So he has to reopen MS Project not connected to Project Server and open the local file which will contain ALL enterprise custom fields from your instance. Finally he opens the organizer and copies the extra custom field(s) from the local file to his global.mpt. You can also do it by yourself: open the local file, copy only the extra cost fields to your global, open a new blank file and save it locally, eventually overwriting the previous file containing all the enterprise custom fields.

Figure 4: copy the extra cost field from the local file to the user's global

Now the user will have his extra cost custom field(s) in his global for any new project, having more than the 10 initial cost custom fields. Here is the final result with an extra 20 cost custom fields in addition to the 10 default local ones.

Figure 5: 20 extra cost custom fields available locally in addition to the 10 default local cost custom fields

Note that in the image below, even if I started MS Project not connected to Project Server, I still have the 20 extra custom fields (from 11 to 30) flagged as "enterprise". Just for information, if connected to a Project Server / Online instance, you'll have the following icon in the status bar:

Figure 6: icon in the status bar when connected to Project Server / Online

Any limitations?
If you go to the project tab then custom fields, you'll not see those 20 "enterprise local" fields, thus you cannot configure anything such as the rollup. You have to think about all settings before doing the operation.

Figure 7: the 20 extra custom fields not appearing in the list of cost custom fields

Another limitation to mention is that while copying the extra fields to another project file, the user in question will see that all settings about sum and rollup are lost. So basically the principle is to stick to the initial file and reuse it.

What do you think? Do you have another ways to extend MS Project capabilities in a standalone context?

Speaker at the Project Virtual Conference 2015!

A few weeks ago was announced the Project Virtual Conference and I blogged about it since it will be a tremendous and unique event for Project users, administrators, consultants, PMO, project managers or simply people who have any interests in project and portfolio management.

I'm pleased to also announce that I'll be speaking at this conference. It will be a great occasion to share with you tools, tips, ideas, concepts, best practices about Cost Management and of course have a discussion with you.

See my profile here and keep posted to have more information about all sessions during the 24hrs conference around Project!

Let's e-meet on October 22nd!

Move entreprise calendars between instances

New week, new blog post! I guess most of you are back from holidays so I'm impatient to share this new post with you and have your comments!

My last Project Server implementation was quite a large and complex project since it involved thousands of users and gateways with HR and financial systems. Thus we needed to have many Project Server environments such as development, test, integration, training, pre-production and production. You can imagine that there are a lot of configurations required and it could be really time consuming to manage those environments. For example calendars might be tedious to manage with 6 environments, particularly in Canada where each province has its own days off. I guess it is pretty the same for the US or Australia...

So let me share share with you a trick to quickly and easily move calendars from an environment to another. 

Let's say that I have an enterprise calendar on my instance 1 and I want to copy it on a destination instance. Here is what you get in the server settings for enterprise calendars:

Figure 1: enterprise calendar to copy

Copy the calendar locally

The first step is to copy the calendar in a local file. Open Project Pro connected to instance 1 and open a blank new project. Click on Save-As and save the project locally selecting the second option: this is important since it will save in the project the enterprise calendars which are part of the global.

Figure 2: save the project locally with the global items

Just to be sure, you can open the project locally (not connected to any instance) and check in the project information dialog box that you do have the given calendar.

Figure 3: calendar imported in the local file

Configure the server settings for local calendars

Before importing the calendar to the destination instance, you have to enable the option for projects to use local base calendars from the server settings, additional server settings.

Figure 4: allow projects to use local base calendars

Import the local calendar

Open MS Project Pro connected to the second instance and open the project file with the local calendar. A procedure which might make sense would be to open the enterprise global and use the organizer to copy the calendar from the local file to the enterprise global. If you try to do so, you'll get the following error message which tells you that you have to use PWA to manage enterprise calendars..

Figure 5: message when trying to use the organizer to copy calendar to enterprise global

The trick here is to go to the Project tab, then "change working time". You can now select any calendars other than the standard one and add it to enterprise giving it a new name.

Figure 6: copy the local calendar to the destinnation instance

Of course you have to belong to a security group which has the correct global permission:

Figure 7: manage enterprise calendats global permission

Then if you go to the destination instance, you can see in PWA that the calendar has been added.

Figure 8: new calendar added to the destination instance

As you can see, in case you have many calendars to maintain between many PWA instances, this can be an easy way to proceed. This also allows keeping history and back-up of all enterprise calendars.

Sum Excel cells based on the background color

Hi and let's talk about an easier subject, since everyone is on holydays!

For once, I'll not blog about Project but about Excel. Actually this is not the first time, I already blogged about the new timeline filtering features in Excel 2013. 

Working on a timesheet report, I was looking for a way to sum the value of cells based on the background color. Indeed Excel reports are still extensively used with Project Server/Online and once the report is build, we often used formatting to make the report easier to read. So I knock at my colleague's door and beg for some help...

Note that strangely, what I'm going to explain below is not applicable for conditional formatting. Don't know why, but I suspect that the conditional formatting is not a "hard" background coloring and it would require to update the module but let's keep it simple for now and maybe this could feed a future post..

So basically, you have to start by creating a new module in Excel.

Press ALT F11, then insert, then module.

Figure 1: new module creation in Excel

Then simply copy paste the following code, which is pretty simple to understand.

Function SUM_IF_COLOR(SumRange As Range, ColorRange As Range) As Variant

'*********************************************************

' Sum cells values based on a given color*

'*********************************************************

Application.Volatile
Dim Sum As DoubleDim Cel As Range

If ColorRange.Cells.Count > 1 Then

SUM_IF_COLOR = CVErr(xlErrValue)

Exit Function

End If

For Each Cel In SumRange

If Cel.Interior.ColorIndex = ColorRange.Interior.ColorIndex Then Sum = Sum + Cel

Next

SUM_IF_COLOR = Sum

End Function

Once done, you can create a new formula in your Excel spreadsheet like:

=SUM_IF_COLOR(Sum Range;Color)

Where : 

• Sum Range is the range where the formula should look at the colored cells,
• Color is the cell with the color to detect.

Note also that the "application.volatile" allows the formula to be refreshed automatically when your range is updated in any ways.

Here is a simple example: my array is C2:H7 and the color to detect is A13. The cells C11 to C14 are automatically updated.

Figure 2: example of the formula for summing values based on the background color

Et voilà!!

Do you have ideas for improving this formula? Or any workaround for the conditional formatting?