Is it possible to put in "IF ...ELSE" or "Case" in WHERE CLAUSE?

Dear all...

need your help... i am now trying to create a report using SQL reporting services... when declare all the @.parameters needed in where clause, i have come across a problem. where one of the parameters that prompting user to key in...i need to put in some condition.

select..............(blah blah).....
.....(SELECT CASE WHEN
((SELECT COUNT(*)
FROM tbl_OutpatientReg OPT
WHERE OPT.PatientID = tbl_Patient.PatientID)) = 1 THEN 0 ELSE 1 END) AS PTType .....................(blah blah)......

where (CONVERT(Varchar(10), tbl_OutpatientReg.VisitDatetime, 103) BETWEEN @.FromDate AND @.ToDate) OR (@.FromDate = ' ') OR (@.ToDate = ' ')
AND (@.PatientType = CASE WHEN
(SELECT COUNT(*)
FROM tbl_OutpatientReg OPT
WHERE OPT.PatientID = tbl_Patient.PatientID) = 1 THEN 0 ELSE 1 END)

my situition is something like above, i know i have done something wrong in teh WHERE clause for the @.PatientType... can i ask how to restrict the parameters entered by user, let's say if user enter parameter "0", then the visitcount is 1, if enter "1" then the visit count refers to more than 1...

thanks in advanced ...............Your post is kind of confusing regarding requirements, but part of your problem may be due to using brackets where they are not necessary, and not using them where they might be necessary to specify logical operations.

--Your Version:
where (CONVERT(Varchar(10), tbl_OutpatientReg.VisitDatetime, 103) BETWEEN @.FromDate AND @.ToDate)
OR (@.FromDate = ' ')
OR (@.ToDate = ' ')
AND (@.PatientType = CASE
WHEN (SELECT COUNT(*)
FROM tbl_OutpatientReg OPT
WHERE OPT.PatientID = tbl_Patient.PatientID) = 1 THEN 0
ELSE 1
END)

--Unnecessary brackets removed:
where CONVERT(Varchar(10), tbl_OutpatientReg.VisitDatetime, 103) BETWEEN @.FromDate AND @.ToDate
OR @.FromDate = ' '
OR @.ToDate = ' '
AND @.PatientType = CASE
WHEN (SELECT COUNT(*)
FROM tbl_OutpatientReg OPT
WHERE OPT.PatientID = tbl_Patient.PatientID) = 1 THEN 0
ELSE 1
END

--Useful brackets added:
where (CONVERT(Varchar(10), tbl_OutpatientReg.VisitDatetime, 103) BETWEEN @.FromDate AND @.ToDate
OR @.FromDate = ' '
OR @.ToDate = ' ')
AND @.PatientType = CASE
WHEN (SELECT COUNT(*)
FROM tbl_OutpatientReg OPT
WHERE OPT.PatientID = tbl_Patient.PatientID) = 1 THEN 0
ELSE 1
END|||ya...thanks for reminding me...as there are too many parameters to pass, i also confused... ;) anyway, really appreciate ur help

Is it possible to prevent user to edit parameters in adress bar?

I have reports in report server which are accessed by url access with parameters.

When the report is generated the whole url string is displayed in the adress bar with parameters. Is there any way to prevent the user to change the parameter value?

Any ider whould be very appreciated!

You could use a html form and use POST instead of using GET via the url.

See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=301748&SiteID=1

Is it possible to hide the report parameters after click on the view report button?

