Solved

Dynamic parameter value list in oracle reports

Posted on 2004-10-03
3
7,017 Views
Last Modified: 2013-12-12
Hi,

I have two parameters in reports: "date" and "worker number".
At runtime, when i select date on parameter form i need
that the parameter's "worker number" list would be dynamic according to
the selected date(i should see anly those workers,
who worked on that date). Is it possible in reports?

It is smt like this:

SELECT worker_number
from WORKERS
WHERE :P_DATE between date1 and date2

Thanks
0
Comment
Question by:sky_lt
3 Comments
 
LVL 22

Accepted Solution

by:
Helena Marková earned 50 total points
ID: 12215594
I have found Note:185951.1 on Metalink, it can be done only on WEB. I think that you can use LOVs in Forms and pass both parameters to the report and parameter form will not be displayed.

SAMPLE - How to create a parameter LOV based on another parameter value? SCR1531

Overview
------------

You have a report with a parameter form. You would like to create a List of Values (LOV) of a user parameter based on the value of another user parameter. Can you do this?

With client/server, this cannot be done.

However, you may do this with web reports using HTML parameter form extensions.

This note gives an example of extending the default parameter form using the HTML extensibility features available in Reports 6i. It was designed to involve as little change to the way the Parameter form currently works. That is, the form is generated prior to being displayed and has no capability to call back to the server to refresh the LOV.

The parameter form works by building "hierarchy lists" within the LOV which are then tied together by JavaScript (generated by the PL/SQL package). You can see the structure in the report output below.

Structure:
------------

DEPTNO JOB ENAME

Cascade values: 10 10:Clerk 10:Clerk:Miller

Parameter values: 10 Clerk Miller

STEPS TO CREATE SAMPLE REPORT:
--------------------------------

1. Using Report Wizard, create a report called cascade.rdf using the following query:

select * from EMP
where deptno = :P_DEPTNO
and job = :P_JOB
and ename = :P_ENAME

Make sure that these user parameters have appropriate the datatype.

For example, P_DEPTNO should be "number" as it is a numeric field,P_ENAME should be "character", etc.

2. The SQL statement is based on the standard parameters.

Create a separate set of parameters to hold the cascades (e.g. CASCADE_DEPTNO <=> DEPTNO). In our case, create 3 user parameters:

CASCADE_DEPTNO

CASCADE_JOB

CASCADE_ENAME

All "cascade" parameters should have datatype=character.

There should be a "cascade" parameter for each user parameter that you want to cascade.

3. Create user parameter and call it "Encoding" with character datatype.

This is used for character set information.

Steps 4 -6 bases the LOV on a SQL statement that joins the data into a "tree" structure within the Pop List. This is displayed as a ":" delimited list within the Pop List (as shown above in the structure).

4. Create LOV for the CASCADE_DEPTNO parameter based on SELECT statement:

SELECT DISTINCT to_char(DEPTNO), to_char(DEPTNO)
FROM EMP
ORDER BY 1

Ensure the following are checked:

- Restrict List to Predetermined Values
- Hide First Column

5. Create LOV for the CASCADE_JOB parameter based on SELECT statement:

SELECT DISTINCT to_char(DEPTNO)||':'||JOB, JOB
FROM EMP
ORDER BY JOB

Ensure the following are checked:

- Restrict List to Predetermined Values
- Hide First Column

6. Create LOV for the CASCADE_ENAME parameter based on SELECT statement:

SELECT DISTINCT to_char(DEPTNO)||':'||JOB||':'||ENAME, ENAME
FROM EMP
ORDER BY ENAME

Ensure the following are checked:

- Restrict List to Predetermined Values
- Hide First Column

7. Put the Reps_UTL.pll ** library in the REPORTS60_PATH.

(Note this is a new version over that shipped with the standard demos - this contains the CASCADE_PARAMETERS package).

8. Attach this library to report:

- Select the "Attached Libraries" in the Object Navigator.
- Click on the green "+" sign on the tools palette to create a library.
- Attach Reps_UTL.pll from file system.

9. Build the Parameter form with the "Cascade Parameters".

- Select Tools' Parameter Form Builder ' select the cascade parameters.

10. Create a Before Parameter Form Trigger which defines the "named" Hierarchies. Name the Cascade "Tree" and specify which "cascade parameters" make up the named Cascade (in order of hierarchy). In this case, it is:

Parameter_Cascade.Define ('Hierarchy1','CASCADE_DEPTNO','CASCADE_JOB','CASCADE_ENAME')||

Hence, the complete Before Parameter Form trigger code is:
--------------------------------------------------------------

function BeforePForm return boolean is

HTMLText VarChar2(32000);

begin

HTMLText :='<html>'||chr(10)||

'<body bgcolor="#ffffff">'||chr(10)||

Parameter_Cascade.Form_Header||

