About category count measure problem?

[lincon]

I have a fact table include the following fileds:
period store product salesperson forcast actual
030501 0001 0001 1001 4 3
030501 0001 0002 1001 3 0
030515 0002 0001 1002 5 0
.....

Now ,I want know how many stores have saled some a products and total forcast.
as above data ,only one store(0001) have saled product(0001) at all
periods and salespersons,total forcast value is 4+3+5=12.

the report format and result like the following :

period product salesPerson totalforcast storecount
all all all 12 1
all 0001 all 9 1
030501 0001 all 7 1
all 0002 1002 5 0

any one can give me a idea,thanks!

 

[CognosProfessional]

Just looks like you want to total up columns and use them in the report. Not sure what else you are trying to do.

CP

 

[BruceReed]

I would transformer to make a basic cube. Once the number crunching is done, you will have a cube to open up in PowerPlay. This should give you the dimensions & measurments you need.

Let me know if this is what you are looking for.

Bruce

 

[lincon]

thanks all
CognosProfessional,I want to need measure named 'Store Count',it be defined that actual>0 and actual is not null.

BruceReed ,I make a cube base on the fact table,include 'Time' dimention(field 'period' in it),'Store' dimention(filed 'store' in it),'Product' dimention(field 'product' in it) and 'Sales' dimention( field 'salesperson' in it),measure are forcast,actual and store count. the measure 'store count' is a calculate measure,calculate level 'store' of 'Store' dimention category count.but the result 'store count' include the store that actual=0.now i want 'store count' exclude the actual=0 and actual is null. can you help me?

Thanks again!

 

[Firecat1970]

I wanted to do similar things and I end up doing the trick in the fact table before building the cubes.

Cheers!

 

[lincon]

Firecat1970 ,can you tell me how to do it in the fact table,thanks!

 

[Firecat1970]

I added a column for the count. Because in my case, I need some extra logic to determine the count for a record. It is okay in my case since I only do it once and it will solve a lot of the downstream issue if I don't do it.

Cheers!
Firecat

 

[lincon]

thanks Firecat1970.
what is the column value. As what i said above,How to define the column?

 

[Firecat1970]

The column value depends on your business requirement. In my case, the value cannot be derived from the same single row, it has to get the sum of the related row, then find the proportion of the current row, and put the weighted figure in the column. So it is up to your ETL logics. My point is, I am doing all this processing during the ETL stage rather than implementing it using Cognos.