سؤال فى التقارير
لو عايز مثلا تقرير عن الموظفين اللى مرتباتهم أكبر من كذا او أقل من كذا
وعايز أعمل جمله [b](أكبر او اصغر او يساوى[/b]) فى
LIST_ITEM
ومثلا على نفس الفورم يكون هناك
TEXT_ITEM
اكتب فيه المرتب
ثم أختار من ال
LIST_ITEM
المعامل الذى أريده حسب الحاجة
إززززااااااااااااااااااااااااااااااااااااااى

ياريت اللى يقدر يساعدنى فى هذا الموضوع وجزاه الله كل خير
شكل الفورم والتقرير اللى عايزه

http://www.4shared.com/file/39848417/16fcc039/porp.html

أنا هقول لك علي فكرة وأعتقد أنها فكرة مبدئية وهي أن تنشأ Function بداخل الـ Database بحيث أن يرتجع مجموعة من السجلات Table وفي هذا الـ Function قم بإنشاء 2 باراميتر ، الأول يستقبل قيمة نصية بحيث أن تمثل مدخل معين يمثل علامات المقارنة ( = ، <> ، < ، > ، <= ، >= ، Between ، In ، ... الي آخرة ) !!!

أما الباراميتر الثاني يتم إدخال القيم المناسبة علي أساس شكل معين !!!

بمعني ، إذا إخترنا أياً من :

= , > , < , >= , <= , <>

في هذه الحالة يتم إجراء شرط واحد علي جملة الـ SQL .

أما في حالة إختيار Between يتم إدخال قيمتين في الباراميتر النصي الثاني بحيث أن يكون بالشكل التالي :

150,20

أما في حالة تحديد القيمة In ففي هذه الحالة يتم إدخال أكثر من قيمة كما يلي :

1,17,17,50,130,99,800, ..... ?

وفي داخل الـ sql*Plus يجب كتابة الـ Function ومتطلباتة ، أنظر الكود التالي :

Create Or Replace Type O_Emp Is Object
(
  Emp_Code VarChar2(20),
  Emp_Name VarChar2(50),
  Emp_Sex VarChar2(4),
  Emp_Salary  Number(10,3)
);
/

Create Or Replace Type T_Emp Is Table By O_Emp;
/

Create Or Replace Function Get_Emp(ID_Operator VarChar2,ID_Values VarChar2) Return T_Emp
As
  P_Emp   T_Emp := T_Emp();
  Cursor Cur_Emp Is Select * From Emp;
  ID_Code	   VarChar2(20);
  ID_Name	  VarChar2(50);
  ID_Sex		 VarChar2(5);
  ID_Salary	 Number;
Begin						   
  If(ID_Operator = '=')Then
  Open Cur_Emp For Select Emp_Code,Emp_Name,Emp_Sex,Emp_Salary  From Emp Where Emp_ID = To_Number(ID_Value);
  ElsIf(ID_Operator = '<>')Then
  Open Cur_Emp For Select Emp_Code,Emp_Name,Emp_Sex,Emp_Salary From Emp Where Emp_ID <> To_Number(ID_Value);
  ElsIf(ID_Operator = '<')Then
  Open Cur_Emp For Select Emp_Code,Emp_Name,Emp_Sex,Emp_Salary From Emp Where Emp_ID < To_Number(ID_Value);
  ElsIf(ID_Operator = '>')Then
  Open Cur_Emp For Select Emp_Code,Emp_Name,Emp_Sex,Emp_Salary From Emp Where Emp_ID > To_Number(ID_Value);
  ElsIf(ID_Operator = '>=')Then
  Open Cur_Emp For Select Emp_Code,Emp_Name,Emp_Sex,Emp_Salary From Emp Where Emp_ID >= To_Number(ID_Value);
  ElsIf(ID_Operator = '<=')Then
  Open Cur_Emp For Select Emp_Code,Emp_Name,Emp_Sex,Emp_Salary From Emp Where Emp_ID <= To_Number(ID_Value);
  ElsIf(ID_Operator = 'between')Then
  Open Cur_Emp For Select Emp_Code,Emp_Name,Emp_Sex,Emp_Salary From Emp Where Emp_ID Between To_Number(SubStr(ID_Value,1,InStr(ID_Value,',') - 1)) And To_Number(SubStr(ID_Value,InStr(ID_Value,',') + 1,1))
  End If;
  
  Fetch CUR_EMP Into ID_Serial,ID_Code,ID_Name,ID_Sex,ID_Salary;
  While Cur_Emp%Found
  Loop
	 P_EMP.Extend;
	 P_EMP(P_EMP.Count) := T_Emp(ID_Serial,ID_Code,ID_Name,ID_Sex,ID_Salary);
	 Fetch CUR_EMP Into ID_Serial,ID_Code,ID_Name,ID_Sex,ID_Salary;
  End Loop;
 
 Close Cur_Emp;
 
  Return P_Emp;
End;
/

أما بالنسبة لفكرة الـ In فيحتاج منك إجراء Loop بصفة مستمرة حتي تتأكد من إنهاء جلب كل الـ Values من متغير ID_Values وإدراجة داخل جدول Temp كما يلي :

Execute Immediate 'Create Table Emp_Temp(Emp_ID   Number(20))'
For X In 1 .. Length(ID_Value)
Loop
  S := SubStr(ID_Value,X,1);
  If(S := ',')Then
   Insert Into Emp_Temp Values(S);
   SValue := '';
  Else
   SValue := SValue || S;
  End If;
End Loop;
Open Cur_Emp For Select Emp_Code,Emp_Name,Emp_Sex,Emp_Salary From Emp Where Emp_ID In(Select Emp_ID From Emp_Temp);

أما الآن عليك إستدعاء الغرض كما يلي :

Select * From Table(Get_Emp('between','50,150'));
Select * From Table(Get_Emp('=','50'));
Select * From Table(Get_Emp('>','50'));
Select * From Table(Get_Emp('<=','50'));

أخي أنا آسف جداً ، هذه هي فكرتي الحالية ، وأكيد سيكون هناك أفكار أفضل منها !!!

لكنني أتمني أن تنال إعجابك فكرتي المتواضعة .

شكراً وبالتوفيق الدائم

تم تعديل 5 مارس 200818 سنة بواسطة MMA

you use the lexical parameters and to tell you how to use it send me the SQL statment where you built your report

you use the lexical parameters and to tell you how to use it send me the SQL statment where you built your report

علي فكرة أنا ضعيف جداً في اللغة الإنجليزية ، لكنني فهمت أنك تطلب كيف يتم إرسال الباراميترات ( أليس كذلك ) !!!

أخي الكريم ، التقرير يحتوي علي جملة :

Select * From Table(Get_Emp('between','50,150'));
أو
Select * From Table(Get_Emp('=','50'));
أو
Select * From Table(Get_Emp('>','50'));
أو
Select * From Table(Get_Emp('<=','50'));

لكن في نفس الوقت أنا لم أكتب الجمله بهذا الشكل ، ولكن يتم كتابتها كما يلي :

Select * From Table(Get_Emp(_Operator,_Value));

علي أساس إرسال قيم الباراميترات من النموذج الي التقرير ، فالباراميتر P_Operator: هو الذي يمثل المعامل ، أما الباراميتر P_Value: هي التي تمثل القيم التي نرغب في جلبها .


شكراً ، وبالتوفيق الدائم

على فكرة ياجماعة الاوراكل ابسط من كده بكتير
فى حاجة فى اليبورت اسمها lexical parameter
وديه ممكن تباسي لها جملة where و أوcolumn أو table_name
الموضوع مش عويص اوي

على فكرة ياجماعة الاوراكل ابسط من كده بكتير
فى حاجة فى اليبورت اسمها lexical parameter
وديه ممكن تباسي لها جملة where و أوcolumn أو table_name
الموضوع مش عويص اوي

غريبة أخي ، طب ليه قلة الرد علي أسئلتي !!!

علي فكرة ، أولاً أنا لسة مبتديء في النماذج والتقارير ، ثانياً أنا ذكرت في البداية أن أكيد هناك حل أفضل من فكرتي !!!

أخي أنا آسف جداً ، هذه هي فكرتي الحالية ، وأكيد سيكون هناك أفكار أفضل منها !!!

طب إذا كان لديك حل أفضل ، إدينا رأيك أخي ، وعلي فكرة هتاخد صدقة العلم وصوابة .

بالإضافة الي ذلك نحن المبتدئين بالفعل نحتاج الي يد العون

شكراً أخي ، ومنتظرين الطريقة الأفضل .

وجزاك الله كل خير

السلام عليكم ورحمه الله وبركاته
اولا
ان شاااكر جدا على سرعه الردود يا جماعه وربنا يجعل هذا العمل فى خدمة الاسلام والمسلمين جميعا
قولوا آميـــــــــــــــــــــــــــــــن

آميــــــــــــــــــــــــــــــن
ثانيا
ياجماعه ممكن حد ينفذ الكلام ده بشكل واقعى على الفورم والريبورت
وينزلوا على المنتدى

وله جزيل الشكـــــــــــــــــــر

فعلياً كنت أنشأ مثال عملي !!!

لكنني أنتظر فكرة الأخ Om MAriam لعلي وعسي أن تكون أسهل !!!

وياريت يستجيب إلينا

علي العموم غداً سأدرج المثال لو أن الأخ الغالي لم يدرج طريقتة ، وياريت تكون سهلة .

شكراً له مقدماً

مشكوور ياأخى على سرعة الرد وبارك الله فيك

التغيرات التي تمت على:
الشاشة
1- غيرت القيم بتاعة ال list item
2- عملت متغير فى كود زرار التقرير و عرفت له قيم where clause
3- باسيت المتغير للتقرير

