mdx help

[crouchm]

Hi! I'm very new to mdx and am trying to learn this w/out microsoft analysis services. I'm currently using a product called Pentaho - it uses Mondrian and they expect you to have a prior knowledge of mdx. Anyway, here's my situation.

I have five dimensions, two measures, and a calculated member. The problem I have is that the calculated member is a percentage of net sales. (Net sales is a measure.) I want a user to be able to drill through any of the dimensions and get the correct net sales calculation. I currently have the following:

WITH
MEMBER [Measures].[Net Sales %]
AS IIF(ISEMPTY(([Measures].[Net Sales], [Business Type].Parent, [Category].Parent)), 1, [Measures].[Net Sales] / ([Measures].[Net Sales], [Business Type].Parent, [Category].Parent)),
FORMAT_STRING = "0.0%"

This gives me the correct percentage of net sales but only when the Business Type and Category dimensions are both drilled thru. I want to show if either one is drilled thru w/out having to do both. I've seen the colon used to define a range of members - is there any way to define a range of dimensions? Any help you can provide will be most appreciated. Thanks!

[Island1]

If I understand the scenario (at least the desired result), you might find that specifiying each dimension for each drillthrough possibility (use Dimension.CurrentMember) in your calculated member will get you where you want to be.

Give it a shot and let us know if I'm missing the boat on the requirement.

Thanks.

Bill

[crouchm]

Hi! Thank you for your reply Island1. I tried your suggestion a few different ways but I couldn't get it to work the way I needed. When you specify more than one dimension in the calculation it meant you had to select each dimension to show the percentage. If I specified BusinessType.CurrentMember, Category.CurrentMember it still made you drill thru each of these dimensions to see the calculation. I want to drill thru either one - say if I select just BusinessType - would like to see correct calculation for that OR if I select just Category.CurrentMember would like to see the correct calculation for that. This also leads to another problem. When you specify currentMember, it divides by the net sales at that current member. I need to be able to divide by the total of net sales at the parent level of all the data. i.e.

Net Sales for Warehouses = $5,000
Net Sales for Stores =$15,000
Total Net Sales = 20,000 (at top Parent level)

The calculation I need is:
25% = Net Sales for Warehouses (5,000) / Total Net Sales (20,000)

and

75% = Net Sales for Stores (15,000) / Total Net Sales (20,000)

Is there a way to make a static variable in mdx? I need Total Net Sales to not change - want it to always be $20,000 at every drill thru.

Sorry for the long post - hope I wasn't too confusing on my problems. Any suggestions / help will be most appreciated.