All projects report: show infographics in a dashboard.

System

This discussion was created from comments split from: Show multiple contacts listed in Assigned To field in a report?.

Jeff Casto

Hello @Genevieve P. !

I have a similar/not too similar question.

My goal is to have an all projects report that shows criteria from multiple projects and then to show infographics of some of that criteria in a dashboard. I am EXTREMELY close to getting this to work out. I have the all projects report and I have the dashboard. I have one wrinkle...

One of the criteria I am monitoring is who from the Creative department is working on what project.

• In the all projects report, I want it to show the names of the Creative people in each project
• In the dashboard, I want it to show the percentage each Creative person is allocated to projects

Scenario 1 All projects report looks good, but the Dashboard is inaccurate - If I have two people in the same cell in my report, which is how I would want the report to look, then the dashboard shows the two people as one person and that will throw the percent allocation off.

Here's a screenshot of the all projects report showing Arron and Richard in the same cell. And another screenshot showing the dashboard donut showing "Aaron Richard" as one person (and also Aaron as a different person as he's on another project also).

Scenario 2 - The all projects report does not show all the Creative people on the project, but the Dashboard is accurate. I made this happen by adding the name of the other Creative person (Richard) in the cell underneath "Aaron's" cell in the project sheet, rather than in the same cell.

Here is a screenshot of the all projects report showing only the one Creative person, "Aaron" in the cell for that project. Then I have a screenshot of how I set up the project sheet with Richard underneath Aaron - so I could get the dashboard donut to be accurate. And then, finally, the dashboard donut screenshot to show that it's accurate.

Scenario 3 is the goal. The report to show the Creative people on the project and the dashboard donut to be accurate.

(Note: For the dashboard, I am pulling data from a % Creative Allocation report which also pulls from the project sheets, as does the all projects report.)

