OraFAQ Forum: Reports & Discoverer » Report Field Value to be Printed Partially in 3 Places

Home » Developer & Programmer » Reports & Discoverer » Report Field Value to be Printed Partially in 3 Places (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Report Field Value to be Printed Partially in 3 Places [message #516818]

Thu, 21 July 2011 01:52

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

Report message to a moderator

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

Littlefoot
Messages: 21808
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.

Report message to a moderator

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

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

Report message to a moderator

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

Littlefoot
Messages: 21808
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.

Report message to a moderator

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

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

Report message to a moderator

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

Littlefoot
Messages: 21808
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

Report message to a moderator

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

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

Report message to a moderator

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

cookiemonster
Messages: 13920
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.

Report message to a moderator

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

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

Report message to a moderator

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

Littlefoot
Messages: 21808
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.

Report message to a moderator

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

cookiemonster
Messages: 13920
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.

Report message to a moderator

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

shanie
Messages: 118
Registered: January 2011

Senior Member

Thanks for the help.

I got the desired output.

Regards,
Shanie

Report message to a moderator

Previous Topic:	Export data from RDF to Excel
Next Topic:	TRACEFILE in report10g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 11:44:37 CDT 2024