dbaspot
Tags Register FAQ Calendar Search Today's Posts Mark Forums Read

MDX Order issue with BDESC - sqlserver-datawarehouse

This is a discussion on MDX Order issue with BDESC - sqlserver-datawarehouse ; I am using the following MDX query on Adventure Works DW sample database. The issue is that SSAS is returning the result that is not fully sorted in descending order even if I am enforcing break hierarchy with BDESC. Please ...


Home > Database Forum > Data Warehousing > sqlserver-datawarehouse > MDX Order issue with BDESC

Reply

 

LinkBack Thread Tools Display Modes
  #1  
Old 09-30-2008, 12:50 AM
Database Bot
  
Join Date: Sep 2009
Posts: 1,236,254
Database Administrator is on a distinguished road
Default MDX Order issue with BDESC
I am using the following MDX query on Adventure Works DW sample
database.

The issue is that SSAS is returning the result that is not fully
sorted in descending order even if I am enforcing break hierarchy with
BDESC.

Please let me know if I am missing something or the MDX has some
issues?

SELECT NON EMPTY {Order(TopCount({Crossjoin({[Measures].[Discount
Amount]},{[Date].[Calendar].[Calendar Year].&[2004].Children})},20,
[Measures].[Discount Amount]),[Measures].[Discount Amount],BDESC)} ON
COLUMNS,
NON EMPTY {Order(TopCount({[Geography].[Geography].[Country].&[United
States].Children},20,[Measures].[Discount Amount]),[Measures].
[Discount Amount],BDESC)} ON ROWS
FROM "Adventure Works DW"

The Query result is as shown below:

Discount Amount
H1 CY 2004
California $5,460.13
Washington $3,168.88
Utah $2,541.77
Tennessee $45.20
Oregon $0.00
Texas $1,811.34
Arizona $2,051.87
Nevada $808.34
Florida $314.27
Colorado $3,407.77
New Mexico $82.38
Michigan $617.76
New Hampshire $752.55
Ohio $322.59
New York $455.17
Missouri $1,091.53
Wyoming $2,409.90
North Carolina $90.40
Georgia $587.43
South Dakota $0.00

Any help on this is highly appreciated.

Regards,
Rupesh
Reply With Quote
  #2  
Old 10-21-2008, 07:24 PM
Database Bot
  
Join Date: Sep 2009
Posts: 1,236,254
Database Administrator is on a distinguished road
Default Re: MDX Order issue with BDESC
Your query dose not mention which Date member is used for Order function.
So, the default member(All Periods) will be used.
I hope my examples could be helpful.

Ohjoo

ex1)
SELECT NON EMPTY
{Order(TopCount({Crossjoin({[Measures].[Discount
Amount]},{[Date].[Calendar].[Calendar Year].&[2004].Children})},20,
[Measures].[Discount Amount]),[Measures].[Discount Amount],BDESC),
([Discount Amount],[Date].[Calendar].CurrentMember)} ON
COLUMNS,
NON EMPTY
Order(TopCount({[Geography].[Geography].[Country].&[United
States].Children},20,[Measures].[Discount Amount])
,[Measures].[Discount Amount],BDESC) ON ROWS
FROM [Adventure Works]

ex2)
SELECT NON EMPTY
{Order(TopCount({Crossjoin({[Measures].[Discount
Amount]},{[Date].[Calendar].[Calendar Year].&[2004].Children})},20,
[Measures].[Discount Amount]),[Measures].[Discount Amount],BDESC),
([Discount Amount],[Date].[Calendar].CurrentMember)} ON
COLUMNS,
NON EMPTY
Order(TopCount({[Geography].[Geography].[Country].&[United
States].Children},20,[Measures].[Discount Amount])
,([Discount Amount],[Date].[Calendar].CurrentMember),BDESC) ON ROWS
FROM [Adventure Works]

ex3)
SELECT NON EMPTY
{Order(TopCount({Crossjoin({[Measures].[Discount
Amount]},{[Date].[Calendar].[Calendar Year].&[2004].Children})},20,
[Measures].[Discount Amount]),[Measures].[Discount Amount],BDESC),
([Discount Amount],[Date].[Calendar].CurrentMember)} ON
COLUMNS,
NON EMPTY
Order(TopCount({[Geography].[Geography].[Country].&[United
States].Children},20, [Measures].[Discount Amount])
,([Measures].[Discount Amount], [H1 CY 2004]),BDESC) ON ROWS
FROM [Adventure Works]

