• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1340
  • Last Modified:

Using substitution variable in Report Builder

Hi there,
    How can one use the substitution variable in Report Builder at runtime so that the end user has the option of using the where clause with any field/column of his choice. In SQL*Plus, the follwoing query works fine:
 
  select empno,ename,&column_name
from emp
where &condition1 and &condition2
order by &order_column;

but when I uses the same syntax in the query builder of report builder, syntax errors are given. Any ideas? How can we use the same query in Report Builder? I have used query in Report Builder having one or more columns defined as bind variable so that their values are given at runtime having the following syntax:

select ename,sal,job
from emp
where empno = :P_1;

I want that the end user should have the option of using any column/field with the where clause at runtime so that he is not restricted to give values of only those columns which have been previously defined in the query.

Thanks in advance.


Hassan
0
hayub
Asked:
hayub
1 Solution
 
Helena Markováprogrammer-analystCommented:
You can use this syntax:

select ename,sal,job
from emp
where empno = nvl(:P_1,empno);

so a user can leave a parameter blank. With this method, if :P_1 is left blank at runtime the query will evaluate to:

select ename,sal,job from emp where empno = empno;

There can be more parameters added to the parameter form there.
0
 
hayubAuthor Commented:
Hi Henka,
    Thanks for you answer. It works but my question is that one should be able to write where clause in the parameter so that one can write statements like where sal >1500 etc. The option which you gave only accepts specific values for the parameters.

 I have used the follwoing query in report builder and it
works fine for only one column:

SELECT ALL EMP.ENAME, EMP.JOB, EMP.HIREDATE, EMP.SAL,emp.deptno
FROM EMP
 &P_1

if I want to add another column what will be the syntax?

The above query accepts the input for any column so that I can give where clause in the following manner for any column

where deptno = 20
where sal >1500

and so on

I hope you undertand my problem now.

Thanks

Hassan
0
 
M-AliCommented:
Hi,

Create a USER_PARAMETER in the report designer, lets say P_FILTER. Then in your query use:

SELECT EMP.ENAME, EMP.JOB, EMP.HIREDATE, EMP.SAL,emp.deptno
FROM EMP
WHERE &P_FILTER;

Here your filter can contain as many conditions as u want like:
(i) ename = 'SMITH'
(ii) ename = 'SMITH' and deptno=10

But the filter can not be empty. For example if no conditions have been specified make sure the filter contains somehtin like '1=1'.

Ali
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
venkat_idCommented:
hi hayub,
u can use leccical parameter.
1.define a user parameter first .say <:def_where>
2.now depending on ur req give the default where in  
   after parameter form
3.in the data model query query give our parameter as
  &def_where
things will work fine.
venkat
0
 
hayubAuthor Commented:
Hi Folks,
    Thanks a lot guys for you comments but my problem actuall lies when the quey is a join query between two tables as follows:



 SELECT ALL EMP.ENAME,
EMP.JOB, EMP.SAL, EMP.HIREDATE, EMP.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE (EMP.DEPTNO = DEPT.DEPTNO)
&where_clause

this gives syntax error. If the above quey is without the join WHERE clause then it works fine but in the present case it gives errors. Any ideas how can I solve this?

TIA

Hassan
0
 
M-AliCommented:
Since you already have one WHERE condition i.e. (EMP.DEPTNO = DEPT.DEPTNO), make sure your WHERE_CLAUSE starts off with a space and an "AND" statement.

If this is not the problem, I suggest you post your actual query string, sample data entered into the where clause, and the error message it gives. Would help ppl to solve the problem..

Regards
Ali
0
 
hayubAuthor Commented:
Thanks a lot Ali, it worked!!!!

regards


Hassan
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now