SQL2K, AS2K. Consider the data table:

VID PID TYPE SUB_TYPE
1 P1 T1 TS1
2 P2 T1 TS2
3 P1 T2 TS1
4 P3 T1 TS2
5 P2 T3 TS3

The 2 measures are VCnt and PCnt.

dim_type would look like:
T1
TS1
TS2
...
T2
TS1
TS2
...

If I filter the data at the level, I would get the following VCnt and PCnt

Level VCnt PCnt
dim_type 5 3
T1 3 3
T2 1 1
T3 1 1
TS1 2 1
TS2 2 2
TS3 1 1

The VCnt works ok. However, the top level count is not a sum of the lower
level cnt for PCnt. How do I do this? Is it possible to build the logic in
OLAP? or I need to go back to SQL to re-build the fact table. Thanks.

Sorry. That's what I am looking for:

Level VCnt PCnt
dim_type 5 3
T1 3 3
TS1 1 1
TS2 2 2
TS3 0 0
T2 1 1
TS1 1 1
TS2 0 0
TS3 0 0
T3 1 1
TS1 0 0
TS2 0 0
TS3 1 1

The PCnt is adding up wrong.

"MJ" wrote:

a Distinct Count measure count the unique values, so a dcount measure will
produce the PCnt result

does its what you looking for?

do you use AS2000 or AS2005?


"MJ" <MJ (AT) discussions (DOT) microsoft.com> wrote