cancel
Showing results for 
Search instead for 
Did you mean: 

How can I sort using a secondary characteristic structure?

Former Member
0 Kudos

Hello -

We have started using chracteristic structures in Query Designer 7.0 to group materials/customers for reporting. Our users love it except that I can't find a way to allow them to sort by a key figure (in a regular column structure) within the characteristic row structure.

Here's an example.

Net Sales is the Key Figure in the Column Structure.

Key Customers / Groups is the structure in the rows. There are selections build for each customer group with indidual customers assigned to each group within the structure.

The users need to sort the individual customers within each customer group in descending order by Net Sales.

There's no sorting icon available for the key figure column.

If you try to sort in the key figure properties, it doesn't work or says "not applicable".

I've tried creating a condition with 100% but that doesn't work either.

We are using BEx Analyzer 7.0 but it doesn't appear to work with the Web Analyzer either.

Can anyone provide any type of "trick" to accomplish this requirement?

Thank you for your help.

Ann

Key Customers / Groups Net Sales

Customer Group A (selection)

Customer 1

Customer 2

Customer Group B (selection)

Customer 3

Customer 4

Customer 5

Customer Group C (selection)

Customer 6

Customer 7

Customer 8

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

using standard BI tools this would be impossible...

I would suggest creating a 2nd sheet in which you copy the "results" of your BI query (workbook) and then sort it there either using Excel functionality or using Visual Basic code

Former Member
0 Kudos

Thank you for your quick response. I was hoping there was a solution within the BEx tools.

We did offer the option of doing it outside of BEx with Excel functionality. Our other option is to create additional worksheets with specific queries for each sub-group with the data sorted in the proper manner. The users would then use the first worksheet to see a sanp-shot of the overall group and sub-groups but would go to the individual worksheets to see the customers within each sub-group. Just seems like a lot of extra work for something that doesn't seem like it should be a big deal -- even though it might be technically.

Thanks again.

Former Member
0 Kudos

Hi Anne,

Did you try to use the condition "Top n"? Try it and put in 99999 as no of items to be ranked, which will be sufficient for a while.

Another way to tackle the problem would be to create (or use an existing) attribute to customer, for some sort of customer grouping. Then you will not need to use a structure. You would then use the rows simply with two dimensions instead, like this.

"Customer group"

Customer

If "Top n" will not work in the structure, it will work in the "customer group scenario".

BR,

Niclas

Former Member
0 Kudos

Thank you for your response. The Top N condition does not work with the structure. All of the condition options I tried proved to be "not applicable". Yes, ideally they would have an attribute for the customer (or for the material, etc.) but we have many businesses working within a global system so we don't support every grouping option that a specific business would like in our system. Setting them up within the query seemed like a good option, and it is, except for the sorting limitation right now.

Thank you for your help.

Ann

Former Member
0 Kudos

I currently have the same problem. It was possible with Bex 3.5, if you put buth structures to columns, so that there were only characterics in the drill down to rows.

But this actually doesn't work any longer in Bex 7 - or does somebody know how to do it?

Answers (1)

Answers (1)

olaf_neubert
Explorer
0 Kudos

Hi,

i think i´ve builded up something similar within a workbook. You just need two querys, one workbook, some standard excel functionality and a makro for sorting the data at the end. I think this should be less work than building a worksheet for every sub group.

The first query will only select the upper elements of the structure (in your example: "customer group") . The second query will select the complete structure like you´ve builded it originally. The first query needs a ranking key figure. (you can do it by the excel ranking function or BW function, depends on how building that query). Now you put the ranking key figure behind the second query by the excel function "v-lookup". Then you add a second column behind the second query with a excel-ranking funktion for the key figure of the second query which should be sorted. At least you add a third column which identifies the kind of characteristic structure element (for example by if-then function on the key: 1= customer group 0=customer).

Then you´ll have only to sort the complete second query by those three new columns ´- in best case by a makro - and thats it.

Hope i could help you with that.

Greetings,

Olaf