OraFAQ Forum: Reports & Discoverer » Report Request/Report Builder have different output

Home » Developer & Programmer » Reports & Discoverer » Report Request/Report Builder have different output

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Report Request/Report Builder have different output [message #187058]

Thu, 10 August 2006 13:06

annagel
Messages: 220
Registered: April 2006

Senior Member

I have a report that is working fine when I test it in Report Builder, but when I copy it to our DB server, register it as a request and run it I get different results.

What the report does. The report produces an internal form that shows a change in our HR system. Each action report has 5 slightly different copies. Also, because many actions are actually grouped together by us the report is run for batches of actions. So you should get 5 copies for action 1, 5 for action 2 and so forth. To do this I use the following base query:

SELECT   *
    FROM (SELECT 'COPY A - PERSONNEL' COPY, cs3_id, seq_no, person_id, cs3_type
            FROM ridot.ridot_cs3_internal_actions
           WHERE batch_name = :batch_name
          UNION ALL
          SELECT 'COPY B - ACCOUNTS & CONTROL' COPY, cs3_id, seq_no, person_id, cs3_type
                 seq_no
            FROM ridot.ridot_cs3_internal_actions
           WHERE batch_name = :batch_name
          UNION ALL
          SELECT 'COPY C - EMPLOYEE' COPY, cs3_id, seq_no, person_id, cs3_type
            FROM ridot.ridot_cs3_internal_actions
           WHERE batch_name = :batch_name
          UNION ALL
          SELECT 'COPY D - DEPARTMENT' COPY, cs3_id, seq_no, person_id, cs3_type
            FROM ridot.ridot_cs3_internal_actions
           WHERE batch_name = :batch_name
          UNION ALL
          SELECT 'COPY E - INITIATING' COPY, cs3_id, seq_no, person_id, cs3_type
            FROM ridot.ridot_cs3_internal_actions
           WHERE batch_name = :batch_name)
ORDER BY person_id, seq_no, COPY

From this query, the copy column goes straight to the report and is used to determine all the conditional formatting between each copy of the report. The others are used by a formula column to query up additional data which is placed in placeholder columns.

When I run this in preview mode, or outputting to a pdf in report builder or reports runtime I get exactly what I would expect. When I run it as a request in the server, all the display data from my placeholder columns is exactly the same throughout each action in the batch. So for test batch I am running which consists of 6 actions, I get 30 pages all with exactly the same data in all the placeholder columns when I should have 6 sets of data each with 5 copies.

I have modified my formula 'value setting' column so that it now also returns the cs3_id value from the query above and displayed this on the report and it does appear correctly meaning fresh data is getting queried.

Also looking at what data is sticking around the data on every action is the data that should be showing up for the last action in the report.

Sorry this got a bit long, I just wanted to be thorough in describing the way the report is set up and what is going wrong.

Thanks for any help,
Andrew

EDIT: Version info:

Report Builder 6.0.8.27.0
Oracle Applications : 11.5.8

[Updated on: Thu, 10 August 2006 13:13]

Report message to a moderator

Re: Report Request/Report Builder have different output [message #188473 is a reply to message #187058]

Fri, 18 August 2006 13:08

annagel
Messages: 220
Registered: April 2006

Senior Member

We have been able to narrow this problem down to a much greater degree, though we are still not sure why this is failing, we do at least now know when it fails.

We have a sample of the error that goes like this.

First the select statement for the report:

select 'Row 1' val from dual
union all
select 'Row 2' val from dual
union all
select 'Row 3' val from dual
union all
select 'Row 4' val from dual

This populates the column val.

second we have a formula column called formula with the following code:

function formulaFormula return Char is
begin
  set_val;
  prog_pack.set_pack_val;
  :from_formula := :val;
  return :val;
end;

This formula returns the val value back as its own value as well as setting the value of a placeholder column from_formula. Also it calls the procedure set_val which is defined in the report as:

PROCEDURE set_val IS
BEGIN
  :from_procedure := :val;
END;

and calls the procedure set_pack_val defined in the prog_pack package which is defined in the report as follows:

--spec
PACKAGE prog_pack IS
  procedure set_pack_val;
END;

--body
PACKAGE BODY prog_pack IS
  procedure set_pack_val is
  	begin
  	:from_package := :val;
  end;
END;

We display the val, formula, from_formula, from_prcedure, and from_package values in a row across the report. When we run this in report builder or report runtime we get the same value all across the page so:

Row 1 Row 1 Row 1 Row 1 Row 1
Row 2 Row 2 Row 2 Row 2 Row 2
Row 3 Row 3 Row 3 Row 3 Row 3
Row 4 Row 4 Row 4 Row 4 Row 4

When we run the report as a request on the application server, or via the ar60run command line call on the server we get this output instead:

Row 1 Row 1 Row 1
Row 2 Row 2 Row 2 Row 1 Row 1
Row 3 Row 3 Row 3 Row 2 Row 2
Row 4 Row 4 Row 4 Row 3 Row 3

Has anyone experienced anything similar to this? Or does anyone know why we would be getting output like this?

Andrew

Report message to a moderator

Previous Topic:	missing field
Next Topic:	REP-1247: Report contains uncompiled PL/SQL.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 02 07:57:43 CDT 2024