Solved

MDX LEAF Memeber Count

Posted on 2004-09-08
3
1,462 Views
Last Modified: 2013-11-23
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
Comment
Question by:GhostRider10
  • 2
3 Comments
 
LVL 23

Accepted Solution

by:
rama_krishna580 earned 500 total points
ID: 12141186
Hi,

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

R.K
0
 
LVL 23

Expert Comment

by:rama_krishna580
ID: 12330660
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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
microsoft dynamic NAV ERP 3 121
Prevent Windows 10 6 77
Steps to architect a software solution 4 74
Zoho  vs Salesforce 2 182
Note: You must have administrative privileges in order to configure lead or case queues. Salesforce.com (http://www.Salesforce.com) is a cloud-based customer relationship management (CRM) system. It is widely used around the world by sales and ma…
Read about achieving the basic levels of HRIS security in the workplace.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question