MDX : Help With Calculated Members & Named Sets

hi,


i working on bi solution , me , mdx don't match
take example 2 star-schemas "processes" , "licenses", following design:

m1 » processes

dimensions
 - process, time, person, processstate

granularity
 process ( 1 row, 1 process tha person delivered in company @ point in time in state "waiting decision" or "done" example )

measures
 number of process

 cost of process

m2 » licenses

dimensions
 - process, time, license

granularity
  license ( 1 row, 1 license company created project @ point in time )

measures
 number of licenses

i have calculate 2 complex measures, , boxing mdx achived ...

measure 1
» ratio of processes licenses ( should easy as  number of process / number of licenses, need slice number of processes because want count process in state "done" ... whow can ? )

measure 2
» ratio of process licences ( again want slice number of process because want exclude top 5% , bottom 5% in  terms of cost of process measure ... whow can ? )



ps: yes have searched in google, mdx books, etc ...


thanks in advance anyone
lucky_jl

 

hi lucky_jl,

 

this might work if have distinct count aggregation.

 

first one:

 

code snippet

( [measures].[number of process], [processstate].[processstate].[processstate].[done] )

/

( [measures].[number of licenses] )

 

 

second 1 (only slice part, processes):

 

code snippet

except

(

process.process.process.members,

union

(

 toppercent( process.process.process.members, 5, [measures].[cost of process] ),

 bottompercent( process.process.process.members, 5, [measures].[cost of process] )

)

)

 

 

 

tomislav piasevoli

business intelligence specialist

http://www.softpro.hr

SQL Server  >  SQL Server Analysis Services