4 Replies Latest reply: Oct 23, 2012 12:22 AM by 690743 RSS

    Date parameter passed through Form, wrongly taken by Reports

    690743
      hello seniors....

      i am passing 2 date parameter from my Form6i into Report6i .
      format mask of FRM_DATE and UPTO_DATE fields on the form is 'DD/MM/RRRR'
      ADD_PARAMETER(PARAM_ID,'P_FRM_DATE', TEXT_PARAMETER,:FRM_DATE);
      ADD_PARAMETER(PARAM_ID,'P_UPTO_DATE',TEXT_PARAMETER,:UPTO_DATE);

      for example, if i enter FRM_DATE=01/01/2000 and UPTO_DATE=01/01/2050,
      i debug by putting in the message both the dates, they gets displayed correctly...before issuing RUN-REPORT command...
      Message('Lease_yr :'||TO_CHAR(:frm_date,'DD/MM/YYYY')||' '||TO_CHAR(:upto_date,'DD/MM/YYYY'));
      Message(' ');

      but in the report's BEFORE_REPORT trigger, when i debug both the dates, i find that report field P_UPTO_DATE is displayed as 01/01/1950
      SRW.MESSAGE('1001','B4 :'||TO_CHAR(:P_FRM_DATE,'DD/MM/RRRR')||' '||TO_CHAR(:P_UPTO_DATE,'DD/MM/RRRR'));
      and thats causing my report to come as blank....as the date range becomes ambigious...
      reports fields P_FRM_DATE and P_UPTO_DATE format mask is DD/MM/RRRR

      the condition within report SQL is.....
      AND LEASE_UPTO_PERIOD BETWEEN TO_CHAR(:P_FRM_DATE,'DD/MM/YYYY') AND TO_CHAR(:P_UPTO_DATE,'DD/MM/YYYY')

      problem with only P_UPTO_DATE....

      Please if someone can help with this.....

      (Forms [32 Bit] Version 6.0.8.25.2 (Production))
      (Report Builder 6.0.8.25.0)
      DB 10g XE and Windows7

      TY
        • 1. Re: Date parameter passed through Form, wrongly taken by Reports
          tony.g
          Hi

          I would change in the form:

          ADD_PARAMETER(PARAM_ID,'P_FRM_DATE', TEXT_PARAMETER, to_char(:FRM_DATE, 'DD/MM/YYYY HH24:MI'));
          ADD_PARAMETER(PARAM_ID,'P_UPTO_DATE',TEXT_PARAMETER, to_char(:UPTO_DATE, 'DD/MM/YYYY HH24:MI'));

          Then have the dates as text parameters in the report, then where you use them, explicitly convert them back to date format with to_date().

          rgds
          Tony
          • 2. Re: Date parameter passed through Form, wrongly taken by Reports
            O.Developer
            HI

            After reading your question,,interestingly tried myself and find it is working without any issue.

            The key point is .....define date parameter type with formatmast as -DD-MON-RRRR' -- both in forms and report.

            And in Add Paramter lsit use To_Char Function instead of passing directly. This will work as you want.

            ---here is my sample code .... calling from when button pressed





            Declare
                 
                 Pl_Id Paramlist;
                 Pl_Name Varchar2(10) :='Testing';
                 
                 
            Begin
                 
            Pl_Id := Get_parameter_list (pl_name);

            IF NOT Id_Null(pl_id) THEN
            Destroy_Parameter_List( pl_id );
            END IF;


            If Id_Null(PL_Id) Then
                 Pl_Id := Create_Parameter_list(pl_name);
                 
            End If;

            Add_Parameter(pl_id, 'PARAMFORM', TEXT_PARAMETER, 'NO');
            Add_Parameter(pl_id , 'R_frm_dt', TEXT_PARAMETER , TO_CHAR(:ctrl.frm_dt,'DD-MON-RRRR'));
            Add_Parameter(pl_id , 'R_to_dt', TEXT_PARAMETER , to_char(:ctrl.to_dt,'DD-MON-RRRR'));
                 

                 
                 Run_Product (Reports, 'D:\Run_Product_date_Range',SYNCHRONOUS, RUNTIME,FILESYSTEM, PL_ID, NULL);
                 

            End ;
            -------------- Hope it work
            • 3. Re: Date parameter passed through Form, wrongly taken by Reports
              DanielB
              what mask have the parameters or are char?

              Edited by: DanielB on Oct 22, 2012 2:45 PM
              • 4. Re: Date parameter passed through Form, wrongly taken by Reports
                690743
                O.Developer wrote:
                The key point is .....define date parameter type with formatmast as -DD-MON-RRRR' -- both in forms and report.
                And in Add Paramter lsit use To_Char Function instead of passing directly. This will work as you want.
                Add_Parameter(pl_id , 'R_frm_dt', TEXT_PARAMETER , TO_CHAR(:ctrl.frm_dt,'DD-MON-RRRR'));
                Add_Parameter(pl_id , 'R_to_dt', TEXT_PARAMETER , to_char(:ctrl.to_dt,'DD-MON-RRRR'));
                -------------- Hope it work
                Thanks Bro...it worked this time....