التقرير:
1- حولت البارمتر _sal من بارامتر عادى الى lexical باضافة علامة "&" بدلا من ":"
2- حولت نوع المتغير من رقم الى حرف

وفى اللينك ديه الشاشة والتقرير وصور كمان بالحل اللي عملته: http://www.4shared.com/file/39935949/ffd5d40/Answer.html

وده درس عن Report Parameters
for mor details send your e-mail
-------------------------------

Creating and Using

Report Parameters


Lesson 13: Creating and Using Report Parameters
................................................................................
....................................................................

Objectives

After completing this lesson, you should be able to
do the following:

• Create and reference a parameter
• Create a list of values for parameter input
• Use and modify a system parameter
• Build a parameter form layout
• Customize a parameter form layout
• Use HTML parameter form extensions
Copyright . Oracle Corporation, 2000. All rights reserved.

®
Instructor Note

Topic Timing

Lecture 50 minutes
Practice 30 minutes
Total 80 minutes

................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Overview
................................................................................
....................................................................

Overview

Introduction

Parameters enable you to develop dynamic report documents that produce variable
report output depending on the parameter value that you enter at run time. This lesson
shows you how to create and reference user and system parameters and how to
customize a parameter form to display parameters at run time.

Objectives

After completing this lesson, you should be able to do the following:

• Control report output by creating and referencing a parameter
• Control parameter input using a list of values
• Manipulate reports at run time using system parameters
• Provide for user input of parameters by building a parameter form
• Customize a parameter form layout
• Use HTML Parameter Form extensions
................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Lesson 13: Creating and Using Report Parameters
................................................................................
....................................................................

User Parameters

• Restrict values in a WHERE clause
_
_
=
SELECT NAME, SALES_REP_ID
FROM S_CUSTOMER
WHERE ID = <a value>
• Substitute any part of a SELECT statement
_
_
SELECT NAME, SALES_REP_ID
FROM S_CUSTOMER
<a where clause>
• Substitute a single column or expression
_
SELECT
FROM S_CUSTOMER
<a column/expression>
Copyright . Oracle Corporation, 2000. All rights reserved.

®
Copyright . Oracle Corporation, 2000. All rights reserved.
®
Creating a User Parameter
Instructor Note

Briefly mention that Report Builder creates the parameter on your behalf when you
reference it in a query. This information is included on the next page.

................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Creating User Parameters
................................................................................
....................................................................

Creating User Parameters

Introduction

You can create your own parameters and use them to change the SELECT statement of
your query at run time.

What Is a User Parameter?

A user parameter is a Data Model object that you create to hold a value that users can
change at run time.
You can reference a parameter anywhere in a query. For example:


• Restrict values in the WHERE clause of the SELECT statement
• Substitute any part of the SELECT statement, including the entire statement
• Substitute a single column or expression in the SELECT list
You can display the contents of a parameter in your report by creating a Layout field
and entering the parameter name in the Field Source property.

How to Create a User Parameter in the Object Navigator

1 In the Object Navigator, click the User Parameter node, and then select the Create
tool.
Note: If this is the first parameter, you can create it by double-clicking the User

Parameter node.
2 Rename the parameter and open the property palette.
3 Verify the data type and width. Enter an initial value if required.


Parameter Properties

Property Description
Datatype Specify whether parameter value is Character, Number, or Date;
default is Number when created in the Object Navigator.
Width Specify maximum allowable width of parameter value; maximum is
64 KB.
Input Mask Allow users to enter a numeric or date value using a specific format.
Initial Value Specify the value to use. You can override this value at run time from
the command line or parameter form.
Validation
Trigger
Validate the parameter value. Enter a PL/SQL function.
List of Values Create a list of values from which users select a valid value at run
time.

................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Lesson 13: Creating and Using Report Parameters
................................................................................
....................................................................

Referencing Parameters in a
Report Query

• Bind reference replaces a value:
– :parameter_name
– Parameter object may be created by default
• Lexical reference replaces a clause:
– &parameter_name
– Parameter object may be created by default
Copyright . Oracle Corporation, 2000. All rights reserved.

®
Instructor Note

Explain the differences between a bind and a lexical reference. Note that substitution
for a bind reference happens after the SELECT statement is parsed; whereas, for a
lexical reference, this happens before the SELECT statement is parsed.

Be brief; there are examples on the next pages.

................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Referencing Parameters in a Report Query
................................................................................
....................................................................

Referencing Parameters in a Report Query

Introduction

There are two ways to reference parameters in a query:

• Use a bind reference
• Use a lexical reference
What Is a Bind Reference?

