WebI - Sort on grouped dimension by a subtotaled measure

[Former Member]

WebI does not provide the functionality to apply sort on grouped (breaked) fields based on a measure. I had a workaround before Before SP03 FP18, but is not applicable anymore. Is there any other known workaround for achieving this?

E.g.

Product Group	Product	sales
GR1	P1	180
	P2	140
	P3	120
GR1 Total		440
GR2	P4	210
	P5	190
	P6	160
GR2 Total		560

I can have the Sales sorted by Product. I cannot have the Product Group sorted by subtotal Sales value.

As for the example, I would like to see GR2 above GR1.

Thank you,

Onur

[ozan_eroglu]

Hi Onur,

I was looking for a solution for the same problem and encountered a note.

SAP Note: 2004893

Ps: Also if you do your grouping according to char sorting 1-GR2, 2-GR1 and than create a new variable with substring to this ( GR2, GR1 ) . it would be working.

Fyi,

Ozan Eroglu

[Former Member]

Hi Onur Tosyali,

I have refer to the workaround method provided above, I am using the formula No2, but I am facing problem at step c), please advice.

a) below is the result after I add the first column as Rank

Rank            |Product Group| Product | Sales

0000000250    GR1                  P1         200
0000000250    GR1                  P2         50
0000001550    GR2                  P3         1200
0000001550    GR2                  P4         350

b) After that I add break for the "Rank" column, and sorting Desc, It split all the row

Rank           |Product Group| Product | Sales

0000001550    GR2                  P3             1200
sub total
0000001550    GR2                  P4              350
sub total
0000000250    GR1                  P1              200
sub total
0000000250    GR1                  P2              50
sub total

c) What I expect is to show as below, not to split all the product

Rank                |Product Group| Product | Sales

0000001550         GR2                       P3    1200
0000001550         GR2                       P4    350
sub total
0000000250         GR1                       P1    200
0000000250         GR1                       P2    50
sub total

[Former Member]

Hi Angie,

It seems as if you also have a break for the Product column; you shouldn't.

Please click on the block, and choose Analysis -> arrow next to Breaks - > "Manage Breaks" from the ribbon at the top of the screen.

You can see all the applied breaks there.

You should only have breaks for Rank and Product Group.

Please let me know.

Onur

[Former Member]

I found a workaround to the problem for now, such that

1. When there is just one dimension that has a break, I use a variable such that the var. is "=rank([measure] ; [dimn. with break])", adding it to the block, breaking it, sorting it and hiding it.
2. When there are more than one dimension with a break (it gets complicated here; this was my case in most of the reports) I have to use a variable such as

"=formatnumber(sum([measure]) in([dim with break]) ; "0000000000")"

, then adding it to the block, inserting a break, sorting, and setting the font color to the same as background, and decreasing the weight of this column to minimum (because webi will not let me hide this.)

It did let me hide this until last few fix packs, as I mentioned. Now I cannot, so I have to have a 0.42cm empty column somewhere in the block.

So I am still looking for any better workarounds.

[Former Member]

A visual example:

I would like to see GR2 above GR1.

I have tried pretty much everything about details, it cannot be done with details because webi does not allow numeric details to be hid.