i am working in SQL Reporting services 2005. i had a requirement that
after selecting all the report paramters and click on the view report
button, i need to turn off or hide the report parameters.
please let me know is it possible to implement to do this.
any suggesstion
Thanks
VinodThere is a button the right side of the page of the reports browser
that look like to arrows on top of each other, click that and it will
hide the parameters. There are actually two sets, one for the
parameters and one for the web page header. Hope that helps.
Vinod wrote:
> i am working in SQL Reporting services 2005. i had a requirement that
> after selecting all the report paramters and click on the view report
> button, i need to turn off or hide the report parameters.
> please let me know is it possible to implement to do this.
> any suggesstion
> Thanks
> Vinod|||Thanks for the reply Kerrie.
i have seen the hide button icon in the report layout.but my
requirement is that i need to hide the parameters when i click on the
view report button in the report layout after selecting the all the
report parameters.
if user wants to modify the criteria again, he needs to click on the
hide icon in the report layout. then select the criteria and view the
report.
please let me know if you are not clear
Thanks
vinod
Kerrie wrote:
> There is a button the right side of the page of the reports browser
> that look like to arrows on top of each other, click that and it will
> hide the parameters. There are actually two sets, one for the
> parameters and one for the web page header. Hope that helps.
>
> Vinod wrote:
> > i am working in SQL Reporting services 2005. i had a requirement that
> > after selecting all the report paramters and click on the view report
> > button, i need to turn off or hide the report parameters.
> >
> > please let me know is it possible to implement to do this.
> >
> > any suggesstion
> >
> > Thanks
> > Vinod|||I'm pretty sure there's no function like that in RS out of the box. But I'm
thinking that if you can modify the View Report button's code, you could add
a java script that redirects to a url with parameters toolbar=false and the
chosen parameters. You'd have to tweek the RS GUI to do it, and I don't know
how easy that is.
Alternatively, create a aspx. page that creates the parameters to be chosen,
and send the users to a report using the same paramtererized url as
suggested above.
Kaisa M. Lindahl Lervik
"Vinod" <vinodsh_82@.hotmail.com> wrote in message
news:1161352815.498895.319680@.h48g2000cwc.googlegroups.com...
> Thanks for the reply Kerrie.
> i have seen the hide button icon in the report layout.but my
> requirement is that i need to hide the parameters when i click on the
> view report button in the report layout after selecting the all the
> report parameters.
> if user wants to modify the criteria again, he needs to click on the
> hide icon in the report layout. then select the criteria and view the
> report.
> please let me know if you are not clear
> Thanks
> vinod
>
> Kerrie wrote:
>> There is a button the right side of the page of the reports browser
>> that look like to arrows on top of each other, click that and it will
>> hide the parameters. There are actually two sets, one for the
>> parameters and one for the web page header. Hope that helps.
>>
>> Vinod wrote:
>> > i am working in SQL Reporting services 2005. i had a requirement that
>> > after selecting all the report paramters and click on the view report
>> > button, i need to turn off or hide the report parameters.
>> >
>> > please let me know is it possible to implement to do this.
>> >
>> > any suggesstion
>> >
>> > Thanks
>> > Vinod
>|||Thanks for the reply Kaisa.
i am not creating a aspx page for criteria. i have added 15 report
parameters in the report layout iteself. everything is fine upto this
point.
but my client requriment is that we dont want to show the report
parameters in the report layout after clicking on the view report
button. it should be hided.
let me know is it possible.
Thanks
Vinod
Kaisa M. Lindahl Lervik wrote:
> I'm pretty sure there's no function like that in RS out of the box. But I'm
> thinking that if you can modify the View Report button's code, you could add
> a java script that redirects to a url with parameters toolbar=false and the
> chosen parameters. You'd have to tweek the RS GUI to do it, and I don't know
> how easy that is.
> Alternatively, create a aspx. page that creates the parameters to be chosen,
> and send the users to a report using the same paramtererized url as
> suggested above.
> Kaisa M. Lindahl Lervik
> "Vinod" <vinodsh_82@.hotmail.com> wrote in message
> news:1161352815.498895.319680@.h48g2000cwc.googlegroups.com...
> > Thanks for the reply Kerrie.
> >
> > i have seen the hide button icon in the report layout.but my
> > requirement is that i need to hide the parameters when i click on the
> > view report button in the report layout after selecting the all the
> > report parameters.
> >
> > if user wants to modify the criteria again, he needs to click on the
> > hide icon in the report layout. then select the criteria and view the
> > report.
> >
> > please let me know if you are not clear
> >
> > Thanks
> > vinod
> >
> >
> > Kerrie wrote:
> >> There is a button the right side of the page of the reports browser
> >> that look like to arrows on top of each other, click that and it will
> >> hide the parameters. There are actually two sets, one for the
> >> parameters and one for the web page header. Hope that helps.
> >>
> >>
> >> Vinod wrote:
> >> > i am working in SQL Reporting services 2005. i had a requirement that
> >> > after selecting all the report paramters and click on the view report
> >> > button, i need to turn off or hide the report parameters.
> >> >
> >> > please let me know is it possible to implement to do this.
> >> >
> >> > any suggesstion
> >> >
> >> > Thanks
> >> > Vinod
> >

Is it possible to have a List box or a combo box instead of a Drop downlist

Hi

Is it possible to have a list box or a combo box for selecting parameters instead of having a drop down list...

and If yes how can i do it?

Regards

Karen

Sure you can. Just drag a listbox control onto your page. Set the autopostback property to true if you want to allow postback and SelectionMode to Multiple if you want to allow more than one item to be selected. Then to access the items programmatically, you can do something like this.

Dim xAsString =String.Empty

For iAsInteger = 0To ListBox1.Items.Count - 1

If ListBox1.Items(i).Selected =TrueThen

x = x & ListBox1.Items(i).Text &","

EndIf

Next

|||

Thanks adyle,

it is possible to do this in the report manager??

Regards

Karen

|||

Hi Karen,

Can u tell me how to add a dropdown to my report ?

thanx

Magik

|||

Magic

do u wanna have a drop down list that has check boxes in it for multiple selections if so.. then

1) in your report parameter select the parameter that u want make as a dropdown and then select the multivalue check boxes...

2) if you want u can create a different dataset to popluate the drop down list and select query from the avaliable values.. and then select the dataset etc. and click ok.

3) in your stored procedure should like this...

Declare the @.Param as nvarchar(max)

@.sqltext nvarchar(max)

if the @.Param is NULL

Begin

select * from whatever and include the joins

Else

Begin

Select @.SqlText = 'Select * from tablename where Field or columnname in @.Param'

Exceute (@.sqltext)

End

Hope this helps,

Regards

Karen

sql

Is it possible to have a List box or a combo box instead of a Drop downlist

Hi

Is it possible to have a list box or a combo box for selecting parameters instead of having a drop down list...

and If yes how can i do it?

Regards

Karen

Where? No, in the Report Manager. Yes, in custom applications that use the report viewer controls.|||

using Report manager.. how can i use it in report viewer control... cause i am letting the users view the same reports using Report viewer.. can u please give me some links as to how to do it...

regards

Karen

|||

