#ProjectOnline / #ProjectServer reporting on auto / manually scheduled tasks #BI #Office365 #Excel #PowerQuery #PS2013

April 13, 2015 at 9:49 am | Posted in Add-on, Administration, Configuration, Functionality, Information, Reporting, Workarounds | 1 Comment
Tags: , , , , , , ,

One issue or query that has been raised a few times is that when reporting on manually scheduled tasks and auto scheduled tasks the Start / Finish date fields do not show the same data that they do in Project or PWA for the manually scheduled tasks. For example, take a look at the example project plan below:

image

If I generate a reporting for this project using the equivalent fields from the OData API, see below:

image

As you can see all tasks have start and finish dates. The Manually scheduled tasks that are either blank or have text displayed in Project Pro contain dates in the report. These default to the Project Start date. The OData query used is below:

Tasks()?$Select=ProjectName,TaskName,TaskStartDate,TaskFinishDate,TaskIndex

This can be quite misleading from a reporting point of view. There are two options (probably more but two documented here!), these are detailed below in order of preference:

Option 1:

The preferred option would be to create a calculated field in the report, in this example, as I am using Excel I will create this in Power Query. I have my dataset in the Power Query Editor like below:

image

The OData query used is below:

Tasks()?$Select=ProjectName,TaskName,TaskStartDate,TaskStartDateString,TaskFinishDate,TaskFinishDateString,TaskIsManuallyScheduled,TaskIndex

Now click Add Column > Add Custom Column:

image

Give the column a name then create the formula:

image

Click OK to add the new custom column, repeat for the finish date:

image

After re-ordering and removing some columns my dataset now looks like this in the Query Editor:

image

The final change is to update the Data Types of the columns, in this example I updated the TaskStartDate and TaskFinishDate columns to Date/Time and my calculated DisplayedStartDate and DisplayedFinishDate columns to Text:

image

Now I am happy with the query I click the Close & Load button on the Home tab:

image

This will load my data into Excel:

image

As you can see the data displayed in my calculated columns on my report match the data in the fields on my Project Plan:

image

Option 2:

The other option is to create new Enterprise Custom Fields in Project Online that are calculated. Using Start date as an example, create a task level text field that is based on  formula, the formula would be =[Start]. I have called the field “DisplayedStartDate”. In Project Pro:

image

In my updated Excel Report:

image

The OData query used is below:

Tasks()?$Select=ProjectName,TaskName,TaskStartDate,TaskFinishDate,DisplayedStartDate,TaskIndex

As you can see this also gives the correct data but does introduce two additional calculated task level fields in your Project Online configuration. It is recommended to keep the task level calculated fields to a minimum – ideally below 5 for performance reasons hence this option being the least preferred. 

#Microsoft #ProjectOnline Reporting Pack v2 #MSProject #SharePointOnline #Office365 #PPM #Excel #BI #Data

October 19, 2014 at 7:05 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, Reporting | 1 Comment
Tags: , , , , , , ,

Following on from my recently released Project Online Reporting Pack, I have since added a new report to the pack – a milestone variance chart. This report is detailed in this post. For screenshots of the other reports and deployment steps, see:

https://pwmather.wordpress.com/2014/09/05/microsoft-projectonline-reporting-pack-msproject-sharepointonline-office365-ppm-excel-bi-data/

The v2 Project Online Reporting pack with the 6 reports can be downloaded here:

https://gallery.technet.microsoft.com/Online-Reporting-Pack-431f075e

Milestone Variance Report:

This report will show all of the marked milestones for the projects in the portfolio, you can filter by the Enterprise Project Type:

image

Look out for more reports / updates in the future.

#Microsoft #ProjectOnline Reporting Pack #MSProject #SharePointOnline #Office365 #PPM #Excel #BI #Data

September 5, 2014 at 9:43 am | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, Reporting | 12 Comments
Tags: , , , , , , ,

I have recently created a reporting pack / report starter pack for Project Online. These can be downloaded from the Microsoft Gallery link below:

http://gallery.technet.microsoft.com/Online-Reporting-Pack-431f075e

Currently there are only 5 reports in the zip file but I will be regularly updating the existing reports and create additional reports. The reports will only use the intrinsic Project Online fields so will works for all deployments, the only requirement will be to repoint / update the data connections with the correct Project Online PWA URL – this is covered on this post.

The reports included so far can be seen below, the data isn’t great as it in my test data but you get the idea!

Issue Report:

This report will show all of the issues in the PWA site collection, you can filter by the Enterprise Project Type:

image

Risk Report:

This report will show all of the risks in the PWA site collection, you can filter by the Enterprise Project Type:

image

Portfolio Dashboard:

