Solved

MDX CALCULATED LEAF MEMEBER COUNT in MS Analysis Serveice

Posted on 2004-09-09
3
413 Views
Last Modified: 2013-11-16
I have put together a cube to analyse sales to certain hotels in MS Analysis Services. 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
3 Comments
 
LVL 17

Accepted Solution

by:
BillAn1 earned 500 total points
ID: 12014984
Try something like this (the calulcated member is called HotelCount)

iif(isleaf([HOTEL]),1, sum([HOTEL].children,[Measures].[HotelCount] ))
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now