Home » Developer & Programmer » Reports & Discoverer » Can I use parameters in views to be called in Discoverer (Oracle 10G, Discover Plus version 10.1)
Can I use parameters in views to be called in Discoverer [message #621844] Tue, 19 August 2014 02:04 Go to next message
kupstar
Messages: 16
Registered: November 2013
Location: PNG
Junior Member
Hi experts,

How can I pass a parameter in a oracle view so these parameters can be passed during running a Discoverer report? Below is a SQL statement which I would like to use to create the view where date1 and date2 would be my parameters: Please advise if there is a workaround.
SELECT s.employee#,
                   paypoint,
                   s.award,
                   REPLACE (street_address, ',') AS Street_address,
                   e.first_name,
                   e.surname,
                   pt.nr_dependants AS Dependents
              FROM substantive s, employee e, peemptax pt
             WHERE     s.employee# = e.employee#
                   AND e.employee# = pt.employee#
                   AND pt.end_date IS NULL
                   AND commence_date =
                          (SELECT MAX (commence_date)
                             FROM substantive
                            WHERE employee# = s.employee#
                                  AND commence_date between to_date(date1,'dd-mon-rrrr') and to_date(date2,'dd-mon-rrrr')))
                               

*BlackSwan added {code} tags. http://www.orafaq.com/forum/t/174502/
Please do so yourself in the future.

[Updated on: Tue, 19 August 2014 21:04] by Moderator

Report message to a moderator

Re: Can I use parameters in views to be called in Discoverer [message #621847 is a reply to message #621844] Tue, 19 August 2014 02:09 Go to previous messageGo to next message
Buchas
Messages: 101
Registered: March 2006
Senior Member
Have you read this one?

http://stackoverflow.com/questions/9024696/creating-parameterized-views-in-oracle11g

(I dont know about Oracle 10 though)

[Updated on: Tue, 19 August 2014 02:10]

Report message to a moderator

Re: Can I use parameters in views to be called in Discoverer [message #621851 is a reply to message #621847] Tue, 19 August 2014 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Both ways explained in this link work in 10g (and even in previous versions since V7 for package variables and 8.1.5 for context ones).

Re: Can I use parameters in views to be called in Discoverer [message #621944 is a reply to message #621851] Tue, 19 August 2014 19:47 Go to previous messageGo to next message
kupstar
Messages: 16
Registered: November 2013
Location: PNG
Junior Member
Thankyou Buchas and Michel,
I created the context and package however, how do I pass in the dates values from the discoverer report? It's not even prompting for parameter values, instead the report returns nothing.

Am i missing something? Please advise.
Re: Can I use parameters in views to be called in Discoverer [message #621950 is a reply to message #621944] Wed, 20 August 2014 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to set the package or context variables before querying the view.

Re: Can I use parameters in views to be called in Discoverer [message #621951 is a reply to message #621950] Wed, 20 August 2014 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Here's one example using the default USERENV context: http://www.orafaq.com/forum/mv/msg/184955/570811/#msg_570811.

[Updated on: Wed, 20 August 2014 01:01]

Report message to a moderator

Re: Can I use parameters in views to be called in Discoverer [message #621952 is a reply to message #621951] Wed, 20 August 2014 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Another example with a custom context: http://www.orafaq.com/forum/mv/msg/155176/443739/#msg_443739.

Re: Can I use parameters in views to be called in Discoverer [message #621953 is a reply to message #621952] Wed, 20 August 2014 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And here are 2 examples with package variables:
http://www.orafaq.com/forum/mv/msg/150912/425657/#msg_425657
http://www.orafaq.com/forum/mv/msg/156918/452007/#msg_452007

Re: Can I use parameters in views to be called in Discoverer [message #622083 is a reply to message #621953] Wed, 20 August 2014 17:51 Go to previous messageGo to next message
kupstar
Messages: 16
Registered: November 2013
Location: PNG
Junior Member
Thanks Michael, but I wont be passing in the parameters from an application or from SQL plus console. I want users to pass in the date values from the Discoverer report. In these examples you posted, the values are set from SQL plus which users would not be able to do that.

I'm still stuck and thinking how I can allow users to pass in the dates from the Discoverer report.
Re: Can I use parameters in views to be called in Discoverer [message #622096 is a reply to message #622083] Thu, 21 August 2014 01:47 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't use Discoverer. However, if it is anything like Reports, then - instead of creating a view (based on the SELECT statement you posted in the first message) and performing
select from your_view
in Discoverer, use the original SELECT statement in Discoverer (i.e. put the whole SELECT from the first message into Discoverer).

Of course, you'd have to create DATE1 and DATE2 parameters first.

Once again: if it is similar to Reports, which contains a parameter form where users can enter parameter values, I hope that Discoverer offers the same (or similar) functionality.
Re: Can I use parameters in views to be called in Discoverer [message #622162 is a reply to message #622096] Thu, 21 August 2014 21:53 Go to previous messageGo to next message
kupstar
Messages: 16
Registered: November 2013
Location: PNG
Junior Member
I noticed discoverers reports cannot be developed from a custom query. Instead they can only be developed from a table or a view which is why I'm unable to pass in the parameters in the complext query.
Re: Can I use parameters in views to be called in Discoverer [message #622164 is a reply to message #622162] Fri, 22 August 2014 00:11 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I see ... sorry, then, I wouldn't know what to do.

As OraFAQ forum lacks in Discoverer users (so you might have to wait indefinitely for the answer), consider asking for help elsewhere (such as OTN forums).
Re: Can I use parameters in views to be called in Discoverer [message #622166 is a reply to message #622164] Fri, 22 August 2014 00:47 Go to previous messageGo to next message
kupstar
Messages: 16
Registered: November 2013
Location: PNG
Junior Member
Noted Littlefoot. I ended up writing a excel macro to generate the report instead.

[Updated on: Fri, 22 August 2014 00:47]

Report message to a moderator

Re: Can I use parameters in views to be called in Discoverer [message #622167 is a reply to message #622166] Fri, 22 August 2014 00:50 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm glad you solved the problem; thank you for letting us know.
Previous Topic: calculation in matrix report
Next Topic: Displaying Images from File System
Goto Forum:
  


Current Time: Thu Mar 28 16:54:16 CDT 2024