I have created a cube as under:-

Measures--------> Quantity Amount

Articles
Countries

How can I create a MDX Query to show above in Hierarchical way (Indented).


Best Regards,

Luqman

Well, actually that sort of depends on your UI. For example, you can create
your own ADOMD / ADOMD.NET UI that will be able to handle the indentation of
Articles
Countries

when you send a MDX query using the descendants function. The descendants
function will provide the entire hierarchy broken out, and then your UI can
review the cellset members leveldepth property to apply the spacing
identation based on the number of levels deep. For example:

For spc = 0 to cst.Axes(1).Positions(i).Members(k).LevelDepth - 1
spacer = spacer & "   "
Next

where
cst is the ADOMD cellset object,
Axes(1) represents the row (vs. column)
Positions(i) represents the positioning that you are looping thorugh
Members(k) is the list of members (in the above example " Articles" and
"Countries")
LevelDepth is the property denoting the number of levels deep this
member is within the hierarchy
in this case, Articles is 1 and Countries is 2

So with that information, the above for loop will add the additional space
(  in HTML is a space) i.e. indentation to the view within the UI.

--
HTH!
Denny Lee
<dennyglee_at_hotmail_dot_com>

Blog at:: http://spaces.msn.com/members/denster/



"Luqman" <pearlsoft (AT) cyber (DOT) net.pk> wrote

You can also do it in you MDX query with something like the following

MEMBER measures.IndentedName AS 'vba!string
(store.currentmember.level.ordinal * 2," ") + store.currentmember.name'
SELECT
{measures.IndentedName, measures.[Unit Sales],measures.[Profit]} ON
COLUMNS,
nonemptycrossjoin(store.members,{measures.[Unit Sales],measures.
[Profit]},1) ON ROWS
FROM Sales
The only problem with this is that you need to introduce the
nonemptycrossjoin if you need to filter out members with empty measures
(which is highly likely). The normal NON EMPTY clause will not work as
every member will calculate a non empty value for the IndentedName
calculation.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell


In article <u5bpZd49FHA.2936 (AT) tk2msftngp13 (DOT) phx.gbl>,
dennyglee (AT) hotmail (DOT) com says...