Need some help with a custom field report.

[Patrick_Yarnell]

Hi Everyone, I'm really new to Workfront, and am still working my way through the Advanced Reporting training in Ascent. Was hoping someone might have a suggestion on where to get started. or if I need to do a text mode calculation of column or grouping data to achieve the following in a pie chart on a single report, or if that is even possible: Report Criteria: Breakdown of portfolio based on (% of projects in the portfolio aligned to one, or multiple, Yes selections of project radio button fields for which of 4 Strategic Goals a project is aligned with, also the % of projects in the portfolio that aren't aligned to ANY strategic goal? Here's a screenshot: Need to report against all projects in the portfolio currently (which I know how to filter), but I'm not sure how to get the count (sum) of when there are multiple selections, OR no selections, to then create the chart, and group by the list of strategic goals, in a single report. I can do this for each field separately in 5 reports then list out in a dashboard, but I then have the "no value" percentage showing for each report, which doesn't really do much for simplicity of viewing, and is somewhat confusing to review, IF the above can be accomplished. Thanks in advance for any guidance you can provide, or if I need to provide more details, please let me know! Patrick Yarnell

[JustinRe]

Hi Patrick, Quick question, is there a particular reason why you're using 4 seperate fields as opposed to one Multi Select Drop Down field that could be titled "Please Indicate which Corporate initiatives your..........." and have the 4 options as values a user could select from? If there is no special reason I recommend going that route instead, then use the "Charting by Multi-select Custom Fields" section in the following article to create a calculated field referencing your multi select field: https://experience.workfront.com/s/article/Group-and-Chart-a-Report-by-a-Multi-select-Custom-Field-1... Once you have that calculated field created you can use it as the grouping in your pie chart report. Otherwise, to accomplish what you want you will have to create a calculated field that you've coded to check for every possible scenario EX: IF(Talent Acquisition Alignment = "Yes" , "Talent Acquisition Allignement", IF(New Business Alignment = "Yes" , "New Business Alignment", IF(Increase Client Retention Alignment = "Yes", "Increase Client Retention", IF(Nondiscretionary Alignment = "Yes", "Nondiscretionary Alignment", IF( Talent Acquisition Alignment = "Yes" && New Business Alignment = "Yes" , "Talent Acquisition Alignment, New Business Alignment",...............) You could then use this new calculated field as the grouping on your pie chart. As for your "no value" question, you can design your new calculated field to display something other than "No Value" when your fields are blank. EX: if you go the route I suggested you take, the calculation would look something like this: IF(ISBLANK(Please Indicate which Corporate initiatives your...........), "No selections have been made" , Please Indicate which Corporate initiatives your...........) The calculation above will then result in a slice of your chart displaying "No selections have been made" if your data contains a project without selections. Please note, I have not tested any of the code I wrote and did it entirely out of memory. There may be typos in the code above. Justin Renteria SSFCU

[Patrick_Yarnell]

Hi Justin, Thanks for the response, I was under the impression you couldn't chart on these fields. IS it possible to do a Calculated Field, off of a previous calculated field? Or would that create a circular/chained reference problem for the reporting? The reason I ask, is because for the report/chart I'd like to finally display, I'd like to aggregate the percentages of the muli-select fields to a single Percentage calculation against "IF" the check boxes are selected per project. For Example. Options A, B, C, D, E exist to be selected. I would like to capture IF selected option, then count + 1. Then take the Sum of A, B, C, D, E selections on each individual project, and calculate the percentage of projects in the portfolio that have each option selected, as well as list a percentage of projects that have NO listed option selected. This is where I get stuck...I'm not sure how to aggregate the calculated field percentages when you have projects with multiple selections chosen, for example Project 1: A, Project 2: A+C, Project 3: B+C, etc.). every KIND of selection option shows up as a separate percentage when I use the single calculated field in the chart. I'm not sure how to get the chart to aggregate those percentages in a single report. Or if that is even possible, and instead you'd have to use some sort of dashboard with other calculations/calculated fields applied, or an extremely advanced TEXT based Grouping by calculated field(s) (which doesn't seem possible to then ALSO CHART) using the calculated field based on the documentation. It seems to be a catch 22. Or maybe there's a better way to achieve the end goal with how I "collect the data" for reporting initially, I'm all ears. Not sure, yet which is the best way to solve the mystery. Sorry for the rambling. Patrick Yarnell Hylant

[ErichGi]

Justin, Thank you your post, this is an excellent resource. I'm wondering if you or anyone else can expand on this for me. Imagine the scenario where I need everyone on my team to select which of the following fruit they enjoy, and my options are Bananas, Strawberries, Grapes, and Kumquats. This would be a required field on every project. I would like to chart something like: Bananas |XXXXXXX 7 Strawberries |XXXXX 5 Grapes |XXXXXXXXXXX 10 Kumquats |X 1 No Value |XX 2 Instead I am getting something more like: Bananas, Strawberries, Grapes |XXXX 4 Bananas, Grapes |XX 2 Bananas |X 1 Grapes, Strawberries |X 1 Grapes |XXX 3 Kumquats |X 1 No Value |X 1 However, instead of fruit the multi-select are impacted area of the business. I am attempting to build a report for a leadership view that explains our portfolio's footprint across the entire business. "We have 10 projects that will impact the Grapes business area, and only 1 project that impacts the Komquats group." Many Thanks and Happy Trails, ErichGiltinan Navy Federal Credit Union

[DougSa]

Erich, I've been trying to do the same thing as you describe above. Have you had any success? Douglas Sampson Federal Reserve Bank of Richmond

[CoreyBe]

Erich, this is the same scenario I am trying to solve for. Have you had any luck figuring this out? Corey Beavers Mohawk Industries, Inc.

[ErichGi]

Douglas & Corey, Unfortunately, I have not been able to accomplish this within Workfront. Moreover, I am convinced it is not possible. Since posting this in June I have transitioned most of my executive-level reporting to using Microsoft PowerBI using the Workfront API as a data source. I have had a lot of success in this area lately. If your organization has an appetite for more sophisticated reporting than what is available through Workfront, you may want to consider moving some of your reporting into a BI tool. From my perspective, the end result has certainly been worth the time invested. I hope that helps. Feel free to reach out if you have any specific questions. I love to talk advanced reporting. Erich Giltinan