Platform Analytics Group By "Project Status" produces undefined as result. Pie Visualization

[rca1992]

I have upgraded the Dashboard/Reports to Platform Analytics and I am now receiving the "undefined" record count.  

I have create the data visualization from scratch with the same results.  

Any idea why its not recognizing the group by? 

Any help is appreciated.

Visualization Type: Pie

Data source: pm_project

Metric: Project Count

Group by: Project Status.

[Vikram Reddy]

Hey @rca1992,

This is a common one with pm_project: Project Status (Green/Yellow/Red) isn't a value you set directly on the project record, it rolls up from the Overall Health field on the most recently submitted Status Report (the project_status table). If a project has never had a status report submitted, that field is simply null on pm_project, and the Platform Analytics pie visualization doesn't bucket nulls into an "(empty)" group the way classic reports do, it renders them as literal "undefined". A pie that's 100% undefined across all 41 records almost always means none of those projects currently have a status report with Overall Health set.

• Open a handful of the 41 projects and check whether Project Status is actually blank on the form
• Check the Status Reports related list on those projects for any submitted records
• If a report exists, confirm Overall Health was selected and saved on it, since that is what feeds Project Status

Thank you,
Vikram Karety
Octigo Solutions INC

[rca1992]

Thank you for the information.

In our case, the field Project Status, is a custom field type Choice.  With choice values  Cancelled, Complete,Delayed,On Hold,Behind Schedule,On Track.  The values are selected manually, therefore have no relationship with the color indicators nor the status report. All of the project records have a value selected, yet total counts shows "undefined"

[Vikram Reddy]

Hey @rca1992,

Good, that rules out the health rollup theory then, since your values are choice-driven and manually set, not fed by Status Reports. That actually points somewhere more mundane, and honestly more annoying to track down: a field label collision on pm_project.

Out of the box, pm_project already ships with an element also labeled Project Status, the one that rolls up Green/Yellow/Red from the Overall Health field on the most recent status report (this is the field my original answer was describing). If your custom choice field was built and labeled "Project Status" without renaming or checking for a collision first, you now have two separate dictionary entries on the same table sharing the exact same display label. ServiceNow's reporting and grouping tools resolve fields by label in a lot of places, and when two elements share a label, it's a known pattern for the tool to bind to the wrong one, in your case almost certainly the original OOB element, which is null on every project that's never had a status report submitted. That would explain exactly what you're seeing: your custom field is populated on all 41 records, but the pie is grouping on a different, empty field underneath and rendering that as undefined.

Steps to confirm and fix:

1. Go to System Definition, Dictionary, and filter on Table equals Project [pm_project] and Column label equals Project Status. If you get back two rows, you've found it. Note the actual column names of each (something like project_status for the stock field versus whatever your custom element is named).
2. Open your data visualization's Group By configuration and check which technical element it's actually bound to. If the picker only shows you the label, add both fields as columns on a pm_project list view instead, that will make it obvious which one is which and which one is actually null across your records.
3. Once confirmed, rename the label on one of the two fields, ideally the OOB one if you're not using it, or your custom one to something distinct like "Project Health Status", so there's no more ambiguity. Then rebuild the Group By selection in the visualization pointing at the correct element and it should stop returning undefined.
4. While you're in there, also check System Definition, Choice Lists, filtered to pm_project and your custom element, and confirm all six values (Cancelled, Complete, Delayed, On Hold, Behind Schedule, On Track) exist as active sys_choice records tied specifically to that element. If any project record's stored value was set by import or script rather than picked from the dropdown, it's possible to have a raw value with no matching active choice record, which also renders as undefined in aggregated views even though it displays fine on the form.

My money's on the duplicate label. That kind of collision is easy to create by accident when a custom field is named to match what people are used to seeing, and it's exactly the kind of thing that only shows up once you start aggregating instead of just viewing the form.

References

• Project Status field and logic - ServiceNow Community

Thank you,
Vikram Karety
Octigo Solutions INC

[rca1992]

Thanks again for the information. I was hoping this was the issue but unfortunately there is only on column with the name Project Status on the pm_project table which just happens to be the custom column.  The choices are all active as well.