This report will display general information for the projects in the portfolio, this includes how the projects are split between EPTs, project costs, project work, issues and risk count then a detail table below. The data can be filtered by the Enterprise Project Type:

image

image

Project Highlight Report:

This Power View report displays general project related information including cost, work, %complete. It also includes milestones and marked tasks from the project plan as well as active issues and risks.

image

Project Dashboard:

This dashboard report displays general project related information including cost, work, %complete and  issue / risk information. It also includes milestones and marked tasks from the project plan.

image

image

These are the first 5 reports, others will follow in the future. When updates / change or additional reports are added I will update the zip file on the Microsoft Gallery but also blog about them too.

To get started with these reports once they are downloaded, see the steps below for one example report, in the example below we use the IssueDashboard file. The steps will need to be repeated for all reports / connections. Before you carry out the steps below, please ensure that your target PWA tenant has some data to populate all of the tables and charts in each Excel file, otherwise Excel will remove the tables or charts etc. If it is a new tenant, make sure you have some projects in there with work and cost including baselines, ensure there are milestones / marked tasks and ensure there are risks and issues for the associated projects.

  1. Open the Excel file in Excel 2013 and enable any connections if Excel prompts. When Excel prompts to login in, cancel this.
  2. Click Data > Connections and select Enterprise Project Type Slicer:
  3. image
  4. Click Properties then the Definition tab
  5. image
  6. The connection will be “https://cpssalesonlinedemo2.sharepoint.com/sites/pwa”, this part of the connection needs to be updated for your PWA URL. There are two places in the connection string it needs to be updated, the Data Source property and the Base Url property. You can see both in the screen shot above where it has cpssalesonlinedemo2 and below I have updated this to Paulmather in both locations:
  7. image
  8. Click OK
  9. At this point Excel might prompt you to login in to your Office 365 PWA tenant – the your credentials if you have access to PWA and the ODATA Reporting Service, alternatively use an account that has admin access to PWA.
  10. clip_image002
  11. Repeat the same steps for the Issues Data connection.
  12. Refresh the data if required and you should see the dashboard update with your data.
  13. An additional step might be required if you want the Excel work book to automatically refresh on open and that is to enable the option “Refresh data when opening the file” on the Usage tab for each connection:
  14. image
  15. Now the file can be saved to your Project Online PWA instance so that users can view this file via the web using Excel Services.
  16. Repeat these steps for all Excel files in the Report Pack download. Please note some reports have more than 2 data connections, you will need to update them all.

Hope you find these useful, look out for more reports / updates in the future.

Remember to get Project Online ODATA Excel based reports to refresh the data in Excel Web App you will need to enable the feature detailed here:

http://office.microsoft.com/en-gb/office365-project-online-help/grant-reporting-access-in-project-online-HA104021109.aspx

*** New Report Added 2014/10/19 ***

https://pwmather.wordpress.com/2014/10/19/microsoft-projectonline-reporting-pack-v2-msproject-sharepointonline-office365-ppm-excel-bi-data/

#ProjectServer #ProjectOnline highlight report example #PS2013 #SP2013 #Excel #BI #Office

August 1, 2013 at 12:02 am | Posted in Administration, Configuration, Customisation, Functionality, Information, Reporting | 8 Comments
Tags: , , , , , ,

A common requirement for clients is to have a report that highlights key information about each project. This usually includes any key project level fields, up and coming tasks / milestones and any active issues or risks. For previous versions of Project Server, in my opinion, the best reporting technology for this was SQL Server Reporting Services (SSRS). For Project Server 2013 on-premise installations SSRS is probably still the best option for most reports due to the flexibility around formatting, layout etc. For Project Online, unfortunately SSRS is not an option, a nice option for a project highlight type report is Power View. This blog post will walkthrough creating a project highlight report using OData and Power View for Project Online. This example report contains key project level data, key milestones due to complete this month, future key milestones, active issues and active risks. The OData feeds used are:

https://<serverurl>/pwa/_api/ProjectData/Projects()?$filter=ProjectType ne 7&$select=ProjectId,ProjectName,ProjectOwnerName,ProjectStartDate,ProjectFinishDate,ProjectWork,ProjectCost,TotalCost,ProjectQuality,ProjectSchedule,ProjectCost,ProjectResource
https://<serverurl>/pwa/_api/ProjectData/Tasks()?$filter=TaskFinishDate gt datetime’2013-08-01T00:00:00′ and TaskFinishDate lt datetime’2013-09-01T00:00:00′ and KeyMS eq ‘Yes’&$select=ProjectId,TaskIndex,TaskName,TaskStartDate,TaskFinishDate
https://<serverurl>/pwa/_api/ProjectData/Tasks()?$filter=TaskFinishDate gt datetime’2013-09-01T00:00:00′ and KeyMS eq ‘Yes’&$select=ProjectId,TaskIndex,TaskName,TaskStartDate,TaskFinishDate
https://<serverurl>/pwa/_api/ProjectData/Issues()?$filter=Status eq ‘(1) Active’&$select=ProjectId,IssueId,Title,Discussion,Resolution,Owner,DueDate
https://<serverurl>/pwa/_api/ProjectData/Risks()?$filter=Status eq ‘(1) Active’&$select=ProjectId,RiskId,Title,MitigationPlan,ContingencyPlan,Owner,DueDate

