Sorting the members of a union of two sets

Question

Sorting the members of a union of two sets

Del Piero

SSCrazy

Points: 2265
More actions
August 7, 2005 at 2:44 am

#110168

Hi all,
      Suppose I have a shared dimension CUSTOMERS like this (the dimension stores the customer names and whether they are CREDITED or NON-CREDITED):
      Level 1: credited or non-credited
      Level 2: customer_name
      Sometimes I would want to retrieve only credited (or sometimes only non-credited) customers, but I also want to issue an MDX to query all customers, credited and non-credited, and I want to sort by their names (which is the default sorting in the customer_name level)
      Can someone tell me how I can use the ORDER function to do so? I issued something similar as below but I didn't get my expected sort orders:
      select { order(  { [CUSTOMERS].[credited].children, [CUSTOMERS].[non-credited].children }, [CUSTOMERS], asc )     } on columns,
     {[measures].[cost]} on rows from [SALES]
      I didn't get my expected sort order with this, and if I create another dimension called [SORTED_CUSTOMERS] containing only the customer names in order, and put the [SORTED_CUSTOMERS] in the 2nd parameter of the ORDER function, the result is the same.
       I want to get all customers sorted altogether (Alan, April, May, Michael, Walter, Zoe) , and not credited customers sorted union with non-credited customers sorted (e.g. April, May, Zoe, Alan, Michael, Walter) . How can I achieve my sorting requirement?
       Thanks a lot.
delpiero

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Hey Del,

You probably want to use the BASC or BDESC in the order function. These are the 'breaking' ascending and descending order statements. They break the hierarchy when doing the sort, which basically means that they will sort asc/desc regardless of the parent (e.g. credited or not).

The following may work (using your example names etc)

SELECT {ORDER({[Customers].[Customer_Name].MEMBERS}, BASC)} ON 0,

{[Measures].[Cost]} ON 1 FROM [Sales]

Steve.

Del Piero SSCrazy Points: 2265 More actions · Answer 2

Thanks steve,

(1) I think the BASC can help me, but is that the ORDER function requires 3 parameters? I dunno exactly what I should put as the 2nd parameter in order to sort the output in customer name.

(2) If I have one more level above [credited], like this:

Level 1: Status (active or non-active)

Level 2: Credited (credited or non-credited)

Level 3: Customer_name

When I put [Customers].[Customer_Name].members as the 1st parameter in the ORDER function, is it possible to retrieve only ACTIVE customers? I want customers, no matter credited or non-credited, who are ACTIVE only. I dun want credited or non-credited customers who are non-active.

Thanks a lot,

delpiero

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

Hey Del,

You could try using the Descendants function to pick up only the descendants of the 'Active' member of the dimension. Unless you've got your level members sorted alphabetically (this is easy to set up in the cube), you may need to create a dummy measure to sort them. Below is a simple example using the foodmart Sales cube.

WITH MEMBER [Measures].[Cust_Sort] AS '[Customers].CurrentMember.Name'

select ORDER({Customers.USA.Children}, [Measures].[Cust_Sort], DESC) ON 1,

{[measures].[store sales]} on 0

from sales

where [time].[1997]

Steve.

Del Piero SSCrazy Points: 2265 More actions · Answer 4

Thanks Steve, the dummy measure really works for my case.

In my dimension, I have 3 levels:

Status: active, non-active

Credited: credited, non-credited

Customer_name: customer names sorted in ascending order

If I issue the following MDX:

select { [Customers].[Status].[active].children } on columns,

{ [Products].[Product_code].members } on rows from [Product_sales]

I should get sth like the total of all Active and credited customers in the first column, and the total of all active and non-credited customers in the second column.

Can I get one single column of the total of all credited+non-credited customers, who are active, by using simple functions? or should I use a calculated member and use CROSSJOIN?

Actually what I want to do is to select all active customers (credited or non-credited, as in my previous question), and then display a total of all these customers in the last column.

Thanks a lot,

delpiero

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 5

Assuming you've got a standard summing roll-up for your dimension, then the value returned for the member 'Actvie' is the sum of it's children, so you'd just need to return it to see the total value of Active_credited and Actvie_noncredited.

I.e. All Customers = Active + Non Active

Active = Credited + Non credited

Credited = Cust1 + Cust 99 + Cust......

Non Credited = Cust 100 + ......

So to answer your last question, in your set (for customers) you need to get the descendants of 'Active' and 'Active' itself.

SELECT

{DESCENDANTS([Customers].Active, 2), [Customers].[Active]} ON 1,

{[Measures].[whatever]} on 0

FROM [Your_cube]

The above could also be written using the level name instead of a distance from the current member e.g.

SELECT

{DESCENDANTS([Customers].[Active], [Customers].[Customer_name]), [Customers].[Active]} on 1,

{[measures].[your_measure]} ON 0

FROM [Your_cube]

Steve.

Del Piero SSCrazy Points: 2265 More actions · Answer 6

Thanks a lot steve. Your solutions helped me a lot!

Best regards,

delpiero