Portlet based on query automatically round numbers

[Martin Snizek]

Hello,

 

I've got a problem with numbers in portlet based on query. The portlet or query automatically round numbers so it's not true then:

For example:

From DB I got: 21.5 or 7.25

And the portlet shows as: 22 and 8

 

Please have anybody idea how to solve this?

 

Regards,

Martin

[Anon Anon]

As long as the SQL in your query is not rounding the number (and returning the value as a number), then this should just be controlled in the portlet settings ; in the column settings (in the fields options for the list view) you can explicitly set the number of decimal places there (setting to 0 would give you the symptoms you described).

[Martin Snizek]

Hello Dave,

 

I tried to set number of decimal places to 2, but instead of 7.25 I got 8.00 and instead of 21.5 I got 22.00.

 

My query is rounding to 2 decimal places and in Queries (in Clarity Studio) are fields with data type number.

 

I'm not able to get to portlet the right result of the query

 

Regards,

Martin

[Anon Anon]

That sounds "odd"...

 

...in the @SELECT bit at the top of the NSQL, are you setting the datatype of the returned attribute explicitly (as an INTEGER perhaps) or using ':IMPLIED:'?

[Martin Snizek]

I use there:

 

@SELECT:DIM_PROP:USER_DEF:IMPLIED:ALI:case when aaa.suma_odpr_nem_hod is null then 0 else aaa.suma_odpr_nem_hod end:suma_odpr_nem_hod@,

 

It is possible to define there, that it is Integer, by using something like this?

 

@SELECT:DIM_PROP:USER_DEF:INTEGER:ALI:case when aaa.suma_odpr_nem_hod is null then 0 else aaa.suma_odpr_nem_hod end:suma_odpr_nem_hod@,

 

Regards,

Martin

[Anon Anon]

OK, I'm out of GUESSes (that last :INTEGER: thing was a GUESS, just tried it and it doesn't liek it (despite what the manual says) - :IMPLIED: should work OK).

 

Something still sounds wrong then, if your query is definately delivering numbers with decimal places and you are not restricting these in the portlet display options then you should see decimal places (I get 9 decimal places in a quick test of a new query and a new portlet).

 

I would be tempted to try to create a new 'clean' portlet using your query (or even a new query) and see if you get the same results - just to eliminate the possibility that the config is somehow "broken" in your current one) - you could post the NSQL here, but since its likely to be specific to your datamodel, not sure if that would help.

[Deactivated User]

That is an old problem and does not get any better if you export it to excel.

What I used to do is multiply the number with 10 - 10 000 and export it to excel.

Then in Excel divide it with 10 - 10 000 respectively.

One way to use brute force is to read the integer and decimal part and convert them to strings and concatenate them in the portlet field.

[Anon Anon]

Must be more "weirdness" to it though ; I'm building a whole bunch of screens at the moment (in v13) and they all behave as I would expect them to (decimals displayed and exported OK)

[Deactivated User]

Are your screens query based portlets?

[Anon Anon]

urmas wrote:

 

Are your screens query based portlets?

Mine are yes - nothing clever; just numeric decimal values returned by the ('wrapped') SQL, :IMPLIED: datatype in the NSQL header bit, appears as 'Numeric' in the query attribute list and then I control how many decimals I want displayed in the portlet/fields settings (as described above).

 

I don't recognise/recall there being any "old problems" in this area either - its always"just worked" as far as I know.

[Martin Snizek]

Hello Dave and urmas,

 

I have now found another wierd think. We have got a calculated attribut on a custom object, the right value which is calculated on one instance is 7.5 in instance detail I can see it OK, but in list I see it as 8.0 (it's standard list of custom object instances - not based on query). So probably there is a bigger problem.

 

Also as urmas wrote, when I export the data from the portlet mentioned in my first post - values are OK.

 

It's quite confusing

 

Regards,

Martin

[Anon Anon]

For OBJECTS there are separate [Fields] settings for the "instance detail" and "list" views - so you can set the system up to show a single numeric value in different ways.

 

Have you managed to try a "clean" portlet?

 

The only other thing I can think of might be something in your browser settings - a very out-there GUESS that one, I know that date formats are affected by the browser config though.

[Anon Anon]

A random left field one, try ... is null then 0.0 else ..., you might be implicitly casting to integer... I haven't tried, but just a thought...

[Kathryn Ellis]

In general, we use the default Java rounding method which is called ROUND_HALF_EVEN is explained here.

http://docs.oracle.com/javase/1.5.0/docs/api/java/math/BigDecimal.html#ROUND_HALF_EVEN

Some examples here:

http://stackoverflow.com/questions/10144636/rounding-with-decimalformat-in-java

So .25 will round down while .35 will round up.

(referring to current release of CA PPM)

[Deactivated User]

Sorry to say, don't get that to work like that when I try a custom object with one number attribute and one calculated attribute.

LIST VIEW

OBJECT BASED PORTLET

NSQL PORTLET where the calculation is done in the query

EXPORT TO EXCEL DATA ONLY

 

What did I do wrong?

The only place where is see rounding is in Export to Excel from the NSQL based portlet and there rounding is done into integers according to the normal arithmetic rounding rules (ie below ,50 round down and above 0,5 round up)

This is in Clarity v13.3

 

Where should the java rounding method be used in Clarity.

[Deactivated User]

THE OLD WAY OF DEALING WITH DECIMALS

So it appears that I have used mainly Export to Excel from NSQL based portlet.

 

This is the orginal NSQL query

 

 

This is the modified query for displaying decimals in Excel

 

 

This the way it makes the NSQL based portlet look

 

The inital results are divided with 1000 in Excel to give the decimals that the fields have in Clarity

 

 

It would interesting to see examples where the java rounding method works.

[Martin Snizek]

Hello everybody,

 

thank you all for your replies. I'm not able to make it work right, but when I placed this query and portlet to another environment (problem is in development environment and now I placed it to test environment) it works fine and it's not rounding.

 

I'm really disappointed by this behavior, but this persuaded me that it's only problem of our development environment.

 

Regards,

Martin

[Deactivated User]

Are the versions including the builds exactly the same?

Is that just one service or all?

[Martin Snizek]

Hello,

 

versions are completely the same. What do you mean with your second question?

 

Regards,

Martin

[Deactivated User]

The second question was for another thread, sorry about that.