Home » Developer & Programmer » Reports & Discoverer » Reports: how to find page number and total number of pages? (windows xp, reports 6i)
Reports: how to find page number and total number of pages? [message #440773] Tue, 26 January 2010 23:46 Go to next message
ka_wish
Messages: 87
Registered: October 2007
Location: karachi
Member

Dear all my friends

Please guide me and give me query for this type

i am ussing oracle developer 6i report builder i required this type of query

exmple

if (:page number LIKE '1')
then
srw.set_text_color('darkred');
end if;

return (TRUE);
end;

but page number is not my table database item how can i use builtan page &<pagenumber> use for conditional format.

thanks
please reply fast everyone.

[EDITED by LF: modified topic title. The original was "Oracle Report Developer 6i (urgent Help)"]

[Updated on: Thu, 28 January 2010 02:48] by Moderator

Report message to a moderator

Re: Oracle Report Developer 6i (urgent Help) [message #440816 is a reply to message #440773] Wed, 27 January 2010 03:17 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Create a format trigger; this example shows a trigger on a field:
function F_enameFormatTrigger return boolean is
  l_page_num number;
begin
  srw.get_page_num (l_page_num);
  
  if l_page_num = 1 then
     srw.set_text_color('darkred');
  end if;

  return (true);
end;
Re: Oracle Report Developer 6i (urgent Help) [message #440832 is a reply to message #440816] Wed, 27 January 2010 05:33 Go to previous messageGo to next message
ka_wish
Messages: 87
Registered: October 2007
Location: karachi
Member

thanks sir
2nd question is required

Please how can define query urgently reply please.

Exmple
Create a format trigger; this example shows a trigger on a field:
function F_enameFormatTrigger return boolean is
l_page_num number;
begin
srw.get_page_num (l_page_num);

if l_page_num = 'Last Page' then
srw.set_text_color('darkred');
end if;

return (true);
end;
Re: Oracle Report Developer 6i (urgent Help) [message #440838 is a reply to message #440832] Wed, 27 January 2010 05:47 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"Last page"? I'm not sure it exists. It is something like Yeti - there are rumors that someone saw it, but there's no evidence.

The same goes for the last report page - you don't know it, until the report reaches it. As there's no SRW "get_total_number_of_pages", I really wouldn't know how to do that. As I've said - now and then people ask the same question, but I didn't see an answer. You can search the board, though, perhaps you'll find something useful.

As of your "urgent" questions - come on, this is just a forum. You might, or might not get the answer - today, tomorrow, next week, never - who can tell? For "urgent" problems contact Oracle Support or hire a consultant.
Re: Oracle Report Developer 6i (urgent Help) [message #440844 is a reply to message #440832] Wed, 27 January 2010 05:59 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Last Page

last page
last page

sriram Smile
Re: Oracle Report Developer 6i (urgent Help) [message #440965 is a reply to message #440844] Thu, 28 January 2010 02:21 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you, @ramoradba! It seems that Metalink note 130432.1 solves the problem. As it is protected by copyright, we can't attach it here. However, I spent some time trying to follow instructions and - here it goes, for future reference (so that I won't have to lie about us being unable to do that).

I used Reports Developer 10g, connected to Oracle database 10g.


Using a Wizard, I created a „dummy" report which spans several pages (as I need more than a single-page-report); this is a Scott's schema based Cartesian product on EMP table:
select e1.ename, e2.job
from emp e1, emp e2

The objective is to display employee name in dark red color on the last report page. Something like this:
if page_number = last_page then
   paint(ename, dark red);
end if;



In Object Navigator, create a package. Metalink document also suggests use of a table which stores session ID as well as total number of pages, but I chose a package instead.

/forum/fa/7361/0/

A package variable will contain total number of pages:
package pkg_page_num is
  p_tpn number;     -- "tpn" stands for "total pages number"
end;



In Paper Layout editor, create additional field, „F_total_pages" whose source is „Total Pages".

/forum/fa/7362/0/


Now create Format Trigger for the „F_total_pages" field:
function F_total_pagesFormatTrigger return boolean is
  l_page_num number;
begin
  srw.get_page_num(l_page_num);
  
  pkg_page_num.p_tpn := nvl(pkg_page_num.p_tpn, l_page_num);
  
  return (TRUE);
end;

What does it do:
  • create a local variable (l_page_num) which will contain page number
  • Page number will be retrieved with SRW built-in package and its GET_PAGE_NUM function
  • Store page number into the package variable.
    NVL function differs from Metalink description; if NVL is not used, total number of pages is wrong and it appears that „page number" is never equal to „total number of pages". I've checked what was going on (using the SRW.MESSAGE), and total number of pages was changing its value when scrolling through report pages, but was never (except for the first report run) correct.
    On the other hand, if a table was used, we'd store „total number of pages" into that table and select MAX value from a table. If that was the case, NVL wouldn't be necessary. Another option might be use of the GREATEST function. Obviously, there are variations to the solution, which is marvelous! It would be a shame if there was none.

Next, ENAME field's format trigger (as we want to paint it red):
function F_enameFormatTrigger return boolean is
  l_page_num number;
begin
  srw.get_page_num(l_page_num);
  
  if l_page_num = pkg_page_num.p_tpn then
     srw.set_text_color('darkred');
  end if;
  
  return (TRUE);
end;

What does it do?
  • create a local variable (l_page_num) which will contain page number
  • if it is equal to the „last page number" (which is now stored in the package variable, "pkg_page_num.p_tpn"), do „something" (for example, paint values red)

That's all; now run the report: on page 4/5, ENAME values are black.

/forum/fa/7363/0/

On the last page (5/5), they are red.

/forum/fa/7364/0/

Re: Oracle Report Developer 6i (urgent Help) [message #440966 is a reply to message #440965] Thu, 28 January 2010 02:30 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Thank you little Foot for spending time to solve it....
Of course..We should not post that here...But here it is on the web right.....thats Why i posted three..

1) from this forum only giving the clue based the metalink content.
2) from dba-village giving the clue based the same metalink content.
But today i refered this for a new member for the same kind of problem he said...He was unable to follow that(insted of last of he want total no of pages).

So i hope this workaround will help that person ....so that Now i will reply that person....to point this message.


Thank you once again for the work around.Basically i am a lazy guy thats Why all my post are like Web pages.... Wink

THANK YOU ONCE AGAIN

sriram Smile
Re: Oracle Report Developer 6i (urgent Help) [message #440970 is a reply to message #440966] Thu, 28 January 2010 02:44 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not complaining about other people / sites / forums posting material they (probably) shouldn't - I said that WE can't do that on OraFAQ. That's why I created my own test case and described what I did to solve the problem.

Besides, my post is much more colorful than Metalink's dull Courier document. But hey, it doesn't matter how pretty it is, what matters is that it helps. Right?
Re: Oracle Report Developer 6i (urgent Help) [message #440975 is a reply to message #440970] Thu, 28 January 2010 03:15 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
/forum/fa/1581/0/

/forum/fa/2115/0/

And One more...

Quote:
[EDITED by LF: modified topic title. The original was "Oracle Report Developer 6i (urgent Help)"]


You modified the title to
Reports: how to find page number and total number of pages?
But still we are getting Oracle Report Developer 6i (urgent Help) ?



sriram Smile

[Updated on: Thu, 28 January 2010 03:20]

Report message to a moderator

Re: Oracle Report Developer 6i (urgent Help) [message #440981 is a reply to message #440965] Thu, 28 January 2010 03:39 Go to previous messageGo to next message
ka_wish
Messages: 87
Registered: October 2007
Location: karachi
Member

I TRIED YOUR QUERY BUT NOT WORKING PROPERLY
DEFINE ALL QUERY PERFECTLY BUT RESULT SAME SHOW TOTAL OF NUMBER PAGE PERFECTLY.

ALWAYS QUERY WORK
PAGE 1 ONE NOT WORKING FOR LAST PAGE LIKE PAGE NO 3

ANY REASON
I AM USING ORACLE 6I DEVELOPERS
Re: Oracle Report Developer 6i (urgent Help) [message #440983 is a reply to message #440981] Thu, 28 January 2010 03:42 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Sorry, I don't quite understand what works and what does not. Also, I don't have Reports 6i so I can't test that version. I'll, though, attach 10g RDF file - perhaps you (or someone near you) can open it so that you'd see how it was done.

Re: Oracle Report Developer 6i (urgent Help) [message #440987 is a reply to message #440983] Thu, 28 January 2010 04:05 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
he saying that the code used by you is working for only at the first page(first page darked) not at the last page. let me try that ....

sriram Smile
Re: Oracle Report Developer 6i (urgent Help) [message #440988 is a reply to message #440983] Thu, 28 January 2010 04:06 Go to previous messageGo to next message
ka_wish
Messages: 87
Registered: October 2007
Location: karachi
Member

ok thanks

talk you after check this report.
Re: Oracle Report Developer 6i (urgent Help) [message #440989 is a reply to message #440988] Thu, 28 January 2010 04:15 Go to previous messageGo to next message
ka_wish
Messages: 87
Registered: October 2007
Location: karachi
Member

SIR I CHECKED YOUR REPORT WORKING ONLY PAGE-1 PLEASE SEE THE ATTACHED PICTURE.
MY PROBLEM NOT BE SOLVE.
ANY REASON.
THANKS
Re: Oracle Report Developer 6i (urgent Help) [message #440990 is a reply to message #440989] Thu, 28 January 2010 04:21 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
No it worked i tried the same in report 6i.

see the attached.

sriram Smile

Re: Oracle Report Developer 6i (urgent Help) [message #440991 is a reply to message #440990] Thu, 28 January 2010 04:24 Go to previous messageGo to next message
ka_wish
Messages: 87
Registered: October 2007
Location: karachi
Member

I KNOW BUT IT WORKING ONLY FIRST PAGE BUT I REQUIRED TO LAST PAGE FIRE TRIGGER OR FUNCTION LIKE TOTAL_NUMBER_PAGE.JPG PLEASE ATTACHED IMAGE.
THANKS
Re: Oracle Report Developer 6i (urgent Help) [message #440992 is a reply to message #440991] Thu, 28 January 2010 04:28 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
What i mean to say is it worked perfectly for me tested on reports 6i.

ITS working.. run the RDF....

sriram Smile
Re: Oracle Report Developer 6i (urgent Help) [message #440993 is a reply to message #440992] Thu, 28 January 2010 04:31 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
for the last page only its giving the colored page observe properly.....

IF not set a message at format trigger to display your package return value......
if it is always 1 you should get that colored at page 1...
again re compile that..it should work then....

The same thing happened here...


Good luck sriram Smile
Re: Oracle Report Developer 6i (urgent Help) [message #440994 is a reply to message #440992] Thu, 28 January 2010 04:32 Go to previous messageGo to next message
ka_wish
Messages: 87
Registered: October 2007
Location: karachi
Member

I KNOW ARE YOU TESTED BUT PLEASE CHECK THE ATTACHED JPG THEN YOU UNDERSTAND WHAT I SAY.

THANKS
Re: Oracle Report Developer 6i (urgent Help) [message #440995 is a reply to message #440994] Thu, 28 January 2010 04:36 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
PLease run the RDF posted by me .....

Replace the sriram with emp.

And post your comments...Re read my message again.

Whats the value returned by your package?

sriram Smile
Re: Oracle Report Developer 6i (urgent Help) [message #440996 is a reply to message #440995] Thu, 28 January 2010 04:41 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
if you still did n`t get the answer..

IT is your wish what to do.
Atleast re read the content from this first link (MY reply to your Question.)

sriram Smile
Re: Oracle Report Developer 6i (urgent Help) [message #440998 is a reply to message #440995] Thu, 28 January 2010 04:45 Go to previous messageGo to next message
ka_wish
Messages: 87
Registered: October 2007
Location: karachi
Member

OTHER ANY ONE SLOVE THIS PROBLEM
PLEASE SEE ATTACHMENT OF .JPG

VALUE OF TOTAL PAGE SHOW AND

IF TOTAL_PAGE_NO=4 THEN -------(LAST-PAGE)----

ENAME READ COLOR

IF TOTAL_PAGE_NO = 5 THEN
ENAME READ COLOR

THANKS
KAWISH

Re: Oracle Report Developer 6i (urgent Help) [message #440999 is a reply to message #440965] Thu, 28 January 2010 04:49 Go to previous messageGo to next message
ka_wish
Messages: 87
Registered: October 2007
Location: karachi
Member

The objective is to display employee name in dark red color on the last report page. Something like this:

THIS QUERY WHERE INSERT

if page_number = last_page then
paint(ename, dark red);
end if;


[EDITED by LF: removed quote of the entire message that contains walkthrough]

[Updated on: Thu, 28 January 2010 05:11] by Moderator

Report message to a moderator

Re: Oracle Report Developer 6i (urgent Help) [message #441001 is a reply to message #440999] Thu, 28 January 2010 05:20 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
can you please post your version number(4 digit).
seems it`s a Bug in oracle reports 6i.

So that... i will try.
sriram Smile
Re: Oracle Report Developer 6i (urgent Help) [message #441002 is a reply to message #441001] Thu, 28 January 2010 05:28 Go to previous messageGo to next message
ka_wish
Messages: 87
Registered: October 2007
Location: karachi
Member

Report Builder 6.0.8.8.3
Re: Oracle Report Developer 6i (urgent Help) [message #441019 is a reply to message #441001] Thu, 28 January 2010 06:16 Go to previous messageGo to next message
ka_wish
Messages: 87
Registered: October 2007
Location: karachi
Member

I HAVE SUCCESSFULLY DONE MY SELF FROM MY PROBLEM

THANKS TO MY ALL FRIENDS
KAWISH
CHECKED MY RDF ATTACHMENT WHAT I REQUIRED.
Re: Oracle Report Developer 6i (urgent Help) [message #441021 is a reply to message #441019] Thu, 28 January 2010 06:22 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
congrats.
Thanks for the feedback.

sriram Smile
Re: Oracle Report Developer 6i (urgent Help) [message #441027 is a reply to message #441021] Thu, 28 January 2010 06:31 Go to previous messageGo to next message
ka_wish
Messages: 87
Registered: October 2007
Location: karachi
Member

use some my logic then result found. editing for report then perfectly result show.


ramoradba wrote on Thu, 28 January 2010 17:22
congrats.
Thanks for the feedback.

sriram Smile

Re: Oracle Report Developer 6i (urgent Help) [message #441028 is a reply to message #441027] Thu, 28 January 2010 06:33 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
if it worked for you then it`s not necessary to work for every one...finally you got the solution.that's it.nothing more than that(which one already worked for us).
so.
And i don`t wanna argue in this topic more.
sriram

[Updated on: Thu, 28 January 2010 06:35]

Report message to a moderator

Re: Oracle Report Developer 6i (urgent Help) [message #441029 is a reply to message #441019] Thu, 28 January 2010 06:34 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ka_wish wrote on Thu, 28 January 2010 13:16
I HAVE SUCCESSFULLY DONE MY SELF FROM MY PROBLEM

You must be kidding, right?

Nothing of how to find the total number of pages adventure was used in your "solution". Because, it solves absolutely nothing. You use a parameter that represents page number (whose value must be entered by end user) and then color fields on a page specified by that parameter.

In order to color fields on the last page, you have to run the report twice: once to find out how many pages report has, and the second time to enter that very number /forum/fa/4706/0/.

I don't know what to say. So I'd rather keep my mouth shut /forum/fa/1599/0/.
Re: Oracle Report Developer 6i (urgent Help) [message #441034 is a reply to message #441029] Thu, 28 January 2010 06:42 Go to previous messageGo to next message
ka_wish
Messages: 87
Registered: October 2007
Location: karachi
Member

yes just kidding 2 days i was upset for this query or logic how can i get total pages and auto last page field colored but here not any one solved my proble.
Re: Oracle Report Developer 6i (urgent Help) [message #441038 is a reply to message #441034] Thu, 28 January 2010 06:46 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
And i am sure..you wont get..
if you don`t look at the logic provided by little foot and metalink note.

Good luck
sriram Smile
Re: Oracle Report Developer 6i (urgent Help) [message #441082 is a reply to message #441034] Thu, 28 January 2010 10:20 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Unfortunately, there's no SRW.GET_LAST_PAGE_NUM. Therefore, we have to use workaround. It is described on Metalink. I tried to do that for you - and it seems that it works just fine, at least on my side (true, on different Reports version).

If there's another workaround, I don't know about it. I didn't even know about this one, until @ramoradba pointed it out. If you ever find it out, I hope you'll share it with us. But entering number of a page you want to paint red is simply stupid (sorry for the word, but that it is). I believe that you were upset; however, a parameter based solution is anti-solution.

I guess we came in front of the wall.
icon14.gif  Re: Oracle Report Developer 6i (urgent Help) [message #448169 is a reply to message #441082] Sat, 20 March 2010 00:44 Go to previous messageGo to next message
wazir
Messages: 18
Registered: February 2010
Junior Member
Hello To all Members...

I Hope All of you will be fine..

A few days before i was faceing same problem..means i wants to print the signatures of manager on last page..I try alot but not successed.. then i start serach and i found this thread...


Yes i found the solution from here and i willl thankx all the members who share their knowledge and i will specially thankx Littlefoot who realy spend his valueable time for this problem..

I do nothing but just fellow his steps...

I will thankx once again all the members...

regards
Wazir
Re: Oracle Report Developer 6i (urgent Help) [message #555196 is a reply to message #440965] Tue, 22 May 2012 05:00 Go to previous messageGo to next message
kame
Messages: 69
Registered: July 2009
Member
I tried this in my Report 6i (Report Builder 6.0.8.8.3)
but not working accordingly. always pkg return 1.

SRW.MESSAGE(0,'Package Value = '||pkg_page_num.p_tpn);

always returning 1 ????
Re: Oracle Report Developer 6i (urgent Help) [message #670704 is a reply to message #440965] Sat, 21 July 2018 04:12 Go to previous message
lispy
Messages: 1
Registered: July 2018
Junior Member
helo sir,
when i run the report it gives me the effect in the first page only i need it to be happen on the last page
Previous Topic: REP-0788 Restricted LOV error, on reports 11g
Next Topic: downgrade my Database Version 11g to 10g, After Downgrade Is there Any impact on My Forms
Goto Forum:
  


Current Time: Thu Mar 28 10:52:31 CDT 2024