COGNOiSe.com - The IBM Cognos Community

I have a cube with this structure:

Time_Dim
Product_Dim
... (other irrelevant dimensions)
Sales_Fact

Now, the data is like this:


Month   Day      Customer   Credit Buys   Normal Buys   Nº Credit Trans
01   2009-01-01   c_1      100      200      2   
01   2009-01-02   c_1      200      300      1
01   2009-01-03   c_2      0      230      0      
01   2009-01-01   c_2      500      500      1
01   2009-01-02   c_2      100      200      2
01   2009-01-03   c_3      0      400      0

I need to create a report with to metrics:

1.- Sales Amount: Sum(Amount) for month, the easy one....
2.- Number of client how made credit buys: if the customer made a buy in the month then I count 1 customer.., so, how many customers made a credit buy form month?

So the report should look like this:


         Month
         01   02   03   ...
Normal Buys      1.830
Credit Buys      900
Number of client                2


I'm trying to create a calculate metric but i don't know how...

arghh, not having access to cognos really handicaps me... for now i can think of the following two solutions (which i cant verify 100%)

Solution 1:
1. Calculate Total (Credit buys for Customer).
2. Create another data item in the query and have the following expression.

Quote
IF ( [Total of credit buys] > 0 )
THEN (1)
ELSE (0)

and have Total as the aggregate function for this data item.

Solution 2:   
1. Have two queries each with the following data items.
Customer
Month
Normal Buys
Credit Buys
No. of customers

2. In Query1 have everything normal (directly pulled from model) except for the No. of customers which should be 0
3. In Query2 all data items should be the same as Query 1 except No. of customers.
3. Add a filter in Query2 as [Credit Buys]>0
4. In [Query2].[No. of customers] have count(distinct Customer) which should give you the total no. of unique customers with credit buys.
5. Union these two queries and have the report pulled out of the final query.

Please let us know if this works for you.