ex4)
SELECT NON EMPTY
{Order(TopCount({Crossjoin({[Measures].[Discount
Amount]},{[Date].[Calendar].[Calendar Year].&[2004].Children})},20,
[Measures].[Discount Amount]),[Measures].[Discount Amount],BDESC),
([Discount Amount],[Date].[Calendar].CurrentMember)} ON
COLUMNS,
NON EMPTY
Order(TopCount({[Geography].[Geography].[Country].&[United
States].Children},20,([Measures].[Discount Amount], [H1 CY 2004]))
,([Measures].[Discount Amount], [H1 CY 2004]),BDESC) ON ROWS
FROM [Adventure Works]




wrote in message
news:a0f2762f-b212-4f58-91a8-8d663cda2db8@z72g2000hsb.googlegroups.com...
>I am using the following MDX query on Adventure Works DW sample
> database.
>
> The issue is that SSAS is returning the result that is not fully
> sorted in descending order even if I am enforcing break hierarchy with
> BDESC.
>
> Please let me know if I am missing something or the MDX has some
> issues?
>
> SELECT NON EMPTY {Order(TopCount({Crossjoin({[Measures].[Discount
> Amount]},{[Date].[Calendar].[Calendar Year].&[2004].Children})},20,
> [Measures].[Discount Amount]),[Measures].[Discount Amount],BDESC)} ON
> COLUMNS,
> NON EMPTY {Order(TopCount({[Geography].[Geography].[Country].&[United
> States].Children},20,[Measures].[Discount Amount]),[Measures].
> [Discount Amount],BDESC)} ON ROWS
> FROM "Adventure Works DW"
>
> The Query result is as shown below:
>
> Discount Amount
> H1 CY 2004
> California $5,460.13
> Washington $3,168.88
> Utah $2,541.77
> Tennessee $45.20
> Oregon $0.00
> Texas $1,811.34
> Arizona $2,051.87
> Nevada $808.34
> Florida $314.27
> Colorado $3,407.77
> New Mexico $82.38
> Michigan $617.76
> New Hampshire $752.55
> Ohio $322.59
> New York $455.17
> Missouri $1,091.53
> Wyoming $2,409.90
> North Carolina $90.40
> Georgia $587.43
> South Dakota $0.00
>
> Any help on this is highly appreciated.
>
> Regards,
> Rupesh


Reply With Quote
  #3  
Old 10-30-2008, 07:14 AM
Database Bot
  
