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!
Stringzz (BOB member since 2004-06-10)