How do I only show detail with multiple counts

[Former Member]

How do limit my report to showing only the groups with more than one line of detail?

[fritzfeltus · Accepted solution]

Create a "count" summary in your group.

Go to the Report > Select Expert > Group menu item.

Enter the selection formula for your group (e.g. Count of Customer.Country): "is not equal to" 1.

Cheers,

Fritz

[Former Member · Accepted solution]

I tried this, but had no luck. Are you talking about the Running Totals area for creating a count?

[fritzfeltus · Accepted solution]

No, just go to "Insert > Summary", choose "count" as summary type, and insert the summary into your group footer.

Now you should be able to create a Group Selection Formula.

[Former Member]

Hi

Open the Section Expert and apply the below formula in the group header, details, group footer

count(fieldname) = 1;

The field would be the field which you have dragged to the details section.

Please let us know if your requirement is defferent from this.

Hope this helps!!

Regards

Sourashree

[Former Member]

It might be better to go to the Select Expert and apply that

condition there.

That way it's only in one place and limits the required records

on the inititial selection.

If that's not possible because of the aggregate needed, then

reworking the query to filter might be better.

Report should run faster too.

The Panda

[Former Member]

I assume this is under the Suppress (No Drill Down) area?

[Former Member]

The Selection Expert said that the function cannot be used because it is evaluated later.

[Former Member]

Hi Steve,

In that case, the best solution might be to write the query

and put it in the Command table.

Depending on your skill set, you might need some help

with this from you DBA.

The benefit is that your report will run much faster.

It will be something like:

"Where count(table.field) > 1" as the filter

In my DB2 world it might be:

SELECT  MVH800.LAST_UPDT_ID, 
              Count(AC_NBR) as Act_Count
FROM SYSINT.MVH800 
GROUP BY MVH800.LAST_UPDT_ID
HAVING Count(AC_NBR) > 1

and the result is only groups with a count of ac_nbr's greater than 1:

LAST_UPDT_ID *	ACT_COUNT *
20	                    10
54	                  146
62	                     2
97	                 174
103	                     3
115	                     2

Hopefully you have a query builder for your db that you can use.

ps. this is the easy (and better) way

Hope this helps,

The Panda