Remove From My Forums

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

Question
0

Sign in to vote
Please help me to do Completed Tasks Excel Report with Odata Query
- Changed type Guillaume Rouyre [MBA, MVP, MCC]MVP Monday, March 23, 2015 3:22 PM Not a discussion
Monday, March 23, 2015 2:27 PM

Answers

0

Sign in to vote
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
Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads
- Proposed as answer by Guillaume Rouyre [MBA, MVP, MCC]MVP Monday, March 23, 2015 3:22 PM
- Marked as answer by PWMatherMVP Tuesday, March 31, 2015 10:00 AM
Monday, March 23, 2015 3:17 PM
0

Sign in to vote
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

Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads
- Marked as answer by PWMatherMVP Tuesday, March 31, 2015 10:00 AM
Monday, March 23, 2015 5:13 PM
0

Sign in to vote
Link to the update https://support.microsoft.com/en-us/kb/2956178 Paul
Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads
- Marked as answer by PWMatherMVP Tuesday, March 31, 2015 10:00 AM
Tuesday, March 24, 2015 7:49 AM

All replies

0

Sign in to vote

Hello VinodProv, what do you need?

Monday, March 23, 2015 2:43 PM
0

Sign in to vote
I need to generate "COMPLETED TASK"( with ProjectName As Slicer ) Excel Report with Odata connection string in EPM -2013
- Edited by donivm Monday, March 23, 2015 3:15 PM
Monday, March 23, 2015 3:08 PM
0

Sign in to vote
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
Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads
- Proposed as answer by Guillaume Rouyre [MBA, MVP, MCC]MVP Monday, March 23, 2015 3:22 PM
- Marked as answer by PWMatherMVP Tuesday, March 31, 2015 10:00 AM
Monday, March 23, 2015 3:17 PM
0

Sign in to vote

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

Monday, March 23, 2015 4:39 PM
0

Sign in to vote

Hello,

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

Paul
Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

Monday, March 23, 2015 4:48 PM
0

Sign in to vote

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

Monday, March 23, 2015 4:56 PM
0

Sign in to vote
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

Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads
- Marked as answer by PWMatherMVP Tuesday, March 31, 2015 10:00 AM
Monday, March 23, 2015 5:13 PM
0

Sign in to vote

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

Kind Regards,

Vinod

Monday, March 23, 2015 6:34 PM
0

Sign in to vote

Hello. Yes a relationship between the two connections. Paul
Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

Monday, March 23, 2015 7:27 PM
0

Sign in to vote

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

Tuesday, March 24, 2015 6:35 AM
0

Sign in to vote

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
Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

Tuesday, March 24, 2015 7:47 AM
0

Sign in to vote
Link to the update https://support.microsoft.com/en-us/kb/2956178 Paul
Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads
- Marked as answer by PWMatherMVP Tuesday, March 31, 2015 10:00 AM
Tuesday, March 24, 2015 7:49 AM
0

Sign in to vote

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

Tuesday, March 24, 2015 11:38 AM
0

Sign in to vote

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
Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

Tuesday, March 24, 2015 11:56 AM
0

Sign in to vote

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

Wednesday, March 25, 2015 10:29 AM
0

Sign in to vote

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
Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

Wednesday, March 25, 2015 10:45 AM
0

Sign in to vote
thanks Paul..
for resource cost which field I need to take in Odata feed??

Thanks In advance..
- Edited by donivm Thursday, March 26, 2015 8:13 AM
Thursday, March 26, 2015 5:31 AM
0

Sign in to vote

Hello,

You can get the rate from the Resouce feed:

/PWA/_api/ProjectData/Resources()?$Select=ResourceName,ResourceStandardRate,ResourceOvertimeRate">
Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

Thursday, March 26, 2015 9:40 AM
0

Sign in to vote

Thanks for your responce.
can you please let me know how to create relationship between AssignmentTimephasedData and AssignmentBaselineTimephasedData. What is the key?

Thursday, March 26, 2015 10:43 AM
0

Sign in to vote

Hello,

AssignmentId?
Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

Thursday, March 26, 2015 10:46 AM
0

Sign in to vote

I tried but it is saying: Both selected columns contain duplicate values

Thursday, March 26, 2015 10:49 AM
0

Sign in to vote

Hello,

I would start a new thread for this as this is a different issue to what has been discussed so far.

Paul
Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

Thursday, March 26, 2015 11:11 AM

Answered by:

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

Question

Answers

All replies