Home » Developer & Programmer » Reports & Discoverer » Multiple Columns in Param Form (Oracle Reports Developer 10.1.2.0.2, Unix, 10 G database)
Multiple Columns in Param Form [message #556486] Mon, 04 June 2012 12:46 Go to next message
TX_developer
Messages: 46
Registered: July 2011
Member
Hi,

I am adding a parameter to the parameter form of existing reports based on a select statement and has two columns. That part is fine. However, Oracle reports is adding a dash between the two values. Still fine, but I am also adding a UNION with ALL in case the user wants to run the report for all values. In the Parameter form it looks like this:

ALL -
RBC - 111
RRG - 234
TEB - 445

How do I get rid of the dash for "ALL" since I did not physically add it?

Below is my select statement for the parameter I created:

SELECT code,
code_num
FROM codes
WHERE code_num <> 0
UNION
SELECT 'ALL', ' '
FROM dual
ORDER BY 1;

Thank you,
~k
Re: Multiple Columns in Param Form [message #556572 is a reply to message #556486] Tue, 05 June 2012 07:05 Go to previous messageGo to next message
ranamirfan
Messages: 529
Registered: January 2006
Location: Pakistan / Saudi Arabia
Senior Member

Dear,

Try this.In report main query after Where Clause put Lexical parameter &user_param_value
then go to report user parameter - create another parameter user_param_list with datatype number
click user_param_list - List of values and add this below code in Select Statement also hide first column.

Select Code_Num,Code||'-'||Code_Num
From Codes
Where  Code_Num<> 0

UNION

Select 777,'ALL'||'-'||'777'
From Dual


After that go to Report triggers BEFORE REPORT Click it and add this below code.
BEGIN

IF :user_param_list = 777 THEN
   :user_param_value :='AND CODE_NUM IS NOT NULL';
ELSE
   :user_param_value :='AND CODE_NUM='||:user_param_list;
END IF;

RETURN TRUE;
END;



Compile & run report.Hope it'll help you.

Regards,
Irfan
Re: Multiple Columns in Param Form [message #556584 is a reply to message #556572] Tue, 05 June 2012 08:33 Go to previous messageGo to next message
TX_developer
Messages: 46
Registered: July 2011
Member
Hi Irfan,

Thank you for the reply. I did something similar but not exactly as you did. I used the following select:

SELECT code,
code||' - '||code_num
FROM codes
WHERE code_num <> 0
UNION
SELECT 'ALL', 'ALL'
FROM dual
ORDER BY 1;

I then checked the check box to hide the first column in the parameter form, but my query in the data model is based on the code field. So now it is displaying like below, which is correct.

ALL
RBC - 111
RRG - 234
TEB - 445

My parameter in the where clause of my query is :p_code and it seems to work fine. Do you see any problem with this versus using the method you described?

Thanks,
~k

Re: Multiple Columns in Param Form [message #556586 is a reply to message #556584] Tue, 05 June 2012 08:44 Go to previous messageGo to next message
ranamirfan
Messages: 529
Registered: January 2006
Location: Pakistan / Saudi Arabia
Senior Member

Dear,
Please read the OraFAQ Forum Guide before posting.
http://www.orafaq.com/forum/t/88153/0/

Post your report query.

Regards,
Irfan
Re: Multiple Columns in Param Form [message #556588 is a reply to message #556586] Tue, 05 June 2012 09:21 Go to previous messageGo to next message
TX_developer
Messages: 46
Registered: July 2011
Member
Here is my report query.

SELECT ca.credit_amt,
ca.debit_amt,
ca.create_date,
ca.transaction_type
ca.code,
ca.code_num
FROM cold_accounts ca,
codes c
WHERE TRUNC(ca.create_date) BETWEEN UPPER(TRUNC(:create_date_from)) AND UPPER(TRUNC(:create_date_to))
AND ca.code = c.code
AND ca.code_num = c.code_num
AND c.code_num <> 0
AND ca.code DECODE(:p_code, 'ALL', c.code,
:p_code);

The select for the :p_code parameter is

SELECT code,
code||' - '||code_num
FROM codes
WHERE code_num <> 0
UNION
SELECT 'ALL', 'ALL'
FROM dual
ORDER BY 1;

I am hiding the first column.

Thank you,
~k
Re: Multiple Columns in Param Form [message #556686 is a reply to message #556588] Wed, 06 June 2012 01:43 Go to previous messageGo to next message
ranamirfan
Messages: 529
Registered: January 2006
Location: Pakistan / Saudi Arabia
Senior Member

Dear,
Create Lexical parameter
SELECT ca.credit_amt, 
ca.debit_amt, 
ca.create_date,
ca.transaction_type
ca.code,
ca.code_num
FROM cold_accounts ca,
codes c
WHERE TRUNC(ca.create_date) BETWEEN UPPER(TRUNC(:create_date_from)) AND UPPER(TRUNC(:create_date_to))
AND ca.code = c.code
AND ca.code_num = c.code_num
&user_param_value 


Go to report user parameter - create another parameter user_param_list with datatype number
click user_param_list - List of values and add this below code in Select Statement also hide first column.
The value for the :user_param_value as following.
SELECT code,
code||' - '||code_num
FROM codes
WHERE code_num <> 0
UNION
SELECT 777,'ALL'
FROM dual
ORDER BY 1;

After that go to Report triggers BEFORE REPORT Click it and add this below code.

BEGIN

IF :user_param_list = 777 THEN
   :user_param_value :='AND CA.CODE IS NOT NULL';
ELSE
   :user_param_value :='AND CA.CODE='||:user_param_list;
END IF;

RETURN TRUE;
END;


Try this.

Regards,
Irfan

Re: Multiple Columns in Param Form [message #556818 is a reply to message #556686] Wed, 06 June 2012 14:57 Go to previous messageGo to next message
TX_developer
Messages: 46
Registered: July 2011
Member
Hi Irfan,

Thank you for all your help. My question is, why is it necessary to do all this? I used the report query and parameter query I posted above, hid the first column in the parameter, and when I select 'ALL' in the param drop down I get all records. If I select a particular code, the report query retrieves records only for that code. So why is it necessary to create the extra parameter and then add the BEFORE REPORT trigger? It seems to work fine the way I coded it, but if there are potential problems doing it the way I did, I would like to know.

Thank you,
~k
Re: Multiple Columns in Param Form [message #556824 is a reply to message #556818] Wed, 06 June 2012 15:16 Go to previous messageGo to next message
Littlefoot
Messages: 19303
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't see any ./fa/3314/0/

Simple solutions are often the best ones (easy to read, easy to maintain, easy to fix).
Re: Multiple Columns in Param Form [message #556835 is a reply to message #556824] Wed, 06 June 2012 18:05 Go to previous message
TX_developer
Messages: 46
Registered: July 2011
Member
Thanks Littlefoot...just making sure. It appears that Oracle reports uses the first column in the parameter form query as the parameter value. I figured hiding it would not change that.

Appreciate all the feedback. I am still learning as I go along.

Thanks,
~k
Previous Topic: How to run Oracle Reports (.rep or .rdf) from .bat file
Next Topic: Graph - Repeating Frame (Change dynamically Y & X axis title )
Goto Forum:
  


Current Time: Wed Jul 23 13:24:00 CDT 2014

Total time taken to generate the page: 0.16178 seconds