Home » Developer & Programmer » Reports & Discoverer » How can I create the dynamic column for the report
How can I create the dynamic column for the report [message #229062] Thu, 05 April 2007 04:26 Go to next message
zhan200012
Messages: 11
Registered: December 2006
Location: China
Junior Member
Now I want to create the column that is dynamic changed for the custom condition in the report!

How to come true?
Re: How can I create the dynamic column for the report [message #229188 is a reply to message #229062] Thu, 05 April 2007 15:36 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is a "custom condition"? How do you plan to implement it? Using a parameter, perhaps?

I'm not sure I understood what you'd like to do, but it smells like you'd have to use a formula column. Basically, it is a user-defined PL/SQL function which allows you to calculate whatever you need, return a value into the item and display it in report.

If this is not what you are looking for, could you, please, explain it again?
Re: How can I create the dynamic column for the report [message #229199 is a reply to message #229062] Thu, 05 April 2007 22:34 Go to previous messageGo to next message
zhan200012
Messages: 11
Registered: December 2006
Location: China
Junior Member
For example.
I have three column in the table as A,B,C!
Now I'm using a parameter as P_A that have contacted with the A column!

When I input the "a" to P_A is shown the A column and hiden other column in the report,or "b" is shown the B column etc.

The result must display in the report.

Do you understand?

I from Asia! So my English is poor!
Re: How can I create the dynamic column for the report [message #229203 is a reply to message #229199] Fri, 06 April 2007 00:17 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh, but your English is much better than my Chinese Smile

I think I understand what bothers you; but, no problem - it can easily be solved. While in Paper Layout, click the item A and press F4. It will open its Property Palette. Select Format Trigger which would then look like this:
function AFormatTrigger return boolean is
begin
  return (:P_A IS NOT NULL); 
end;
As this function returns Boolean, it checks whether parameter P_A value is null. If not, it returns TRUE so item A gets displayed.

Do the same for the rest of your items.
Re: How can I create the dynamic column for the report [message #229480 is a reply to message #229062] Sun, 08 April 2007 20:37 Go to previous messageGo to next message
zhan200012
Messages: 11
Registered: December 2006
Location: China
Junior Member
Thank you!

This is a good idea!

If I want to set the A,B,C column into the same field in the report. How to come true?

For example!
I input the "a" to P_A and the field is shown the A column,
or input the "b" to P_A and the field is shown the B column,etc.
Re: How can I create the dynamic column for the report [message #229826 is a reply to message #229480] Tue, 10 April 2007 02:30 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You could do that using such an example: if parameter value = 'a', return department number; if it is 'b', return department name.
SQL> select
  2    decode('&p_a', 'a', to_char(deptno),
  3                   'b', dname
  4          ) result
  5  from dept;

Enter value for p_a: a
old   2:   decode('&p_a', 'a', to_char(deptno),
new   2:   decode('a', 'a', to_char(deptno),

RESULT
----------------------------------------
10
20
30
40

SQL> /
Enter value for p_a: b
old   2:   decode('&p_a', 'a', to_char(deptno),
new   2:   decode('b', 'a', to_char(deptno),

RESULT
----------------------------------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS

SQL>

As I don't know what kind of data you are going to return (I mean column A, B and C datatypes), I can only say that you'll have to take care about the fact that the result of the DECODE function has a datatype of the first column you select. In my example, it is DEPTNO - as it is a NUMBER, DECODE expects that the rest of the columns are also numbers. But, as DNAME is a VARCHAR2 column, query would return an INVALID NUMBER error. That's why I used TO_CHAR function with the 'deptno' column - to unify the result datatypes.

Basically, such an approach should work correctly.
Re: How can I create the dynamic column for the report [message #230103 is a reply to message #229062] Tue, 10 April 2007 21:12 Go to previous messageGo to next message
zhan200012
Messages: 11
Registered: December 2006
Location: China
Junior Member
Ok!Thanks!

Let me try!
Re: How can I create the dynamic column for the report [message #230104 is a reply to message #229062] Tue, 10 April 2007 21:38 Go to previous messageGo to next message
zhan200012
Messages: 11
Registered: December 2006
Location: China
Junior Member
If I want to change the field's source in the report and not use the 'Decode' in the SQL!
How to come true?

This source in the Property Palette of the field in the report.
Re: How can I create the dynamic column for the report [message #230142 is a reply to message #230104] Wed, 11 April 2007 01:03 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Then, in a Query, create a SELECT statement which will select all three columns.
Create additional formula column and apply DECODE solution from my previous post. I don't see a way to put three different values into a single column without some sort of IF-THEN-ELSE structure. In SQL, it is DECODE (or CASE). In PL/SQL, you can use any of those (IF-THEN-ELSE / DECODE / CASE).
Re: How can I create the dynamic column for the report [message #230415 is a reply to message #229062] Wed, 11 April 2007 21:27 Go to previous messageGo to next message
zhan200012
Messages: 11
Registered: December 2006
Location: China
Junior Member
Oh Sorry!

You misunderstood my point!

I said my English was poor!

My problem is I want to use the PL/SQL to alter the field's source in the trigger in the report.The field's source that changed the A or B or C column is made a choice base on parameter P_A!
A,B,C column is the same datatype as VARCHAR2!

It must use the (If...else...end/Decode/Case) structure!

Do you understand?
Re: How can I create the dynamic column for the report [message #230435 is a reply to message #230415] Thu, 12 April 2007 00:09 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Use your imagination!
<snip>
   retval some_table.col_A%rowtype;
BEGIN
   IF :p_a = 'a' 
   THEN
      SELECT col_A into :retval
      FROM some_table
      WHERE ...;
   ELSIF :p_a = 'b' 
   THEN
      SELECT col_B into :retval
      FROM some_table
      WHERE ...;
   ELSIF
   ...
   END IF;

   RETURN (retval);
</snip>
Re: How can I create the dynamic column for the report [message #230482 is a reply to message #229062] Thu, 12 April 2007 02:04 Go to previous messageGo to next message
zhan200012
Messages: 11
Registered: December 2006
Location: China
Junior Member
Thank You!

Function has been achieved!

I upload the file!
  • Attachment: dy.rdf
    (Size: 60.00KB, Downloaded 1211 times)
Re: How can I create the dynamic column for the report [message #230494 is a reply to message #229062] Thu, 12 April 2007 02:23 Go to previous messageGo to next message
zhan200012
Messages: 11
Registered: December 2006
Location: China
Junior Member
You can understand my imagination until download the file!

Thanks for you to help me!

I must study the English hardly!
Re: How can I create the dynamic column for the report [message #230767 is a reply to message #230494] Thu, 12 April 2007 14:23 Go to previous message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm glad you found the solution!
Previous Topic: Replce 0 with blanks in metrix reports
Next Topic: Column formula Function required
Goto Forum:
  


Current Time: Sat Nov 30 01:37:53 CST 2024