(Note 2: I understand this could all be accomplished with resource allocation, but our company is not set up for that at the moment, so we're using the humans as objects to get general information.)

Ok, I hope this isn't too much of a mind bender. It's super close and I'm not sure there is an adjustment I can make, but if there is...cool. If not, I appreciate any effort made in thinking about it!

Thank you!

Jeff

Genevieve P.

Hi @Jeff Casto

You've outlined the differences exactly! With a multi-select cell, multiple values in one cell are seen as a joined string, so you'll get "Aaron Richard" in a chart, as you've found. The chart won't be able to parse out the individual people and then merge the data together.

What you'll want to do for Scenario 3 is have two different data sources for your two different visuals.

• Keep the Report from Scenario 1 for your Report Widget view
• Create a Metric Sheet with cross-sheet formulas to generate the correct numerical values for your chart

This way you can use a HAS function in a COUNTIFS formula to see if the multi-select cell has the name that you're looking for:

=COUNTIFS({Sheet 1 Names}, HAS(@cell, Name@row)) + COUNTIFS({Sheet 2 Names}, HAS(@cell, Name@row)) + COUNTIFS({Sheet 3 Names}, HAS(@cell, Name@row))

Cheers,

Genevieve

Jeff Casto

@Genevieve P. Thank you again and again!

I have dumb follow-up questions.

1. In the formula, where you have =COUNTIFS({Sheet 1 Names}, ({Sheet 2 Names}, ({Sheet 3 Names}, I would place the name of my sheets (Project names) - instead of sheet 1, 2, 3... and then for the "Names" would I put the column name I am trying to track in those sheets, for example "Creative" or "Account"? So, it would be ({TTP23 Account}...or am I way off/complicating things too much? (I tried that and I got the "Invalid Ref" note)
2. Or do I need a "Names" column in each of my sheets/projects?
3. Or does this (@cell, Name@row) refer to the columns with the names in them?

Ugh...I know this is awful of me to ask...Thank you for your patience.

Genevieve P.

Hey @Jeff Casto

No problem at all 🙂

1) These references are an example to show you what sheet and column to reference... however to build the reference you would need to use the "reference another sheet" link in the formula builder window to find the correct sheet & column.

Here's an article that goes through creating cross-sheet references, and a free webinar with a video example (part II).

2) Based on your image I believe your "names" column is the one titled "Creative". This is the column in each of your sheets that you would need to reference individually.

3) Name@row is looking at the cell to the left of the formula. In the example above, the Name@row is "Joseph", but then the row below it would be "Rosanna". Does that make sense?

Cheers,

Genevieve

Jeff Casto

Thank you again @Genevieve P. !

I will dig in! Thank you for your guidance!

Jeff Casto

Hello @Genevieve P. !

At risk of you disowning me...I have a couple follow up questions. I tried reviewing the videos and articles regarding formulas and reports and dashboards, but I got confused when my formula went goofy.

First of all, after reviewing the educational links you provided as well as some others, I set up the formula as you showed and it worked great...THANK YOU!

I then attempted to add a new sheet to the formula. The results changed and don't seem to be showing who is working on something properly.

Here is a screenshot of my metric sheet with the Creative team and what the formula is saying as far as how many projects they are on.

Here is a screenshot of the formula (This is the formula after I tried adding a new sheet and then trying to go back to my original formula with just the three sheets)

Please note that is the formula, I am referencing TTP23 sheet.

Here is a screenshot of who's assigned in the TTP23 sheet (Monica)

The first problem I noticed is that Monica is showing 0 in the formula column, but she is on one of the projects - TTP23.

The other problem I see is after I made a report of the metric sheet and included it on the dashboard as a donut, everyone is showing 5%. Some of the people are not assigned to anything, which should be 0%. Here's a screenshot:

If this isn't too much of a headache for you to point out my screw up, I would appreciate any further guidance.

Jeff

Genevieve P.

Hey Jeff!

Your formula looks fantastic... I can't actually see anything wrong with the structure at the moment. This leads me to believe it may be something to do with your references, could that be possible?

Can you do me a favour and check each of them, to make sure they're looking at that one specific column?

One tip with references is you want to ensure if you're adding a new reference, you delete the old reference and fully add in a new one. If you click a reference the use edit it will update that {reference} across all the formulas that use it in your sheet. This is a great way to adjust where a specific {reference} is looking.

In regards to your Chart, can you change the display values from being % to Amount for now, just until we figure out what's going on with the formula?

Cheers,

Genevieve

Jeff Casto

Thank you again @Genevieve P. !

Ugh, so i got all discombobulated with my last attempt, that I built a new Metrics sheet and a new report using that metrics sheet data. I think the reason all the creative resources were showing a number is because in my report I grouped them by number of projects.

Here's the play by play from my new, clean attempt:

I created the Metrics sheet using new projects to pull from and used this formula to pull in the number of projects the people were assigned to

Here's the result in the metrics sheet so far, so good

Here is the original grouping I used in the report - it shows 20 projects and everyone gets a "1". I think that resulted in everyone being "assigned" on my donut.

Here's what I changed the report to

Now...that's nice, you'll see the donut shows people who are assigned and how many projects they're assigned to...My new question is...How can I get the people to show up who are not assigned to anything. So, they appear on the donut, but as 0? I tried all kinds of filters in the report...just not the right one I guess! If it's not possible, that's cool too.

Thank you for your help and patience!

Jeff

Genevieve P.

Hey @Jeff Casto

Thanks so much for this detailed breakdown! Well-done, this all looks great.

I would suggest using the SUM feature in the Summary of your report to get the totals instead... although can I ask if there's a reason you're using the Report?

It looks like you already have the information you need right from the sheet! You could eliminate one element by charting directly from the sheet. 🙂

As you suspected, zeros cannot currently be mapped in a Donut or Pie chart. Please feel free to vote on this Product Idea on the same topic: Display all series in the legend for Pie & Donut Charts even when the value is 0

Cheers,

Genevieve

Jeff Casto

@Genevieve P. !

You certainly may ask why I am using the report to show the donut rather than using the metric sheet.

It's because the option to show the information as a donut when I tried adding the sheet as a metric in the dashboard was not active. I saw the option, but couldn't click it. The available options were to show bars or lines...and I like the donuts...who doesn't like donuts? Or pies?

When I attempted to add the sheet as a chart, I got the dreaded..."Selected data cannot be charted"...

Is there a way I can see a donut or pie with the metric in the dashboard?

Genevieve P.

Hi @Jeff Casto

As long as at least one of your selections has a value greater than 0, then you should be able to use your sheet as the source!

Is it possible you were selecting a range at a time when all of the names had 0 next to them? (In which case a Pie or Donut chart wouldn't be possible, as there are no values to go into that pie! 🥧)

Let me know what happens when you select your two columns like this:

Cheers,

Genevieve

Jeff Casto

Hello @Genevieve P. !

I have a qq if you have a second. I was using the formula you provided to me to create the metrics sheets to view the donuts in a dashboard. It was working PERFECTLY...until it stopped!

Now, in each of my cells, I get the #REF.

1. I didn't touch to the metrics sheets
2. The code I used looks ok to me. I didn't see any boo-boos (again, I didn't change the code at all when it all of the sudden gave me the #REF)

I'm thinking something in one or more of the sheets may have changed. I am not the person managing those sheets, so I can't say if anything significant was changed.

Questions:

1. Would changing one or more of the sheets referenced in the code result in #REF to the code?
2. If so, what change in the sheets would make this #REF result? 2a. Would it be any change (like adding or removing an item from a cell in a column? or 2b. Would it be the column property has changed (drop down vs text entry, etc...)?
3. Does changing something in the sheets have nothing to do with it?

Anyhoo, thanks a ton again for all your help!

Jeff

Paul Newcome

Double check the individual sheets. If that error is found in even one cell of just one reference, it will push through to any formulas referencing that cell whether it be directly or within a range.

Genevieve P.

I agree with @Paul Newcome!

Any chance you have a an error in ANY of the referenced columns?