#Excel Services REST API in #SSRS report #ProjectServer 2010 #PS2010 #MSProject #SP2010 #SharePoint

September 23, 2011 at 3:20 pm | Posted in Configuration, Customisation, Functionality, Reporting | Leave a comment
Tags: , , , , , ,

Following on from my previous post that demonstrated embedding Excel Services objects in Word using the Excel Services REST API, this post demonstrates using the Excel Services REST API in SSRS reports.

Once you have gone to the time and effort creating the charts in Excel services you don’t want to have to go to that effort again to create the same chart in an SSRS dashboard report. The Excel Services REST API will enable you to embed the previously created chart in the SSRS report. This post will just cover creating a simple SSRS report that only displays an Excel Services chart using SQL Server Reporting Services Report Builder 3.0.

Firstly load Report Builder 3.0 and choose blank report, give the report a title as shown below

image

Now click Insert > Image and click on the report in the location where you want the chart. the following window will appear.

image

Change the “Select the image source:” option to external and paste in the Excel Services REST API URL for the chart created in the previous post, for this example this is shown below

http://vm353/_vti_bin/ExcelRest.aspx/PWATEST/ProjectBICenter/Sample%20Reports/English%20(United%20States)/ProjectCostReport.xlsx/model/Charts(‘Chart 1’)

image

Click OK then move / resize the chart as required

image

Save this to the report server and the report will then be visible and update the chart on load.

#ProjectServer 2010 reports in #MSWord using the #Excel Services REST API #PS2010 #SP2010

September 19, 2011 at 1:08 pm | Posted in Configuration, Customisation, Functionality, Reporting | 2 Comments
Tags: , , , ,

I have put together a quick example to show how easily and quickly a dynamic report can be created in Microsoft Word. You could easily create monthly reports for Project Sponsors, directors etc. In this example I have just created a simple Project Cost report to show the concept. The report can be seen below:

image

The steps to create this example can be seen below.

Firstly create the required chart in Excel using the relevant ODC file, in this example the OLAPPortfolioAnalyzer ODC file was used to create the Pivot Table below:

image

A chart was then added based on the data from the Pivot table:

image

We now need to publish this report to the SharePoint document library, in this case it will be published to the Sample Reports library. Below is the report in Excel Services:

image

Now we need to get the URL for the Excel Services REST API URL for the Chart, notice in the Excel Screen the chart is called Chart 1, this is key. The URL we need in this example is:

http://vm353/_vti_bin/ExcelRest.aspx/PWATEST/ProjectBICenter/Sample%20Reports/English%20(United%20States)/ProjectCostReport.xlsx/model/Charts(‘Chart 1’)

Open Microsoft Word, add the text required in the report then click Insert > Quick Parts > Field. Select IncludePicture from the field names, paste the URL into the Fieldname or URL box and check the Data not stored with document check box

image

Click OK and you should see the chart appear as below:

image

This chart will update every time the Word document is opened, providing the URL is accessible and data refresh is allowed from REST on the trusted file location for the Excel Services report.

This is just a basic example of what can be done to demonstrate other possibilities  for creating Project Server reports. For more information on the Excel Services REST API please see the following MSDN article: http://msdn.microsoft.com/en-us/library/ff640648.aspx#restapi

#ProjectServer task level and resource level custom field values in PWA assignment views #PS2007 #PS2010 #MSProject

September 15, 2011 at 4:24 pm | Posted in Administration, Configuration, Functionality | Leave a comment
Tags: , , ,

Quite frequently I see questions relating to new task level custom fields added to the My assignments view on the Tasks page or the Timesheet views don’t show the custom field values set against the tasks. Similarly, new resource level custom fields added to the Summary view on the Resource Assignments page don’t show the custom field values set for that resource. This is usually due to the the custom field settings, the setting in question is the “Calculation for Assignment Rows”

image

Set this to Roll down as displayed above then you should see your custom field values populated in the relevant PWA views.

For more details please see the following MSDN article.

#ProjectServer 2010 Reminders web part displays new task assignments but the tasks page doesn’t show any tasks #PS2010 #MSProject

September 13, 2011 at 4:10 pm | Posted in Information, Issue | Leave a comment
Tags: , ,

**** UPDATE – This issue is resolved in the Project Server 2010 October 2011 CU ****

You will see this when the user has created a new project plan and assigned generic resources. As generic resource can’t own assignments the user who created the Project Plan will be the assignment owner as shown below:

