en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Carl  
#1 Posted : Tuesday, August 19, 2014 10:47:15 PM(UTC)
Carl
Rank: Member

Groups: extranet\Forum
Joined: 8/12/2014(UTC)
Posts: 9

Dear experts,

I'm having issues to sort a cross tab on 2 levels
Level 1: customer group
level 2: customer

Customer group is sorted based on a key, because we don't want an alphabetical sorting, but a predefined one. Within each customer group we want to sort customer based on revenue.

The problem is that when I click on sorting based on revenue using the context menu (in combination with hierarchical collation), the sorting of level 1 (customer group) is changed. It does not anymore sort based on the key. Instead, it will sort the subgroups based on their total value.

Example

I want

Group A total : €2500
customer 1 €1000
customer 2 €800
Customer 3 €700

Group B Total € 600
customer 4 €300
customer 5 €200
Customer 6 €100

Group C Total € €1500
customer 4 €700
customer 5 €500
Customer 6 €300

But I get:

Group A total : €2500
customer 1 €1000
customer 2 €800
Customer 3 €700

Group C Total € €1500
customer 4 €700
customer 5 €500
Customer 6 €300

Group B Total € 600
customer 4 €300
customer 5 €200
Customer 6 €100

On the other hand, when I disable hierarchical collation, I do get the correct sorting if I first add the customer group to the sort list, and then the revenue. However,in that case I lose subtotals and totals,so the analysis becomes useless.

I also tried using calculations, but no success so far. I also made a small hierarchy in SSAS with customer and customer group, but same behaviour.

Any help is appreciated.

Best regards,
Carl

Ole  
#2 Posted : Wednesday, August 20, 2014 11:17:58 AM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 324

Hi Carl,

I think you have been pretty close to a solution with the "Add to sort list" thing.

Try this:

1. Keep the default "Hierarchical collation" setting.
2. Right click the Customer Group column and "Add to sort list".
3. Right click the Revenue column and "Add to sort list".
4. Right click the Revenue column and "Add to sort list" - again! (to invert revenue sort order).

Best regards,
Ole
I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S.

UserPostedImage Ole Dyring
Carl  
#3 Posted : Wednesday, August 20, 2014 9:55:39 PM(UTC)
Carl
Rank: Member

Groups: extranet\Forum
Joined: 8/12/2014(UTC)
Posts: 9

Hi Ole,

Thanks for your swift response.

Unfortunately, this does not work for me...

Off course I tried out your suggestion, keeping the default collation setting:

1/ When I open the analyses, the customer group is sorted correctly based on key. The customers are sorted alphabetically.

2/ Then I right click the customer group column and "add to sort list" -> now here is already goes wrong, because it now sorts alphabetically, so not anymore based on key as one would expect.

3/ Then I click on revenue column twice: it sorts the revenue as expected, but withing the 'wrong' alphabetical sorting obtained in step 2.

-> I think I need to find out why I get wrong results in step 2. Then my issue will be resolved.

Best regards,
Carl
Ole  
#4 Posted : Thursday, August 21, 2014 8:18:47 AM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 324

All right. I thought/hoped that your customers was already in alphabetical order by name. From your description I now presume that your Customer Group dimension properties have been set to NameColumn = Customer Group Name, KeyColumn = Customer Group Key, OrderBy = Key.

Please try this instead:

You will need the Customer Group Key to be available as an attribute in your Customer dimension. Either it is already available when looking at Source Data in TARGIT, otherwise you will have to go to your Data Warehouse definition to make it visible to end users.

Once it is available in the TARGIT client, you should add it as the first column in your crosstab so you end up with crosstab columns: Customer Group Key, Customer Group Name, Customer Name, Revenue.

Now, use my previous recipe:

1. Keep the default "Hierarchical collation" setting.
2. Right click the Customer Group Key column and "Add to sort list".
3. Right click the Revenue column and "Add to sort list".
4. Right click the Revenue column and "Add to sort list" - again! (to invert revenue sort order).

Finally, you may go to Properties / Visibility settings in the TARGIT client to hide the Customer Group Key column.

BR / Ole

Edited by user Thursday, August 21, 2014 8:20:08 AM(UTC)  | Reason: Not specified

I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S.

UserPostedImage Ole Dyring
Carl  
#5 Posted : Wednesday, August 27, 2014 4:28:32 PM(UTC)
Carl
Rank: Member

Groups: extranet\Forum
Joined: 8/12/2014(UTC)
Posts: 9

Hi Ole,

I did as you suggested.

I also used dynamic captions to rename the customer group key to be customer name again.

Kind of a workaround, but it works.

Rgs, Carl
Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error