A bind reference replaces a single value or expression.
To create a bind reference in a query, prefix the parameter name with a colon (.
If the parameter object does not exist, Report Builder automatically creates it for you


and displays a message. In this case, the parameter default datatype is CHARACTER,
not NUMBER.


What Is a Lexical Reference?

A lexical reference is a text string and can replace any part of a SELECT statement,
such as column names, the FROM clause, the WHERE clause, or the ORDER BY
clause.


To create a lexical reference in a query, prefix the parameter name with an ampersand


(&).
If the parameter object does not exist, Report Builder automatically creates it for you
and displays a message. In this case, the parameter default datatype is CHARACTER,
not NUMBER.


Comparing Bind and Lexical Reference Types

Type Prefix Use to Replace Parameter Created by Default?
Bind : Single value or expression in
the following clauses:
WHERE, GROUP BY,
ORDER BY, HAVING,
CONNECT BY, START
WITH
Yes, if it does not already exist.
Report Builder displays a warning
message and adds the parameter to User
Parameters in the Object Navigator.
Lexical & Any part of a SELECT
statement
Yes, if it does not already exist.
Report Builder displays a warning
message and adds the parameter to User
Parameters in the Object Navigator.
Datatype must always be Character.

................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports 13-7


Lesson 13: Creating and Using Report Parameters
................................................................................
....................................................................

Using Bind References

• Restrict values in a WHERE clause
_
_
SELECT NAME, SALES_REP_ID
FROM S_CUSTOMER
WHERE ID > _CUST
• Substitute a single value or expression in the
select statement
®
_
_
SELECT NAME, SALES_REP_ID
FROM S_CUSTOMER
ORDER BY DECODE( 1, NAME,
2, STATE, COUNTRY)
:SORT
Copyright . Oracle Corporation, 2000. All rights reserved.

Instructor Note

Run L13BIND.RDF. You want to restrict the Payment Type.

Add to the query: AND S_ORD.PAYMENT_TYPE = _TYPE
Notice the message telling you that the parameter is being created.
In the Object Navigator, show the user parameters.
In the Parameter property palette, show that the default data type is CHARACTER.


Change the following: Width=6, Initial Value=CASH. Run the report using the Live

Previewer. Now run the report in a browser.
Show the other method of creating a parameter. In the Object Navigator, create a
parameter called P_CUST and show the properties. Point out that the default data type
is NUMBER, so be careful of the data type when you create parameters. Set the initial
value to 201.

Add to the query: AND S_ORD.CUSTOMER_ID > _CUST

................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Referencing Parameters in a Report Query
................................................................................
....................................................................

Using Bind References

Use a bind reference anywhere in a query where you can use a single literal value,
such as a character string, number, or date.

Examples

SELECT NAME, SALES_REP_ID


FROM S_CUSTOMER
WHERE ID = _CUST


This statement enables you to enter a specific customer number at run time. For
example, if you enter 102, the WHERE clause uses the value 102 to restrict data
fetched and to fetch the one customer that has ID 102.

SELECT NAME, SALES_REP_ID


FROM S_CUSTOMER
ORDER BY DECODE(:SORT, 1, NAME, 2, STATE, COUNTRY)


This statement enables you to output different versions of the report:

• Enter 1 for the parameter SORT to display the output ordered by customer name.
• Enter 2 to display the output ordered by state.
Where Can You Not Use Bind References?

You cannot use a bind reference to:

• Replace a column name in the SELECT clause, although you can reference a
value, such as the contents of a parameter:
SELECT LAST_NAME, SALARY * _RATE


FROM S_EMP


• Replace any part of a FROM clause
• Replace reserved words or clauses
................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Lesson 13: Creating and Using Report Parameters
................................................................................
....................................................................

Using Lexical References

• Use to substitute any part of the query
SELECT NAME, SALES_REP_ID
FROM S_CUSTOMER
SELECT NAME, SALES_REP_ID
FROM S_CUSTOMER
SELECT
FROM
&P_WHERE_CLAUSE
&P_ORD_CLAUSE
&P_WHERE_ORD_CLAUSE
&P_CUST CUST, &P_SALESREP REP
&P_TABLE
• Ensure that the number of values and datatypes
match at run time
Copyright . Oracle Corporation, 2000. All rights reserved.

®
Instructor Note

Demonstration:
Add a lexical parameter to alter the order of output.
Add to query: ORDER BY &P_ORDCLAUSE. Show the error. The parameter is not created


by default, so the syntax ORDER BY <null> is not valid.

Create the lexical parameter in the Object Navigator. Show the properties. Point out
the default data type and size and modify as required: CHARACTER, 50.
Set an initial value. This time, include the keywords ORDER BY S_CUSTOMER.NAME


DESC, so that you can leave the clause blank at run time. Now add the reference to the
end of the query: &P_ORDCLAUSE. Rebuild the parameter form.


Run with different values, including NULL.
Continue the demonstration to add a dynamic list of values to the bind parameter and a
static list to the lexical parameter, or teach the next page and then demonstrate. Run
the report using the Live Previewer and then run the report in a browser.


................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Referencing Parameters in a Report Query
................................................................................
....................................................................

Using Lexical References

Use a lexical reference to replace any clause in a SELECT statement, or even to
replace the entire statement.

Examples

The following statements use lexical references to substitute parts of the query at run
time:

• To specify a WHERE clause, ORDER BY clause, or both at run time (as two
separate parameters):
SELECT NAME, SALES_REP_ID


FROM S_CUSTOMER
&P_WHERE_CLAUSE
&P_ORD_CLAUSE


• To specify a WHERE clause, ORDER BY clause, or both at run time (as one
parameter):
SELECT NAME, SALES_REP_ID


FROM S_CUSTOMER
&P_WHERE_ORD_CLAUSE


• To specify two column names and the table names at run time:
SELECT &P_CUSTNAME CUST, &P_SALESREP REP


FROM &P_TABLE


Note: When you use lexical references in the SELECT list, you must, at run time,
specify the same number of items of the correct data type, as defined in the Data
Model.

................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Lesson 13: Creating and Using Report Parameters
................................................................................
....................................................................

Hints and Tips When Referencing
Parameters


Always do the following:

• Specify column aliases when substituting column
names
• Create lexical parameters explicitly in the Object
Navigator
• Enter an initial value for parameters that affect
query validation when NULL
Copyright . Oracle Corporation, 2000. All rights reserved.

®
Instructor Note

Use the whiteboard to work through a SQL example of the first and third points shown
in the slide.

................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Hints and Tips for Referencing Parameters
................................................................................
....................................................................

Hints and Tips for Referencing Parameters

Specifying Column Aliases

If you substitute the name of a column or expression in the SELECT list with a lexical

reference in the SELECT list, always add a column alias after the reference.
Otherwise, when the parameter value changes at run time, the column name in the
SELECT list does not match the report column object and the report does not run.

Referencing Nonexistent and Null Parameters

Remember that Report Builder automatically creates a parameter when you make a


reference to a nonexistent parameter.
When you create a lexical parameter, you may need to include an initial value before
referencing the parameter in the query, because depending on your use of the
parameter, a NULL value can cause a syntax failure.


Example

The following statement does not cause a syntax error when P_ORDER_CLAUSE is
NULL.

SELECT...FROM...
&P_ORDER_CLAUSE


The following statement does cause a syntax error when P_ORDER_CLAUSE is
NULL.

SELECT...
FROM...
ORDER BY &P_ORDER_CLAUSE


Remember to define an initial value for lexical parameters that affect the validity of

the statement, and to validate those that you allow a user to enter at run time.
Note: Always use column aliases when substituting column names with lexical
references.

................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Lesson 13: Creating and Using Report Parameters
................................................................................
....................................................................

Static List of Values

1
2
3
4
5
Copyright . Oracle Corporation, 2000. All rights reserved.

®
1 Choose Static Values. 4 To remove a value; select a
value and click Remove.
2 Enter a value and click
Add.
5 Select OK to accept the list and
exit.
3 Repeat for each value to
build the list of values.

Instructor Note

Use L13BIND.RDF to demonstrate a static list of values for payment type, or by
creating a number of different ORDER BY clauses.
Examples:


ORDER BY S_CUSTOMER.NAME DESC (our initial value)

ORDER BY S_CUSTOMER.NAME


ORDER BY S_ORD.ID DESC


Make sure the Restrict List to Predetermined Values check box is clear to allow for
other values. Run the report. Select one of the ORDER BY clauses from the list of
values. Run the report again. Select ORDER BY O.ID, but delete DESC.

Suggest that students can use column aliases to make the ORDER BY clause more
readable.

................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Creating a List of Values
................................................................................
....................................................................

Creating a List of Values

Introduction

You can create a list of values from which users select a valid value at run time. You
can restrict users to only those values in the list, or allow them to enter a different
value.


For bind parameters, the list can be a static list of values or a dynamic list that selects
values from the database at run time.
For lexical parameters, you can enter a static list of values.


How to Create a Static List of Values
1 In the Parameter property palette, Select the List of Values property. The Static


Values option button is selected by default.

2 Enter a value in the Value field and click Add.

3 Repeat for each value you want in the list.
Note: To remove a value, select the value in the list and click Remove.

Restricting Users to Values in the List

Set the Restrict List to Predetermined Values property to determine whether to prevent
users from entering any value not included in your list. By default, this property is Yes.
If you set the property to No, users can still select a value from the list, but they can
also enter other values or leave the value blank.

................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Lesson 13: Creating and Using Report Parameters
................................................................................
....................................................................

Dynamic List of Values

1
2
3
4
5
Copyright . Oracle Corporation, 2000. All rights reserved.

®
1 Choose SELECT
Statement.
4 Hide the first column from
the list.
2 Enter a valid query. 5 Click OK to accept the query
and exit.
3 Select or clear the Restrict
List to Predetermined
Values check box.

Instructor Note

Demonstrate a dynamic list of values by selecting a list of customer IDs and names
from the S_CUSTOMER table.


Select the Restrict List to Predetermined Values check box.
Demonstrate the Hide First Column check box. Explain to the students that they can
select more than one column in the SELECT statement so that they can display a more
descriptive list to the user. The first column should be the value that restricts the query.


Run the report and show that this time you cannot edit or enter your own value.


................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Creating a List of Values
................................................................................
....................................................................

How to Create a Dynamic List of Values
1 In the Parameter property palette, select the List of Values property.
2 Choose SELECT Statement.

The SQL Query Statement field is displayed.
3 Enter a query to populate the list of values. You can include more than one column.
The parameter takes its value from the first column in the list and the column
values appear concatenated in the list at run time.
4 Set the Restrict List to Predetermined Values property, as required.

Displaying Meaningful Values Instead of Codes

To see a more meaningful list of descriptive values, instead of the primary or foreign

key column that you reference in the query, select Hide First Column.
Make sure that the primary or foreign key column is first in the SQL query statement,
because this is still the value that the parameter object contains at run time.


................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Lesson 13: Creating and Using Report Parameters

................................................................................
....................................................................


Copyright . Oracle Corporation, 2000. All rights reserved.
®
System Parameters
Instructor Note

Demonstration:
Show the system parameters in the Object Navigator.
Explain the DESNAME, DESFORMAT, and DESTYPE parameters briefly.
BACKGROUND: In Microsoft Windows, Reports simulates background by sending


the report to RWRBE60.
COPIES: Only if destination is a printer
CURRENCY, THOUSANDS, DECIMAL: Only if symbols are used in format masks
Remind the students that these are also arguments that they can include on the


command line for RWBLD60 or RWRUN60.
Run the report with DESTYPE set to File, DESNAME set to my_rep.rtf, and
DESFORMAT set to .rtf.


................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Referencing System Parameters
................................................................................
....................................................................

Referencing System Parameters

Introduction

Report Builder provides system parameters to allow you to modify standard run-time
settings for each report definition.
There are currently eleven system parameters. You cannot delete them.
Each parameter has a default value that you can modify.


Parameter Use to specify Values Default
BACKGROUND Whether report is to run in
background (in Microsoft Windows,
setting this parameter to Yes invokes
the Background Engine RWRBE60 if
not already running)
Yes, No No
COPIES Number of copies if destype=printer Any integer 1
CURRENCY Symbol for currency indicator Maximum 4
characters
DECIMAL Symbol for decimal indicator Any single
character
DESFORMAT Output device definition; ignored if
type=screen/preview
Examples: dflt,
pslan80, pdf,
HTML
dflt
DESNAME Destination name, such as filename,
printer name, mail userid; ignored if
type=screen/preview
<report>.lis
DESTYPE Destination type for output File, printer,
mail, screen,
preview
Screen
MODE Whether report executes in bitmapped
or character mode
Bitmap,
character
Default
ORIENTATION The print direction of printer output Portrait,
landscape
Portrait
PRINTJOB Whether print job dialog box is
displayed at run time, if destype=file
or printer
Yes, No Yes
THOUSANDS Symbol for thousands indicator Any single
character

................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports 13-19


Lesson 13: Creating and Using Report Parameters
................................................................................
....................................................................

Displaying a Currency Symbol for
Monetary Values

Build:

report:Parameter - CURRENCY
Initial Value £
Copyright . Oracle Corporation, 2000. All rights reserved.
£1000
£6000
£5000Format Mask $NNN0
FF1000
FF6000
FF5000 Runtime:
RWRUN60 report=fred.rep...currency=FF
report:Layout Field - F_Money
®

Instructor Note

The diagram above shows the method described opposite.
Mention the alternative: Setting NLS_LANG and using the L, G, and D symbols


(which are used by the currency symbols in the stylebar). NLS is discussed later in the


course.


Demonstration: Alter the format of F_TOTAL to $N,NNN,NN0.00 (there are some


credit orders that are more than $1 million).
Change CURRENCY initial value to FF (or something else).
Run the report.
You might want to point out that the currency parameter does not appear on the current


default run-time parameter form (if nobody mentions it before you). You can show it


later, if you want, when you create a parameter form.


If you have time: Create a program item for the demonstration report (you can save


time by creating this before the lesson). The command line should be:
<oracle home>\bin\rwrun60 report=reportname
userid=<user/password@host> currency=FF

Show that this command overrides the initial value of the parameter.

................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Referencing System Parameters
................................................................................
....................................................................

Displaying a Currency Symbol for Monetary Values

A common requirement is to format currency values preceded by a currency symbol.

You may require the symbol to be £ for pounds sterling, $ for U.S. dollars, FF for
french francs, and so on. You can set different initial values in different reports.

How to Display a Different Currency Symbol

1 Enter the currency symbol ($) in the format mask of one or more layout fields.

2 From the Object Navigator, open the property palette for the currency system

parameter and enter a maximum of four characters in the initial value.
Note: Currency, thousands, and decimal symbols appear in the report output only if
you use them in the format mask of a layout field.

Altering the Parameter Value at Run Time

You can modify the parameter value at run time in one of two ways:

• Include the currency parameter on the run-time command line
• Enable users to alter the parameter value on the run-time parameter form
If the currency symbol you choose is several characters in length, remember to make
the relevant fields wide enough to include it.

Priority of Parameter Values

RWRUN60: Run-time parameter form overrides command line. Command line


overrides initial value.
RWBLD60: Run-time parameter form overrides initial value. Initial value overrides
command line.


Note: When running a report in Report Builder, an initial value for the current report
takes priority over a default that you specify in the RWBLD60 command line. Use
Tools—>Preferences—>Runtime Values and Runtime Settings for testing.


................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Lesson 13: Creating and Using Report Parameters
................................................................................
....................................................................

Building a Parameter Form

®
+ Report properties:
• Width
• Height
• Number of Pages
Copyright . Oracle Corporation, 2000. All rights reserved.

Instructor Note

Demonstrate how to display the Parameter Form in the Report editor:

• Show the Parameter Form icon in the top left corner of the Report editor.
• Show the menu option View—>Parameter Form.
• Show the Parameter Form node and icon in the Object Navigator.
The Parameter Form layout is currently blank; no parameter form is defined.
You may want to show that there is no Link File tool in the Parameter Form toolbar.
Point out that although you can create your parameter form from scratch, it is usually
easier to let Report Builder do some of the work by creating a Default Parameter Form
and then modifying it. This is discussed on the next page.


................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Building a Parameter Form
................................................................................
....................................................................

Building a Parameter Form

Introduction

The reports that you created in previous lessons ran successfully without a parameter
form. However, when you created parameters, a default run-time parameter form
appeared to enable you to change the values at run time.

You can build your own parameter form, select the parameters that you want to
display, and then customize the appearance of the parameter form in the Report editor.

How to Build a Parameter Form
1 Select menu item Tools—>Parameter Form Builder.
The Parameter Form Builder dialog box appears.
2 If desired, modify the title, hint, and status line text. These lines are displayed at
the top of the parameter form at run time.
3 Select or deselect parameters in the parameter section as necessary.
Note: Selected parameters are displayed in the dialog box as highlighted on a

black background. Deselected parameters do not appear in the parameter form.

4 Modify label text of the selected parameters, if you wish.

5 Click OK to create the parameter form.
If you create many parameters in your report, the run-time parameter form can spread
over more than one page. You can view subsequent pages at run time by clicking Next.

Setting Report Properties for the Parameter Form

There are three properties in the Report property palette, under the Parameter Form
Window node, that specifically apply to the parameter form.

Report Property Use to
Form Width Define the width of the parameter form that
the user sees at run time
Form Height Define the height of the parameter form that
the user sees at run time
Number of Pages Define the number of physical pages that
make up the parameter form

................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Lesson 13: Creating and Using Report Parameters

................................................................................
....................................................................


Copyright . Oracle Corporation, 2000. All rights reserved.
®
Customizing a Parameter Form
PF_P_PAY_TYPE
P_PAY_TYPE
Instructor Note

Demonstration:

Continue with the previous demonstration report that contains a parameter.
Build a parameter form. Add CURRENCY and DECIMAL parameters.
Modify the parameter form editor.
Import p2q40.gif or some other graphics. Save the report and run it in a browser.
Show the property palette for a field and for some boilerplate text. Point out the default


naming convention, PF and PB.
You can alter parameter properties from the field as well as in the Data Model
parameter object.


................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Building a Parameter Form
................................................................................
....................................................................

Customizing the Parameter Form Layout

You can customize the parameter form layout in a similar way to customizing the
report layout. You can modify colors and fill patterns (except in fields), move or delete
existing objects, create additional objects, import file contents such as a company
logo, and so on.

Creating Parameter Fields

A parameter field is a placeholder for a parameter value on the run-time parameter

form, in the same way that a field is a placeholder for a column value in the layout.
You cannot modify the color or fill patterns of a parameter field. However, the
property palette enables you to modify all parameter properties.

Report Builder creates one field for each parameter that you select in the Parameter
Form Builder. If a parameter in your Data Model does not have an associated field, it
does not appear in the run-time parameter form because it does not have any display
attributes.

You can create additional fields by using the field tool in the parameter form toolbar
and sourcing the field to an existing parameter.

Creating Parameter Boilerplate Objects

Parameter boilerplate is text or graphics that either you create or the Parameter Form


Builder creates by default.
The Parameter Form Builder creates parameter boilerplate objects for the labels of
each parameter field as well as for the title, hint, and status lines.


You can create additional boilerplate objects to customize your parameter form, for
example, create report heading pages, modify default labeling, import images, and so
on.


Note: You cannot create a link file in the parameter form; however, you can import file
contents, as you can in the layout.


................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Lesson 13: Creating and Using Report Parameters
................................................................................
....................................................................

Using Parameter Form HTML Extensions

• Boilerplate text with HTML tags
• Parameter fields with JavaScript
• Parameter Form header
• Parameter Form footer
Copyright . Oracle Corporation, 2000. All rights reserved.

®
Instructor Note

Demonstration:
Open the property palette for par_htm.rdfand show the setting for the Before
Form Type and the Before Form Value properties. This displays a header on the runtime parameter form when the report is output to an HTML file. What you see is the
default value for both properties. Now point out the After Form Type and After Form
Value properties. This will display a customized footer. Run the report on the Web.

................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Using Parameter Form HTML Extensions
................................................................................
....................................................................

Using Parameter Form HTML Extensions

HTML Parameter Form extensions enable you to enhance your run-time parameter
form with HTML tags and JavaScript. Using HTML, you can create boilerplate text.
Incorporating JavaScript extends your parameter form to support client-side validation
eliminating network roundtrips. You can do the following:

Extension Purpose
Create boilerplate text with HTML tags Add hyperlinks or HTML tagged text
Insert parameter fields with Javascript Define input or select events such as
validation or raising errors
Create a Parameter Form header Place a logo or standard links in the header
of the HTML Parameter Form
Create a Parameter Form footer Place a logo or standard links in the footer
of the HTML Parameter Form

Creating a Boilerplate Text Object for HTML Tags
1 Create a boilerplate text object using the Text tool.
2 Enter or import the desired HTML code.
3 Open the property palette of the text object and set the Contains HTML Tags

property to Yes.
Note: The text only shows for HTML output formats.


Creating HTML Parameter Form Fields with Input or Select Events
1 Create or edit a Parameter Form field.
2 Open the property palette of the Parameter Form field and set the Additional

Attributes(HTML) property to a valid JavaScript event handler.
Note: In some cases, such as raising messages, the JavaScript code may have to be
entered in the Before Form report properties. To insert the JavaScript code in the
Before Form report properties:


– Open the report property palette.
– Set the Before Form Type property to Text if you enter the JavaScript code, or
to File if you will import the code from a file.
– Set the Before Form Value property by clicking the ... button to either enter the
JavaScript, or select the HTML file with the JavaScript.
................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Lesson 13: Creating and Using Report Parameters
................................................................................
....................................................................

Copyright . Oracle Corporation, 2000. All rights reserved.
®
Parameter Form Header and Footer
Header
Footer
................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Using Parameter Form HTML Extensions
................................................................................
....................................................................

Creating an HTML Parameter Form Header
1 Open the property palette of the report.
2 Set the Before Form Type property to Text if you will enter the header, or to File if

you will import the header from a file.
3 Set the Before Form Value property by clicking the ... button to either enter the
HTML code or select an HTML file to import.


Creating an HTML Parameter Form Footer
1 Open the property palette of the report.
2 Set the After Form Type property to Text if you will enter the footer, or to File if

you will import the footer from a file.
3 Set the After Form Value property by clicking the ... button to either enter the
HTML code, or select an HTML file to import.


................................................................................
.....................................................................
Oracle Reports Developer: Build Internet Reports


Lesson 13: Creating and Using Report Parameters
................................................................................
....................................................................

By the way it's obvious from my name that I'm a lady not man

الله أكبر عليك يا أخي !!!

والله ممتاز جداً !!!

وبسيطة وواضحة جداً جداً جداً !!!

أنا بصراحة لا أعلم ماذا أقول ، ولكن ربنا يجزيك كل خير !!!

أنا بالفعل كنت مصمم مثال عملي للأخ حسام ولكنة صراحتاً صعب جداً ، أو مش صعب لكنة معقد نسبياً خصوصاً علي أي مبتديء في الـ PL/SQL .

بس أخي إسمحلي ، أنا جديد في التقارير والنماذج ، وبدايتاً أعلم أن في حالة إستقبال أي باراميتر تتم كما يلي :

:ParamName

لكن بجد أول مرة أعرف فائدة & للباراميتر .

ياريت تراعي هذه النقطة .

علي العموم ، جزاك الله كل خير وبعد إذنك إسمحلي أن أضع مثالي الخاص حتي تكتمل الصورة ويستفيد منها الجميع

شكراً وجزاك الله كل خير

لا إله إلا الله ، محمد رسول الله ،

Code.zip

بسم الله الرحمن الرحيم

فى الحقيقة انا مش عارف أرد أقول ايه وايه هيا كلمات الشكر والثناء ليكم يا جماعه
الاخ MMA
والاخت الفاضلة أم مريـــم

شكرا جزيلا ويارب يجعل هذا العمل فى ميزان حسناتكم يوم القيامه