|
|
Re: Reference fields in detail group at report level [message #549624 is a reply to message #549375] |
Mon, 02 April 2012 09:23 |
|
hnguyen
Messages: 5 Registered: February 2012
|
Junior Member |
|
|
Hi,
The report has header and detail and in detail for example: it may return 10 line items that may expand onto 2 pages.
Page: 1/2
Line
Item Quantity UOP Stock Code
* 001 236 EA 000017738
* 002 36 EA 000017739
003 6 EA 000017740
Notes: * represents VOC item.
Page: 2/2
Line
Item Quantity UOP Stock Code
008 136 EA 000017745
009 6 EA 000017746
010 5 EA 000017747
On page 1 there is * next to line item, so at the margin I have
a displayed text "Notes: * represents VOC item.". On page 2 there is no * then I do not want to display anything.
Here is what I did, I had the CP_NOTE at the report level, the display field(F_NOTE) at the bottom of the report(margin section), and in the detail I had the formula like the following:
function CF_VOCFormula return Char is
v_temp char;
begin
if :QUALITY_CLASS = 'VOC' then
v_temp := '*';
:CP_NOTE := 'Notes: * represents VOC item.';
else
v_temp := '';
:CP_NOTE := '';
end if;
return v_temp;
RETURN NULL; exception when others then
return '';
end;
I assigned CP_NOTE to F_NOTE, but all I got was 'Notes: * represents VOC item.' on all pages(page 1 and page 2). Here is what I would like to do when a page load, it should check if there is * then display text otherwise not display text. I think I need to reset :CP_NOTE every time a page load, but I don't know where to put it.
Please help.
Thanks,
HN
|
|
|
Re: Reference fields in detail group at report level [message #549651 is a reply to message #549624] |
Mon, 02 April 2012 15:05 |
|
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Example you provided suggests that a "note" shouldn't be related to a report because "report" is what it says - the WHOLE report. Notes you mention can be displayed on page 1, but not on page 2. This is not a report level, but page level. I don't have Reports here so I can't test it; however, I can't remember whether it is possible to decide whether to display something (or not) based on page contents. It would be simpler if you could decide that based on a group, for example.
I have an idea, though - if possible, create that note regardless you're going to display it or not. That can be decided in field's Format Trigger, which returns Boolean. In there you could do something like this:
return (:quality_class = 'VOC'); So, if quality_class equals VOC, it would return TRUE and the field would be displayed. Otherwise, it wouldn't.
|
|
|
|
Re: Reference fields in detail group at report level [message #549668 is a reply to message #549653] |
Tue, 03 April 2012 00:34 |
|
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
I see; you reset summary column at page.
I'm glad you made it work!
However, if I may, I'd like to rewrite your code, just to make it a little bit simpler.
First of all, avoid WHEN OTHERS exception handler unless you know what you are doing. It is unnecessary in the first function (CF_VOC1 - it will never be raised here), while the second one (CF_NOTE2) requires other handlers, such as NO-DATA-FOUND or TOO-MANY-ROWS. WHEN OTHERS causes many problems as it obfuscates what's going on and, when you find your code "not working" but no error is raised, you are really puzzled. For some more reading, see Wiki.
function CF_VOC1Formula return Number is
begin
return case when :quality_class = 'VOC' then 1
else 0
end;
end;
function CF_NOTE2Formula return Char is
v_temp varchar2(2000);
begin
if :CS_NOTE > 0 then
select 'NOTE: ' || STANDARD_NOTE_DESC
into v_temp
from SA_STANDARD_NOTES
where standard_note_id = 'PO-VOC'
and plant = '01';
end if;
return v_temp;
exception
when no_data_found then
return null;
-- If SA_STANDARD_NOTES table contains only a single record for each NOTE_ID,
-- TOO_MANY_ROWS can't be raised. If it can, what do you want to return, then?
-- If you know, handle it! If not, and for all other possible errors, let Oracle
-- raise an exception.
end;
|
|
|