In the Report Viewer control you can hide the parameter area and replace it with your own. You may find the following article useful.

|||

Teo Lachev,

Thanks for ur answer... is it possible to do something like this in the stored procedure directly.. cause i am calling a stored procedure for that particular parameter and it list the all the plans in that drop down box...

My main Stored procedure to run the Report has 4 Parameter... and to populate one of the Parameters in the report i am using another sproc so that i can get all the plans... and if this is the case .... how do i get the values in a list...

Regards,

Karen

|||

So, once you decide to implement your own parameter area, it is up to you what controls to use or validation logic to implement to validate the parameter values. Essentially, you are building a custom WinForms or ASP.NET front end to handle parameters.

Is it possible to have a List box or a combo box instead of a Drop downlist

Hi

Is it possible to have a list box or a combo box for selecting parameters instead of having a drop down list...

and If yes how can i do it?

Regards

Karen

Where? No, in the Report Manager. Yes, in custom applications that use the report viewer controls.|||

using Report manager.. how can i use it in report viewer control... cause i am letting the users view the same reports using Report viewer.. can u please give me some links as to how to do it...

regards

Karen

|||

In the Report Viewer control you can hide the parameter area and replace it with your own. You may find the following article useful.

|||

Teo Lachev,

Thanks for ur answer... is it possible to do something like this in the stored procedure directly.. cause i am calling a stored procedure for that particular parameter and it list the all the plans in that drop down box...

My main Stored procedure to run the Report has 4 Parameter... and to populate one of the Parameters in the report i am using another sproc so that i can get all the plans... and if this is the case .... how do i get the values in a list...

Regards,

Karen

|||

So, once you decide to implement your own parameter area, it is up to you what controls to use or validation logic to implement to validate the parameter values. Essentially, you are building a custom WinForms or ASP.NET front end to handle parameters.

Is it possible to customize the look of the parameters form?

