OraFAQ Forum: Reports & Discoverer » creating a parameter to pull ALL records or one in reports

Home » Developer & Programmer » Reports & Discoverer » creating a parameter to pull ALL records or one in reports (reports 10G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

creating a parameter to pull ALL records or one in reports [message #437995]

Wed, 06 January 2010 12:38

tuckersuz
Messages: 26
Registered: July 2009

Junior Member

Hello:

I would like to create a report that when run, a user can select the text 'ALL', or the rest of the list of available fields from the user parameter on the runtime parameter form. I tried unioning the query in the list of values with 'ALL' from dual, and the report is not recognizing this. Any tips/suggestions would be appreciated.

Many thanks

Report message to a moderator

Re: creating a parameter to pull ALL records or one in reports [message #438002 is a reply to message #437995]

Wed, 06 January 2010 13:17

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What is ALL, actually? From the topic title, I understood that it means "all records that query returns". For example, if it was a report based on the EMP table in Scott's sample schema, "ALL" would return 14 records (all employees).

From the message text, I changed my mind and now I think that you are talking about fields (columns), so if the parameter value was "ALL", you'd

select empno, ename, job, mgr, hiredate, sal, comm, deptno
from emp

but you could also choose "EMPNO" which would then imply

select empno
from emp

or "ENAME" which would be

select ename 
from emp

etc.

Now which one is it?

If the first one (i.e. you are talking about number of records), you could do that with a lexical parameter. If "ALL", then lexical parameter would look like

WHERE 1 = 1

(i.e. no restriction). If it was, for example, "10", it would be

WHERE rownum <= 10

.

If it is the second option, I think that you can't do that easily; you could, though, use field's Format Trigger and return FALSE if you don't want to display values different from the one passed by a parameter, but - dynamically create output - I don't think so.

Report message to a moderator

Re: creating a parameter to pull ALL records or one in reports [message #438259 is a reply to message #438002]

Thu, 07 January 2010 12:52

tuckersuz
Messages: 26
Registered: July 2009

Junior Member

Thanks for the help. To clarify:

The report has several parameters. With each parameter in this report, I have the following: (this is just a simplified example)

select e.name
      ,e.title
      ,e.job
      ,t.task
from employees e
    ,task t
where e.job=nvl(decode(:p_job,'ALL','',:p_job),e.job)
  and e.title=nvl(decode(:p_title,'ALL','',:p_title),e.title)
  and t.emp_id=e.emp_id

In the report, I want to use the paper parameter form, and within each parameter I put on the pp form, for the list of values, I want to offer the user the ability to select 'ALL' to return all records. So, when the user runs the report, they can select 'ALL', or click the down arrow to choose an individual item. For example, when the report is run, there will be a text:
(this is the runtime parameter form)

              ENTER PARAMETER VALUES:
  Select Job:             ALL (down arrow here)
                          raking_leaves  (these are the 
                          cleaning        individual choices)
                          washing_cars

  Select title:           ALL (down arrow here)
                          greenskeeper  (these are the
                          housekeeper   individual choices)
                          general_staff

I'm having trouble with putting values in the list of values for each parameter. I tried unioning 'ALL' from dual with the table that produces the LOV values, done in the Property Inspector, List of Values,but I cannot seem to get reports to recognize 'ALL' from dual. Now, if I remove 'ALL' from the code in the data model :
where e.job=nvl(decode(:p_job,'ALL','',:p_job),e.job)[/code] changed to [code]e.job=nvl(decode(:p_job,'','',:p_job),e.job)
and e.title=nvl(decode(:p_title,'ALL','',:p_title),e.title) changed to and e.title=nvl(decode(:p_title,'ALL','',:p_title),e.title)
and t.emp_id=e.emp_id and t.emp_id=e.emp_id

and I change the parameter values as follows (to return all records, the user selects the blank at the end of the list of values,
which is a null value pulled from the table where the parameter values are being pulled):

              ENTER PARAMETER VALUES:
  Select Job:             raking_leaves  (these are the       (down arrow here)
                          cleaning        individual choices)
                          washing_cars
                          (null, or blank value at the end of the LOV, which pulls all records)

  Select title:             greenskeeper                    (down arrow here)
                          housekeeper
                          general_staff
                          (null, or blank value at the end of the LOV, which pulls all records)

Now the report will pull all records if I make all the changes mentioned above. The user runs the report, and, if they want all records (say, all jobs), then the user clicks the down arrow, and scrolls all the way down to the end, and selects the last, blank value.
How can I change the SQL, or the LOV, so that the text 'ALL' shows, and, if the user selects 'ALL', all records show? Likewise, if I can place the text 'ALL' within the LOV, I would like it to show either at the top or the bottom of the LOV.

many thanks

[EDITED by LF: removed superfluous empty lines, added a few [code] and [pre] tags to improve readability]

[Updated on: Thu, 07 January 2010 13:09] by Moderator

Report message to a moderator

Re: creating a parameter to pull ALL records or one in reports [message #438263 is a reply to message #438259]

Thu, 07 January 2010 13:05

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Thank you for the explanation, now it is much more clear!

Quote:

I cannot seem to get reports to recognize 'ALL' from dual

That's odd; I don't have Reports here to test now, but I'll try to take a look tomorrow.

Until then, did you try something like this in LoV?

select value 
from (select 'ALL' value from dual
      union
      select value       -- this returns 'raking_leaves', 'washing_cars', etc.
      from some_table
     )

As of NULL value which seems to be working (but is placed at the end of the list), how about "ORDER BY value NULLS FIRST" option which would place it to the beginning so - if you don't touch anything, you'll get all records by default?

Report message to a moderator

Re: creating a parameter to pull ALL records or one in reports [message #439487 is a reply to message #437995]

Sun, 17 January 2010 14:10

dude4084
Messages: 222
Registered: March 2005
Location: Mux

Senior Member

Hi the forum by filtering my name.

I think i have replied such question in this forum before. Kindly search

Anyhow, here is quick tip.

use where clause as follow

where ((job is null and job=job) or (job is not null or job=:mjob))

and then for LOV for :mjob

write query as follow:

(correct it yourself if wrong syntax as i dont have SQL terminal with me near by)

SELECT * FROM (
Select to_char(null) "JOBECODE", 'ALL RECORDS'
from dual
union all
select jobcode, jobtitle from yourtable)
ORDER BY DECODE(JOBCODE, NULL, 0, 1)

GOOD LUCK

-DUDE

Report message to a moderator

Previous Topic:	Report Server Clarification
Next Topic:	Query in Reports

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 08:16:55 CDT 2024