Omitting Single Totals

[donlste]

Hi All,

I have a table with serial numbers in the one column, and a count of how many serial numbers in the other.

However, what I'm trying to achieve is to omit any totals of '1'. But I'm having no luck.

Could anyone suggest anything?

 

[blom0344]

Hello Donlste,

In SQL-terms this is a typical group by ... having ...
construction. If you want to put a comdition on a calculated field the SQL-syntax will always look like this:

select
table.field,
count(table.field)
from
table
group by
table.field
having
(count(table.field) <> 1)

With the proper definition of the count object in the universe, BO will automatically generate the correct SQL-structure. If you select more fields, you are forced to mention them in the group by part. This will however cause the count-function to count over more than field.

As an alternative you can suppres the data with count 1 in the report. The standard filter-options of BO do not include such direct filters as >1, but you can work around this by defining a variable (dim or detail) that stores a 'Y' when count > 1 and a 'N' if count = 1.
Simply put a filter on this variable that shows only 'Y'.

blom0344