How to Show Unique Records (Distinct Assets) in Report Drill-down When Using DB

[shubhamkhai]

Hi Community,

I’ve built a bar chart report to track hardware assets that were In Stock and transitioned to another state in the last 45 days.

Setup
I created a Business Rule to record state duration using asset metrics.

Then I built a DB View combining alm_asset and metric_instance.

The report uses these filters:

• End > javascript:gs.daysAgo(45)
• Field value = In Stock
• State != In Stock
• Aggregation is Count Distinct on alm_asset.sys_id.

Chart view works correctly and shows proper unique asset counts.

Grouped by Model Category and stacked by State.

Problem -
When I click on any bar (drill-down view), the list shows multiple metric rows for the same asset, because the asset may have transitioned out of In Stock multiple times.

I want the drill-down list to also show only unique assets, just like the aggregated chart count — not multiple entries for the same asset.

1. Question
   Is there a way in ServiceNow to:
2. Make the drill-down table display only unique asset records,
   OR
3. Define a custom drill-down report that replaces the default DB View result,
   OR

Any best practices to avoid duplicates in drill-down when reporting on DB Views?

Basically, I want the chart to show distinct counts and the drill-down to show a unique list of assets, not raw metric rows.

Any suggestions or recommended patterns?

Thanks!