Measure for Number of Records 1

[MissouriTiger]

Hi Guys,

I'm having difficulty create a measure for 'Number of Orders'. Let me explain...

In our DB we have an ORDERS table, and a LINEITEM TABLE. One order can have many lineitems.

In our OLAP cube we want to have a measure for 'Number of Orders'. I've tried for 2 days to do this with a Calculated Member, but it never works right. MDX is driving me insane.

I might be able to build a column into the view that stores a 1 only for the first lineitem per order number and otherwise stores a 0, but I have no idea how to do that in a Create View SQL Query.

Any suggestions would be gratefully appreciated.

-Greg

________________________________________________
Constructed from 100% recycled electrons.

 

[MDXer]

Build a physical measure that utlizes the Distinct Count Aggregate on the Order Number.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[MissouriTiger]

I'm not exactly sure what you mean by 'physical measure'. Do you mean a column in a view? Or do you mean in the cube?

________________________________________________
Constructed from 100% recycled electrons.

 

[MDXer]

in the cube. bring in the id column and specify an aggregate type of distinct count.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck