Home » Developer & Programmer » Reports & Discoverer » Report Field Value to be Printed Partially in 3 Places (10g)
Report Field Value to be Printed Partially in 3 Places [message #516818] Thu, 21 July 2011 01:52 Go to next message
shanie
Messages: 118
Registered: January 2011
Senior Member
Dear All,

I need to print a value of a report partially at three places.

Example: If Field Value is 12-24-15 then I need to print 12 at One Place then 24 at second Place and 15 at third Place on the same page of a report and omitting -(Hyphen).

Can this be Possible?
Please Help.

Thanks & Regards,
Shanie
Re: Report Field Value to be Printed Partially in 3 Places [message #516821 is a reply to message #516818] Thu, 21 July 2011 01:57 Go to previous messageGo to next message
Littlefoot
Messages: 19520
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There are several options. All of them include SUBSTR + INSTR combinations so that you'd separate three pieces. The first option is to create three fields in query. Another one is to select the original value (12-24-15) in query, and then create three formula columns - each of them would extract only a part of the original value.
Re: Report Field Value to be Printed Partially in 3 Places [message #516824 is a reply to message #516821] Thu, 21 July 2011 02:11 Go to previous messageGo to next message
shanie
Messages: 118
Registered: January 2011
Senior Member
Dear Sir,

I retrieve value through query.
In Formula column what exactly I have to write If you could specify then it would be helpful for me.

Thanks for your instant reply.

Shanie
Re: Report Field Value to be Printed Partially in 3 Places [message #516826 is a reply to message #516824] Thu, 21 July 2011 02:17 Go to previous messageGo to next message
Littlefoot
Messages: 19520
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You only think that it would be helpful. But no - it would be spoonfeeding. Functions I mentioned are described here. Take a look, learn how to use them, write a query which extracts 12, 24 and 15 from 12-24-15.
Re: Report Field Value to be Printed Partially in 3 Places [message #516833 is a reply to message #516826] Thu, 21 July 2011 02:41 Go to previous messageGo to next message
shanie
Messages: 118
Registered: January 2011
Senior Member
At least mentioning the function name will be helpful for me as lot of functions are there.

Thanks
Shanie
Re: Report Field Value to be Printed Partially in 3 Places [message #516836 is a reply to message #516821] Thu, 21 July 2011 02:46 Go to previous messageGo to next message
Littlefoot
Messages: 19520
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Littlefoot wrote on Thu, 21 July 2011 08:57
All of them include SUBSTR + INSTR combinations

Re: Report Field Value to be Printed Partially in 3 Places [message #516975 is a reply to message #516836] Thu, 21 July 2011 10:13 Go to previous messageGo to next message
shanie
Messages: 118
Registered: January 2011
Senior Member
Hi,

I wrote the function in Formula Column but partial value is not coming. The same full value is coming

Query for Report is
select * from dept1 where deptno = :no;


Table dept1 has 3 Fields deptno,dname,loc
dname has values fin-ance,Mar-keting etc.

Code written in Formula column is

function CF_1Formula return Char is
begin
  return substr(:dname,1,instr(:dname,'-',1)-1); 
end;


I'm getting full value of dname.

Please help as how to display partial value of dname

Thanks
Shanie
Re: Report Field Value to be Printed Partially in 3 Places [message #516985 is a reply to message #516975] Thu, 21 July 2011 10:49 Go to previous messageGo to next message
cookiemonster
Messages: 10921
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well problem is not the function:
SQL> SELECT substr('12-24-15',1,instr('12-24-15','-',1)-1) FROM dual; 

SU
--
12

SQL> 

So I can only assume you're not actually displaying the function on the report layout.
Re: Report Field Value to be Printed Partially in 3 Places [message #517153 is a reply to message #516985] Sat, 23 July 2011 01:47 Go to previous messageGo to next message
shanie
Messages: 118
Registered: January 2011
Senior Member
Dear Sir,

Displaying middle value of a variable is causing problem.
As the values are not fixed.It can be single digit also.

Possible Scenarios are 15-9-24,24-34-45,5-9-12,7-12-9,8-23-26

I have done first and last value with the following code.
return substr(:sprmbox_mailbox_key,1,instr(:sprmbox_mailbox_key,'-',1)-1);
return substr(:sprmbox_mailbox_key,-2,instr(:sprmbox_mailbox_key,'-',-1) -1);

How to display middle value through formula column?

Please help!

Regards,
Shanie
Re: Report Field Value to be Printed Partially in 3 Places [message #517163 is a reply to message #517153] Sat, 23 July 2011 09:58 Go to previous messageGo to next message
Littlefoot
Messages: 19520
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You need to study SUBSTR and INSTR a little bit more. It appears that you didn't quite understand how they work.
Re: Report Field Value to be Printed Partially in 3 Places [message #517292 is a reply to message #517163] Mon, 25 July 2011 04:14 Go to previous messageGo to next message
cookiemonster
Messages: 10921
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you use instr to get the position of the 1st and 2nd '-' it is then simple maths to work out the length of the substr you need.
Re: Report Field Value to be Printed Partially in 3 Places [message #517458 is a reply to message #517292] Tue, 26 July 2011 03:22 Go to previous message
shanie
Messages: 118
Registered: January 2011
Senior Member
Thanks for the help.

I got the desired output.

Regards,
Shanie
Previous Topic: Export data from RDF to Excel
Next Topic: TRACEFILE in report10g
Goto Forum:
  


Current Time: Thu Aug 28 09:04:19 CDT 2014

Total time taken to generate the page: 0.14703 seconds