cancel
Showing results for 
Search instead for 
Did you mean: 

Calendar Year/Month sorting issue

Former Member
0 Kudos

By default Calendar Year/Month field is not getting sorted, I have tried creating few formula variables but I couldn't make it. I am using this object in graph also so it affects that too.

For example:

Calendar Year/Month has values 10/2011 11/2011 12/2011 01/2012 02/2012 .......10/2012 11/2012 12/2012 but when I include this object in WebI it shows like this 01/2012 02/2012 ..................10/2011 10/2012 11/2011 11/2012 12/2011 12/2012. Drawing graph was also wrong as the data marked in the sequence mentioned.

Calendar Year/Month is in character data type which is causing this issue i.e not able to sort

Can anyone suggest me to overcome this ..

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Gokul,

First Convert this char Data type Oject to Date by using Todate( ) ,this will resolve your issue.

eg : var_CalMonth= ToDate([Calendar Year/Month];"MM/dd/yyyy")

and then you can display the date to the format you want using FormatDate( )

Former Member
0 Kudos

Thanks Kiruthika,

I have used this formula

=ToDate([L01 Calendar Year/Month Key];"MM/yyyy")

it gave me 10/1/08 for 10/2008, I have checked the format it was in date

Then I used formatdate() function to make it 10/2008, it turned correctly but still the datatype is string

can you suggest me what could be the problem ?

Former Member
0 Kudos

Hi Gokul,

Check the Object Datatype at the Universe level, in the Object Properties.

and try changing to Date if it is Char at the universe.

Former Member
0 Kudos

I have tried that before trying this variable, If I change the property in universe, WebI is not executing it is showing some error.

Thanks

Former Member
0 Kudos

A database error occured. The database error text is: (CS) "Driver not capable" . (WIS 10901)

This is the error I am getting when I changed the datatype in universe and executed the WebI report

Former Member
0 Kudos

Are you using OLAP universe based on Bex Query ? if so check the Object Data type at Bex Level.

Former Member
0 Kudos

Below thread may also help you

http://scn.sap.com/thread/2088275

Former Member
0 Kudos

can you please explain how could I check the object data type ? If I could change the datatype to date in BEx level will it affect other queries using 0calmonth(Calendar Year/Month) ?

Former Member
0 Kudos

Changing the Datatype at Bex Level is not advisable , as this may affect other reports which is using the same object.

You can try an alternative solution by creating a User Defined Dimension at universe level with the Cal month object with Datatype as Date and try using this User Define Object at your report.

Former Member
0 Kudos

Hi Kiruthika,

As you told I have created user defined dimension and used it in my WebI report it is showing the same error as mentioned earlier. What could I do now ? Creating a new universe will be last option, any other idea..

Former Member
0 Kudos

Is there any Detail Object Under Cal Month?...If so then,in the User Defined Variable select the detail object which will be relevant to the Calmonth and try...if that option is also not working , then we should go for a new Universe.

Former Member
0 Kudos

I have tried with both dimension and detail object, it is not working .. I ll try creating new universe

Former Member
0 Kudos

Hello Kiruthika,

It didn't work for me after creating new universe also.. This is troubling me a lot..

Former Member
0 Kudos

Is your Bex Query Based on Infocube or DSO? Check whether the Calmonth object is coming as Char or as Date from the backend.

Try using the Detail Object Date Key in the User defined Dimension created in the universe as, we have tried this solution when we face the same issue and it worked perfectly.

Former Member
0 Kudos

Bex query is based on a multiprovider. 0calmonth as the datatype NUMC and I can't follow you using what "Detail Object Date Key" object is this ? can you please elaborate ?

Former Member
0 Kudos

Can you list me the Detail Objects available under Calendar Year Month??

Former Member
0 Kudos
Former Member
0 Kudos

The issue is because you have only Month,Year and other keys and you don't have Date Key inside Calendar Year/Month Object.

Can you check the with the BW team if they can add Dae Key under Calendar Year/Month.

This could solve your issue.

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

I got a much easier solution. I include Year in the report. First sort by year, then by month/year.  After, if you don't want to display Year, just hide it, and done