Incorrect sort for Year/Period

[Former Member]

Hi,

In my OLAP report I am trying to sort year period field but it is giving wrong output.

Please check below snaps to understand it more.

I had made Year/Period filed Display as key and am trying to sort it in ascending order but unable to sort it. But at backend in BEx I am able to sort it in correct way.

I’m not sure we can create a variable, in OLAP, to strip some characters and get aresult with only MM.YYYY(001.2013).

Regards,

Mithun

[I_MCA · Accepted solution]

Hi Mithun

If you create a variable to only get the result MMM.YYYY (eg 001.2013) then you will likely have the same problem... ie the sorted result will be:

001.2013

001.2014

002.2013

002.2014

etc.

When you choose the display option to show "Key" what do you see?

With AOLAP you can display one thing but sort on the other... and this is often the solution for sorting dates... ie you display the "Text" but when you apply the sort you choose to sort the "Key".

Regards

[Former Member · Accepted solution]

Hi Ian,

That’s true, i am agree with your opinion. Then what is the alternative here?

Regards,

Mithun

[I_MCA · Accepted solution]

Hello Mithun

You mentioned that BEx is sorting this correctly but not AOLAP. Can you do two thing please...

1) Right click on the column and set the "Display" option to just "Key". Take a screenshot and post the result here.

2) Do you have access to Analysis Office? If so, does that work correctly?

Kind Regards

[Former Member · Accepted solution]

Hi Ian,

Yes, Bex is sorting correctly, please check below screen shot.

1. Display By Key.

2. Yes, I have access to analysis office, please check below screen shot from analysis.

    Sort is not working correctly hear.

Regards,

Mithun.

[I_MCA · Accepted solution]

Hi Mithun

Thanks for the screenshots. You mentioned you do have access to Analysis Office. Can you please attach a screenshot of the same query using Analysis Office? Does it work? This will help determine if the core issue is with AOLAP or with the underlying connectivity component.

Separately, I assume this Year/Period hierarchy is a compound characteristic comprising of a Year characteristic and a Period characteristic? In the screenshots you have shown, the Year element appears to be redundant... it always shows "Y1" whereas the Period element comprises both the period and year.

To help us also determine if the issue is related to the fact that we are sorting a compound characteristic, could you just try sorting the Period characteristic instead of the compound characteristic?

Also, the screenshots you have shown above... is that the key or the text that we are seeing? If it's the key then it won't work... at least not for AOLAP... though I'd be interested to see what AOFFICE does. To get it to work for AOLAP I would expect the key to be something like 2014.001, 2014.002, 2014.003 etc.

Thanks

[Former Member · Accepted solution]

Hi Ian,

In analysis office also am facing same issue, please check below screen shot.

Yes, year/period is a compound characteristic of year and period characteristic.

If I am sorting period characteristic then also am facing same issue, please check below screen shot.

In above message I have sent all screen shot with display as key.

Regards,

Mithun

[Former Member]

Hello ,

i have a similar mistake but only for a position bin column . When i try to sort in ascending order , only the firs number is sort .

Could you help me please ?

Thank you , Melo

[I_MCA]

Hello Melo

The problem is not that only the first number is being sorted, the problem is that the Position Bin column is character based and not numeric. If you look closely you will see that the sort is behaving correctly and is performing an alphabetic sort... but you expect a numeric sort.

If you are displaying the "Text" and the "Key" has the same/similar value then the simple solution is when you sort, make sure that you select the option to Sort by Key and not by Text.

If the Position Bin numbers are only ever numeric then another option is to change the Position Bin field to be numeric.

Regards

[Former Member]

Thank you Ian McAlpine !

Have a nice day ..

Melo

[Former Member]

Hi Ian,

As per above Kathy's example I followed same steps but still same behavior.

Already I am following up with Support team as well and they are suggesting to post issue on SCN forum , so what should I do now?

Regards,

Mithun.

[Former Member]

Hi All,

Any updates on this.

Regards,

Mithun.

[former_member109716]

Hello,

We encountered a similar issue.  We are only allowing a connection to both Analysis for Office and Analysis for OLAP via BEx Queries.  To address our issue, we modified the BEx Query definition's sort properties for Fiscal Year/Period as depicted below:

Please note that we are using V9 as the fiscal year variant; therefore, fiscal period 001 is calendar month October.

This is our order when displaying by key:

[I_MCA]

Hi Mithun

Because both AOLAP and AOFFICE exhibit the same behavior, this indicates a difference of behavior with these tools compared to BEx. Both AOLAP and AOFFICE can sort by text or key so either of these must be modified so that the sort order is correct. For dates the common solution is to adjust the Key so that the sort performs correctly while leaving the Text in a "user friendly" format.

In Kathy's screenshot notice that she is sorting by Key but is displaying Text... and this has been defined in the query.

You can contact support to raise an issue why the sort behavior for compound characteristics is different between BEx and the Analysis tools... but a faster solution may be to change the key of the compound characteristics so that the sort order is correct.

Regards

[TammyPowlas]

Hi Mithun - would you please share what version of BI4 you are using and the SP?

[Former Member]

Hi Tammy,

We are on BO 4.1 and SP2.

Regards,

Mithun

[TammyPowlas]

Is it possible to remove the compounded key - the Y1 fiscal year variant?  According to this KBA http://service.sap.com/sap/support/notes/1700253 you should be able to on your version

[Former Member]

Hi Tammy,

My main issue is sort issue with current output am unable to sort Year/Period in proper manner so am trying to remove the compounded key by creating new variable but in OLAP am not getting an option to create variable.

In above link they not provided any navigation steps to remove compounded key.

Regards,

Mithun

[gowdatimma_ramu]

Hello Mithun,

In AOLAP you can only consume the Variables created on Bex Query.

Unfortunately there is no option to remove compound characterisitcs in analysis OLAP.

You may consider entering an idea for this in Idea Place SAP BusinessObjects Analysis edition for OLAP

Regards,

Ramu.

[Former Member]

Hi Ramu,

Thanks for your suggestion, yse i am going to create idea on idea place.

Regards,

Mithun