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]

We are even good just to know the actual users information that have the hardwares assigned.

There is one issue using the related list condition.

If we do the option "equals to or greater than 2" or "greater than 1" it will check first the relationship without filters if they have 2 hardwares assigned. Assuming there is 1 retired and 1 active it will query against both anyways because according to the related list filter meets the criteria. Hopefully I was able to explain myself and this is where we get stuck.

[Dr Atul G- LNG]

Hi @AndresGT1 

That’s the expected behavior, and that’s why I suggested creating a Database View. This will allow you to apply a filter so that if a CI/Asset is not in the operational state, it won’t appear here.

So, create a Database View and set a filter condition accordingly

[YaswanthKurre]

Hi @AndresGT1 ,

1. Create a New Report: Navigate to Reports > Create New.

2. Data Tab:

   • Table: alm_hardware

   • Filter: Set your condition: Model category.Class is Computer.

3. Type Tab:

   • Type: Select a type

4. Configure Tab:

   • Group by: Assigned to

   • Aggregation: Count (on any field, usually Sys ID or just Count).

   • Limit: Condition

   • Aggregation Filter: Set the condition here: Count (Sys ID) (or just Count) Greater than 1.

   • Run the report.

 The drill-down (list view) might not retain the aggregate filter, but the chart itself and the data series it displays will be correctly filtered.

or you can try the scripting option which is better for record-level detail, you'll typically need to use a Script Include with a GlideAggregate query. This leverages the database's ability to filter groups.

Please mark this as helpful and correct, if this helps.

Thanks,

Yaswanth

[AndresGT1]

I don't find the following on the reports

• Aggregation Filter: Set the condition here: Count (Sys ID) (or just Count) Greater than 1.

Maybe share an image or more detailed instructions on where to find this?

[Voona Rohila]

Hi @AndresGT1 

This can be acheivable by calling script include method from your report and that should return the sys_id's of hardware with more than 1 assigned to.

The filter in the report will be 'sys_id || is One of || javascript: new scriptincludename().methodname()';

Groupby - Assigned to.

Sample logic:

'sys_id || is One of || javascript: new getReportConditions().getHardwaresReport()';

var getReportConditions = Class.create();
getReportConditions.prototype = {
    initialize: function() {
    },
	getHardwaresReport: function()
	{
		var hardwares = [];
		//Glide logic to push hardware sysid's
		return hardwares;
	},
    type: 'getReportConditions'
};

Give a try and let us know if you're stuck.

You can also write filter according to 'Assigned to' and your script should return assigned to sysid's which has count more than 1.
example - 'Assigned To.sys_id || is oneOf || new getReportConditions().getHardwaresReport()';