#ProjectOnline / #ProjectServer reporting on auto / manually scheduled tasks #BI #Office365 #Excel #PowerQuery #PS2013April 13, 2015 at 9:49 am | Posted in Add-on, Administration, Configuration, Functionality, Information, Reporting, Workarounds | 1 Comment
Tags: Excel, Excel Services, Office 2013, Office365, Power Query, Project 2013, Project Online, Project Server 2013
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:
If I generate a reporting for this project using the equivalent fields from the OData API, see below:
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:
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:
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:
The OData query used is below:
Now click Add Column > Add Custom Column:
Give the column a name then create the formula:
Click OK to add the new custom column, repeat for the finish date:
After re-ordering and removing some columns my dataset now looks like this in the Query Editor:
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:
Now I am happy with the query I click the Close & Load button on the Home tab:
This will load my data into Excel:
As you can see the data displayed in my calculated columns on my report match the data in the fields on my Project Plan:
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:
In my updated Excel Report:
The OData query used is below:
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 #DataOctober 19, 2014 at 7:05 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, Reporting | 1 Comment
Tags: Excel Services, Office 2013, PPM, Project 2013, Project Online, Project Server 2013, PS2013, SharePoint Online
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:
The v2 Project Online Reporting pack with the 6 reports can be downloaded here:
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:
Look out for more reports / updates in the future.
#Microsoft #ProjectOnline Reporting Pack #MSProject #SharePointOnline #Office365 #PPM #Excel #BI #DataSeptember 5, 2014 at 9:43 am | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, Reporting | 12 Comments
Tags: Excel Services, Office 2013, PPM, Project 2013, Project Online, Project Server 2013, PS2013, SharePoint Online
I have recently created a reporting pack / report starter pack for Project Online. These can be downloaded from the Microsoft Gallery link below:
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!
This report will show all of the issues in the PWA site collection, you can filter by the Enterprise Project Type:
This report will show all of the risks in the PWA site collection, you can filter by the Enterprise Project Type:
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:
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.
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.
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.
- Open the Excel file in Excel 2013 and enable any connections if Excel prompts. When Excel prompts to login in, cancel this.
- Click Data > Connections and select Enterprise Project Type Slicer:
- Click Properties then the Definition tab
- 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:
- Click OK
- 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.
- Repeat the same steps for the Issues Data connection.
- Refresh the data if required and you should see the dashboard update with your data.
- 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:
- 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.
- 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:
*** New Report Added 2014/10/19 ***
Tags: Excel Services, Office 2013, Office365, Project 2013, Project Online, Project Server 2013, PS2013
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:
Paste the feed URL into the Link or File field:
Select the Projects table:
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.
Set up the relationships required, for this example the relationships are:
On the INSERT tab click Power View
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:
Once the report layout meets your requirements, save this to Project Online. The report can then be accessed and refreshed in the browser:
Clicking a different project on the slicer will filter the data:
Tags: Excel Services, Office 2010, Office 2013, PPM, Project 2010, Project 2013, Project Server 2010, Project Server 2013, PS2010, PS2013
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:
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:
Now Excel will execute the query and return the data as expected.
Tags: Excel Services, Office 2013, Project 2013, Project Online, Project Server 2013, PS2013
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:
The relationship has been set up as follows:
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:
Looking at the Pivot Table, it looks the CPS Test project has many tasks but in fact this project only has 4 tasks:
To resolve the issue you have to add a numerical field from the Tasks table to correct the aggregation:
Hope that helps
Tags: Excel Services, Office 2013, Project 2010, Project Server 2010, PS2010
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
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:
Tags: Excel Services, Project 2010, Project Server 2010, PS2010, SharePoint 2010, SP2010, SSRS
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
Now click Insert > Image and click on the report in the location where you want the chart. the following window will appear.
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
Click OK then move / resize the chart as required
Save this to the report server and the report will then be visible and update the chart on load.