ssas - MDX Calculated Member SubCube -

i relatively new depth of mdx, here dilemma. goal implement calculated member using .net stored procedure. calculation (xirr) based on set of cash flow dates , cash flow amounts. ideally calculation in cube available measure excel/browser users.

so start simple trying implement own count calculated member/measure (not using .net) count # of members in given dimensions based on current context. lets have dimensions customer customer id key. , let's there total of 100 customers in database. count(customer.customerid.allmembers) 100. when start using browser , filter on customer.customerid.&1, customer.customerid.&2 (customer id 1 , 2) expect count calculated member return 2 returns total 100 count. have tried using exists. sure there fundamentally not understanding yet.

hopefully makes sense, hugely appreciate has understanding of ssas/mdx , calculations. in advance.

marty

you may have issues here, did when tried similar thing.

your calculated member not honouring client sub-select, normal. in theory create dynamic set, , use in calculated member force dimension count evaluated in context of subcube filters have created. mosha has article here: http://sqlblog.com/blogs/mosha/archive/2007/08/25/mdx-in-katmai-dynamic-named-sets.aspx

so you'd end like:

create dynamic set currentcube.customers existing(customer.customerid.children);  create member currentcube.measures.customercount customers.count 

now real problem you'll have bug in ssas https://connect.microsoft.com/sqlserver/feedback/details/484865/calcuated-member-with-a-reference-to-dynamic-named-set-kills-the-cubes-performance code above, work fine locally, kill production cube. exciting learning experience me.

see if can of workarounds work, couldn't.

i able wanted, had create query-scoped dynamic sets part of mdx query, wasn't able create cube object:

with dynamic set customers existing(customer.customerid.children);  member measures.customercount customers.count  select measures.customercount  on columns [cube] customer.customerid.&[1] 

let know how on.