Help with counts duplicating on report

I need some help with counts in a report. I am counting cost centers that are linked up to a table from my main fact table via an org id key. The “Cost Center count” table fields have a 1 for each cost center that is current, and a 0 if it is closed. The count is a measure in my universe. When I show the counts in my report and I have just my organizational hierarchies in my report, it runs fine with the right count #. If I bring in a hierarchy of products, it will duplicate the counts. Here is an example that might better explain it:

This is a query without products:

Month End	Entity	Total Deposits	Total Product Line	Rec Count	Cost Center #
1/31/2004	XYZ Inc	Total Deposits	Business Deposits	331,242	1,816
1/31/2004	XYZ Inc	Total Deposits	Consumer Deposits	13,843,108	1,830

This is a query with products:

Month End	Entity	Total Deposits	Rec Count	Cost Center #
1/31/2004	XYZ Inc	Total Deposits	14,174,350	3,646

As you can see, when I drill up to total deposits or remove the total product line, it sums the two rows of counts and gives me 3646, when I would like to see 1830.

Any insight would be greatly appreciated. Thanks! :confused:


Stringzz (BOB member since 2004-06-10)

When you define a measure in the universe, it uses SUM as the default aggregate function. Try changing that in the universe designer for the “count” measure in your universe…this may help.

kashif


Kashif Saeed :pakistan: (BOB member since 2004-06-02)

I probably should have given the code for the count measure. I am actually using count distinct.

Here is the the select statement I am using in my universe for the Count Measure:

count(distinct decode(HIEROWNER.ORG_HIER_COUNT.COUNT,1,HIEROWNER.ORG_HIER_COUNT.ORG_ID,0))

Stringzz (BOB member since 2004-06-10)