Please help me to do Completed Tasks Excel Report with Odata Query

• Changed type Guillaume Rouyre [MBA, MVP, P-Seller]MVP, Moderator Monday, March 23, 2015 3:22 PM Not a discussion

Hello VinodProv, what do you need?

I need to generate "COMPLETED TASK"( with ProjectName As Slicer ) Excel Report with Odata connection string in EPM -2013

• Edited by VinodKumara Monday, March 23, 2015 3:15 PM

Hello,

Create an Odata connection in Excel and use this URL:

<site URL>/_api/ProjectData/Tasks()?$Filter=TaskPercentCompleted eq 100&$Select=ProjectName,TaskName,TaskPercentCompleted

Update <site URL> with your PWA url.

Paul

Thanks for quick responce but If iam using above string In slicer i am not geeeting uncompleted tasks projects

Hello,

That Odata URL only includes tasks that are 100% complete... That was your original query.. What is the requirement?

Paul

All Project Names in Slicer if I select any Project then completed tasks of Selected project.

Ok, have two Odata connections in the Excel work book and create a data model using the ProjectId as the key:

This one for the slicer: <site URL>/_api/ProjectData/Projects()?$Filter=ProjectType ne 7&$Select=ProjectId,ProjectName

This once for the table: <site URL>/_api/ProjectData/Tasks()?$Filter=TaskPercentCompleted eq 100&$Select=ProjectId,ProjectName,TaskName,TaskPercentCompleted

Paul

Thank You so much paul. Data Model means Relationship right??

Kind Regards,

Vinod

Hello. Yes a relationship between the two connections. Paul

Hi Paul One more Request:

I tried for milestone tasks but im getting Error: The content of data feed is not valid an atom feed

Hello. Does that URL work in Internet Explorer? If your using Project Server on-premise you will need the Project Server 2013 March 2015 Cu to be able to filter Boolean fields. Fully test the update on a non production farm first if you do apply it before applying to production. Paul

Link to the update https://support.microsoft.com/en-us/kb/2956178 Paul

Respected Paul, I installed CU its working fine. Now I need to capture ConstraintSet of a Project(ExcelReport). Please tell me which Odata feed i need to use..? 

Kind Regards,

Vinod

Hello,

If you use the link below in Internet Explorer you will find all of the properties and feeds for the Odata API:

< site URL>/_api/ProjectData/$metadata

That should help you find the data you need.

For more details, see this post: https://pwmather.wordpress.com/2014/07/17/getting-started-with-projectonline-part-9-ps2013-office365-project-ppm-sharepointonline-pm-sp2013/

Hope that helps

Paul

Dear Paul in the bellow conn string im getting again same error:"The content of the data feed is not valid of an atom feed"

http://ServerName/pwa/_api/projectdata/AssignmentTimephasedDataSet()?$select=ResourceId,ProjectName,TimeByDay,AssignmentCombinedWork

Hello,

Does that work in Internet Explorer for you? That works on my test PWA instance in IE with no issues so providing the http://servername/pwa part is correct for you it should work fine..

Paul

thanks Paul..
Can you please tell me for resource cost which field I need to take in Odata feed??

Thanks In advance..

• Edited by VinodKumara 2 hours 4 minutes ago

http://<<serverName>>/PWA/_api/projectData/TimeSet()?$filter=day(TimeByDay) eq 1 and TimeByDay gt datetime2014-01-01T00:00:00.00 and TimeByDay lt datetime2015-01-01T00:00:00&$select=TimeByDay

Im getting error:

Invalid an atom feed