Hello,

I have a dimension called [customers] with the hierarchy: State, City then
Customer Number. What MDX function can I use to return the lowest level
name?

--
Tom

try using the descendants function:
descendants(hierarchy.currentmember,, LEAVES).item(0).Level.Name

"xpan" <xpanWYTNIJTO (AT) poczta (DOT) onet.pl> wrote

xpan wrote:
I think Jéjé meant you to replace "hierarchy" with your dimension
hierarchy
i.e. try

descendants([customers].currentmember,, LEAVES).item(0).Level.Name

-- a --

using the DSO object model you can identify the lowest level of a dimension.
scan the dimensions to find the date dimension, then search the lowest
level, something like:
MyDSO.Dimensions("Date").Levels(MyDSO.Dimensions(" Date").Levels.count -1).UniqueName
(check the syntax but the DSO object model contains everything you need.)

or try this:
([Measures].[budget],ClosingPeriod(descendants([datetime_dim_name].[all
level name],, LEAVES).item(0).Level))
or
([Measures].[budget],ClosingPeriod(descendants([datetime_dim_name].levels(0).item(0),,LEAVES).item(0).Level))"xpan" <xpanWYTNIJTO (AT) poczta (DOT) onet.pl> wrote in messagenews:ee4mbp$iud$1 (AT) atlantis (DOT) news.tpi.pl...>I created calculated member as expression:>([Measures].[budget],ClosingPeriod([datetime_dim_name].[lowest_level_name]))> and it works.> Since I created this calculated member programmically (using DSO) and I> don't know names of the levels I want to replace> [datetime_dim_name].[lowest_level_name] with function (or expression)> without using the lowest level name.>> I used the sugestion that Jéjé and addup gave me:>([Measures].[budget],ClosingPeriod(descendants([datetime_dim_name].currentme> mber,, LEAVES).item(0).Level))> but I got #ERR error in the cell.>> Help, please> --> Tom>>> Uzytkownik "addup" <adi.jog (AT) gmail (DOT) com> napisal w wiadomosci> news:1157998290.825514.141410 (AT) i3g2000cwc (DOT) googlegroups.com...> xpan wrote:>> After using descendants function: "descendants(hierarchy.currentmember,,>> LEAVES).item(0).Level.Name" in Value expression field in Calculate member>> builder I received:>> Formula error - syntax error - token in not valid:>> "descendants(^hierarchy^.currentmember,, LEAVES).item(0).Level.Name">>>> I want to get the lowest level name because I want to replace>> [datetime].[lowest level name] with function or expression that returns>> lowest level name in expression:>> ([Measures].[budget],ClosingPeriod([datetime].[lowest level name]))>>>> Help, please.>> -->> Tom>>>> U¿ytkownik "Jéjé" <willgart_A_ (AT) hotmail_A_ (DOT) com> napisa³ w wiadomo¶ci>> news:%23bupZ5Z1GHA.1300 (AT) TK2MSFTNGP05 (DOT) phx.gbl...>> > try using the descendants function:>> > descendants(hierarchy.currentmember,, LEAVES).item(0).Level.Name>> >>> > "xpan" <xpanWYTNIJTO (AT) poczta (DOT) onet.pl> wrote in message>> > news:1m27wce7vdv0g.drb2edl7hk1u.dlg (AT) 40tude (DOT) net...>> > > Hello,>> > >>> > > I have a dimension called [customers] with the hierarchy: State, City>> then>> > > Customer Number. What MDX function can I use to return the lowest> level>> > > name?>> > >>> > > -->> > > Tom>> I think Jéjé meant you to replace "hierarchy" with your dimension> hierarchy> i.e. try>> descendants([customers].currentmember,, LEAVES).item(0).Level.Name>> -- a -->

I tried using
([Measures].[budget],ClosingPeriod(descendants([datetime_dim_name].currentmember,,LEAVES).item(0).Level))
and
([Measures].[budget],ClosingPeriod(descendants([datetime_dim_name].[All
level name],,LEAVES).item(0).Level))
but I got #ERR in the cell.

When I used
([Measures].[budget],ClosingPeriod(descendants([datetime_dim_name].levels(0).item(0),,LEAVES).item(0).Level))
I got "Formula error - cannot convert level to Tuple in the
<dimensions>.Levels(...) function".

I know that using the DSO object model you can identify the lowest level of
a dimension, but I want to obtain the same results using MDX expression

--
Tom

first correction:
([Measures].[budget],ClosingPeriod(descendants([datetime_dim_name].levels(0).MEMBERS.item(0),,LEAVES).item(0).Level) )but also send the date in the closing period function: (second part of thefunction)ClosingPeriod(<the level I want>, datetime_dim_name.currentmember)and remember that if you use AS2005 you have to use the hierarchy unqiuename:<dimension>.<hierarchy>.<level>Like:dat etime_dim_name.[Fiscal Calendar].[Day]ordatetime_dim_name.[Fiscal Calendar].currentmember"xpan" <xpanWYTNIJTO (AT) poczta (DOT) onet.pl> wrote in messagenews:1ihkwawwynx9o.1i7c13cqdyn09.dlg (AT) 40tude (DOT) net...>I tried using>([Measures].[budget],ClosingPeriod(descendants([datetime_dim_name].currentmember,,LEAVES).item(0).Level))> and> ([Measures].[budget],ClosingPeriod(descendants([datetime_dim_name].[All> level name],,LEAVES).item(0).Level))> but I got #ERR in the cell.>> When I used>([Measures].[budget],ClosingPeriod(descendants([datetime_dim_name].levels(0).item(0),,LEAVES).item(0).Level))> I got "Formula error - cannot convert level to Tuple in the> <dimensions>.Levels(...) function".>> I know that using the DSO object model you can identify the lowest levelof> a dimension, but I want to obtain the same results using MDX expression>> --> Tom

After using descendants function: "descendants(hierarchy.currentmember,,
LEAVES).item(0).Level.Name" in Value expression field in Calculate member
builder I received:
Formula error - syntax error - token in not valid:
"descendants(^hierarchy^.currentmember,, LEAVES).item(0).Level.Name"

I want to get the lowest level name because I want to replace
[datetime].[lowest level name] with function or expression that returns
lowest level name in expression:
([Measures].[budget],ClosingPeriod([datetime].[lowest level name]))

Help, please.
--
Tom

U¿ytkownik "Jéjé" <willgart_A_ (AT) hotmail_A_ (DOT) com> napisa³ w wiadomo¶ci
news:%23bupZ5Z1GHA.1300 (AT) TK2MSFTNGP05 (DOT) phx.gbl...

I created calculated member as expression:
([Measures].[budget],ClosingPeriod([datetime_dim_name].[lowest_level_name]))
and it works.
Since I created this calculated member programmically (using DSO) and I
don't know names of the levels I want to replace
[datetime_dim_name].[lowest_level_name] with function (or expression)
without using the lowest level name.

I used the sugestion that Jéjé and addup gave me:
([Measures].[budget],ClosingPeriod(descendants([datetime_dim_name].currentme
mber,, LEAVES).item(0).Level))
but I got #ERR error in the cell.

Help, please
--
Tom


Uzytkownik "addup" <adi.jog (AT) gmail (DOT) com> napisal w wiadomosci
news:1157998290.825514.141410 (AT) i3g2000cwc (DOT) googlegroups.com...
xpan wrote:
I think Jéjé meant you to replace "hierarchy" with your dimension
hierarchy
i.e. try

descendants([customers].currentmember,, LEAVES).item(0).Level.Name

-- a --