Expand my Community achievements bar.

Report API

Avatar

Level 2
Does anyone use the Report API with success? We are trying to get get hours per time card grouped by a custom field on the company object. It seems like the Report API doesnt allow for "Group By" or "Filtering" beyond 1 level. EX /attask/api/v10.0/proj/report?apiKey=YOURKEY&hours:hours_AggFunc=sum&hours:timesheet:endDate=2019-02-09&company:name_2_GroupBy=true&owner:name_1_GroupBy=true Error: { "error": { "class": "com.attask.common.InvalidParameterException", "message": "Invalid Parameter: Search Parameter value \"hours:timesheet:endDate\"", "title": null, "msgKey": "exception.invalidparameter", "attributes": [ "Search Parameter", "hours:timesheet:endDate" ], "code": 1000 } } /attask/api/v10.0/proj/report?apiKey=YOURKEY&hours:hours_AggFunc=sum&hours:project:company:DE:Account Code_2_GroupBy=C&owner:name_1_GroupBy=true The Group By doesnt error, it just returns back an empty node. "Person 1": { "": { "dcount_ID": 32, "sum_hours_hours": 206.75, "owner_name": "Person 1" } }, "Person 2": { "": { "dcount_ID": 7, "sum_hours_hours": 23.68, "owner_name": "Person 2" } } Brad Baker
21 Replies

Avatar

Level 2
No one uses the Report API I take it? Brad Baker MMC - General

Avatar

Level 2
Hi, yes I do use the GET API to pull a JSON response. You wont be able to use groups or filters as easily as drawing down the data and manipulating it yourself. you should be using: " https://mrc.my.workfront.com/attask/api/v10.0/issue/search?" then your filters, custom fields, projectID's etc. Then any of the items in the API Basics/ Explorer: API Basics: View this on Workfront > API Explorer: View this on Workfront > Nick Patel MRC Global

Avatar

Level 2
Doesnt that mean you dont use the ReportAPI? You told me to use the search api to filter, and then do everything myself via more code. My understanding is the ReportAPI is suppose to handle this all for you so you dont need to issue 1000 API calls to get hours for 1000 users just to sum them. Brad Baker MMC - General

Avatar

Level 2
Brad, There is not an API string for only reporting. there is just the REST API that Workfront has available. You can use excel or Power Bi to draw te data down into a table after you get the string in the correct format. You can use filters and groups, but they are a bit different than the regular camel case syntax in the system itself. Try using an Application called Postman. It will give you more detail on why certain items are not working in your API string. Nick Patel MRC Global

Avatar

Level 2
Please review the API documentation. I know I can do all that extra work to get the data I want. But the API says I can call a REPORT API TO GET AGGREGATE DATA which is what I want to do. But if you see my example above there is an error. Yet no one seems to be able to explain why, or point me to true documentation other than this 1 blurb. Requesting a Report You can perform a report request, where only the aggregate of some field is desired with one or more groupings. As shown in the following example, the report syntax is the same as the syntax for the SOAP API: GET /attask/api/v9.0/hour/report?project:name_1_GroupBy=true&hours_AggFunc=sum Brad Baker MMC - General

Avatar

Level 2
I get a response with aggregated data and no errors with that string that you provided. https://mrc.my.workfront.com/attask/api/v9.0/hour/report?project:name_1_GroupBy=true&hours_AggFunc=s... Nick Patel MRC Global

Avatar

Level 2
what is the entire string you are wanting to use? Nick Patel MRC Global

Avatar

Level 2
It all in my first post if you took the time to read it. Brad Baker MMC - General

Avatar

Level 2
Also is "company" a custom field? Nick Patel MRC Global

Avatar

Level 2
You can call with just this and see the exact error: /attask/api/v10.0/proj/report?apiKey=YOURKEY&hours:hours_AggFunc=sum&hours:timesheet:endDate=2019-02-09 Brad Baker MMC - General

Avatar

