Three quries one report

I have to create a report with Three quries,
Q1, Q2, and Q3

all three quries use same columns and same tables but different WHARE clauses

And now this is what I want,

When user runs the report Parameter forms comes up and asks the user for input, If user selects 1 report will run Q1, and if user selects 2 reports will run Q2 and so on.

How do I accomplish that.

Thanks in advance.

u should create parameter (user parameter) for example Rep_number (type - number) and include this parameter in Parameter Form.

in your queries Q1,2,3 u should white like this:

Q1 -> where ...... and Rep_number = 1
Q2 -> where ...... and Rep_number = 2
Q3 -> where ...... and Rep_number = 3

and more - u can't link your query - its shoul be independent and place pictures of queries on Data Model one under another.

I pulled this off of the Oracle Metalink. If you customize your call to the report (Query 1, Query2, .....) and the AFTERPARAMFORM Trigger, this should work.

Good Luck.

Here is the link in case you have access:

http://metalink.oracle.com/metalink/...T&p_id=37787.1

This is achieved by employing the use of a lexical parameter in your query and a bind parameter to accept the user's input at runtime.

In the report outlined in this article the user can either specify a particular employee's name (ENAME) to run the report on or choose not to specify an ENAME and run the report for ALL EMPLOYEES.

Follow the steps outlined below to develop the report:

1. Creating the parameters.

Create a user parameter called P_ENAME
(Datatype: character, Width: 20)

/* This will be used to accept the users runtime input */

Create a user parameter P_WHERECLAUSE
(Datatype: character, Width: 200, Initial Value: where 1=1)

/* This is the lexical parameter that will be populated in the AFTERPARAMFORM
trigger. It has an initial value of 'where 1=1' in order to allow the report
QUERY to parse.*/

2. Create the following query in the Data Model that includes the lexical
parameter.

Select * from emp
&P_WHERECLAUSE

3. Perform a Default Layout. Choose Tabular Style.

4. Perform a Default Parameter Form Layout from the
Tools ->Default Parameter Form.. menu.

Deselect the P_WHERECLAUSE parameter.
Then choose OK to accept that.

5. Code the following AFTER PARAMETER FORM trigger.

function AfterPForm return boolean is
begin

/* if the user has actually specified an ename */

if :p_ename is not null then

/* Assign a where clause that includes the users input parameter to the
lexical :p_whereclause, and change the input parameter to upper case */

:p_whereclause := 'where ename = upper(:p_ename)';
end if;
return(true);
end;

6.Run report

KASH

in my mind exists more simple way for this report:

1. u create only 1 query with 3 queries and UNION
2. each select in union in WHERE constraction has:

select .....
where :P_NB_REPORT = 1 and
( ... your where for 1 case ...)
UNION
select .....
where :P_NB_REPORT = 2 and
( ... your where for 2 case ...)
UNION
select .....
where :P_NB_REPORT = 2 and
( ... your where for 2 case ...)

and thats it.

good luck.

Hi, I have almost the same problem that in the original post ‘irehman’ having (every thing same except the WHERE clause), But in my case I have 4 different quires, same tables, same where clause but column are different.

I have to develop this report in D2K I am little confuse that how I should handle this. Is there any way to handle multi query d2k report? As I said consider exactly the original posting of 'irehman'.

I am used to develop single query report this is first time I have new challenge. Please help to resolve this issue.

E.g.

Q1: select ENAME col from emp
Where job = ‘ANALYST’
AND option = 1

Q2: select SAL col from emp
Where job = ‘ANALYST’;
AND option = 2

Q3: select HIREDATE col from emp
Where job = ‘ANALYST’;
AND option = 3

Q4: select EMPNO col from emp
Where job = ‘ANALYST’;
AND option = 4

Do I have to create 4 SQL separate queries under data model; do I have to link each other or what else I can do? User will be entering option (1 or 2 or 3 or 4) from the parameter run time.

Thanks in advance.

for aph:

U have little bit another report, because datatypes in each query different.

u can use, for example -

select ENAME col from emp Where job = ‘ANALYST’
AND option = 1
union
select to_char(SAL, '99999.99') col from emp Where job = ‘ANALYST’;
AND option = 2
union
select to_char(HIREDATE,'mm/dd/rrrr') col from emp Where job = ‘ANALYST’
AND option = 3
union
select to_char(EMPNO) col from emp Where job = ‘ANALYST’
AND option = 4

Hi Shestakov,

Thanks for this solution, but I have to run one query at a time, depends what value user pass from the parameter it could be 1, 2, 3 or 4. The question is in this union solution how we can define :opt_no, I mean how the query will understand that option 2 will execute. Just give me little more detail so that I can start build query based on union.

Thanks in advance.
aph

1 step
- in DataModel->UserParameters u should create user parameter:
for examp. NB_OF_REPORT (type number)
2 step (depend from your startup procedure :
-- if u use parameter form :
--- create parameter form, in parameter form create field (with any names) and link this field with :NB_OF_REPORT parameter
(in this case had better for u - set type of field in parameter form - static list and type values (1,2,3,4) )
-- if u use RUN_PRODUCT(...) u should add new parameter in paramer_list
NB_OF_REPORT as text parameter and set needed value
-- if u use command line then :
runrep usr/pwd@db .... NB_OF_REPORT=2 (for example)

3. little change query:

select ENAME col from emp Where job = ‘ANALYST’
AND :NB_OF_REPORT = 1
union
select to_char(SAL, '99999.99') col from emp Where job = ‘ANALYST’;
AND :NB_OF_REPORT = 2
union
select to_char(HIREDATE,'mm/dd/rrrr') col from emp Where job = ‘ANALYST’
AND :NB_OF_REPORT = 3
union
select to_char(EMPNO) col from emp Where job = ‘ANALYST’
AND :NB_OF_REPORT = 4

lood luck.

Hi Shestakov,

According to your suggestion I have tried to create union query but I am getting this error. Actually the requirement of the report is similar to the below example, I mean I have to use group functions.

select deptno, sum(sal) from emp
where :p_opt_no = 1
UNION
select deptno, sum(comm) from emp
where :p_opt_no = 2
group by deptno
order by deptno

SQL> /
select deptno, sum(sal) from emp
*
ERROR at line 1:
ORA-00937: not a single-group group function

Is there any other way to get it done, Or do I have to change the query to make it correct.

Regards,
aph

select deptno, sum(sal) from emp
where a_opt_no = 1
group by deptno
UNION
select deptno, sum(comm) from emp
where a_opt_no = 2
group by deptno
order by deptno
/

because GROUP BY relates for each select in UNION.

Thanks Shestakov,

What an excellent knowledge and professional approach.
I am very happy I got my 100% accurate results.

I am very glad that you spend your valuable time to resolve this issue.

Thanks again for this help.
Regards,

aph