Skip to Content
author's profile photo Former Member
Former Member

How can I sort using a secondary characteristic structure?

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on May 22, 2009 at 07:19 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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?

  • Posted on Nov 12, 2009 at 04:44 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.