Level 2
/attask/api/v10.0/proj/report?apiKey=YOURKEY&hours:hours_AggFunc=sum & hours:timesheet:endDate=2019-02-09 https://mrc.my.workfront.com/attask/api/v10.0/project/report?hours:hours_AggFunc=sum_hours:timesheet... Try this one. Does it return the information you need? Nick Patel MRC Global

Avatar

Level 2
https://mrc.my.workfront.com/attask/api/v10.0/project/report?hours:hours_AggFunc=sum_hours:timesheet... Looks like this works fine now. it seems it was the & between sum and hours throwing your request string into separate pieces. Nick Patel MRC Global

Avatar

Level 2
The $$ROLLUP function will give you multiple group levels as well. https://mrc.my.workfront.com/attask/api/v10.0/project/report?hours:hours_AggFunc=sum_hours:timesheet... Doug Patton": { "MRC Global": { "dcount_ID": 1, "owner_name": "Doug Patton", "company_name": "MRC Global" Nick Patel MRC Global

Avatar

Community Advisor
Hi Brad, You are correct: for straight up aggregate functions, using a single report call can efficiently achieve the same thing as multiple search calls. I've just run a report test that is similar what you're trying to do, which I invite you to study so you can then build something similar that meets your needs. In it, I requested all of the hours entered by an owner whose name contains "Michael" (to keep the data set small) from time sheets with a particular end date (as you're doing), grouped first by owner name (as you're doing), grouped second by project name (similar to what you're doing with company, but as such companies are null in my test data), summing the hours , with a grand total for good measure. Here's what that looks like in the url (once you've logged in to YOURDOMAIN): https://YOURDOMAIN.my.workfront.com/attask/api/v10.0/ hour / report ? owner:name=Michael&owner:name_Mod=contains & timesheet:endDate=2018-10-15&timesheet:endDate_Mod=eq &fields= owner:name , project:name , hours & owner:name_1_GroupBy=true & project:name_2_GroupBy=true & hours_AggFunc=sum & $$ROLLUP=true I've also pasted the (obfuscated) results below, for reference. Regards, Doug [DDH: corrected to refer to project:name, vs project:portfolio:name] { data : { Michael 1 : { Project A : { dcount_ID : 1 , sum_hours : 1 , owner_name : "Michael 1" , project_name : "Project A" }, Project B : { dcount_ID : 1 , sum_hours : 3 , owner_name : "Michael 1" , project_name : "Project B" }, : { dcount_ID : 5 , sum_hours : 40 , owner_name : "Michael 1" , project_name : null }, $$ROLLUP : { dcount_ID : 7 , sum_hours : 44 } }, Michael 2 : { Project B : { dcount_ID : 2 , sum_hours : 2 , owner_name : "Michael 2" , project_name : "Project B" }, : { dcount_ID : 10 , sum_hours : 38 , owner_name : "Michael 2" , project_name : null }, $$ROLLUP : { dcount_ID : 12 , sum_hours : 40 } }, Michael 3 : { $$ROLLUP : { dcount_ID : 8 , sum_hours : 48 }, : { dcount_ID : 8 , sum_hours : 48 , owner_name : "Michael 3" , project_name : null } }, Michael 4 : { Project C : { dcount_ID : 11 , sum_hours : 30 , owner_name : "Michael 4" , project_name : "Project C" }, $$ROLLUP : { dcount_ID : 13 , sum_hours : 40 }, : { dcount_ID : 2 , sum_hours : 10 , owner_name : "Michael 4" , project_name : null } }, $$ROLLUP : { dcount_ID : 40 , sum_hours : 172 } } } Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads -------------

Avatar

Level 2
Still have issue with my custom field showing "" Brad Baker MMC - General

Avatar

Level 2
Doug, can you go more than 1 level deep with the grouping fields? So I would need to go from project -> company -> custom field. Your example works, but its never more than 1 level past ex project -> name Brad Baker MMC - General

Avatar

Level 2
EX Doug: v10.0/hour/report?owner:name=Brad&owner:name_Mod=contains&timesheet:endDate=2019-02-09&timesheet:endDate_Mod=eq&owner:name_1_GroupBy=true&project:company:name_2_GroupBy=true&hours_AggFunc=sum&$$ROLLUP=true { "data": { "Brad Baker": { "": { "dcount_ID": 10, "sum_hours": 40, "owner_name": "Brad Baker" } }, "$$ROLLUP": { "dcount_ID": 10, "sum_hours": 40 } } } Brad Baker MMC - General

Avatar

Level 2
Just FYI doug you said you grouped by portfolio:name but you didnt. If you do this: project:portfolio:name_2_GroupBy you will get the same issue I have. If the field you want to groupBy is more than 1 hop away, report api doesnt seem to be able to include the values. Brad Baker MMC - General

Avatar

Community Advisor
Building on my previous example, Brad... This works ( "one hop" from hour to project :portfolioID ); in that it groups by Owner, then Project.PortfolioID: https://YOURDOMAIN.my.workfront.com/attask/api/v10.0/hour/report?owner:name=Michael&owner:name_Mod=contains&timesheet:endDate=2018-10-15&timesheet:endDate_Mod=eq&fields=owner:name,project :portfoioID ,hours&owner:name_1_GroupBy=true&project :portfolioID _2_GroupBy=true&hours_AggFunc=sum&$$ROLLUP=true And this works ( "two (and a half) hops" from hour to the DE:project:ProjectNews custom data parameter on a custom form behind each Project); in that it groups by Owner, then Project.ProjectNews: https://YOURDOMAIN.my.workfront.com/attask/api/v10.0/hour/report?owner:name=Michael&owner:name_Mod=contains&timesheet:endDate=2018-10-15&timesheet:endDate_Mod=eq&fields=owner:name, DE:project:ProjectNews ,hours&owner:name_1_GroupBy=true& DE:project:ProjectNews _2_GroupBy=true&hours_AggFunc=sum&$$ROLLUP=true This does not ( "three hops" from hour to project:portfolio:name ); in that it runs, but groups only by Owner (ignoring Project.Portfolio.Name, which is "too far"): https://YOURDOMAIN.my.workfront.com/attask/api/v10.0/hour/report?owner:name=Michael&owner:name_Mod=contains&timesheet:endDate=2018-10-15&timesheet:endDate_Mod=eq&fields=owner:name, project:portfolio:name ,hours&owner:name_1_GroupBy=true& project:portfolio:name _2_GroupBy=true&hours_AggFunc=sum&$$ROLLUP=true So, to get what you're after, I suggest you cheat using the concepts from our "http://store.atappstore.com/hows-my-portfolio/">How's My Portfolio blog post by pulling the custom data parameter "down" to the Project level in a calculated custom parameter (e.g. "ProjectCompanyName", or some other convention that Workfront will not confuse with a reserved field name) whose formula refers to the Project.Company.Name and group by it, instead ( "two (and a half) hops" from hour to DE:project:ProjectCompanyName ); thereby grouping by Owner then Project.ProjectCompanyName: https://YOURDOMAIN.my.workfront.com/attask/api/v10.0/hour/report?owner:name=Michael&owner:name_Mod=c..., DE:project:ProjectCompanyName ,hours&owner:name_1_GroupBy=true& DE:project:ProjectCompanyName _2_GroupBy=true&hours_AggFunc=sum&$$ROLLUP=true Regards, Doug Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads

Avatar

Level 2
How do you make the calculated field? Can you point me to documentation or provide the steps. I'm not a Workfront user per say, just a Software Engineer and our Admins aren't tech savy. Honestly Im not a fan of WF, always seems like we have to hack something to make it work. Not to mention it doesn't really work for software projects when you want to link to commits and builds and such, but that's another story. Brad Baker MMC - General