Announcement

Collapse
No announcement yet.

BI Publisher report is pulling in shipments that shouldn't be in the report.

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • BI Publisher report is pulling in shipments that shouldn't be in the report.

    Hi,
    We have an OTM report that uses BI Publisher. When you run the report in OTM, you pass a carrier and a date. The report is pulling in incorrect shipments based on the date. Currently the SQL uses to_char() on both dates to compare them. I also see that BI Publisher says you need to use a Java date format. Could anyone provide any information on what they think my issue may be?
    Thanks!
    Joe

  • #2
    1) In BI Publisher, set the input parameters as Data Type: String and Parameter Type text.

    2) In your query in BI, you will then make the date conversion. Example given below:

    TRUNC(to_date(:P_SHIPMENT_DATE,'YYYY-MM-DD HH24:MI:SS'))

    3) When entering the date in the report from OTM, select "Date/Time" as the Parameter Type in the report configuration

    Essentially, you are passing date/time from OTM, which is then converted to a string in BI Publisher, which is then converted back to a date using the query in BI Publisher. This is the only way I've gotten date input parameters to work properly

    Comment


    • #3
      Thank you ttwam. I will try this out and let you know how it works.
      Best regards,
      Joe

      Comment


      • #4
        Hi ttwam,
        Below is the original logic and update in place for this report. How can I compare the date to a date in the shipment table? It seems after I updated the SQL the reports still mimic each other.
        Thanks,
        Joe

        ORIGINAL - and to_char(sh.update_date,'DD/MM/YYYY') = to_char(:P_L_SHIP_DATE,'DD/MM/YYYY')

        UPDATE - and TRUNC(to_date(sh.update_date,'YYYY-MM-DD')) = TRUNC(to_date(:P_L_SHIP_DATE,'YYYY-MM-DD'))

        Comment

        Working...
        X