Planning Analytics

 View Only

  • 1.  PAW Drill Process query - Formatting numbers

    Posted Thu November 04, 2021 12:18 PM
    Edited by System Fri January 20, 2023 04:43 PM
    PAW Drill Process query - Formatting numbers

    Hi everyone!

    I created a Drill Down process in PAW using an Oracle SQL query for fetching some detailed data for a call.
    This works fine, and PAW automatically displays my DB records with the column headings as defined in the query.

    The only thing is that numbers are not displayed correctly.
    1. the numbers are left-aligned, not decimal
    2. the decimal point is displayed as a dot "." but should be a comma "," for my region

    Is there a way to display formatted numbers in PAW from the query?

    In our region, the formatted numbers should look like this example: 208.530,27
    The DB returns 208530.27 and as said, paw displays the numbers also left-aligned, as is.

    This image shows an example.


    ------------------------------
    Asgeir Thorgeirsson
    ------------------------------
    #PlanningAnalyticswithWatson


  • 2.  RE: PAW Drill Process query - Formatting numbers

    IBM Champion
    Posted Thu November 04, 2021 02:39 PM
    Hi Asgeir,

    Are all of your users in the same region and expecting the same format?

    If so you can alter your query to do the formatting there.
    Its a bit easier with MS SQL where you can just use the Format function like:
    format ( [MyColumn], 'N', 'de-DE' ) MyColumnName​

    But I believe in Oracle you can get around it with something like:
    to_char ( MyColumn, '999G999G990D00', 'NLS_NUMERIC_CHARACTERS = '',.''') MyColumnName​


    I don't believe there is anything you can do to get Numerics to appear right-aligned in this one (happy to be proven wrong on that though) and if that's the case it doesn't really matter if you are formatting as a text instead of a numeric - when the user downloads to a CSV - Excel can do its thing anyway.

    Thanks,

    Declan



    ------------------------------
    Declan Rodger
    Technical Director
    Spitfire Analytics
    ------------------------------

    Original Message


  • 3.  RE: PAW Drill Process query - Formatting numbers

    Posted Fri November 05, 2021 06:10 AM
    Edited by System Fri January 20, 2023 04:17 PM
    The first priority would be to have the numbers easily readable as right-aligned and decimal. 

    I remembered that in PM, there is the possibility to define the sql parameter type in a process.
    The "Decimal" option is there (see image)

    When you define a process in PAW, there is only the TEXT and NUMERIC option

    In PAW, is it possible to override the variable definition to decimal in the prolog of a process?




    ------------------------------
    Asgeir Thorgeirsson
    ------------------------------

    Original Message