Home » Developer & Programmer » Reports & Discoverer » creating a parameter to pull ALL records or one in reports (reports 10G)
creating a parameter to pull ALL records or one in reports [message #437995] Wed, 06 January 2010 12:38 Go to next message
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
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 Go to previous messageGo to next message
Littlefoot
Messages: 20894
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.
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 20894
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?
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 Go to previous message
dude4084
Messages: 221
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
Previous Topic: Report Server Clarification
Next Topic: Query in Reports
Goto Forum:
  


Current Time: Mon Dec 05 08:46:19 CST 2016

Total time taken to generate the page: 0.11799 seconds