image

When I log into PWA, the reminders web part will show 2 new tasks assigned to me as seen below:

image

Clicking the ‘2 new tasks’ will load the tasks page, but here no tasks are displayed as seen below, worth noting is that this is new PWA instance so I don’t have any actual assignments!

image

The reason why you see this is because the SQL query used for the reminders web part selects a count of all assignment UIDs where you are the assignment owner and the assignment is new where as the query used for the Tasks page filters out the generic resource assignments. In Project Server 2007 the My Tasks page used to included the generic assignments but a lot of people didn’t like this so I guess this is why Microsoft changed the behaviour.

One option to save confusion is to remove the new tasks feature from the reminders web part. To do this edit the reminders web parts, expand out the Project Web App menu and uncheck the ‘Show new tasks assigned to me’ option as below:

image

Click Apply then stop editing the page and you will see that the new tasks section has been removed. Please note, this change will affect all users. If you repeat this change but select the ‘Personalise this Page’ option from the welcome menu as below then make edit the web part to remove the ‘Show new tasks assigned to me’ option this will only apply to the current user.

image

#ProjectServer webcast on Deploying Project Server on #VMware with Shared Infrastructure #MSProject #PS2010 #PS2007 #EPM #SP2010 #SharePoint

September 9, 2011 at 12:12 pm | Posted in Information | 1 Comment
Tags: , ,

One of my colleagues, Sacha Cohn, is presenting a webcast for best practices on Successfully Deploy Project Server on VMware with Shared Infrastructure, sign up below:

https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032493923&Culture=en-US

This webcast will highlight some of the challenges faced with deploying on a virtual environment and detail key areas that could impact performance.

#ProjectServer 2003 migration to Project Server 2007 issue #PS2003 #PS2007 #MSProject

September 7, 2011 at 9:50 pm | Posted in Fixes, Issue | Leave a comment
Tags: , , ,

I came across an issue today running the P12 migration tool and thought I would share the error and simple fix. The command prompt didn’t update for around an hour, the last entry mentioned about custom field name conflicts. Looking in the log file it was just repeating the following messages:

Received serializer callback: 4
Waiting for Job to be Processed. percentage complete: 0
Time to execute P12ProjectUpgrader::SerializerCallBack(): 0 ms

After looking in Project Server 2007 PWA it appeared that there was an issue with the queue service as the jobs were waiting to be processed. After a restart of the Project Server Queue service, the jobs processed though the queue and the migration then progressed and completed successfully.

Accessing custom fields that allow multiple values from look up tables in #ProjectServer via T-SQL #MSProject #PS2010 #PS2007

September 7, 2011 at 2:12 pm | Posted in Customisation, T-SQL | 3 Comments
Tags: , , , , ,

At recent query came up on the Project Server forum regarding accessing custom fields that allow multiple values in the PWA_Reporting database. This is quick post to show how this can be achieved. In this example I have a single value lookup custom field called ProjectStatus and a custom field that allows multiple values called MVLookUpField. The example SQL is below:

SELECT        proj.ProjectName
        ,    proj.ProjectStatus — example single value lookup field
        ,    lt.MemberFullValue AS N’MVLookupField’
FROM        dbo.MSP_EpmProject_UserView AS proj
LEFT OUTER JOIN    [dbo].[MSPCFPRJ_MVLookupField_AssociationView] AS MVassoc — view for multi value field
            ON proj.ProjectUID = MVassoc.EntityUID
LEFT OUTER JOIN    dbo.MSP_EpmLookupTable AS lt
            ON MVassoc.LookupMemberUID = lt.MemberUID
order by    ProjectName asc

It is just simple select query but will hopefully it will give others an idea of how to get multi value custom fields from the PWA_Reporting database.

#Project Server 2010 August 2011 Cumulative update #PS2010 #SP2010 #MSProject

September 1, 2011 at 8:18 pm | Posted in CU | Leave a comment
Tags: ,

The Office 2010 August 2011 Cumulative Updates are now available, please see the link below:

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

Project Server 2010 August 2011 CU:

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

Project 2010 August 2011 CU:

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

As always, test these updates on a test environment before deploying to production.

For more information, please see the following link:

http://blogs.technet.com/b/projectadministration/archive/2011/09/01/microsoft-project-server-and-sharepoint-2007-and-2010-august-cu-2011-announcement.aspx

Blog at WordPress.com.
Entries and comments feeds.