Hi all...
When a report is viewed in web browser, a parameters form appear at the top
of the screen so that user can enter parameters and then press View Report.
Is it possible to display a title before that form? or to customize the
colors and fonts for that form?
Thanks
JaimeHave the SAME question!! =:)
"Jaime Stuardo" wrote:
> Hi all...
> When a report is viewed in web browser, a parameters form appear at the top
> of the screen so that user can enter parameters and then press View Report.
> Is it possible to display a title before that form? or to customize the
> colors and fonts for that form?
> Thanks
> Jaime|||You can adjust the colours of the parameters form by changing the
htmlviewer.css stylesheet. Supposedly if you have installed the hot fix for
sp2 then you should find a proptery called htmlviewrstylesheet which you can
set to any style sheet you have created to be th edefault for all reports but
I havent found this.
so in the url of the reports when they are called from another web page I
use the command rc:Stylesheet=stylesheetname
"=:)" wrote:
> Have the SAME question!! =:)
> "Jaime Stuardo" wrote:
> > Hi all...
> >
> > When a report is viewed in web browser, a parameters form appear at the top
> > of the screen so that user can enter parameters and then press View Report.
> >
> > Is it possible to display a title before that form? or to customize the
> > colors and fonts for that form?
> >
> > Thanks
> > Jaime|||IS there a way of hiding the parameters completely as I dont want to
allow the users to change the parameters.
Sorry for the dumb question but this is my 2nd day on reporting
services and i'm trying to get to grips with it very quickly|||Hi Jim,
&rc:Parameters=False is an option.
(have a look here:
http://weblogs.asp.net/mhawley/archive/2004/11/17/259178.aspx for more).
HTH,
Ed Richard
"JimW13UK" <jimw13uk@.uku.co.uk> wrote in message
news:1120725823.751966.67920@.g14g2000cwa.googlegroups.com...
> IS there a way of hiding the parameters completely as I dont want to
> allow the users to change the parameters.
> Sorry for the dumb question but this is my 2nd day on reporting
> services and i'm trying to get to grips with it very quickly
>|||Thanks Ed. The link you posted has answered my question. I can program my
custom parameter form using either VB.NET or C# and using ReportViewer
control to actually show the report.
Jaime
"Ed Richard" wrote:
> Hi Jim,
> &rc:Parameters=False is an option.
> (have a look here:
> http://weblogs.asp.net/mhawley/archive/2004/11/17/259178.aspx for more).
> HTH,
> Ed Richard
> "JimW13UK" <jimw13uk@.uku.co.uk> wrote in message
> news:1120725823.751966.67920@.g14g2000cwa.googlegroups.com...
> > IS there a way of hiding the parameters completely as I dont want to
> > allow the users to change the parameters.
> > Sorry for the dumb question but this is my 2nd day on reporting
> > services and i'm trying to get to grips with it very quickly
> >
>
>

Is it possible to call an Oracle stored procedure and pass parameters to them from reporti

I've set up a shared datasource to oracle with the option of prompting for
credientals. The report works with just static SQL in the Query designer. I
have been trying for a number of days now to use a call to an oracle stored
procedure or function as my report data set without success.
The function called sp_ListEmp has one parameter of type number. To call the
function I have tried adding {call sp_ListEmp(?)} into the Query designer
and setting the command type to Text. I have added the parameter ? and made
that equal to a Report parameter EMPNO of Integer type that I have added to
the report. So I have in the parameter tab of the Dataset
?=Parameters!EMPNO.Value. I recieve the following error message 'An error
occurred while executing the query. ORA-01036: illegal variable
name/number.'
For the stored procedure curspkg_join.open_join_cursor1 I have not even been
able to call the procedure with recieving an error for the second parameter
which is of type refcursor. Does anyone know if it is possible to call
Oracle stored procedures and pass parameters to them from reporting
services?
I have added the code for the stored procedure and function and also the
tables for which I have been working on below and also the commands I use in
SQL Plus to prove that the actual function and stored procedure work. This
is causing me much woe.
Create DEPT table and insert some rows
CREATE TABLE DEPT
(DEPTNO NUMBER(2,0) NOT NULL,
DNAME VARCHAR2(14) NULL,
LOC VARCHAR2(13) NULL,
PRIMARY KEY (DEPTNO)
);
INSERT INTO Dept VALUES(11,'Sales','Texas');
INSERT INTO Dept VALUES(22,'Accounting','Washington');
INSERT INTO Dept VALUES(33,'Finance','Maine');
Create EMP table and insert some rows
CREATE TABLE EMP
(EMPNO NUMBER(4,0) NOT NULL,
ENAME VARCHAR2(10) NULL,
JOB VARCHAR2(9) NULL,
MGR NUMBER(4,0) NULL,
SAL NUMBER(7,2) NULL,
COMM NUMBER(7,2) NULL,
DEPTNO NUMBER(2,0) NULL,
FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
PRIMARY KEY (EMPNO)
);
INSERT INTO Emp VALUES(123,'Bob','Sales',555,35000,12,11);
INSERT INTO Emp VALUES(321,'Sue','Finance',555,42000,12,33);
INSERT INTO Emp VALUES(234,'Mary','Account',555,33000,12,22);
Create package for Function example
create or replace package packperson
as
type cursorType is ref cursor;
end;
/
Create Function for Function example
create or replace function sp_ListEmp (n_EMPNO NUMBER) return
packperson.cursortype
as
l_cursor packperson.cursorType;
begin
open l_cursor for select ename as NAME, empno as NUM from emp where
empno = n_EMPNO order by ename;
return l_cursor;
end;
From SQL Plus I call this by
SQL> variable c refcursor
SQL> exec :c := sp_ListEmp(123)
SQL> print c
Create package for sp example
CREATE OR REPLACE PACKAGE curspkg_join AS
TYPE t_cursor IS REF CURSOR ;
Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
t_cursor);
END curspkg_join;
/
Create package body for sp example
CREATE OR REPLACE PACKAGE BODY curspkg_join AS
Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
t_cursor)
IS
v_cursor t_cursor;
BEGIN
IF n_EMPNO <> 0
THEN
OPEN v_cursor FOR
SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND EMP.EMPNO = n_EMPNO;
ELSE
OPEN v_cursor FOR
SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;
END IF;
io_cursor := v_cursor;
END open_join_cursor1;
END curspkg_join;
/
From SQL Plus I call this by
SQL> variable c refcursor
SQL>exec curspkg_join.open_join_cursor1(123,:c)
SQL>print :cThe cursor in the stored procedure has to be an OUT REF cursor rather than a
IN OUT cursor.
Also make sure you use the managed Oracle provider (i.e. "Oracle" in the
data source dialog) and not OleDB.
You might also want to check this previous posting for further information
and a sample:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=049fe955-cec8-4a79-a5e5-a9c02873e19d&sloc=en-us
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Joe" <booksnore2@.netscape.net> wrote in message
news:%23dZ118wiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> I've set up a shared datasource to oracle with the option of prompting for
> credientals. The report works with just static SQL in the Query designer.
I
> have been trying for a number of days now to use a call to an oracle
stored
> procedure or function as my report data set without success.
> The function called sp_ListEmp has one parameter of type number. To call
the
> function I have tried adding {call sp_ListEmp(?)} into the Query designer
> and setting the command type to Text. I have added the parameter ? and
made
> that equal to a Report parameter EMPNO of Integer type that I have added
to
> the report. So I have in the parameter tab of the Dataset
> ?=Parameters!EMPNO.Value. I recieve the following error message 'An error
> occurred while executing the query. ORA-01036: illegal variable
> name/number.'
> For the stored procedure curspkg_join.open_join_cursor1 I have not even
been
> able to call the procedure with recieving an error for the second
parameter
> which is of type refcursor. Does anyone know if it is possible to call
> Oracle stored procedures and pass parameters to them from reporting
> services?
> I have added the code for the stored procedure and function and also the
> tables for which I have been working on below and also the commands I use
in
> SQL Plus to prove that the actual function and stored procedure work. This
> is causing me much woe.
>
> Create DEPT table and insert some rows
> CREATE TABLE DEPT
> (DEPTNO NUMBER(2,0) NOT NULL,
> DNAME VARCHAR2(14) NULL,
> LOC VARCHAR2(13) NULL,
> PRIMARY KEY (DEPTNO)
> );
> INSERT INTO Dept VALUES(11,'Sales','Texas');
> INSERT INTO Dept VALUES(22,'Accounting','Washington');
> INSERT INTO Dept VALUES(33,'Finance','Maine');
> Create EMP table and insert some rows
> CREATE TABLE EMP
> (EMPNO NUMBER(4,0) NOT NULL,
> ENAME VARCHAR2(10) NULL,
> JOB VARCHAR2(9) NULL,
> MGR NUMBER(4,0) NULL,
> SAL NUMBER(7,2) NULL,
> COMM NUMBER(7,2) NULL,
> DEPTNO NUMBER(2,0) NULL,
> FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
> PRIMARY KEY (EMPNO)
> );
> INSERT INTO Emp VALUES(123,'Bob','Sales',555,35000,12,11);
> INSERT INTO Emp VALUES(321,'Sue','Finance',555,42000,12,33);
> INSERT INTO Emp VALUES(234,'Mary','Account',555,33000,12,22);
> Create package for Function example
> create or replace package packperson
> as
> type cursorType is ref cursor;
> end;
> /
> Create Function for Function example
> create or replace function sp_ListEmp (n_EMPNO NUMBER) return
> packperson.cursortype
> as
> l_cursor packperson.cursorType;
> begin
> open l_cursor for select ename as NAME, empno as NUM from emp where
> empno = n_EMPNO order by ename;
> return l_cursor;
> end;
> From SQL Plus I call this by
> SQL> variable c refcursor
> SQL> exec :c := sp_ListEmp(123)
> SQL> print c
>
> Create package for sp example
> CREATE OR REPLACE PACKAGE curspkg_join AS
> TYPE t_cursor IS REF CURSOR ;
> Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> t_cursor);
> END curspkg_join;
> /
> Create package body for sp example
> CREATE OR REPLACE PACKAGE BODY curspkg_join AS
> Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> t_cursor)
> IS
> v_cursor t_cursor;
> BEGIN
> IF n_EMPNO <> 0
> THEN
> OPEN v_cursor FOR
> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> FROM EMP, DEPT
> WHERE EMP.DEPTNO = DEPT.DEPTNO
> AND EMP.EMPNO = n_EMPNO;
> ELSE
> OPEN v_cursor FOR
> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> FROM EMP, DEPT
> WHERE EMP.DEPTNO = DEPT.DEPTNO;
> END IF;
> io_cursor := v_cursor;
> END open_join_cursor1;
> END curspkg_join;
> /
> From SQL Plus I call this by
> SQL> variable c refcursor
> SQL>exec curspkg_join.open_join_cursor1(123,:c)
> SQL>print :c
>|||Thank you for your help,
I'm still unsure of the syntax that I should use in Reporting Services when
calling the sp. Where/how do I reference the out ref cursor? Example I call
the procedure using -
{ call test_package.get_customers(?, ) }
I get the following error 'The data extension Oracle does not support
unnamed parameters. Use named parameters instead.'
So my question is how do I reference the out ref cursor in Reporting
services?
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:e1$dlvyiEHA.1656@.TK2MSFTNGP09.phx.gbl...
> The cursor in the stored procedure has to be an OUT REF cursor rather than
a
> IN OUT cursor.
> Also make sure you use the managed Oracle provider (i.e. "Oracle" in the
> data source dialog) and not OleDB.
> You might also want to check this previous posting for further information
> and a sample:
>
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=049fe955-cec8-4a79-a5e5-a9c02873e19d&sloc=en-us
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Joe" <booksnore2@.netscape.net> wrote in message
> news:%23dZ118wiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> > I've set up a shared datasource to oracle with the option of prompting
for
> > credientals. The report works with just static SQL in the Query
designer.
> I
> > have been trying for a number of days now to use a call to an oracle
> stored
> > procedure or function as my report data set without success.
> > The function called sp_ListEmp has one parameter of type number. To call
> the
> > function I have tried adding {call sp_ListEmp(?)} into the Query
designer
> > and setting the command type to Text. I have added the parameter ? and
> made
> > that equal to a Report parameter EMPNO of Integer type that I have added
> to
> > the report. So I have in the parameter tab of the Dataset
> > ?=Parameters!EMPNO.Value. I recieve the following error message 'An
error
> > occurred while executing the query. ORA-01036: illegal variable
> > name/number.'
> >
> > For the stored procedure curspkg_join.open_join_cursor1 I have not even
> been
> > able to call the procedure with recieving an error for the second
> parameter
> > which is of type refcursor. Does anyone know if it is possible to call
> > Oracle stored procedures and pass parameters to them from reporting
> > services?
> >
> > I have added the code for the stored procedure and function and also the
> > tables for which I have been working on below and also the commands I
use
> in
> > SQL Plus to prove that the actual function and stored procedure work.
This
> > is causing me much woe.
> >
> >
> > Create DEPT table and insert some rows
> >
> > CREATE TABLE DEPT
> > (DEPTNO NUMBER(2,0) NOT NULL,
> > DNAME VARCHAR2(14) NULL,
> > LOC VARCHAR2(13) NULL,
> > PRIMARY KEY (DEPTNO)
> > );
> >
> > INSERT INTO Dept VALUES(11,'Sales','Texas');
> > INSERT INTO Dept VALUES(22,'Accounting','Washington');
> > INSERT INTO Dept VALUES(33,'Finance','Maine');
> >
> > Create EMP table and insert some rows
> >
> > CREATE TABLE EMP
> > (EMPNO NUMBER(4,0) NOT NULL,
> > ENAME VARCHAR2(10) NULL,
> > JOB VARCHAR2(9) NULL,
> > MGR NUMBER(4,0) NULL,
> > SAL NUMBER(7,2) NULL,
> > COMM NUMBER(7,2) NULL,
> > DEPTNO NUMBER(2,0) NULL,
> > FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
> > PRIMARY KEY (EMPNO)
> > );
> >
> > INSERT INTO Emp VALUES(123,'Bob','Sales',555,35000,12,11);
> > INSERT INTO Emp VALUES(321,'Sue','Finance',555,42000,12,33);
> > INSERT INTO Emp VALUES(234,'Mary','Account',555,33000,12,22);
> >
> > Create package for Function example
> >
> > create or replace package packperson
> > as
> > type cursorType is ref cursor;
> > end;
> > /
> >
> > Create Function for Function example
> >
> > create or replace function sp_ListEmp (n_EMPNO NUMBER) return
> > packperson.cursortype
> > as
> > l_cursor packperson.cursorType;
> > begin
> > open l_cursor for select ename as NAME, empno as NUM from emp where
> > empno = n_EMPNO order by ename;
> >
> > return l_cursor;
> > end;
> >
> > From SQL Plus I call this by
> >
> > SQL> variable c refcursor
> > SQL> exec :c := sp_ListEmp(123)
> > SQL> print c
> >
> >
> >
> > Create package for sp example
> >
> > CREATE OR REPLACE PACKAGE curspkg_join AS
> > TYPE t_cursor IS REF CURSOR ;
> > Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> > t_cursor);
> > END curspkg_join;
> > /
> >
> > Create package body for sp example
> >
> > CREATE OR REPLACE PACKAGE BODY curspkg_join AS
> > Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> > t_cursor)
> > IS
> > v_cursor t_cursor;
> > BEGIN
> > IF n_EMPNO <> 0
> > THEN
> > OPEN v_cursor FOR
> > SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> > FROM EMP, DEPT
> > WHERE EMP.DEPTNO = DEPT.DEPTNO
> > AND EMP.EMPNO = n_EMPNO;
> >
> > ELSE
> > OPEN v_cursor FOR
> > SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> > FROM EMP, DEPT
> > WHERE EMP.DEPTNO = DEPT.DEPTNO;
> >
> > END IF;
> > io_cursor := v_cursor;
> > END open_join_cursor1;
> > END curspkg_join;
> > /
> >
> > From SQL Plus I call this by
> >
> > SQL> variable c refcursor
> > SQL>exec curspkg_join.open_join_cursor1(123,:c)
> > SQL>print :c
> >
> >
>|||Make sure you use the generic text-based query designer with 2 panes, rather
than the visual query designer with 4 panes.
Regarding the OUT cursor - don't reference it, just omit that parameter.
A better practice for stored procedures is to set the command type of the
query to StoredProcedure instead of Text. In that case, the query text is
just the name of the stored procedure "test_package.get_customers". The
parameters will automatically be determined by report designer on clicking
on the refresh fields icon.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Joe" <booksnore2@.netscape.net> wrote in message
news:uZsHcq%23iEHA.384@.TK2MSFTNGP10.phx.gbl...
> Thank you for your help,
> I'm still unsure of the syntax that I should use in Reporting Services
when
> calling the sp. Where/how do I reference the out ref cursor? Example I
call
> the procedure using -
> { call test_package.get_customers(?, ) }
> I get the following error 'The data extension Oracle does not support
> unnamed parameters. Use named parameters instead.'
> So my question is how do I reference the out ref cursor in Reporting
> services?
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:e1$dlvyiEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > The cursor in the stored procedure has to be an OUT REF cursor rather
than
> a
> > IN OUT cursor.
> > Also make sure you use the managed Oracle provider (i.e. "Oracle" in the
> > data source dialog) and not OleDB.
> >
> > You might also want to check this previous posting for further
information
> > and a sample:
> >
>
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=049fe955-cec8-4a79-a5e5-a9c02873e19d&sloc=en-us
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "Joe" <booksnore2@.netscape.net> wrote in message
> > news:%23dZ118wiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> > > I've set up a shared datasource to oracle with the option of prompting
> for
> > > credientals. The report works with just static SQL in the Query
> designer.
> > I
> > > have been trying for a number of days now to use a call to an oracle
> > stored
> > > procedure or function as my report data set without success.
> > > The function called sp_ListEmp has one parameter of type number. To
call
> > the
> > > function I have tried adding {call sp_ListEmp(?)} into the Query
> designer
> > > and setting the command type to Text. I have added the parameter ? and
> > made
> > > that equal to a Report parameter EMPNO of Integer type that I have
added
> > to
> > > the report. So I have in the parameter tab of the Dataset
> > > ?=Parameters!EMPNO.Value. I recieve the following error message 'An
> error
> > > occurred while executing the query. ORA-01036: illegal variable
> > > name/number.'
> > >
> > > For the stored procedure curspkg_join.open_join_cursor1 I have not
even
> > been
> > > able to call the procedure with recieving an error for the second
> > parameter
> > > which is of type refcursor. Does anyone know if it is possible to call
> > > Oracle stored procedures and pass parameters to them from reporting
> > > services?
> > >
> > > I have added the code for the stored procedure and function and also
the
> > > tables for which I have been working on below and also the commands I
> use
> > in
> > > SQL Plus to prove that the actual function and stored procedure work.
> This
> > > is causing me much woe.
> > >
> > >
> > > Create DEPT table and insert some rows
> > >
> > > CREATE TABLE DEPT
> > > (DEPTNO NUMBER(2,0) NOT NULL,
> > > DNAME VARCHAR2(14) NULL,
> > > LOC VARCHAR2(13) NULL,
> > > PRIMARY KEY (DEPTNO)
> > > );
> > >
> > > INSERT INTO Dept VALUES(11,'Sales','Texas');
> > > INSERT INTO Dept VALUES(22,'Accounting','Washington');
> > > INSERT INTO Dept VALUES(33,'Finance','Maine');
> > >
> > > Create EMP table and insert some rows
> > >
> > > CREATE TABLE EMP
> > > (EMPNO NUMBER(4,0) NOT NULL,
> > > ENAME VARCHAR2(10) NULL,
> > > JOB VARCHAR2(9) NULL,
> > > MGR NUMBER(4,0) NULL,
> > > SAL NUMBER(7,2) NULL,
> > > COMM NUMBER(7,2) NULL,
> > > DEPTNO NUMBER(2,0) NULL,
> > > FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
> > > PRIMARY KEY (EMPNO)
> > > );
> > >
> > > INSERT INTO Emp VALUES(123,'Bob','Sales',555,35000,12,11);
> > > INSERT INTO Emp VALUES(321,'Sue','Finance',555,42000,12,33);
> > > INSERT INTO Emp VALUES(234,'Mary','Account',555,33000,12,22);
> > >
> > > Create package for Function example
> > >
> > > create or replace package packperson
> > > as
> > > type cursorType is ref cursor;
> > > end;
> > > /
> > >
> > > Create Function for Function example
> > >
> > > create or replace function sp_ListEmp (n_EMPNO NUMBER) return
> > > packperson.cursortype
> > > as
> > > l_cursor packperson.cursorType;
> > > begin
> > > open l_cursor for select ename as NAME, empno as NUM from emp
where
> > > empno = n_EMPNO order by ename;
> > >
> > > return l_cursor;
> > > end;
> > >
> > > From SQL Plus I call this by
> > >
> > > SQL> variable c refcursor
> > > SQL> exec :c := sp_ListEmp(123)
> > > SQL> print c
> > >
> > >
> > >
> > > Create package for sp example
> > >
> > > CREATE OR REPLACE PACKAGE curspkg_join AS
> > > TYPE t_cursor IS REF CURSOR ;
> > > Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> > > t_cursor);
> > > END curspkg_join;
> > > /
> > >
> > > Create package body for sp example
> > >
> > > CREATE OR REPLACE PACKAGE BODY curspkg_join AS
> > > Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> > > t_cursor)
> > > IS
> > > v_cursor t_cursor;
> > > BEGIN
> > > IF n_EMPNO <> 0
> > > THEN
> > > OPEN v_cursor FOR
> > > SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> > > FROM EMP, DEPT
> > > WHERE EMP.DEPTNO = DEPT.DEPTNO
> > > AND EMP.EMPNO = n_EMPNO;
> > >
> > > ELSE
> > > OPEN v_cursor FOR
> > > SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> > > FROM EMP, DEPT
> > > WHERE EMP.DEPTNO = DEPT.DEPTNO;
> > >
> > > END IF;
> > > io_cursor := v_cursor;
> > > END open_join_cursor1;
> > > END curspkg_join;
> > > /
> > >
> > > From SQL Plus I call this by
> > >
> > > SQL> variable c refcursor
> > > SQL>exec curspkg_join.open_join_cursor1(123,:c)
> > > SQL>print :c
> > >
> > >
> >
> >
>|||Thanks - got it working now, your help is much appreciated.
"Robert Bruckner [MSFT]" wrote:
> Make sure you use the generic text-based query designer with 2 panes, rather
> than the visual query designer with 4 panes.
> Regarding the OUT cursor - don't reference it, just omit that parameter.
> A better practice for stored procedures is to set the command type of the
> query to StoredProcedure instead of Text. In that case, the query text is
> just the name of the stored procedure "test_package.get_customers". The
> parameters will automatically be determined by report designer on clicking
> on the refresh fields icon.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Joe" <booksnore2@.netscape.net> wrote in message
> news:uZsHcq%23iEHA.384@.TK2MSFTNGP10.phx.gbl...
> > Thank you for your help,
> > I'm still unsure of the syntax that I should use in Reporting Services
> when
> > calling the sp. Where/how do I reference the out ref cursor? Example I
> call
> > the procedure using -
> >
> > { call test_package.get_customers(?, ) }
> >
> > I get the following error 'The data extension Oracle does not support
> > unnamed parameters. Use named parameters instead.'
> > So my question is how do I reference the out ref cursor in Reporting
> > services?
> >
> >
> >
> > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> > news:e1$dlvyiEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > The cursor in the stored procedure has to be an OUT REF cursor rather
> than
> > a
> > > IN OUT cursor.
> > > Also make sure you use the managed Oracle provider (i.e. "Oracle" in the
> > > data source dialog) and not OleDB.
> > >
> > > You might also want to check this previous posting for further
> information
> > > and a sample:
> > >
> >
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=049fe955-cec8-4a79-a5e5-a9c02873e19d&sloc=en-us
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > >
> > > "Joe" <booksnore2@.netscape.net> wrote in message
> > > news:%23dZ118wiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> > > > I've set up a shared datasource to oracle with the option of prompting
> > for
> > > > credientals. The report works with just static SQL in the Query
> > designer.
> > > I
> > > > have been trying for a number of days now to use a call to an oracle
> > > stored
> > > > procedure or function as my report data set without success.
> > > > The function called sp_ListEmp has one parameter of type number. To
> call
> > > the
> > > > function I have tried adding {call sp_ListEmp(?)} into the Query
> > designer
> > > > and setting the command type to Text. I have added the parameter ? and
> > > made
> > > > that equal to a Report parameter EMPNO of Integer type that I have
> added
> > > to
> > > > the report. So I have in the parameter tab of the Dataset
> > > > ?=Parameters!EMPNO.Value. I recieve the following error message 'An
> > error
> > > > occurred while executing the query. ORA-01036: illegal variable
> > > > name/number.'
> > > >
> > > > For the stored procedure curspkg_join.open_join_cursor1 I have not
> even
> > > been
> > > > able to call the procedure with recieving an error for the second
> > > parameter
> > > > which is of type refcursor. Does anyone know if it is possible to call
> > > > Oracle stored procedures and pass parameters to them from reporting
> > > > services?
> > > >
> > > > I have added the code for the stored procedure and function and also
> the
> > > > tables for which I have been working on below and also the commands I
> > use
> > > in
> > > > SQL Plus to prove that the actual function and stored procedure work.
> > This
> > > > is causing me much woe.
> > > >
> > > >
> > > > Create DEPT table and insert some rows
> > > >
> > > > CREATE TABLE DEPT
> > > > (DEPTNO NUMBER(2,0) NOT NULL,
> > > > DNAME VARCHAR2(14) NULL,
> > > > LOC VARCHAR2(13) NULL,
> > > > PRIMARY KEY (DEPTNO)
> > > > );
> > > >
> > > > INSERT INTO Dept VALUES(11,'Sales','Texas');
> > > > INSERT INTO Dept VALUES(22,'Accounting','Washington');
> > > > INSERT INTO Dept VALUES(33,'Finance','Maine');
> > > >
> > > > Create EMP table and insert some rows
> > > >
> > > > CREATE TABLE EMP
> > > > (EMPNO NUMBER(4,0) NOT NULL,
> > > > ENAME VARCHAR2(10) NULL,
> > > > JOB VARCHAR2(9) NULL,
> > > > MGR NUMBER(4,0) NULL,
> > > > SAL NUMBER(7,2) NULL,
> > > > COMM NUMBER(7,2) NULL,
> > > > DEPTNO NUMBER(2,0) NULL,
> > > > FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
> > > > PRIMARY KEY (EMPNO)
> > > > );
> > > >
> > > > INSERT INTO Emp VALUES(123,'Bob','Sales',555,35000,12,11);
> > > > INSERT INTO Emp VALUES(321,'Sue','Finance',555,42000,12,33);
> > > > INSERT INTO Emp VALUES(234,'Mary','Account',555,33000,12,22);
> > > >
> > > > Create package for Function example
> > > >
> > > > create or replace package packperson
> > > > as
> > > > type cursorType is ref cursor;
> > > > end;
> > > > /
> > > >
> > > > Create Function for Function example
> > > >
> > > > create or replace function sp_ListEmp (n_EMPNO NUMBER) return
> > > > packperson.cursortype
> > > > as
> > > > l_cursor packperson.cursorType;
> > > > begin
> > > > open l_cursor for select ename as NAME, empno as NUM from emp
> where
> > > > empno = n_EMPNO order by ename;
> > > >
> > > > return l_cursor;
> > > > end;
> > > >
> > > > From SQL Plus I call this by
> > > >
> > > > SQL> variable c refcursor
> > > > SQL> exec :c := sp_ListEmp(123)
> > > > SQL> print c
> > > >
> > > >
> > > >
> > > > Create package for sp example
> > > >
> > > > CREATE OR REPLACE PACKAGE curspkg_join AS
> > > > TYPE t_cursor IS REF CURSOR ;
> > > > Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> > > > t_cursor);
> > > > END curspkg_join;
> > > > /
> > > >
> > > > Create package body for sp example
> > > >
> > > > CREATE OR REPLACE PACKAGE BODY curspkg_join AS
> > > > Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> > > > t_cursor)
> > > > IS
> > > > v_cursor t_cursor;
> > > > BEGIN
> > > > IF n_EMPNO <> 0
> > > > THEN
> > > > OPEN v_cursor FOR
> > > > SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> > > > FROM EMP, DEPT
> > > > WHERE EMP.DEPTNO = DEPT.DEPTNO
> > > > AND EMP.EMPNO = n_EMPNO;
> > > >
> > > > ELSE
> > > > OPEN v_cursor FOR
> > > > SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> > > > FROM EMP, DEPT
> > > > WHERE EMP.DEPTNO = DEPT.DEPTNO;
> > > >
> > > > END IF;
> > > > io_cursor := v_cursor;
> > > > END open_join_cursor1;
> > > > END curspkg_join;
> > > > /
> > > >
> > > > From SQL Plus I call this by
> > > >
> > > > SQL> variable c refcursor
> > > > SQL>exec curspkg_join.open_join_cursor1(123,:c)
> > > > SQL>print :c
> > > >
> > > >
> > >
> > >
> >
> >
>
>