SharePoint (and Project Server) Shenanigans

I am a Project Server and SharePoint consultant but my main focus currently is around Project Server. I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011. I am also a certified Prince2 Practitioner. This article has been cross posted from pwmather.wordpress.com (original article)

A quick blog post to highlight the use of one of the Project Server Reporting database functions to resolve an issue when using an SSRS multi value parameter.

There are several methods to get multi value parameters working in SQL Server Reporting Services (SSRS) including dataset filters, joining parameters and custom SQL functions – other blogs / forum posts detail these. This post demonstrates using a function that is available in the Project Server Reporting database. The function is called MSP_FN_Utility_ConvertStringListToTable. An example SQL Stored Procedure that will allow multi values can be seen below:

CREATE PROCEDURE [dbo].[SP_ProjectData] (
        @ProjUID NVARCHAR (max)
)AS
BEGIN
select            P.ProjectName
           ,    T.TaskName
from            MSP_EPMProject_UserView P
INNER JOIN        MSP_EPMTask_UserView T
ON                P.ProjectUID = T.ProjectUID
INNER JOIN        MSP_FN_Utility_ConvertStringListToTable (@ProjUID) AS PU
On              P.ProjectUID IS NULL or P.ProjectUID like PU.TokenVal
END

Create the SQL query as normal but instead of using a where clause to filter the Project UIDs join on to the function as shown above.

I am a Project Server and SharePoint consultant but my main focus currently is around Project Server. I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011. I am also a certified Prince2 Practitioner. This article has been cross posted from pwmather.wordpress.com (original article)

This post will take a brief look at creating a map view of Project Server data – this does assume you tag your projects with a location!

For the purpose of this post I will use the example Excel file shown below – this pulls data from one of my test Project Server PWA instances, hence the project names!

In Excel 2013, click Insert > Power View Reports:

You will now see a Power View report:

To create a map with the projects plotted in the correct location by cost, see the steps below.

On the design tab, click Map and you will see the following:

Now click the map and modify the Power View fields shown below:

For this example, add ProjectCost to the size property, add Project Locations to the Locations property and set the colour property to ProjectName:

Increase the size of the map and add a title:

You can hover over the data circles and a tooltip will appear with the project details:

The data can be refreshed and the map updates.

A quick and simple report to show projects by location.

I am a Project Server and SharePoint consultant but my main focus currently is around Project Server. I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011. I am also a certified Prince2 Practitioner. This article has been cross posted from pwmather.wordpress.com (original article)

Now that the first CU’s for 2013 are available, I will include both 2010 and 2013 updates in the future posts.

The Office 2013 February 2013 Cumulative Updates are now available, please see the links below:

http://support.microsoft.com/kb/2802843

Project Server 2013 Server Roll up package February 2013 CU (Recommended):
(Delayed)
 
Project Server 2013 February 2013 CU (Included in the Server Roll up package):
(Delayed)
 
Project 2013 February 2013 CU:
http://support.microsoft.com/kb/2738031

The Office 2010 February 2013 Cumulative Updates are now available, please see the links below:

http://support.microsoft.com/kb/2800779
 
Project Server 2010 Server Roll up package February 2013 CU (Recommended):
http://support.microsoft.com/kb/2767794
 
Project Server 2010 February 2013 CU (Included in the Server Roll up package):
http://support.microsoft.com/kb/2760772
 
Project 2010 February 2013 CU:
http://support.microsoft.com/kb/2760778
 
Remember SP1 is a pre-requisite for the Office 2010 February 2013 CUs.
 
For more details please see:

http://blogs.technet.com/b/projectsupport/archive/2013/02/14/microsoft-project-server-2007-2010-and-2013-february-2013-cu-announcement.aspx
 
As always, test these updates on a replica test environment before deploying to production

I am a Project Server and SharePoint consultant but my main focus currently is around Project Server. I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011. I am also a certified Prince2 Practitioner. This article has been cross posted from pwmather.wordpress.com (original article)

One question we have from many clients is, can we have the project timeline image on the project site. The answer is yes, but this will not update automatically in Project Server 2010. On a side note, it is possible to have the timeline view in Project Server 2013 PWA!

This blog post covers a workaround to this limitation in Project Server 2010 using VBA. At this point I will state that I am no VBA expert, my VBA knowledge is very limited to say the least. This is a simple macro but I’m sure it could be improved  

For this to work a custom DLL is required on the workstations as there is no option to save a clipboard image to a file in VBA (as far as I know!). The DLL can be downloaded here: http://www.vbforums.com/showthread.php?585616-clipboard-activex-for-vba-vbs-etc Use this at your own risk as I do not know the code behind this DLL!

For this example, I created a file share to store my images \\vm353\ProjectSiteTimeLineImages. All users will need read / write access to this share.

The VBA code can be seen below, add this to the Enterprise Global:

Private Sub App_ProjectBeforePublish(ByVal pj As Project, Cancel As Boolean)
Dim clip As Object
Dim proj As Project
Set proj = ActiveProject
Dim path As String
path = "\\vm353\ProjectSiteTimeLineImages\"
Dim file As String
file = ".bmp"
Dim fileName As String
fileName = path & proj & file

Application.ViewApply Name:="Timeline"
Application.TimelineExport SelectionOnly:=0, ExportWidth:=1000
Application.ViewApply Name:="Gantt Chart"
Set clip = CreateObject("clipbrd.clipboard")
SavePicture clip.GetData, fileName
Set clip = Nothing
End Sub

Before a project is published the timeline image will be saved as a file to the share. See the example / walkthrough below:

File share before publish:

Create a new project, save and publish:

File share after publish:

Image:

Now we have the timeline as an image, this needs to be added to the project site – I won’t go into the details as this is basic SharePoint end user config task. See the Project site below with the image:

Notice only 3 tasks are added to the timeline, lets add some additional tasks.

I have added task T5 and added this to the timeline:

After publishing this project, refresh the project site:

A nice simple solution to a dynamic timeline image on the Project Server 2010 Project Sites. After publishing more projects, more timeline images will appear in the shared folder:

As mentioned, the VBA could be improved with error handling etc. Use this and the custom DLL at your own risk and fully test on a test workstation and PWA instance first.