Sorting in Hierarchy Using Bex as the Source (BICS Connection) BO 4.1

[former_member243717]

Hello Guys,

I have an issue.  Am trying to change a hierarchy structure to certain sort order according to requirement but am finding it difficult because hierarchies behaves differently when it comes to sorting when using BICS connection (Bex).  It uses ascending and descending orders.  I have BO 4.1 SP5.  My report has already been set as a Section.  I have tried to replicate the two SAP Note 1892608 by re-arranging the query panel by putting the hierarchy structure first but did not work.  The SAP Note 1407617 cannot be applied to my situation because of the way the report is built because there is nowhere the Row Index can be dropped in the report and the report has already been sectioned.  Any other idea please?

Thanks,

Recca  

[amitrathi239 · Accepted solution]

Hi,

You can not do the custom sort on the hierarchy object.Either place the order in the  BW to get the required order in the webi.If section is placed on the hierarchy object then instead of hierarchy object use the normal object in the webi query to do custom sort.

Amit

[former_member243717 · Accepted solution]

Thanks guys for your inputs.

That is true, Custom Sorting will not work on Hierarchy because I have tried that. 

Amit:  The order I was trying to achieve in Webi is the same order that is in BW

and cannot be changed because other queries uses this hierarchy.

Also, in terms of using the normal objects in Webi, how is that possible?  The objects

am trying to sort are part of the hierarchy.  How do I change that from being a hierarchy to

just normal objects?

Lastly, do you know how to remove a "Section" from a hierarchy in 4.1.  The button is greyed out

and you cannot undo the sectioning.  I have seen other versions that you can remove a section.

Thanks,

Recca

[amitrathi239 · Accepted solution]

Hi,

If you are not displaying level 1,2 etc.. data of hierarchy  in the report and section is on the hierarchy object  (section on the summary data) then might be you can use normal object in the webi query panel instead of hierarchy object.

To remove section-right click on the the section ->delete->Cell & Section.

Amit

[Former Member]

can you purge the report and try again?

[former_member243717]

Hi Victor and Amit,

I tried the purging and it did not work.  I also tried using the flat objects (normal objects) as Amit suggested, that did not work either.  Please keep in mind that in my hierarchy structure, I have some nodes with two leaves and I have some with only one leave.  If they were all one leave, it could have been easy because the sorting works that way for me when I tried it but I will not get the totals correctly.  The moment you select the two leaves inside the node in the query panel, the sorting disappears when the report is ran.  I am not using any levels.

Thanks,

Recca

[Former Member]

can you create a new Report and insert only the hierarchy there, is that the order you expect?

[former_member243717]

Yes Victor.  Very good thought !!!  I tried that and "Yes" the sort order I did in the hierarchy worked just as I wanted it to show up in the report when viewed.  The report have lots of formatting done to it and don't want to have to create a new report because I was thinking it might be something small that I need to remove before I can get it to work right.  When I tried to create a new report, I even positioned the hierarchy in different positions in the query panel and they all worked perfect.

Thanks,

Recca

[Former Member]

Can you re-Point the Data source to the same query?:

http://scn.sap.com/docs/DOC-29683

[former_member243717]

Thanks Victor.

It did not work.  I have also tried that before, but at the time, I used the same existing query that was there in the first existing query window.  This time, I selected new one from the list below, made changes to the hierarchy last, no luck.  I again make changes to the hierachy first and then change the datasource, no luck.

My gutt feeling is that there must be a formatting issue that is preventing this, since I can build a new report from the scratch and it works perfect !!!

Recca

[Former Member]

Hi,

Go for Custom Sort where you can decide the sorting according to your requirement.

Thanks & Regards,

Kumar V

[Former Member]

HI

Custom sorting will work try that!!