dbTalk Databases Forums  

Re: MDX - descendants

Go Back dbTalk Databases Forums Databases microsoft.public.sqlserver.olap Re: MDX - descendants

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap


Discuss Re: MDX - descendants in the microsoft.public.sqlserver.olap forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Tom Chester
 
Posts: n/a

Default Re: MDX - descendants - 02-11-2004 , 10:24 AM





Andrea, is the last month the last member in the month level, the last month
with data, or is it assumed to be the currentmember?

public @ the domain below
www.tomchester.net

"Andrea Worley" <k_rage (AT) hotmail (DOT) com> wrote

Quote:
I am using the DESCENDANTS function to display unit sales by month across
columns, with marital status on the rows. I want to not show the last
month. (In real data, this is to avoid showing data for the current month
that isn't complete yet). Here's the MDX I've worked out on food mart. How
can I make it not show the last month? Thanks for any help.
Quote:
SELECT CROSSJOIN( {[measures].[Unit Sales]},
DESCENDANTS({[Time].[1997]},[Time].[Month], SELF)) on columns,
{[marital status].members}
on rows
from Sales
where [gender].[f]



Reply With Quote
  #2  
Old   
Andrea Worley
 
Posts: n/a

Default Re: MDX - descendants - 02-11-2004 , 11:26 AM





It is the last month with data. So if I were using real data, I would have records with February 2004 values, but I wouldn't want to show them until February was over (ie: March had begun, so always leave off the last month)

Thanks for your help today

----- Tom Chester wrote: ----

Andrea, is the last month the last member in the month level, the last mont
with data, or is it assumed to be the currentmember

public @ the domain belo
www.tomchester.ne

"Andrea Worley" <k_rage (AT) hotmail (DOT) com> wrote in messag
news:4C4E67FA-CDCB-4E9C-B166-ACA61EA2BA1F (AT) microsoft (DOT) com..
Quote:
I am using the DESCENDANTS function to display unit sales by month acros
columns, with marital status on the rows. I want to not show the las
month. (In real data, this is to avoid showing data for the current mont
that isn't complete yet). Here's the MDX I've worked out on food mart. Ho
can I make it not show the last month? Thanks for any help
Quote:
SELECT CROSSJOIN( {[measures].[Unit Sales]}
DESCENDANTS({[Time].[1997]},[Time].[Month], SELF)) on columns
{[marital status].members
on row
from Sale
where [gender].[f




Reply With Quote
  #3  
Old   
Tom Chester
 
Posts: n/a

Default Re: MDX - descendants - 02-11-2004 , 01:28 PM


Something like this...? (not syntax checked)

nonemptycrossjoin(
{ Time.[1997].FirstChild.FirstChild :
Time.[1997].LastChild.LastChild.Lag(1) } ,
{Measures.[Unit Sales]}, 1)

public @ the domain below
www.tomchester.net

"Andrea Worley" <k_Rage (AT) hotmail (DOT) com> wrote

Quote:
It is the last month with data. So if I were using real data, I would
have records with February 2004 values, but I wouldn't want to show them
until February was over (ie: March had begun, so always leave off the last
month).
Quote:
Thanks for your help today.

----- Tom Chester wrote: -----

Andrea, is the last month the last member in the month level, the
last month
with data, or is it assumed to be the currentmember?

public @ the domain below
www.tomchester.net

"Andrea Worley" <k_rage (AT) hotmail (DOT) com> wrote in message
news:4C4E67FA-CDCB-4E9C-B166-ACA61EA2BA1F (AT) microsoft (DOT) com...
I am using the DESCENDANTS function to display unit sales by month
across
columns, with marital status on the rows. I want to not show the
last
month. (In real data, this is to avoid showing data for the current
month
that isn't complete yet). Here's the MDX I've worked out on food
mart. How
can I make it not show the last month? Thanks for any help.
SELECT CROSSJOIN( {[measures].[Unit Sales]},
DESCENDANTS({[Time].[1997]},[Time].[Month], SELF)) on columns,
{[marital status].members}
on rows
from Sales
where [gender].[f]



Reply With Quote
  #4  
Old   
Andrea Worley
 
Posts: n/a

Default Re: MDX - descendants - 02-11-2004 , 02:16 PM


That's just the trick I wanted, thank you

----- Tom Chester wrote: ----

Something like this...? (not syntax checked

nonemptycrossjoin
{ Time.[1997].FirstChild.FirstChild
Time.[1997].LastChild.LastChild.Lag(1) }
{Measures.[Unit Sales]}, 1

public @ the domain belo
www.tomchester.ne

"Andrea Worley" <k_Rage (AT) hotmail (DOT) com> wrote in messag
news:79860773-F041-4604-B1E6-4AB3B48507CF (AT) microsoft (DOT) com..
Quote:
It is the last month with data. So if I were using real data, I woul
have records with February 2004 values, but I wouldn't want to show the
until February was over (ie: March had begun, so always leave off the las
month)
Quote:
Thanks for your help today
----- Tom Chester wrote: ----
Andrea, is the last month the last member in the month level, th
last mont
with data, or is it assumed to be the currentmember
public @ the domain belo
www.tomchester.ne
"Andrea Worley" <k_rage (AT) hotmail (DOT) com> wrote in messag
news:4C4E67FA-CDCB-4E9C-B166-ACA61EA2BA1F (AT) microsoft (DOT) com..
I am using the DESCENDANTS function to display unit sales by mont
acros
columns, with marital status on the rows. I want to not show th
las
month. (In real data, this is to avoid showing data for the curren
mont
that isn't complete yet). Here's the MDX I've worked out on foo
mart. Ho
can I make it not show the last month? Thanks for any help
SELECT CROSSJOIN( {[measures].[Unit Sales]}
DESCENDANTS({[Time].[1997]},[Time].[Month], SELF)) on columns
{[marital status].members
on row
from Sale
where [gender].[f

Reply With Quote
  #5  
Old   
Sanka
 
Posts: n/a

Default Re: MDX - descendants - 02-11-2004 , 04:11 PM


How do we remove the Hardcoding of [1997] from the
Expression. (Incase I want the application to get the
latest year automatically.)

I was assuming [Time].LastChild would denote [Time].[1998]
in the FoodMart 2000 Database but it was never the case.

Can someone explain the behaviour.

Cheers,
Sanka

Quote:
-----Original Message-----
That's just the trick I wanted, thank you.

----- Tom Chester wrote: -----

Something like this...? (not syntax checked)

nonemptycrossjoin(
{ Time.[1997].FirstChild.FirstChild :
Time.[1997].LastChild.LastChild.Lag(1) } ,
{Measures.[Unit Sales]}, 1)

public @ the domain below
www.tomchester.net

"Andrea Worley" <k_Rage (AT) hotmail (DOT) com> wrote in message
news:79860773-F041-4604-B1E6-
4AB3B48507CF (AT) microsoft (DOT) com...
It is the last month with data. So if I were
using real data, I would
have records with February 2004 values, but I
wouldn't want to show them
until February was over (ie: March had begun, so
always leave off the last
month).
Thanks for your help today.
----- Tom Chester wrote: -----
Andrea, is the last month the last member in
the month level, the
last month
with data, or is it assumed to be the
currentmember?
public @ the domain below
www.tomchester.net
"Andrea Worley" <k_rage (AT) hotmail (DOT) com> wrote
in message
news:4C4E67FA-CDCB-4E9C-B166-
ACA61EA2BA1F (AT) microsoft (DOT) com...
I am using the DESCENDANTS function to display
unit sales by month
across
columns, with marital status on the rows. I
want to not show the
last
month. (In real data, this is to avoid
showing data for the current
month
that isn't complete yet). Here's the MDX
I've worked out on food
mart. How
can I make it not show the last month?
Thanks for any help.
SELECT CROSSJOIN( {[measures].[Unit Sales]},
DESCENDANTS({[Time].[1997]},[Time].[Month],
SELF)) on columns,
{[marital status].members}
on rows
from Sales
where [gender].[f]

.

Reply With Quote
  #6  
Old   
Tom Chester
 
Posts: n/a

Default Re: MDX - descendants - 02-11-2004 , 05:28 PM


If there was an ALL TIME it would be a bit simpler...

MEMBER [Time].[ThisYR] AS ' Tail(Year.Members,1).Item(0).Item(0) '

public @ the domain below
www.tomchester.net

"Sanka" <loonysan (AT) mailcity (DOT) com> wrote

Quote:
How do we remove the Hardcoding of [1997] from the
Expression. (Incase I want the application to get the
latest year automatically.)

I was assuming [Time].LastChild would denote [Time].[1998]
in the FoodMart 2000 Database but it was never the case.

Can someone explain the behaviour.

Cheers,
Sanka

-----Original Message-----
That's just the trick I wanted, thank you.

----- Tom Chester wrote: -----

Something like this...? (not syntax checked)

nonemptycrossjoin(
{ Time.[1997].FirstChild.FirstChild :
Time.[1997].LastChild.LastChild.Lag(1) } ,
{Measures.[Unit Sales]}, 1)

public @ the domain below
www.tomchester.net

"Andrea Worley" <k_Rage (AT) hotmail (DOT) com> wrote in message
news:79860773-F041-4604-B1E6-
4AB3B48507CF (AT) microsoft (DOT) com...
It is the last month with data. So if I were
using real data, I would
have records with February 2004 values, but I
wouldn't want to show them
until February was over (ie: March had begun, so
always leave off the last
month).
Thanks for your help today.
----- Tom Chester wrote: -----
Andrea, is the last month the last member in
the month level, the
last month
with data, or is it assumed to be the
currentmember?
public @ the domain below
www.tomchester.net
"Andrea Worley" <k_rage (AT) hotmail (DOT) com> wrote
in message
news:4C4E67FA-CDCB-4E9C-B166-
ACA61EA2BA1F (AT) microsoft (DOT) com...
I am using the DESCENDANTS function to display
unit sales by month
across
columns, with marital status on the rows. I
want to not show the
last
month. (In real data, this is to avoid
showing data for the current
month
that isn't complete yet). Here's the MDX
I've worked out on food
mart. How
can I make it not show the last month?
Thanks for any help.
SELECT CROSSJOIN( {[measures].[Unit Sales]},
DESCENDANTS({[Time].[1997]},[Time].[Month],
SELF)) on columns,
{[marital status].members}
on rows
from Sales
where [gender].[f]

.



Reply With Quote
  #7  
Old   
Tom Chester
 
Posts: n/a

Default Re: MDX - descendants - 02-12-2004 , 11:14 AM


Wait a sec Andrea, sorry that's not right. This should work:

{[1997].Firstchild.Firstchild :
tail( nonemptycrossjoin(
descendants([1997], month), {Measures.[Unit Sales]}, 1 ),
1).item(0).Item(0).lag(1) }

public @ the domain below
www.tomchester.net

"Andrea Worley" <k_rage (AT) hotmail (DOT) com> wrote

Quote:
That's just the trick I wanted, thank you.

----- Tom Chester wrote: -----

Something like this...? (not syntax checked)

nonemptycrossjoin(
{ Time.[1997].FirstChild.FirstChild :
Time.[1997].LastChild.LastChild.Lag(1) } ,
{Measures.[Unit Sales]}, 1)

public @ the domain below
www.tomchester.net

"Andrea Worley" <k_Rage (AT) hotmail (DOT) com> wrote in message
news:79860773-F041-4604-B1E6-4AB3B48507CF (AT) microsoft (DOT) com...
It is the last month with data. So if I were using real data, I
would
have records with February 2004 values, but I wouldn't want to show
them
until February was over (ie: March had begun, so always leave off the
last
month).
Thanks for your help today.
----- Tom Chester wrote: -----
Andrea, is the last month the last member in the month level,
the
last month
with data, or is it assumed to be the currentmember?
public @ the domain below
www.tomchester.net
"Andrea Worley" <k_rage (AT) hotmail (DOT) com> wrote in message
news:4C4E67FA-CDCB-4E9C-B166-ACA61EA2BA1F (AT) microsoft (DOT) com...
I am using the DESCENDANTS function to display unit sales by month
across
columns, with marital status on the rows. I want to not show
the
last
month. (In real data, this is to avoid showing data for the
current
month
that isn't complete yet). Here's the MDX I've worked out on
food
mart. How
can I make it not show the last month? Thanks for any help.
SELECT CROSSJOIN( {[measures].[Unit Sales]},
DESCENDANTS({[Time].[1997]},[Time].[Month], SELF)) on columns,
{[marital status].members}
on rows
from Sales
where [gender].[f]



Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top