How to add calculated Facts in Measure Group

hi,

I am new to SSAS. i am working up on cube. I want to add calculated fact in the cube.

like... Suppose we have fact table in which we have fields like (ID, Status)

and status can have 3 values (1,2 ,3) . My requirement is Count of rows having Status 1 + Count of rows having status 2.

How to accomplish this task.

Additionally if dimension is required suppose i have Status(statusID) dimension also.

Please provide me urgent solution!

Thanks in advance.

Mandip

Hi Mandip,

before I start I beg for pardon, because I am working on a German version of SSAS.

You can put two new named calculations in Your datasource view.

First.

CASE WHEN Status = 1 THEN 1 ELSE NULL END

(suggested columnname Stat1)

Second:

CASE WHEN Status = 2 THEN 1 ELSE NULL END

(suggested columnsname Stat2)

Now You can use these two new columns as new measures (count or sum).

cheers

B.

|||

Thanks very much!

I know if the status entries are going to be static this would work.

but in my case at anytime in future we can have new status suppose (4)...

then can there any other method by which i need not to add new columns in fact table... and still i can be able to take calculated fact? Means i don't want to change design structure of cube.

Mandip

|||In the situation you describe, status should be implemented as a dimension. As a rule of thumb any time you want to report amounts "by" something, it is a good indicator that you should implement it as a dimension.