Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1481
  • Last Modified:

MDX LEAF Memeber Count

I have put together a cube to analyse sales to certain hotels in MS Analysis Services (I didn't see a group for it under databases and thought ERP was closest). These hotels roll up into countries and then sales regions. One of the accounts in the cube is sales cycle days. This is the amount of days between 'Contact made date' and 'Contract signed Date'. This is great except that when the hotel dimension is showing countries or sales regions, the account just rolls up all of is decesndants values. What I am wanting to achieve is an average sales cycle account. What I have though of doing is keeping the 'sales cycle days' account, but adding a hotel count account and an AVG_SALES_CYLCE account. The AVG_SALES_CYCLE would be a calculated member ('Sales cycle days/hotel count').
  The question is ... how do I create the 'hotel count', curently I have
IiF([HOTEL].currentmember.children.count=0, 1, [hotel].currentmember.children.count) . This works ok but is obviously just counting the immediate children, What i need is a count of all the leaf level descendants i.e. hotels. I have tried the DESCENDANTS funciton with LEAVES parameter, but can't seem to get that to work - IiF([HOTEL].currentmember.children.count=0, 1, decendants([hotel].currentmember,LEAVES).count) or IiF([HOTEL].currentmember.children.count=0, 1, count(decendants([hotel].currentmember,LEAVES)))
  This needs to be a dimesion formula in the account dimesion.
many thanks
0
GhostRider10
Asked:
GhostRider10
  • 2
1 Solution
 
rama_krishna580Commented:
Hi,

You may look at here , will get some tips or Info.
http://www.georgehernandez.com/xDatabases/MD/MDXFunctions.htm

R.K
0
 
rama_krishna580Commented:
Hi,

I have provided the full functional list for the Authors question and answer is there in the link
>>> Dimensions.Count. "Returns the number of dimensions in a cube, including the [Measures] dimension. " 
{Dimension|Hierarchy}.Levels.Count. "Returns the number of levels in a dimension or hierarchy, including the [All] level if applicable

points should be given.

R.K
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now