How to use DECODE and show Agent Description while passing the Agent Id in Parameter

skumar16jan · 2013-02-28T02:37:26+00:00

Dear All, Please advice on below points: i) How to show Agent Description when selecting Agent Code in Parameter form ii) How to show Date Range along with Report Title iii) How to use decode for ALL Agent Code in main query <strong…

skumar16jan

Dear All, 
 
Please advice on below points: 
i) How to show Agent Description when selecting Agent Code in Parameter form 
ii) How to show Date Range along with Report Title 
iii) How to use decode for ALL Agent Code in main query 
 
PARAMETER DECLARED: 
P_DATE_FROM 
P_DATE_TO 
P_AGENT 
 
 
MAIN QUERY: 
SELECT POLICY_NO, AGENT_CODE, AGENT_DESC 
FROM DUAL 
WHERE agent_join_date between ? AND ? 
AND AGENT_CODE = DECODE(?,'ALL',%,P_AGENT) 
 
PARAMETER WINDOW: 
DATE FROM : 01/01/2012 DATE TO: 15/02/2013 
AGENT : A101-RAJKUMAR 
 
OUTPUT: 
 
AGENT LISTING FROM 01/01/2012 TO 15/02/2013 
AGENT: RAJKUMAR 
 
POLICY NUMBER AGENT CODE AGENT NAME 
TL2013101 A101 RAJKUMAR

Find more posts tagged with

Comments

Hans_vd

i) Not sure if this is possible - don't think so 
ii) Drag a dynamic text item to the report and write an expression like this: 
<pre class='_prettyXprint _lang-auto _linenums:0'>"Title from " + params["p_date_from"] + " to " + params["p_date_to"]</pre>
iii) this will work: AND AGENT_CODE = DECODE(?,'ALL', AGENT_CODE, P_AGENT)

skumar16jan

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="114654" data-time="1362044745" data-date="28 February 2013 - 02:45 AM">
i) Not sure if this is possible - don't think so 
ii) Drag a dynamic text item to the report and write an expression like this: 
<pre class='_prettyXprint _lang-auto _linenums:0'>"Title from " + params["p_date_from"] + " to " + params["p_date_to"]</pre>
iii) this will work: AND AGENT_CODE = DECODE(?,'ALL', AGENT_CODE, P_AGENT) </blockquote>