Home » Developer & Programmer » Reports & Discoverer » Report Request/Report Builder have different output
Report Request/Report Builder have different output [message #187058] |
Thu, 10 August 2006 13:06 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
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
|
|
|
Goto Forum:
Current Time: Sun Aug 18 18:04:16 CDT 2024
|