These feeds need to be added to Excel 2013 using the “From OData Data Feed” option found on the DATA tab:

image

Paste the feed URL into the Link or File field:

image

Click Next

Select the Projects table:

image

Click Next

image

Click Finish

image

Select Only Create Connection and Click OK

Repeat these steps to add the other OData feeds using the same steps.

Once all of the feeds have been added, the table relationships need to be created. On the DATA tab in Excel click Relationships. This can be found in the Data Tools group.

image

Click New

image

Set up the relationships required, for this example the relationships are:

image

Click Close

On the INSERT tab click Power View

image

Design the report as required, in this example we are using Project Name as a slicer to filter the data on the other tables, this can be seen below:

image

Once the report layout meets your requirements, save this to Project Online. The report can then be accessed and refreshed in the browser:

image

Clicking a different project on the slicer will filter the data:

image

Enjoy Smile

#ProjectServer #Excel report with SQL Temporary Tables #PS2010 #PS2013 #SQL

July 19, 2013 at 11:45 pm | Posted in Administration, Configuration, Customisation, Functionality, Information, Reporting, T-SQL | 1 Comment
Tags: , , , , , , , , ,

I came across an issue a while back and meant to blog about it but forgot until a colleague of mine today mentioned the same issue. This jogged my memory of the fix so I thought it was a good time to write the post. The issue isn’t Project Server related but the reports and queries we were creating were for Project Server.

If your SQL query in an Excel file uses temporary tables Excel will throw an error like the one seen below:

image

For the search engines the error is below:

The query did not run, or the database table could not be opened.

Check the database server or contact your database administrator. Make sure the external database is available and hasn’t been moved or reorganized, then try the operation again.

The error will occur if you have the select statement in the connection file definition command text or even calling a SQL stored procedure from the command text. The same fix applied to both, at the start of the select statement add “SET NOCOUNT ON” as shown below:

image

Now Excel will execute the query and return the data as expected. Smile

#ProjectServer #PS2013 #Excel Pivot Table limitation

July 4, 2013 at 11:38 am | Posted in Administration, Customisation, Functionality, Information, Issue, Reporting, Workarounds | 1 Comment
Tags: , , , , ,

Just a quick post to highlight a limitation / issue with Excel Pivot Tables that I hadn’t seen before, details below.

This particular Pivot Table uses a Data Model that contains two OData feeds from Project Server 2013, one for Project details and one for Task details. The connections can been seen below:

image

The relationship has been set up as follows:

image

I have added Programme, Project Name, Task Name and Task Start to the Pivot Table. Programme and Project Name are from the Projects table and Task Name and Task Start are from the Tasks table. The Project Name is filtered to just one project:

image

Looking at the Pivot Table, it looks the CPS Test project has many tasks but in fact this project only has 4 tasks:

image

To resolve the issue you have to add a numerical field from the Tasks table to correct the aggregation:

image 

Hope that helps Smile

#ProjectServer 2010 #Excel Services Reports fail to Refresh #Office2013 #SP2010

November 23, 2012 at 3:43 pm | Posted in Administration, Configuration, Functionality, Information | 1 Comment
Tags: , , , ,

A quick post to highlight an issue you may face if you have upgraded to Office / Excel 2013. I recently created some Excel reports for Project Server 2010 using Excel 2013. These reports failed to refresh in Excel Services. After a quick check on the connection string in Excel, the provider is MSOLAP.5 rather than the usual MSOLAP.4 provider when using Excel 2010. See below

Excel 2010:

image

Excel 2013:

image

The ULS log error is:

The workbook ‘http://vm730:81/pwa/ProjectBICenter/Reports/Actual Work.xlsx’ attempted to access external data using the unsupported provider ‘Provider=MSOLAP.5

The fix is very simple, navigate to Central Admin > Manager Service Applications > Excel Services > Trusted Data Providers then add the MSOLAP.5 provider as below:

image

#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.

Create a free website or blog at WordPress.com.
Entries and comments feeds.