Join Date: Sep 2009
Posts: 1,236,254
Database Administrator is on a distinguished road
Default Re: MDX Order issue with BDESC
On Oct 22, 4:24*am, "Ohjoo Kwon" wrote:
> Your query dose not mention which Date member is used for Order function.
> So, the default member(All Periods) will be used.
> I hope my examples could be helpful.
>
> Ohjoo
>
> ex1)
> SELECT NON EMPTY
> {Order(TopCount({Crossjoin({[Measures].[Discount
> Amount]},{[Date].[Calendar].[Calendar Year].&[2004].Children})},20,
> [Measures].[Discount Amount]),[Measures].[Discount Amount],BDESC),
> ([Discount Amount],[Date].[Calendar].CurrentMember)} ON
> COLUMNS,
> NON EMPTY
> Order(TopCount({[Geography].[Geography].[Country].&[United
> States].Children},20,[Measures].[Discount Amount])
> ,[Measures].[Discount Amount],BDESC) ON ROWS
> FROM [Adventure Works]
>
> ex2)
> SELECT NON EMPTY
> {Order(TopCount({Crossjoin({[Measures].[Discount
> Amount]},{[Date].[Calendar].[Calendar Year].&[2004].Children})},20,
> [Measures].[Discount Amount]),[Measures].[Discount Amount],BDESC),
> ([Discount Amount],[Date].[Calendar].CurrentMember)} ON
> COLUMNS,
> NON EMPTY
> Order(TopCount({[Geography].[Geography].[Country].&[United
> States].Children},20,[Measures].[Discount Amount])
> ,([Discount Amount],[Date].[Calendar].CurrentMember),BDESC) ON ROWS
> FROM [Adventure Works]
>
> ex3)
> SELECT NON EMPTY
> {Order(TopCount({Crossjoin({[Measures].[Discount
> Amount]},{[Date].[Calendar].[Calendar Year].&[2004].Children})},20,
> [Measures].[Discount Amount]),[Measures].[Discount Amount],BDESC),
> ([Discount Amount],[Date].[Calendar].CurrentMember)} ON
> COLUMNS,
> NON EMPTY
> Order(TopCount({[Geography].[Geography].[Country].&[United
> States].Children},20, [Measures].[Discount Amount])
> ,([Measures].[Discount Amount], [H1 CY 2004]),BDESC) ON ROWS
> FROM [Adventure Works]
>
> ex4)
> SELECT NON EMPTY
> {Order(TopCount({Crossjoin({[Measures].[Discount
> Amount]},{[Date].[Calendar].[Calendar Year].&[2004].Children})},20,
> [Measures].[Discount Amount]),[Measures].[Discount Amount],BDESC),
> ([Discount Amount],[Date].[Calendar].CurrentMember)} ON
> COLUMNS,
> NON EMPTY
> Order(TopCount({[Geography].[Geography].[Country].&[United
> States].Children},20,([Measures].[Discount Amount], [H1 CY 2004]))
> ,([Measures].[Discount Amount], [H1 CY 2004]),BDESC) ON ROWS
> FROM [Adventure Works]
>
> wrote in message
>
> news:a0f2762f-b212-4f58-91a8-8d663cda2db8@z72g2000hsb.googlegroups.com...
>
>
>
> >I am using the following MDX query on Adventure Works DW sample
> > database.
>
> > The issue is that SSAS is returning the result that is not fully
> > sorted in descending order even if I am enforcing break hierarchy with
> > BDESC.
>
> > Please let me know if I am missing something or the MDX has some
> > issues?
>
> > SELECT NON EMPTY {Order(TopCount({Crossjoin({[Measures].[Discount
> > Amount]},{[Date].[Calendar].[Calendar Year].&[2004].Children})},20,
> > [Measures].[Discount Amount]),[Measures].[Discount Amount],BDESC)} ON
> > COLUMNS,
> > NON EMPTY {Order(TopCount({[Geography].[Geography].[Country].&[United
> > States].Children},20,[Measures].[Discount Amount]),[Measures].
> > [Discount Amount],BDESC)} ON ROWS
> > FROM "Adventure Works DW"
>
> > The Query result is as shown below:
>
> > * * * * * * * * * * * *Discount Amount
> > * * * * * * * * * * * * *H1 CY 2004
> > California * * * * * * * * * * $5,460.13
> > Washington * * * * * * *$3,168.88
> > Utah * * * * * * * * * $2,541.77
> > Tennessee * * * * * * * * * * *$45.20
> > Oregon * * * * * * * * $0.00
> > Texas * * * * * * * * *$1,811.34
> > Arizona * * * * * * * *$2,051.87
> > Nevada * * * * * * * * $808.34
> > Florida * * * * * * * *$314.27
> > Colorado * * * * * * * * * * * $3,407.77
> > New Mexico * * * * * * $82.38
> > Michigan * * * * * * * * * * * $617.76
> > New Hampshire * * * *$752.55
> > Ohio * * * * * * * * * $322.59
> > New York * * * * * * * * * * * $455.17
> > Missouri * * * * * * * * * * * $1,091.53
> > Wyoming * * * * * * * *$2,409.90
> > North Carolina *$90.40
> > Georgia * * * * * * * * $587.43
> > South Dakota * *$0.00
>
> > Any help on this is highly appreciated.
>
> > Regards,
> > Rupesh- Hide quoted text -
>
> - Show quoted text -

Could you please tell me in the below MDX how can I do modification to
make the data output sorted on the basis of total sum of all the year
value[CY 2001 +CY 2004 + CY 2002+ CY 2003 ].

SELECT NON EMPTY {Order(TopCount({Crossjoin({[Measures].[Discount
Amount]},{[Date].[Calendar].[All Periods].Children})},20,[Measures].
[Discount Amount]),[Measures].[Discount Amount],ASC)} ON COLUMNS,
NON EMPTY {Order(TopCount({[Geography].[Geography].[Country].&[United
States].Children},20,[Measures].[Discount Amount]),[Measures].
[Discount Amount],ASC)} ON ROWS
FROM "Adventure Works DW"

the current output is shown below:

