Creation of a report containing the information in the view of Resource Planner under planning which is under people

[BillPe]

I have been trying to create a report that mimics the data in the view (Resource Planner). I want for each individual in a team – Available hours, Planned hours, Different between Planned and Available, Percent of Planned hours out of available hours. The biggest issue I have is finding available hours. I would like to do this on a monthly basis. Any help is appreciated. Bill Percival PMP Senior Project Manager, Tribal Government Durant 1802 Chukka Hina 4^th Floor West (South Hall – Far West Section) Durant, OK 74701 (580) 924-8280 X2397 (office) (580) 775-8931 (mobile) This message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure. If you have received this message in error, you are hereby notified that we do not consent to any reading, dissemination, distribution or copying of this message. If you have received this communication in error, please notify the sender immediately and destroy the transmitted information. Please note that any view or opinions presented in this email are solely those of the author and do not necessarily represent those of the Choctaw Nation.

[Doug_Den_Hoed__AtAppStore]

Hi Bill, Not to discourage you, but that is a tall order you're seeking to fill. The data required (particularly peoples' availability) is stored across a number of objects within the Workfront database, which will prevent you from consolidating everything into a single Workfront report. On the other hand, I believe that our "https://store.atappstore.com/product/capacity-charting/" Capacity Charts solution which aligns well with the requirements you're describing. I invite you to consider it, and to email me at doug.denhoed@atappstore.com if you think it might be a fit. Regards, Doug Doug Den Hoed - AtAppStore

[DavidCo3]

And whilst it was a tall order indeed, we did go ahead and build this in Power BI. We brought in all the related info (via API) regarding Schedules, PTO etc and calculated the daily available hours for all of our resources. We could then build all these kinds of resource productivity reports as needed. Even though this workaround is possible, it is really not feasible for most customers and so I really hope Workfront can one day make Availability values available within the report building environment in Workfront. This is something that would be super powerful. David Cornwell

[Doug_Den_Hoed__AtAppStore]

Good on ya, mate! That makes two of us who've noodled all of that out. We are SO overdue for a pint, David. I do hope you can make it across the pond one of these days,. Also (on behalf of Canada) our thoughts and prayers are with you and your whole country for some relief from the fires. Regards, Doug Doug Den Hoed - AtAppStore

[DavidCo3]

Thanks Doug. We appreciate the support of the whole world, including especially the Canadian, USA, and NZ firefighters who are on the ground. There's even offers of planes from Singapore, military from PNG and other assistance from France. I'll certainly let you know if/when I do make it 'across the pond' in the future! :) Cheers David Cornwell

[Rick_MacDuffie]

@David Cornwell‚ I just started digging into a few Power BI scenarios myself and wondering if you had any advice for not exposing the apiKey in the datasources? While I work through my proof of concept I'm using Json.Document(Web.Contents(blah blah blah)) to get the data. Needed to do some noodling to traverse the thousands of rows of data (tasks, projects, issues, & hours are the primary objects I'm working with) in DAX, but have a solid pattern to manage that.

The concern is that the apiKey is embedded in the Web.Contents function call and included in the Published dataset, which is downloadable, which means my apiKey would get hijacked. No bueno!

Assuming you took a similar approach; how'd you handle securing that apiKey?

[kynabaker16]

@David Cornwell‚ I believe you've changed roles but still might be interested in participating in this conversation, so tagging your new profile!

[Rick_MacDuffie]

Thanks @Kyna Baker - inactive‚ !

So I figured out how to conceal the apiKey, but doing so causes auto-refresh of the dataset in the Power BI Service to fail. Nothing is ever easy, is it?

For sake of documenting how to conceal the apiKey... Power BI needs the Web.Contents() call to be formatted as follows:

Source = Json.Document(Web.Contents("https://<YOUR INSTANCE NAME>.my.workfront.com/attask",

[

RelativePath="/api/v9.0/optask/search",

Query=

[

#"$$FIRST"="0",

#"$$LIMIT"="10",

fields="ID,name"

],

ApiKeyName = "apiKey"

]))

Then change your Data Source Settings (either Data Sources in current file OR Global permissions) to use the Web API credential type. This will prompt you to enter your apiKey value and will securely store it in your local credentials cache. Works great in Power BI Desktop, and publishing to Power BI Service does work, but all refreshes (from anywhere other than your desktop) will fail because the Web API apikey value is only local. Connecting to a gateway doesn't seem to help either as the Web API authentication method is not available on the gateway.

If anyone has handled the refresh, while protecting the apiKey value I'd appreciate some guidance.

[DavidCornwellLP]

Hi @Rick MacDuffie‚ . This is something I'll have to revisit as I haven't been working with Power BI recently. One option/workaround is to use the workspace settings to prevent your users from being able to download the PBIX file (that's what I did before). Not ideal, but it's an option.

I'll do a bit more research soon on this topic. One option is to look at using a service like Azure Data Factory to manage the data extraction from Workfront, then load the data to Data Lake or SQL then you can connect Power Query to that. I expect that Data Factory will be a good option that lets you securely store the API credentials away from your users, and then you could let them download the datasets if need be. I've certainly used Data Factory and DataLake as a source for PowerBI before...just not Data Factory and Workfront as it data source yet.

Keep me posted on what you find out also!