Sorting BEx structure dimension values in universe

[former_member760545]

Hi everybody,

I am building a universe which is made on a BEx query.

The BEx query contains a structure with many selections.

Each selection is a restriction on 0calmonth based on Query variable that the user inputs when a Webi report is run.

The name of each selection is a text variable + a string (an example: "JULY 2010 MAT"). Some selections contain only one month and some selections contain several months.

The order of the selections in the BEx structure is not kept when building reports in Webi. It seems like Webi orders the dimension values alphabetically.

Is it somehow possible to override the sorting made in Webi? I would like if the sorting in Webi always looked at the way the dimension values are sorted in the BEx structure as selections.

I know that I can make small fixes in the Webi report by using variables and formulas. However, our users are building many reports and it is not good if they have to use complicated formulas just to sort the dimension values.

I cannot use the custom sort feature in Webi either as my dimension values are based on text variables and therefore change name depending on the user input.

So is it possible to make some changes to the universe, so the dimension values are always sorted as in the BEx query???

I am using XI3.1 SP3 and SAP BW 7.01 SP6.

Thanks!

Best regards,

Morten

[Former Member · Accepted solution]

Hi,

I confirm that WebI does not support date sorting.

However there are 2 ways to sort dates in WebI:

<li>Build a custom sort (but this is not what you want)

<li>Create a WebI formula that used the date object and return data such like this:

<ul><li>01 - January

<li><li>02 - February

<li><li>01 - March

<li><li>etc

</ul>

The last one makes the variable always valid whatever the user selection is.

Didier

[former_member760545 · Accepted solution]

Hi Didier,

Thank you for confirming that Webi cannot sort the dates.

I think I will ask my users if they can accept that the format would be for instance "2010 01". This way the sorting would work, but the labels don't look to good. Then I can just arrange my text variables in the BEx query with this format. So the users will never have to think about creating new webi variables for sorting the dates when building reports.

Best regards,

Morten

Edited by: Morten Bjernvig on Jul 19, 2010 3:02 PM

[Former Member · Accepted solution]

Hi,

In XI 4.0 it will possible to hide a column in a WebI block. So you will be able to make a sort on a given column and hide it.

In XI 3.x you can cheat by creating a column with a small font and a foreground and backcgorud color identical then apply the sort on this column.

Regards

Didier

[former_member760545 · Accepted solution]

Hi Didier,

The trick with an extra column that is sorted and then hiding it sounds good. However, we are using many charts and I have not found a way to hide the sorted dimension in the charts. The labels underneath each bar in a bar charts will be called ("sorted hidden dimension" / "my visible dimension").

So each value label in the chart would still look a bit strange to users.. Unless you have a way to hide the value label for only one of the dimensions in a charts? I only know how to hide the whole value label, which is not a good solution for me.

Best regards,

Morten