Discount Amount Discount Amount Discount Amount
Discount Amount
CY 2001 CY 2004 CY
2002 CY 2003
South Dakota $0.00 $0.00
$0.00 $2,877.79
Georgia $1.32 $587.43
$1,822.65 $624.25
North Carolina $0.00 $90.40
$232.30 $2,731.57
Wyoming $0.00 $2,409.90
$488.59 $633.47
Missouri $0.00 $1,091.53
$492.69 $3,447.49

I want a output which is sorted on the basis of total Discount Amount
i.e.CY 2001 +CY 2004 + CY 2002+ CY 2003

Thanking you in anticipation.

Regards,
Rupesh
Reply With Quote
  #4  
Old 10-30-2008, 11:04 AM
Database Bot
  
Join Date: Sep 2009
Posts: 1,236,254
Database Administrator is on a distinguished road
Default Re: MDX Order issue with BDESC
I did not check my samples work against Adventure Works DW.



WITH MEMBER Measures.[Total Discount Amt] AS Aggregate({[CY 2001], [CY
2004], [CY 2002], [CY2003]}, [Discount Amount])

SELECT

NON EMPTY {Order(

TopCount({Crossjoin({[Measures].[Discount
Amount]},{[Date].[Calendar].[All Periods].Children})},

20,

[Measures].[Discount Amount]

),

[Measures].[Discount Amount],

ASC)} ON COLUMNS,

NON EMPTY {Order(

TopCount({[Geography].[Geography].[Country].&[United
States].Children},

20,

[Measures].[Discount Amount]

),

[Measures].[Total Discount Amount],

BASC)} ON ROWS

FROM "Adventure Works DW"



Of course, you can replace {[CY 2001], [CY 2004], [CY 2002], [CY2003]} with
other set you want.



If the Aggregated result is the value of All Periods,



SELECT

NON EMPTY {Order(

TopCount({Crossjoin({[Measures].[Discount
Amount]},{[Date].[Calendar].[All Periods].Children})},

20,

[Measures].[Discount Amount]

),

[Measures].[Discount Amount],

ASC)} ON COLUMNS,

NON EMPTY {Order(

TopCount({[Geography].[Geography].[Country].&[United
States].Children},

20,

[Measures].[Discount Amount]

),

([Measures].[Discount Amount], [Date].[Calendar].[All
Periods]),

BASC)} ON ROWS

FROM "Adventure Works DW"





Ohjoo



