count of groups, based on data within the group

building reports desktopintelligence
1

My report returns sales receipts, and lines within receipts.
lines can be men’s products, women’s products or children’s products.

I need to return:
count of reciepts where all lines are men’s,
count of receipts where all lines are women’s,
count of receipts where all lines are children’s,
and count of mixed receipts

Business objects does not seem to allow contitional counts

I have tried setting a variable - 0 if mens, summing at the receipt level, but I still can’t count the zeros.

Any thoughts?

ddewar53 :us: (BOB member since 2003-07-25)

2

Use


=Count(<Line Id>) Where (Type="Mens")


=Count(<Line Id>) Where (Type="Womens")

… so on

BO_Chief :us: (BOB member since 2004-06-06)

3

What do you mean by mixed receipts?

Jansi :india: (BOB member since 2008-05-12)

4

Sorry Jansi
Mixed receipts are receipts were not all lines are in the same category
a receipt with one mens line and one womens line would be a mixed receipt

Thanks, BO_Chief, but that just gives me a count of lines

I need a count of receipts

example:

Receipt No 1
Line 1 - Mens
Line 1 - Mens
Receipt No 2
Line 1 - Mens
Line 2 - Womens
Receipt No 3
Line 1 - Womens
Receipt No 4
Line 1 - Childrens
Line 2 - Childrens
Receipt No 5
Line 1 - Childrens
Line 2 - Womens
Receipt No 6
Line 1 - Mens

Result:

Mens - 2 Womens - 1 Childrens - 1 Mixed - 2

ddewar53 :us: (BOB member since 2003-07-25)

5

Okay… it is better if you handle this at Universe Level.

Its little difficult at report level…, To solve at the report level you have to introduce flag variables… and count and sum them

BO_Chief :us: (BOB member since 2004-06-06)

6

Was afraid of that. Doesn’t leave much flexibility - the user is already talking about other categories. looks like I’ll be busy with this one.

Thanks

ddewar53 :us: (BOB member since 2003-07-25)

7

For anyone interested in the solution I used -
first, created in in-line view (Derived Table) in the universe as a temporary measure
second, as a long term solution I wrote an Oracle function and added it to the database. It’s taken until now for our DBA to review and approve it :slight_smile:

ddewar53 :us: (BOB member since 2003-07-25)