YEAR not displaying correctly in Listbox

[former_member190722]

Hi All,

I have a chart created in design studio 4.0 with BO 4.1 universe as data source.

I need to have a list box created on 'YEAR' column so that when user selects a year from the listbox , data gets filtered on the selected year.

I created a Datasource on Year object(numeric) and mapped it to the charts datasource.

But when i select a year, invalid year format is being passed.

This is because,the year listbox is displaying year as 2010.0    ,2011.0  ,2012.0  instead of 2010,2011,2012.

Due to the decimal place and zero value, the filter in the WHERE clause of chart data source is failing.

I have formatted the year object to display as 2010,2011,2012 but its not showing accordingly.

Could someone assist please?

Regds,

Samson

[former_member190722 · Accepted solution]

Hi all,

I was able to fix the issue and the selector works fine now.

Fix:

I converted the format of year object to String using TO_CHAR and then the number formatting applied by Design Studio does not occur.

Thanks all for your comments.

[IngoH]

Hi Samson,

there is a very simple solution for it.

Just define a "Display Format" in the Universe for the dimension. The issue is not the listbox, the issue is that most likely in your case the Year is a numeric value and then by default you get that format.

In the Universe go to the Dimension object and setup a Display format without any decimals and any thousand separator.

regards

Ingo Hilgefort, Visual BI

[former_member190722]

I already tried this option of using "Display Format" in Universe.

What happens is that, the YEAR is displayed correctly in Universe but in Design studio , formatting is again applied by Design Studio and hence it displays 2011.0

[MustafaBensan]

Hi Samson,

To further elaborate, the script code would look something like this:

var myYears = DS_1.getMembers("Year Sel",100);

var formattedYear = "";

myYears.forEach(function(element, index) {

formattedYear = Convert.floatToString(element.internalkey,"####");

LISTBOX_1.addItem(formattedYear, formattedYear);

});

Regards,

Mustafa.

[MustafaBensan]

Hi Samson,

Based on your comments, the Year object in the Universe is defined as a numeric field if I understood correctly, so it appears as though your issue is being caused by the number formatting Design Studio is applying to this field.  How are you filling the items in the listbox?  One quick fix (instead of redefining the Year object as a string in the Universe) could be to reformat the numeric year value via scripting using the Convert.floatToString() function as shown below before adding it to the listbox:

Regards,

Mustafa.

[Former Member]

Hi

You can use substring function to solve your problem-

While passing the value you can use this kind of scripting

var getyear=Listbox.getselectedValue();

getyear will have "2010.0"

var convert_year=getyear.substring(0,3);

convert_year will show 2010

You can try passing this value to the dimension.

If you wish to display the year in correct format you can use the following scripting logic

var getyear=DS.getMember(YEAR_DIMENSION);

getyear.forEach(element,index){

{

var convert_year=element.substring(0,3);

listbox.additem(element,element);

}

});

Now your DS_2.setfilter(YEAR,lisboc.getselectedvalue()); will work properly.

[TammyPowlas]

Samson:

What is the format of the year back in the Universe?  Is it text or ?