Clarity

Expand all | Collapse all

Portlet based on query automatically round numbers

  • 1.  Portlet based on query automatically round numbers

    Posted 12-21-2014 08:26 AM

    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



  • 2.  Re: Portlet based on query automatically round numbers

    Posted 12-21-2014 11:33 AM

    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).



  • 3.  Re: Portlet based on query automatically round numbers

    Posted 12-21-2014 02:50 PM

    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



  • 4.  Re: Portlet based on query automatically round numbers

    Posted 12-21-2014 05:03 PM

    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:'?



  • 5.  Re: Portlet based on query automatically round numbers

    Posted 12-21-2014 05:10 PM

    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



  • 6.  Re: Portlet based on query automatically round numbers

    Posted 12-22-2014 05:28 AM

    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.



  • 7.  Re: Portlet based on query automatically round numbers

    Posted 12-22-2014 08:33 AM

    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.



  • 8.  Re: Portlet based on query automatically round numbers

    Posted 12-22-2014 08:43 AM

    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)



  • 9.  Re: Portlet based on query automatically round numbers

    Posted 12-22-2014 02:55 PM

    Are your screens query based portlets?



  • 10.  Re: Portlet based on query automatically round numbers

    Posted 12-22-2014 03:10 PM

    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.



  • 11.  Re: Portlet based on query automatically round numbers

    Posted 12-22-2014 04:22 PM

    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



  • 12.  Re: Portlet based on query automatically round numbers

    Posted 12-22-2014 04:31 PM

    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.



  • 13.  Re: Portlet based on query automatically round numbers

    Posted 12-22-2014 07:59 PM

    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...



  • 14.  Re: Portlet based on query automatically round numbers

    Posted 12-23-2014 12:51 PM

    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)



  • 15.  Re: Portlet based on query automatically round numbers

    Posted 01-03-2015 02:41 PM

    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.



  • 16.  Re: Portlet based on query automatically round numbers

    Posted 01-04-2015 02:03 PM

    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

     

    Orig_NSQL.JPG

     

    This is the modified query for displaying decimals in Excel

     

    1000xNSQL.JPG

     

    This the way it makes the NSQL based portlet look

    1000xNSQL_portlet.JPG

     

    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.



  • 17.  Re: Portlet based on query automatically round numbers

    Posted 01-06-2015 08:27 AM

    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



  • 18.  Re: Portlet based on query automatically round numbers

    Posted 01-06-2015 02:50 PM

    Are the versions including the builds exactly the same?

    Is that just one service or all?



  • 19.  Re: Portlet based on query automatically round numbers

    Posted 01-08-2015 07:13 AM

    Hello,

     

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

     

    Regards,

    Martin



  • 20.  Re: Portlet based on query automatically round numbers

    Posted 01-08-2015 02:46 PM

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