Cast a ServerVariable as Date in Dashboard — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Cast a ServerVariable as Date in Dashboard

Received Response
81
Views
15
Comments
Dimitris Karydis
Dimitris Karydis Rank 4 - Community Specialist
edited Aug 13, 2024 5:51PM in Oracle Analytics Cloud and Server

Hi,

I am using a ServerVariable in my reports to present today's date.

When I call it in a report it works fine and uses the data type that the database column has:

pastedImage_0.png  ------>     pastedImage_1.png

But I also want to call the variable in a section title in my dashboard. When I try to do this I get a different type, the one that oracle supports.

pastedImage_2.png          ------------->               pastedImage_3.png

I checked through the session manager in the online RPD and the type for this variable is LONGVARCHAR giving back this TIMESTAMP string. This also creates some other issues when I link through reports.

I tried to change the default initializer to DATE but didn't help. I also tried to change the SQL in my initialization block and cast as date but again nothing happened.

Is there any way that I can cast this as DATE so I can just see the date and not the hour?

Thanks

«1

Answers

  • Joel
    Joel Rank 8 - Analytics Strategist

    Why don't you simple TO_DATE the column in your SELECT statement of your initialisation block for the curr_date_RT session variable? It's being treated as a string because it is a string and not a date

  • Dimitris Karydis
    Dimitris Karydis Rank 4 - Community Specialist

    Yes I know it's a string (I don't know why though), so I am trying to figure out how to change it to a date format so it works in my dashboard.

    I changed the select statement in the initialization block using the TO_DATE function but still when I test the value of the variable remains the same

    pastedImage_0.png

  • Joel
    Joel Rank 8 - Analytics Strategist

    Is it listed as a DATETIME type when you check the session manager in the Admin Tool?

    pastedImage_0.png

  • Dimitris Karydis
    Dimitris Karydis Rank 4 - Community Specialist

    No, as I mentioned the type in the Session Manager is LONGVARCHAR. To clarify,  this is a Dynamic Repository variable and not a Session one.

  • Joel
    Joel Rank 8 - Analytics Strategist

    Try something like:

    'Today - '||CAST(CAST (VALUEOF(NQ_SESSION.curr_date_RT) AS DATE) AS CHAR(10))

  • Venkata Yaswanth Ramisetty
    Venkata Yaswanth Ramisetty Rank 5 - Community Champion

    Hi,

    Try this..

    Create repositiory initialization block (SELECT TO_CHAR(SYSDATE,'DD-MM-YYYY') FROM DUAL;) and dynamic variable accordingly.

    Hope this helps..!

    pastedImage_0.png

    Regards,

    Yaswanth

  • Dimitris Karydis
    Dimitris Karydis Rank 4 - Community Specialist

    None of these commands is being recognised in the Titles.

    For a biServer variable (have to mention again my variable is not a Session one but a Repository one) tthe right syntax to call in a title is @{biServer.variables[‘variablename’]} and no other command can be combined with that (I tried cast and a lot other options, so this is an assumption, if you have found something that works I will be glad to let me know)

    This is why I am trying to find a way to set this variable as date in the first place

  • Dimitris Karydis
    Dimitris Karydis Rank 4 - Community Specialist

    Venkata Yaswanth Ramisetty I will try this, thanks

  • Joel
    Joel Rank 8 - Analytics Strategist

    For a repository variable:

    'Today - '||CAST(VALUEOF("curr_date_RT") AS CHAR(10))

  • Dimitris Karydis
    Dimitris Karydis Rank 4 - Community Specialist

    Joel Acha No, Unfortunately none of these commands that work fine in Answers seem to work in the Section titles