6 Replies Latest reply: Jan 29, 2013 12:10 AM by HamidHelal RSS

    Looping through report paramters

    UsmanPK
      Dear All,
      I have a problem regarding report parameters.
      I have given a taskt save report paramters to a table REP_INFO structure blow.

      Report Name, Paramter name, Value, Report Run Date/Time
      ------------------------------------------------------------------------------------

      Now I have 5 report paramters, ORG_ID, P_FDATE,P_TDATE, LOT_NO, LOT_QUANTITY
      I have applied an INSERT query in AFTER PARAMTER FORM TRIGGER.

      Report Trigger: AFTER_PARAMTER_FORM
      ----------------------------------------------------------
      INSERT INTO REP_INFO VALUES (......................)

      ISSUES:
      --------------------------------
      1) Should I have to issue seperate INSERT statements for each paramter?
      2) Problem get worse when want to add three additional paramters to the report. What the solution?
      3) Can't we dynamically refer report paramter name and insert values in a LOOP through using a single INSERT statement????



      Best Regards,
      Usman
        • 1. Re: Looping through report paramters
          yoonas
          Hi,
          What are the column you have in the table?
          If you don't have seperate columns, How are you saving it in the table comma, seperated values or?

          If you have columns for each parameter why don't you insert all the parameters together
          insert into rep_info values(ORG_ID, P_FDATE,P_TDATE, LOT_NO, LOT_QUANTITY)
          
          or --coma seperated
          
          insert into rep_info values(ORG_ID||','|| P_FDATE||','||P_TDATE...etc)
          Regards
          Yoonas
          • 2. Re: Looping through report paramters
            UsmanPK
            Thanks for your reply...But my problem persist........
            I have following columns in the the table and can't change them...

            1) Report Name
            2) Paramter Name
            3) Paramter Value
            4) Report Run Date/Time

            I can only save one paramter per row. What is the possible solution?
            • 3. Re: Looping through report paramters
              yoonas
              I think you better consider changing the design as you will have so much redundant data. For the same report you are saving 'report name', Report Run Date/Time again and again the more parameter you have the more records you are inserting with same data

              What about this
              Insert into rep_info values(col_1,col_2,P_1...)
              Insert into rep_info values(col_1,col_2,P_2...)
              Insert into rep_info values(col_1,col_2,P_3...)
              Insert into rep_info values(col_1,col_2,P_4...)
              • 4. Re: Looping through report paramters
                UsmanPK
                Well....Younus thanks for your suggestion...
                May be we should open this for another possible solution.
                • 5. Re: Looping through report paramters
                  yoonas
                  You are welcome
                  If your post is answered please mark it answered. If any post is correct or helpful mark it accordingly as well :)

                  Edited by: yoonus on Jan 28, 2013 10:00 PM
                  • 6. Re: Looping through report paramters
                    HamidHelal
                    UsmanPK wrote:
                    Well....Younus thanks for your suggestion...
                    May be we should open this for another possible solution.
                    Well,
                    What you want ?
                    1. Do you want to insert all parameter in a single record ?
                    or
                    2. Do you want to insert each record for each parameter ?

                    or
                    3. Only those parameter what are not null ?
                    Or
                    What your want ?

                    Answer
                    For point 1 and 2 may follow the Younus example
                    For point 3
                    may follow
                    if :paramert1 is not null then
                    insert into()
                    values();
                    end if;
                    
                    if :paramer2 is not null then
                    insert into()
                    values();
                    end if;
                    Hope this helps

                    Hamid