Hi All,

In a BICS environment where the Bex Query is taken as a source for creating Webi, sometimes we face a challenge in sorting(Ascending/Descending) using Fiscal Year Period – Text.

Here am trying to explain some steps which we need to do, so that the soring issue can be avoided in Webi.

We face this issue mainly when we want to show the analysis of a period(say 6 months) and a change in year happens within the range of analysis.

Below Screenshots shows the Ascending and Descending behaviour, when we drag the Fiscal Year Period text and a measure(in this case, CRM Lead Qty)

/wp-content/uploads/2013/09/1_288386.png/wp-content/uploads/2013/09/2_288388.png

In Such Scenarios, request the BW Team to add 0FISCYEAR also in the same query along with 0FISCPER.

Then try creating the corresponding Webi again and ensure that you drag in the Fiscal Year also to the query panel along with other dimensions and measures.(See Below Screenshot). You can also notice that the there is a change in Fiscal year in the data which we have taken for analysis.

/wp-content/uploads/2013/09/3_288395.png

Now do the required sorting first on Fiscal Year and then on the fiscal year period-Key(as shown in below screenshots)

/wp-content/uploads/2013/09/4_288396.png

/wp-content/uploads/2013/09/5_288397.png

Now you can see that the Fiscal Year Period – Text is properly sorted.

In case, if you really don’t want Fiscal Year and Fiscal Year/Period – Key for your further analysis, Hide them as shown below:

/wp-content/uploads/2013/09/6_288434.png                                             

/wp-content/uploads/2013/09/7_288435.png

Now we are able to show the Fiscal Year/Period in a sorted order.

/wp-content/uploads/2013/09/8_288411.png

You can also explore the following option in Bex side for meeting the same requirement.

/wp-content/uploads/2013/09/9_288412.png

PS: The same steps are applicable for 0CALYEAR and 0CALMONTH combination scenarios as-well.

Hope, this is helpful!

Thanks and Regards

Prabhith

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Shabnam Shourav

    We were also facing this issue while using calmonth/year from BICS connection. Nice blog could use in my project..

    In addition to this,if calyear is not coming from Bex query then we can extract year using =Right([Calmonth/year];4) in webi report itself and use this year for sorting.

    This will also work.

    Regards

    Shabnam

    (0) 

Leave a Reply