Oracle Transactional Business Intelligence

Products Banner

Changing date format to remove comma - OTBI Report

Received Response


Been working on an Absence Calendar for a Dashboard and the prompt for Year dislays as '202,0'. Is there a way to change the format so it displays as '2020'.

Content (required):

Version (include the version you are using, if applicable):


Code Snippet (add any code snippets that support your topic, if applicable):


  • Nathan CCC
    Nathan CCC ✭✭✭✭✭

    Assume this is your number format. Sounds like yours has a thousands separator?

    You can either change the number format or edit the formula to cast the number as a string.

    To change the number format in your criteria select the column, select Column Properties, select Data Format, select override, then make "Use 1000's Seperator" false. By default horizontal alignment is right. But you can change that in Column Properties Style.

    To change the data type select the column, select Edit Formula, make it cast("Presentation Table"."Presentation Column" as character). By default horizontal alignment is left. But you can change that in Column Properties Style.

  • Thank you ever so much Nathan, this is really helpful - what I'm also noticing is the prompt displays this the same way - this is a filter:

    And so when in the calendar, when attempting to select the year:

    This appears - it was originally displaying with the comma - because of the thousands seperator, however I am unsure if this is able to be amended?

    Would this be better as a prompt rather than a filter?


  • Nathan CCC
    Nathan CCC ✭✭✭✭✭


    You cant have a prompt without a filter. The column in a prompt must match the column in the filter. If you do not "lock" your filter with "Protect Filter" = True then it will be replaced by the value from a matching prompt. So in this case year = 2020 in your analysis is therefore just your "default". If your user selects 1999 from a matching prompt at runtime then your logical query will become instead where year = 1999.

    But you can select the fx button, on both the column in the filter in your analysis, and the column in your prompt, to make them use the same function if you want the prompt to look like a string too.

    In prompt

    In analysis

  • Hi,

    This is great, thank you ever so much!