'<center>'||chr(10)||

'<p><table border=0 cellspacing=0 cellpadding=0>'||chr(10)||

' <tr>'||chr(10)||

Parameter_Cascade.Submit_Button||'<td width=15>'||chr(10)||

Parameter_Cascade.Reset_Button ||

' </tr>'||chr(10)||

'</table>'||chr(10)||

'<p><hr><p>'||chr(10);

SRW.SET_BEFORE_FORM_HTML(SRW.TEXT_ESCAPE,HTMLText);

HTMLText := '</FORM>'||chr(10)||

'<SCRIPT LANGUAGE="JavaScript1.2">'||chr(10)||

Parameter_Cascade.Define ('Hierarchy1','CASCADE_DEPTNO','CASCADE_JOB','CASCADE_ENAME')||

Parameter_Cascade.Load||

'</SCRIPT>';

SRW.SET_AFTER_FORM_HTML(SRW.TEXT_ESCAPE,HTMLText);

return (TRUE);

end;

--------------------------------------------------------------

11. Add entry to the After Parameter Form trigger to convert the returned tree parameter back to a scalar parameter for use in the report itself. (One entry for each parameter is required from a cascade.) In this case, it is:

:P_DEPTNO := Parameter_Cascade.Convert_Param_to_Char('CASCADE_DEPTNO');

:P_JOB := Parameter_Cascade.Convert_Param_to_Char('CASCADE_JOB');

:P_ENAME := Parameter_Cascade.Convert_Param_to_Char('CASCADE_ENAME');

Hence, the complete After Parameter Form trigger code is:
--------------------------------------------------------------

function AfterPForm return boolean is

reports_nls_lang varchar2(255);

begin

-- convert the Cascade "Tree" back to the base Parameter

:P_DEPTNO := Parameter_Cascade.Convert_Param_to_Char('CASCADE_DEPTNO');

:P_JOB := Parameter_Cascade.Convert_Param_to_Char('CASCADE_JOB');

:P_ENAME := Parameter_Cascade.Convert_Param_to_Char('CASCADE_ENAME');

-- read the NLS_LANG env. variable

tool_env.getvar('NLS_LANG',reports_nls_lang);

-- Obtain equivalent IANA character set

:encoding:=web_nls.encoding(reports_nls_lang);

/*

srw.set_xml_prolog(srw.text_escape,

'<?xml version="1.0" encoding="'||:ENCODING||'"?>');

*/

SRW.SET_BEFORE_REPORT_HTML(SRW.TEXT_ESCAPE,

'<html>

<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset='||:encoding||'">

<head>

<SCRIPT LANGUAGE = "JavaScript">

var helpwin = null;

function openhelpwin(urltopen)

{

helpwin = window.open(urltopen,''ReportHelp'',''width=650,height=535,resizable=yes,status=no,menubar=no,scrollbars=yes,toolbar=no'')

helpwin.location.href=urltopen;

}

</SCRIPT>

</HEAD>

<body dir=&Direction bgcolor="#ffffff">');

return (TRUE);

end;

---------------------------------------------------------------------------------------------

12. In order to get the Parameter list to "react" to changing values, you need to put in some code in the "Additional Attributes (HTML)" parameter against the parameter field itself (for all levels of the cascade but the last child). You need to use the following code:

onChange="LoadDetail('Hierarchy1',this);"

In our example, follow these steps:

- In Object Navigator, expand on "Parameter Form"

- Expand on "Fields"

- Select PF_CASCADE_DEPTNO

- Open Property Palette

- In the "Additional Attributes (HTML)" section, put in code:

onChange="LoadDetail('Hierarchy1',this);"

- Now repeat the above steps for all cascade parameter fields, except the last child. So, repeat the steps for PF_CASCADE_JOB.

- Do not do this for PF_CASCADE_ENAME as it is the last child.

13. Run Report on the web. Sample syntax:

http://webserver:port/dev60cgi/rwcgi60.exe?server=rep6i.world+report=cascade.rdf+destype=cache+desformat=html+userid=scott/tiger@db+paramform=html

14. Select the top level of a cascade and cycle through, and you will see the other levels change along with it.

Note: The cascade only works on the web. If you also want the parameter form to run Client/Server, you will need to apply the appropriate format triggers to turn off the cascade parameters and not process the code in the Before/After Parameter Form triggers.

References:
-----------------

A sample of this report was created using Reports 6.0.8.x.

** This sample report is located in the Oracle Sample Code at Repository

Entry # 1531. In order to request the sample binary files (rep_util.pll file and the cascade.rdf), please log a TAR via MetaLink requesting Oracle

Sample Code #1531.

Note:166810.1 How to create LOV on user parameter based on value from another parameter?

Caution
---------------

The sample program in this article is provided for educational purposes only and is NOT supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now