1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Report gives correct output in previewer but not when deployed

Discussion in 'Oracle Forms and Reports' started by rohan_de_silva, Feb 19, 2009.

  1. rohan_de_silva

    rohan_de_silva Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    We have a very unusual problem (For Reports 10g Release1).

    A report we developed gives correct output when run in the report designer's previewer (paper layout).

    But when we deploy the report in the web server, give the same inputs and run it it gives the wrong output. I.e. it shows extra rows that should not be there.

    Why do you think this is?

    We are unable to locate the problem source.

    Any help will be greatly appreciated.

    Regards,

    Rohan
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Hi, I presume you have developed the report then try running your report Query remotely on the database. Connect too SQL Plus or any client you use and run your report query by hardcoding the parameters. Check out the number of rows you are getting, that will tell you whether the issue is with your code or report designer.
     
  3. rohan_de_silva

    rohan_de_silva Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    This is not possible. The report's data model consist of the a 3-tire master/detail query.

    The lowest level query is shown below. It uses a user parameters :)P_TH_VAL) which is actually NOT passed by to the report, BUT calculated while the query is being run by a formula.

    Regards,
    Rohan
    ---------
    Code (Text):

    (SELECT   roamer_code, imsi, service_code,
              SUM (NVL (call_duration, 0)) call_duration,
              SUM (NVL (data_vol_in + data_vol_out, 0)) data_volume,
              COUNT (service_code) sms_count,
              SUM (NVL (total_units, 0)) total_units,
              SUM (NVL (charge, 0.0)) charge
         FROM nr_incall_events
        WHERE roamer_code LIKE :code
          AND service_code LIKE :service_code1
          AND calldate >= TO_DATE (:p_from_date, 'dd/mm/yyyy')
          AND calldate <= TO_DATE (:p_to_date, 'dd/mm/yyyy') + 1
     GROUP BY roamer_code, imsi, service_code
       HAVING SUM (NVL (call_duration, 0)) >= :p_th_val
     UNION
     SELECT   roamer_code, imsi, service_code,
              SUM (NVL (call_duration, 0)) call_duration,
              SUM (NVL (data_vol_in + data_vol_out, 0)) data_volume,
              COUNT (service_code) sms_count,
              SUM (NVL (total_units, 0)) total_units,
              SUM (NVL (charge, 0.0)) charge
         FROM nr_incall_events
        WHERE roamer_code LIKE :code
          AND service_code LIKE :service_code1
          AND 1 <= NVL (call_duration, 0)
          AND calldate >= TO_DATE (:p_from_date, 'dd/mm/yyyy')
          AND calldate <= TO_DATE (:p_to_date, 'dd/mm/yyyy') + 1
     GROUP BY roamer_code, imsi, service_code
       HAVING SUM (NVL (data_vol_in + data_vol_out, 0)) >= :p_th_val
     UNION
     SELECT   roamer_code, imsi, service_code,
              SUM (NVL (call_duration, 0)) call_duration,
              SUM (NVL (data_vol_in + data_vol_out, 0)) data_volume,
              COUNT (service_code) sms_count,
              SUM (NVL (total_units, 0)) total_units,
              SUM (NVL (charge, 0.0)) charge
         FROM nr_incall_events
        WHERE roamer_code LIKE :code
          AND service_code LIKE :service_code1
          AND calldate >= TO_DATE (:p_from_date, 'dd/mm/yyyy')
          AND calldate <= TO_DATE (:p_to_date, 'dd/mm/yyyy') + 1
     GROUP BY roamer_code, imsi, service_code
       HAVING COUNT (service_code) >= :p_th_val)
    ORDER BY imsi;
     
  4. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    there's nothing in your query to suggest anything is wrong. Tell me are you just getting extra blank space or extra data rows. If it's extra data rows obviuosly the query is returning extra data from the database. When you are previewing it, is it on the same database instance where you deploy it?
     
  5. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    hi can you not come up with the result of :p_TH_VAL calculation and pass it to the query and run it and see how many rows are being passed?
     
  6. rohan_de_silva

    rohan_de_silva Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    We get extra data lines.

    Oh, yes, we are using the dame DB for both.

    Please check out the screenshot I uploaded (scr 4.jpg).

    The circled ones are extra and should not be there because they are below the threshold value. In the designer previewer these lines do not come.
     

    Attached Files:

  7. rohan_de_silva

    rohan_de_silva Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    This is not possible. The value of :p_TH_VAL changes from row to row by a the forumula CF_set_thr_val which is in the midel group.

    The calculation function for the formula is:
    Code (Text):

    function CF_set_thr_valFormula return Number is
    begin
      IF :service_code1 = '00' THEN
        :p_th_val := :p_th_basic;
      ELSIF :service_code1 = 'IDD' THEN
        :p_th_val := :p_th_idd;
      ELSIF :service_code1 = 'GPRS' THEN
        :p_th_val := :p_th_gprs;
      ELSIF :service_code1 LIKE 'SMS%' THEN
        :p_th_val := :p_th_sms;
      END IF;  
     
      RETURN 1;

    end;
     

    Attached Files:

  8. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
  9. rohan_de_silva

    rohan_de_silva Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    We finally solved it.

    Problem was we were using a ''User Parameter'' (which did not actually get any input from when the report was being called) as a placeholder column.

    Once we removed it and put a placeholder in place of that it worked fine both in developer and server.

    This goes to show that we should not use user parameters to do calculations in the report.

    Thanks for your inputs.

    Really appreicate it.
     
  10. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Glad you solved it :) and thanks for sharing for the benefit of others!