How to make a report show all categories including those with a count of 0?

[jayeshyadav252]

Hi all,

I have a Pivot Table report on a custom scoped table. Configuration:

• Aggregation: Count
• Row: Region (a reference field on the record)
• Column: A record type/category field
• Filter: A condition of the form Sys ID in javascript&colon; <Script Include call> — the Script Include returns the list of record sys_ids the current user is allowed to see, based on their group membership mapped to a region. A super-admin sees everything.

Current behaviour: If a region has no matching records, that region does not appear in the report at all.

Required behaviour: Every region should always appear — and if it has no records, it should show a count of 0. This is especially important for the super-admin view, which needs to see all regions regardless of whether they have data.

What I understand so far: Because the filter is a Sys ID in [...] condition, it can only ever return rows that already exist. If a region has zero records there is no sys_id to return, so the pivot has nothing to group on and the region is simply dropped. So I suspect this can't be solved from within the filter itself — the region axis would need to be defined independently of the data.

My question: What's the recommended way to make empty categories (regions) render as 0 in a report while still enforcing role-based, per-region access control on the underlying records.

Has anyone solved this "show all categories including zero counts" requirement while keeping row-level, group-based access control? Is there a cleaner built-in approach I'm missing — a report setting, a different source type, or a supported way to inject zero rows?

Any guidance or examples would be much appreciated. Thanks!

[Mark Manders]

Only if the region has nothing at all, the region isn't shown. Otherwise it will be empty for that category column. And in that case it's a simple: none = 0.
And the same with no region shown: none = 0.

In Platform Analytics pivot data visualizations, there is an option under 'additional options' to set 'show 0 when no data available'. That should put some zeros in there.

[jayeshyadav252]

Hi @Mark Manders 

Thanks for the Quick response.

Is there any possibility where we can implement this in Native report.