Business Intelligence

Get Involved. Join the Conversation.

Posts

  • 1-3 of 3
  • Srekan
    Date with timestamp parameter in BI publisher report 12c
    Topic posted October 11, 2019 by Srekan Red Ribbon: 250+ Points, tagged BI Publisher, Business Intelligence 
    Title:
    Date with timestamp parameter in BI publisher report 12c
    Summary:
    datetime parameters passing in BI publisher report
    Content:

    Hi All,

    i am creating datewithtime parameterized report.

    i have database time column(xx_time) and the data as in the format of  10-OCT-19 09.45.22.846000000 AM  -------------> it is in (CST) format. But i have to show in EST format. For that i have used CAST(FROM_TZ(CAST(xx_ba.xx_time AS TIMESTAMP), 'CST') at TIME zone 'America/New_York' AS DATE) AS TIMEST. its working here.

    now i have to fetch records on TIMEST (which is calcualted column)  between start date and enddate parameters. whereas parameters datetime format as 10/13/2019 10:30:15 AM (MM/dd/yyyy hh:mi:ss AM).

    i have used query as

    SELECT xx_id,
        xx_time,
        TIMEST
        
      FROM
        (SELECT xx_ba.xx_id,
          xx_ba.xx_time,
      CAST(FROM_TZ(CAST(xx_ba.xx_time AS TIMESTAMP), 'CST') at TIME zone 'America/New_York' AS DATE) AS TIMEST
          
        FROM xx_ba xx_ba
        )
      WHERE TIMEST >= to_date(to_char(:startDate, 'MM/dd/yyyy hh:mi:ss AM'),'MM/dd/yyyy hh:mi:ss AM')
      AND TIMEST <= to_date(to_char(:endDate, 'MM/dd/yyyy hh:mi:ss AM'), 'MM/dd/yyyy hh:mi:ss AM')

    throwing error

    ORA-01722: invalid number
    01722. 00000 -  "invalid number"
    *Cause:    
    *Action:

     

    tried different options but no luck, Any help on this really appreciated.

    Regards,

    Joes

  • Srekan
    Date with time data changes after exporting to Excel from BI...2
    Topic posted October 7, 2019 by Srekan Red Ribbon: 250+ Points, tagged BI Publisher, Business Intelligence 
    Title:
    Date with time data changes after exporting to Excel from BI publisher report - using NON RTF method
    Summary:
    Time data changes after exporting to Excel from BI publisher report - not used rtf template
    Content:

    Hello everyone,

    i have date data format in the database as 07-OCT-19 12.22.29.994000000 PM. (oracle database).  

    created datamodel and report  based on above date field. properties set like 

    formating mask: MM/dd/yyyy hh:mm:ss tt

    formatting style: Microsoft

    ignore user timezone: True.

    once i set above properties, in the report editor the date value displaying correctly as 10/07/2019 12:22:29 PM

    Whereas once i generate the report and view in the excel output its displaying time as 10/07/2019 06:22:29 PM

    why this time is changing? how to avoid this issue? please help me on this really appreciate it.

    P.S. i am not using rtf template method design. in 11g getting properly values wheras in 12c getting issue.

    Regards,

    Joe

  • Srekan
    BI Publisher SQL - Date parameter wrong data2
    Topic posted October 2, 2019 by Srekan Red Ribbon: 250+ Points, tagged BI Publisher, Tip 
    Title:
    BI Publisher SQL - Date parameter wrong data
    Content:

    Hi All,

    i  am trying to execute report with data parameters. In oracle database date column data format is 01-OCT-19 01.40.09.857000000 AM.

    whereas in parameter the date is 10/03/2019 (i.e MM/DD/YYYY). Looks like my parameter data is not comparing properly with database format data. please let me know do i need to any conversion? Thanks in advance.

    example:


    SELECT ID,orderdate FROM order
    where orderdate >= '10/02/2019' AND orderdate <= '10/03/2019' order by orderdate ASC

    i have to get results for  date 10/02 and 10/03 records only with timestamps.

    Regards,

    Joes