Home » Developer & Programmer » Reports & Discoverer » Reference fields in detail group at report level (Oracle report 10g)
icon5.gif  Reference fields in detail group at report level [message #549368] Thu, 29 March 2012 21:55 Go to next message
hnguyen
Messages: 5
Registered: February 2012
Junior Member
Hi Experts,
I am an oracle report beginner. I have a report w/ master group and detail group and at the report level I would like to display a text based on a value of a field in the detail group. I created a column placeholder at the report level and then assigned it a field value in the detail group. I wanted to show my text based on this value, but it did not work. Please tell me how can I make it work.


Thank you very much,

HN
Re: Reference fields in detail group at report level [message #549375 is a reply to message #549368] Fri, 30 March 2012 00:09 Go to previous messageGo to next message
Littlefoot
Messages: 21561
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As usual, it is quite difficult to debug code you can't see. Could you post it?

What does "did not work" mean? Any error? Which one?
Re: Reference fields in detail group at report level [message #549624 is a reply to message #549375] Mon, 02 April 2012 09:23 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 21561
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 #549653 is a reply to message #549651] Mon, 02 April 2012 16:18 Go to previous messageGo to next message
hnguyen
Messages: 5
Registered: February 2012
Junior Member
I found the solution. Here what I did:
In the detail group, I created Formula column(CF_VOC1) like the following:

function CF_VOC1Formula return Number is
begin
if :QUALITY_CLASS = 'VOC' then
return 1;
else
return 0;
end if;

RETURN NULL; exception
when others then return 0;
end;

I created a summary column at report level named CS_NOTE:
Function = Sum
Source = CF_VOC1
Reset At = Page

I created a formula column at report level named CF_NOTE:

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';
else
v_temp := '';
end if;

return v_temp;
RETURN NULL; exception when others then
return '';
end;

And at the report margin I created a field F_NOTE:
Source = CF_NOTE

It works for me.

Thank you very much for your suggestion,
HN
Re: Reference fields in detail group at report level [message #549668 is a reply to message #549653] Tue, 03 April 2012 00:34 Go to previous message
Littlefoot
Messages: 21561
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;
Previous Topic: Convert RDF to REP Problem
Next Topic: change in layout
Goto Forum:
  


Current Time: Fri May 29 06:30:33 CDT 2020