wrote in message
news:feac3438-9b1e-4d61-ae84-0a5769b8f43e@s1g2000prg.googlegroups.com...
On Oct 22, 4:24 am, "Ohjoo Kwon" wrote:
> Your query dose not mention which Date member is used for Order function.
> So, the default member(All Periods) will be used.
> I hope my examples could be helpful.
>
> Ohjoo
>
> ex1)
> SELECT NON EMPTY
> {Order(TopCount({Crossjoin({[Measures].[Discount
> Amount]},{[Date].[Calendar].[Calendar Year].&[2004].Children})},20,
> [Measures].[Discount Amount]),[Measures].[Discount Amount],BDESC),
> ([Discount Amount],[Date].[Calendar].CurrentMember)} ON
> COLUMNS,
> NON EMPTY
> Order(TopCount({[Geography].[Geography].[Country].&[United
> States].Children},20,[Measures].[Discount Amount])
> ,[Measures].[Discount Amount],BDESC) ON ROWS
> FROM [Adventure Works]
>
> ex2)
> SELECT NON EMPTY
> {Order(TopCount({Crossjoin({[Measures].[Discount
> Amount]},{[Date].[Calendar].[Calendar Year].&[2004].Children})},20,
> [Measures].[Discount Amount]),[Measures].[Discount Amount],BDESC),
> ([Discount Amount],[Date].[Calendar].CurrentMember)} ON
> COLUMNS,
> NON EMPTY
> Order(TopCount({[Geography].[Geography].[Country].&[United
> States].Children},20,[Measures].[Discount Amount])
> ,([Discount Amount],[Date].[Calendar].CurrentMember),BDESC) ON ROWS
> FROM [Adventure Works]
>
> ex3)
> SELECT NON EMPTY
> {Order(TopCount({Crossjoin({[Measures].[Discount
> Amount]},{[Date].[Calendar].[Calendar Year].&[2004].Children})},20,
> [Measures].[Discount Amount]),[Measures].[Discount Amount],BDESC),
> ([Discount Amount],[Date].[Calendar].CurrentMember)} ON
> COLUMNS,
> NON EMPTY
> Order(TopCount({[Geography].[Geography].[Country].&[United
> States].Children},20, [Measures].[Discount Amount])
> ,([Measures].[Discount Amount], [H1 CY 2004]),BDESC) ON ROWS
> FROM [Adventure Works]
>
> ex4)
> SELECT NON EMPTY
> {Order(TopCount({Crossjoin({[Measures].[Discount
> Amount]},{[Date].[Calendar].[Calendar Year].&[2004].Children})},20,
> [Measures].[Discount Amount]),[Measures].[Discount Amount],BDESC),
> ([Discount Amount],[Date].[Calendar].CurrentMember)} ON
> COLUMNS,
> NON EMPTY
> Order(TopCount({[Geography].[Geography].[Country].&[United
> States].Children},20,([Measures].[Discount Amount], [H1 CY 2004]))
> ,([Measures].[Discount Amount], [H1 CY 2004]),BDESC) ON ROWS
> FROM [Adventure Works]
>
> wrote in message
>
> news:a0f2762f-b212-4f58-91a8-8d663cda2db8@z72g2000hsb.googlegroups.com...
>
>
>
> >I am using the following MDX query on Adventure Works DW sample
> > database.
>
> > The issue is that SSAS is returning the result that is not fully
> > sorted in descending order even if I am enforcing break hierarchy with
> > BDESC.
>
> > Please let me know if I am missing something or the MDX has some
> > issues?
>
> > SELECT NON EMPTY {Order(TopCount({Crossjoin({[Measures].[Discount
> > Amount]},{[Date].[Calendar].[Calendar Year].&[2004].Children})},20,
> > [Measures].[Discount Amount]),[Measures].[Discount Amount],BDESC)} ON
> > COLUMNS,
> > NON EMPTY {Order(TopCount({[Geography].[Geography].[Country].&[United
> > States].Children},20,[Measures].[Discount Amount]),[Measures].
> > [Discount Amount],BDESC)} ON ROWS
> > FROM "Adventure Works DW"
>
> > The Query result is as shown below:
>
> > Discount Amount
> > H1 CY 2004
> > California $5,460.13
> > Washington $3,168.88
> > Utah $2,541.77
> > Tennessee $45.20
> > Oregon $0.00
> > Texas $1,811.34
> > Arizona $2,051.87
> > Nevada $808.34
> > Florida $314.27
> > Colorado $3,407.77
> > New Mexico $82.38
> > Michigan $617.76
> > New Hampshire $752.55
> > Ohio $322.59
> > New York $455.17
> > Missouri $1,091.53
> > Wyoming $2,409.90
> > North Carolina $90.40
> > Georgia $587.43
> > South Dakota $0.00
>
> > Any help on this is highly appreciated.
>
> > Regards,
> > Rupesh- Hide quoted text -
>
> - Show quoted text -

Could you please tell me in the below MDX how can I do modification to
make the data output sorted on the basis of total sum of all the year
value[CY 2001 +CY 2004 + CY 2002+ CY 2003 ].

SELECT NON EMPTY {Order(TopCount({Crossjoin({[Measures].[Discount
Amount]},{[Date].[Calendar].[All Periods].Children})},20,[Measures].
[Discount Amount]),[Measures].[Discount Amount],ASC)} ON COLUMNS,
NON EMPTY {Order(TopCount({[Geography].[Geography].[Country].&[United
States].Children},20,[Measures].[Discount Amount]),[Measures].
[Discount Amount],ASC)} ON ROWS
FROM "Adventure Works DW"

the current output is shown below:

Discount Amount Discount Amount Discount Amount
Discount Amount
CY 2001 CY 2004 CY
2002 CY 2003
South Dakota $0.00 $0.00
$0.00 $2,877.79
Georgia $1.32 $587.43
$1,822.65 $624.25
North Carolina $0.00 $90.40
$232.30 $2,731.57
Wyoming $0.00 $2,409.90
$488.59 $633.47
Missouri $0.00 $1,091.53
$492.69 $3,447.49

I want a output which is sorted on the basis of total Discount Amount
i.e.CY 2001 +CY 2004 + CY 2002+ CY 2003

Thanking you in anticipation.

Regards,
Rupesh


Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools
Display Modes
Linear Mode Linear Mode



All times are GMT -4. The time now is 05:16 AM.