How to create a report to only show where count is > 1?

[AndresGT1]

We are currently going through a requirement to build a report of type count and to only show records where the count is > 1.

Take this as an example.
Report should query alm_hardware with model category/class of Computer and group by assigned to.

It should only display records where when grouped by count is > 1 (Saying only to display hardwares where a user has 2 or more assigned).

Any ideas?

[AndresGT1]

Do you have a sample image on how this should be called in the report? I think could be what I'm looking for.

[Dr Atul G- LNG]

Hi @AndresGT1 

Did you check my response? My concern is — did you add more than one field to assign an asset to a user? If yes, what’s the business case for that? Because technically and logically, an asset should be assigned to only one user at a time, and if that’s the case, the scenario mentioned above will not work.

[kaushal_snow]

@AndresGT1 ,

Standard reporting engine you cannot apply a filter that only shows groups where the count (after a Group By with aggregation of Count) is > 1, because the report filters apply before aggregation and there is no oob having style filter for the result rows.....

As a workaround you could build a report or pivot with the grouping (e.g., on assigned_to for alm_hardware filtered to model category/class = computer), export that result and then filter externally, or you could create a custom field (via a scheduled job or business rule) that pre calculates the count of assets per user, store that on the user or some helper table, and then report on that field with a filter > 1.....

If you found my response helpful, please mark it as ‘Accept as Solution’ and ‘Helpful’. This helps other community members find the right answer more easily and supports the community.