Order in which datasets executed

Can one specify the prder in which datasets are executed in the report?
Thanks
Sanjeev
Order in which datasets executedI dont know why you want order of execution, because SSRS runs all the query
and takes it to a Intermediate format, which is ready for rendering in any
format. So all the substitution parameter happens before creating this format.
Amarnath
"Sanjeev Rampersad" wrote:
> Can one specify the prder in which datasets are executed in the report?
> Thanks
> Sanjeev
> Order in which datasets executed
>
>

Order Date prompt descending doesn't work

Hi,

In the report model I'm defining attribute date as ValueSelection: dropdown and SortDirection: Descending.

In the report builder I define the field as a prompt.

When I execute the report the values in the prompt date are order ascending and not descending.

This occur all over my model, can't order date field as ascending.

Any idea?

Thanks,

Assaf

In the report builder: There is a button "Sort and Group". Did you set the "Sort by" property of the date? I think that has to be set in order to sort right.|||

Hi,

Thanks for your reply.

The "Sort by" sort the records on the report.

My problem is that the sort in the prompt itself is always stay ascesnding although in the .NET I set it to descending

Any Idea?

Assaf

sql

Order By with Query then bottom border

Reporting Services 2000
I have a SQL query that is properly sorting a list of items I have by using
the order by clause. I created a report using the report wizard and didn't
use any fields to group by because I already have the list in the correct
order. I did use RS to keep each team on it's own page. How can I have a
border or bgcolor change when the owner of a project changes. For example
I'd like a border after Bob and before John.
Sample Data:
Team Owner Project
NY bob Remote Access
NY bob Server Reboot
NY John Network Upgrade
Here is my order by clause:
ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 ENDDid you try to use an expression to set border width?
=IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
"Colin" wrote:
> Reporting Services 2000
> I have a SQL query that is properly sorting a list of items I have by using
> the order by clause. I created a report using the report wizard and didn't
> use any fields to group by because I already have the list in the correct
> order. I did use RS to keep each team on it's own page. How can I have a
> border or bgcolor change when the owner of a project changes. For example
> I'd like a border after Bob and before John.
> Sample Data:
> Team Owner Project
> NY bob Remote Access
> NY bob Server Reboot
> NY John Network Upgrade
> Here is my order by clause:
> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
>
>|||I didn't try that. Thank you.
Where can I find a list of all the functions that can be called?
"isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
news:E28C729B-AE19-4ADF-9983-233B666CE5D1@.microsoft.com...
> Did you try to use an expression to set border width?
> =IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
> "Colin" wrote:
>> Reporting Services 2000
>> I have a SQL query that is properly sorting a list of items I have by
>> using
>> the order by clause. I created a report using the report wizard and
>> didn't
>> use any fields to group by because I already have the list in the correct
>> order. I did use RS to keep each team on it's own page. How can I have
>> a
>> border or bgcolor change when the owner of a project changes. For
>> example
>> I'd like a border after Bob and before John.
>> Sample Data:
>> Team Owner Project
>> NY bob Remote Access
>> NY bob Server Reboot
>> NY John Network Upgrade
>> Here is my order by clause:
>> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
>>
>>|||Can I confuse this just a tad more. What if I only want a border the very
first time the owner changes and don't need a border after that?
"Colin" <legendsfan@.spamhotmail.com> wrote in message
news:O1DEBwKwGHA.4460@.TK2MSFTNGP04.phx.gbl...
>I didn't try that. Thank you.
> Where can I find a list of all the functions that can be called?
> "isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
> news:E28C729B-AE19-4ADF-9983-233B666CE5D1@.microsoft.com...
>> Did you try to use an expression to set border width?
>> =IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
>> "Colin" wrote:
>> Reporting Services 2000
>> I have a SQL query that is properly sorting a list of items I have by
>> using
>> the order by clause. I created a report using the report wizard and
>> didn't
>> use any fields to group by because I already have the list in the
>> correct
>> order. I did use RS to keep each team on it's own page. How can I have
>> a
>> border or bgcolor change when the owner of a project changes. For
>> example
>> I'd like a border after Bob and before John.
>> Sample Data:
>> Team Owner Project
>> NY bob Remote Access
>> NY bob Server Reboot
>> NY John Network Upgrade
>> Here is my order by clause:
>> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
>>
>>
>|||For the function list go to SqlServer Books Online, "Using Functions in
Reporting Services" is the name of the topic.
To show the border the first time only, try this
=IIF(Fields!OwnerValue <> Previous(Fields!Owner.Value) AND
Previous(Fields!Owner.Value)= Min(Fields!Owner.Value),1pt,0pt)
"Colin" wrote:
> Can I confuse this just a tad more. What if I only want a border the very
> first time the owner changes and don't need a border after that?
> "Colin" <legendsfan@.spamhotmail.com> wrote in message
> news:O1DEBwKwGHA.4460@.TK2MSFTNGP04.phx.gbl...
> >I didn't try that. Thank you.
> >
> > Where can I find a list of all the functions that can be called?
> >
> > "isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
> > news:E28C729B-AE19-4ADF-9983-233B666CE5D1@.microsoft.com...
> >> Did you try to use an expression to set border width?
> >>
> >> =IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
> >>
> >> "Colin" wrote:
> >>
> >> Reporting Services 2000
> >> I have a SQL query that is properly sorting a list of items I have by
> >> using
> >> the order by clause. I created a report using the report wizard and
> >> didn't
> >> use any fields to group by because I already have the list in the
> >> correct
> >> order. I did use RS to keep each team on it's own page. How can I have
> >> a
> >> border or bgcolor change when the owner of a project changes. For
> >> example
> >> I'd like a border after Bob and before John.
> >>
> >> Sample Data:
> >> Team Owner Project
> >> NY bob Remote Access
> >> NY bob Server Reboot
> >> NY John Network Upgrade
> >>
> >> Here is my order by clause:
> >> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
> >>
> >>
> >>
> >>
> >
> >
>
>

Order By Param

Is there a way to have a report parameter for the selection of what field
the user wants a table to be grouped on? For instance, the parameter would
have 3 options, clientid, clientname, and clientcity. I want to be able to
select one of these and have the table group on this selection.
Or any generic ways or ideas on how to accomplish this?
Thanks!!!I am looking for the same solution. I have a client that I have promised 10
reports to and they gave me a few but want sort capability on 5 different
fields. I could say that those are 5 different reports, but there should be
an easy way to list the fields in a drop down parameter and adjust the ORDER
BY based on this. Shouldn't there?
Thanks in advance.
ANDY
"Amon Borland" wrote:
> Is there a way to have a report parameter for the selection of what field
> the user wants a table to be grouped on? For instance, the parameter would
> have 3 options, clientid, clientname, and clientcity. I want to be able to
> select one of these and have the table group on this selection.
> Or any generic ways or ideas on how to accomplish this?
> Thanks!!!
>
>|||can you pass the field to sort on into the report as a parameter and use a
CASE in your query to conditionally sort based on whatever you passed in as
a parameter?
Bill
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:C80B4FC3-FA92-4ADE-BFEA-D6B0867FDE97@.microsoft.com...
> I am looking for the same solution. I have a client that I have promised
10
> reports to and they gave me a few but want sort capability on 5 different
> fields. I could say that those are 5 different reports, but there should
be
> an easy way to list the fields in a drop down parameter and adjust the
ORDER
> BY based on this. Shouldn't there?
> Thanks in advance.
> ANDY
> "Amon Borland" wrote:
> > Is there a way to have a report parameter for the selection of what
field
> > the user wants a table to be grouped on? For instance, the parameter
would
> > have 3 options, clientid, clientname, and clientcity. I want to be able
to
> > select one of these and have the table group on this selection.
> >
> > Or any generic ways or ideas on how to accomplish this?
> >
> > Thanks!!!
> >
> >
> >|||It seems all my replied are this..
Sorting is easy
DECLARE @.sql as varchar(100)
SET @.sql = 'SELECT * FROM blah WHERE blah = blah ORDER BY '
@.paramete
EXEC sp_executesql @.sq
Grouping on the other hand I don't think you can do dynamically

Order by of a table passed as parameters?

Is it possible to parametrise the 'order by' options of a table/group so
that a single report could be ordered by users on run time depending of
their needs?
The alternative is to write/copy the same report once and again and have it
repeated (one instance) for each orderable column.
Regards.Sorry for this post. I just found it on RS' BOL.
Regards.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> escribió en el mensaje
news:eXv4RWQXFHA.3572@.TK2MSFTNGP12.phx.gbl...
> Is it possible to parametrise the 'order by' options of a table/group so
> that a single report could be ordered by users on run time depending of
> their needs?
> The alternative is to write/copy the same report once and again and have
> it repeated (one instance) for each orderable column.
> Regards.
>

ORDER BY - parameter

In order to allow our users to create custom report queries, we've created a report with two parameters: "Where" and "OrderBy". In the Where parameter we pass the columns to be checked (Division = 'south' AND price > 100). This works great. The "OrderBy" parameter also works, if and only if, one column name is passed. If we attempt to pass a parameter (both in the designer and URL string) formatted as "col1, col2", the preview fails when the comma is encountered. Is it not possible to create a parameter that contains more than one column for the ORDER BY clause (parameter)?
ThanksThis works, I used this type of parameter all the time. Is you query an
expression? Or are you using an @. parameter. It will only work as an
expression. To pass the parameter on the ULR it must be encoded.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
news:C26612BE-A503-44F8-9575-1B7C09332181@.microsoft.com...
> In order to allow our users to create custom report queries, we've created
a report with two parameters: "Where" and "OrderBy". In the Where parameter
we pass the columns to be checked (Division = 'south' AND price > 100). This
works great. The "OrderBy" parameter also works, if and only if, one column
name is passed. If we attempt to pass a parameter (both in the designer and
URL string) formatted as "col1, col2", the preview fails when the comma is
encountered. Is it not possible to create a parameter that contains more
than one column for the ORDER BY clause (parameter)?
> Thanks|||John.,
I changed your Where parameter name to WherePrm and added OrderBy1, OrderBy2
parameters. If needed assign default values.
SELECT TR_COID, TR_TranDate, TR_TranAmt, TR_Merchant, TR_MerchState,
TR_MerchCity, TR_MerchZip, TR_AcctCode, TR_MCC, TR_AcctNbr FROM TranDet
WHERE @.WherePrm ORDER BY @.OrderBy1, @.OrderBy2
Cem
"John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
news:7A469A37-A660-4626-A73A-09BCC1C28F46@.microsoft.com...
> Jason,
> Here is the actual statement: (again, the "where" works fine with multiple
entries)
> ="SELECT TR_COID, TR_TranDate, TR_TranAmt, TR_Merchant, TR_MerchState,
TR_MerchCity, TR_MerchZip, TR_AcctCode, TR_MCC, TR_AcctNbr FROM TranDet
WHERE (" & Parameters!Where.Value & ") ORDER BY (" &
Parameters!OrderBy.Value & ")"
> Do I need to further define whatever in the designer?
> Thanks,
> "Jason Carlson [MSFT]" wrote:
> > This works, I used this type of parameter all the time. Is you query an
> > expression? Or are you using an @. parameter. It will only work as an
> > expression. To pass the parameter on the ULR it must be encoded.
> >
> > --
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
> > news:C26612BE-A503-44F8-9575-1B7C09332181@.microsoft.com...
> > > In order to allow our users to create custom report queries, we've
created
> > a report with two parameters: "Where" and "OrderBy". In the Where
parameter
> > we pass the columns to be checked (Division = 'south' AND price > 100).
This
> > works great. The "OrderBy" parameter also works, if and only if, one
column
> > name is passed. If we attempt to pass a parameter (both in the designer
and
> > URL string) formatted as "col1, col2", the preview fails when the comma
is
> > encountered. Is it not possible to create a parameter that contains more
> > than one column for the ORDER BY clause (parameter)?
> > >
> > > Thanks
> >
> >
> >|||Remove the parens in the order by. It is not valid SQL.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
news:7A469A37-A660-4626-A73A-09BCC1C28F46@.microsoft.com...
> Jason,
> Here is the actual statement: (again, the "where" works fine with multiple
entries)
> ="SELECT TR_COID, TR_TranDate, TR_TranAmt, TR_Merchant, TR_MerchState,
TR_MerchCity, TR_MerchZip, TR_AcctCode, TR_MCC, TR_AcctNbr FROM TranDet
WHERE (" & Parameters!Where.Value & ") ORDER BY (" &
Parameters!OrderBy.Value & ")"
> Do I need to further define whatever in the designer?
> Thanks,
> "Jason Carlson [MSFT]" wrote:
> > This works, I used this type of parameter all the time. Is you query an
> > expression? Or are you using an @. parameter. It will only work as an
> > expression. To pass the parameter on the ULR it must be encoded.
> >
> > --
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
> > news:C26612BE-A503-44F8-9575-1B7C09332181@.microsoft.com...
> > > In order to allow our users to create custom report queries, we've
created
> > a report with two parameters: "Where" and "OrderBy". In the Where
parameter
> > we pass the columns to be checked (Division = 'south' AND price > 100).
This
> > works great. The "OrderBy" parameter also works, if and only if, one
column
> > name is passed. If we attempt to pass a parameter (both in the designer
and
> > URL string) formatted as "col1, col2", the preview fails when the comma
is
> > encountered. Is it not possible to create a parameter that contains more
> > than one column for the ORDER BY clause (parameter)?
> > >
> > > Thanks
> >
> >
> >

Oracle with Reporting Services. "illegal variable name/number"

I'm developing a report with SQL Server Reporting Solutions (obviously ;)) against an Oracle DB and have run into a problem.

This is the code that I'm using for the dataset query that my report is to be based on:

="SELECT * FROM IRMFE_*************_V WHERE " &
IIf(Parameters!Invoice_No.Value(0)<> "ALL", "SUPPLIER_INVOICE_NO IN (:InvNo)", "SUPPLIER_INVOICE_NO LIKE '%'")

The parameter ":InvNo" is a paramter in the report and the user can select multiple values. I also included an "ALL" option manually in the query for the parameter. The query works fine with single or multiple options but if I select "ALL" I get the following error:

"Query execution failed for dataset 'FREIGHT'.
ORA-01036: illegal variable name/number'

There seems to be no problem when I manually run the query (SELECT * FROM IRMFE_*************_V WHERE SUPPLIER_INVOICE_NO LIKE '%')

The reason I used "SUPPLIER_INVOICE_NO LIKE '%'" is because I'll be adding a few more parameters and wanted to avoid any problems with adding "AND" between the conditions.

Any resolution or suggestion on this would be much appreciated guys.
Kev.After 1/2 of bangin my head against the wall I've resolved it. The reason it wasn't picking it up in the first place was that the check should have been:

IIf(Parameters!Invoice_No.Value(0)<> "'ALL'"

instead of

IIf(Parameters!Invoice_No.Value(0)<> "ALL"

I found Chris Hays solution and will just use that though.

Oracle support and Report Builder

Can anyone confirm whether Oracle support for Report Builder will be in SSRS 2005/SP1?

I think a long time (6-7 months) ago this was kicked around as a possibility, but as far as I know there are currently no plans to include direct access to Oracle via Report Builder in SP1.

You could do this by using a UDM/Cube in SSAS (which points to Oracle) or linking Oracle tables into SQL, though...

|||

|||

I have been trying to create a new report builder model. I would like the data source to be Oracle 10g, I'm not having any joy. Were you able to get it working.

|||Do not waste your time. It will not work against Oracle. A huge disappointment.|||

Oracle support for Report Builder didn't make it RS 2005 SP1. We are working on it, and hope to be able to announce more details soon.

Stay tuned.

Thanks

Tudor Trufinescu

|||We really need this feature Microsoft.

Oracle support and Report Builder

Can anyone confirm whether Oracle support for Report Builder will be in SSRS 2005/SP1?

I think a long time (6-7 months) ago this was kicked around as a possibility, but as far as I know there are currently no plans to include direct access to Oracle via Report Builder in SP1.

You could do this by using a UDM/Cube in SSAS (which points to Oracle) or linking Oracle tables into SQL, though...

|||

|||

I have been trying to create a new report builder model. I would like the data source to be Oracle 10g, I'm not having any joy. Were you able to get it working.

|||Do not waste your time. It will not work against Oracle. A huge disappointment.|||

Oracle support for Report Builder didn't make it RS 2005 SP1. We are working on it, and hope to be able to announce more details soon.

Stay tuned.

Thanks

Tudor Trufinescu

|||We really need this feature Microsoft.

Oracle support and Report Builder

Can anyone confirm whether Oracle support for Report Builder will be in SSRS 2005/SP1?

I think a long time (6-7 months) ago this was kicked around as a possibility, but as far as I know there are currently no plans to include direct access to Oracle via Report Builder in SP1.

You could do this by using a UDM/Cube in SSAS (which points to Oracle) or linking Oracle tables into SQL, though...

|||

|||

I have been trying to create a new report builder model. I would like the data source to be Oracle 10g, I'm not having any joy. Were you able to get it working.

|||Do not waste your time. It will not work against Oracle. A huge disappointment.|||

Oracle support for Report Builder didn't make it RS 2005 SP1. We are working on it, and hope to be able to announce more details soon.

Stay tuned.

Thanks

Tudor Trufinescu

|||We really need this feature Microsoft.

oracle support - Report Model

If my understanding is correct, Oracle is not yet supported as a data source
for report models in SSRS 2005 (and thus not available for report builder).
Is there a timeframe estimate to when this might be available?I would also like to know. Also if there are any APIs to develop one. Can we
develop own application which can use Oracle database to build report model?
Thank you - Ashok
"chicagoclone" wrote:
> If my understanding is correct, Oracle is not yet supported as a data source
> for report models in SSRS 2005 (and thus not available for report builder).
> Is there a timeframe estimate to when this might be available?|||I burned a MSDN Developer Incident on this one. Basically I was able to ceate
a Report Model by using a Linked Server and creating a view on the linked
server. From there I used the Linked View in the report Modeler, it is a wee
it slow (30-45 seconds for 18,000 rows over a T-1) however until they support
Oracle it's how I am going to apporach it. Hope this helps...
"Ashok" wrote:
> I would also like to know. Also if there are any APIs to develop one. Can we
> develop own application which can use Oracle database to build report model?
> Thank you - Ashok
> "chicagoclone" wrote:
> > If my understanding is correct, Oracle is not yet supported as a data source
> > for report models in SSRS 2005 (and thus not available for report builder).
> > Is there a timeframe estimate to when this might be available?|||Oracle support is scheduled to be added in SP2.
"chicagoclone" wrote:
> If my understanding is correct, Oracle is not yet supported as a data source
> for report models in SSRS 2005 (and thus not available for report builder).
> Is there a timeframe estimate to when this might be available?|||Thank you Bob! This is great news.
"Bob Meyers - MSFT" wrote:
> Oracle support is scheduled to be added in SP2.
> "chicagoclone" wrote:
> > If my understanding is correct, Oracle is not yet supported as a data source
> > for report models in SSRS 2005 (and thus not available for report builder).
> > Is there a timeframe estimate to when this might be available?

Oracle Stored Procedure Syntax

I'd like to write a report with a data set that calls an Oracle data source
for its result set. I have a package with a procedure that returns a ref
cursor as an out variable. What is the syntax for calling this procedure
from the Report Designer? Does the out variable with the cursor need to be
mapped to anything? Are input parameters on the procedure anything other
than regular @.parameter references?
Thanks!John,
The sql syntax is identical to the that used for MS SQL Server, except for
the @.. Switch it to a : (colon)
and the parameters will work just fine, example:
Select Blah
From tblBlah
where Blah.tblBlah = :param
I like to use the IN Keyword. If you set your parameters as multi-select,
the parameters are passed in as an string seperated by commas, so your Where
clause would look like this:
Where Blah.tblBlah IN (:Param)
Hope this helps,
rwiethorn
"John W at Sungard HE" wrote:
> I'd like to write a report with a data set that calls an Oracle data source
> for its result set. I have a package with a procedure that returns a ref
> cursor as an out variable. What is the syntax for calling this procedure
> from the Report Designer? Does the out variable with the cursor need to be
> mapped to anything? Are input parameters on the procedure anything other
> than regular @.parameter references?
> Thanks!|||Hi,
Continuing with John's question, I want to know, What is the syntax for
calling the oracle stored procedure from the Report Designer? My procedure is
returning a ref cursor as an out variable.
Regards,
Aruna
"rwiethorn" wrote:
> John,
> The sql syntax is identical to the that used for MS SQL Server, except for
> the @.. Switch it to a : (colon)
> and the parameters will work just fine, example:
> Select Blah
> From tblBlah
> where Blah.tblBlah = :param
> I like to use the IN Keyword. If you set your parameters as multi-select,
> the parameters are passed in as an string seperated by commas, so your Where
> clause would look like this:
> Where Blah.tblBlah IN (:Param)
> Hope this helps,
> rwiethorn
>
> "John W at Sungard HE" wrote:
> > I'd like to write a report with a data set that calls an Oracle data source
> > for its result set. I have a package with a procedure that returns a ref
> > cursor as an out variable. What is the syntax for calling this procedure
> > from the Report Designer? Does the out variable with the cursor need to be
> > mapped to anything? Are input parameters on the procedure anything other
> > than regular @.parameter references?
> >
> > Thanks!|||Thanks for the response - the bit with the parameters defintely helps. Do
you have an example that references a stored procedure instead of a select
statement? For example, do I have to wrap the procedure call in a PL/SQL
block like:
BEGIN
CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
END
or is it sufficient to call the procedure without the block like:
CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
And is the above parameter syntax going to work? What about the output
parameter that accepts the cursor back from the stored procedure? Is that
just set up as a report parameter like any other, even though it's an output
parameter? Does it matter where the output parameter is placed in the call
list, i.e. does it need to appear either first or last?
Thanks!
JW
"rwiethorn" wrote:
> John,
> The sql syntax is identical to the that used for MS SQL Server, except for
> the @.. Switch it to a : (colon)
> and the parameters will work just fine, example:
> Select Blah
> From tblBlah
> where Blah.tblBlah = :param
> I like to use the IN Keyword. If you set your parameters as multi-select,
> the parameters are passed in as an string seperated by commas, so your Where
> clause would look like this:
> Where Blah.tblBlah IN (:Param)
> Hope this helps,
> rwiethorn
>
> "John W at Sungard HE" wrote:
> > I'd like to write a report with a data set that calls an Oracle data source
> > for its result set. I have a package with a procedure that returns a ref
> > cursor as an out variable. What is the syntax for calling this procedure
> > from the Report Designer? Does the out variable with the cursor need to be
> > mapped to anything? Are input parameters on the procedure anything other
> > than regular @.parameter references?
> >
> > Thanks!|||change the command type to stored procedure.
The syntax then looks like this
packagename.procedurename
no need to put any parameters in here.
John W at Sungard HE wrote:
> Thanks for the response - the bit with the parameters defintely helps. Do
> you have an example that references a stored procedure instead of a select
> statement? For example, do I have to wrap the procedure call in a PL/SQL
> block like:
> BEGIN
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> END
> or is it sufficient to call the procedure without the block like:
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> And is the above parameter syntax going to work? What about the output
> parameter that accepts the cursor back from the stored procedure? Is that
> just set up as a report parameter like any other, even though it's an output
> parameter? Does it matter where the output parameter is placed in the call
> list, i.e. does it need to appear either first or last?
> Thanks!
> JW
> "rwiethorn" wrote:
> > John,
> > The sql syntax is identical to the that used for MS SQL Server, except for
> > the @.. Switch it to a : (colon)
> > and the parameters will work just fine, example:
> > Select Blah
> > From tblBlah
> > where Blah.tblBlah = :param
> >
> > I like to use the IN Keyword. If you set your parameters as multi-select,
> > the parameters are passed in as an string seperated by commas, so your Where
> > clause would look like this:
> > Where Blah.tblBlah IN (:Param)
> >
> > Hope this helps,
> > rwiethorn
> >
> >
> > "John W at Sungard HE" wrote:
> >
> > > I'd like to write a report with a data set that calls an Oracle data source
> > > for its result set. I have a package with a procedure that returns a ref
> > > cursor as an out variable. What is the syntax for calling this procedure
> > > from the Report Designer? Does the out variable with the cursor need to be
> > > mapped to anything? Are input parameters on the procedure anything other
> > > than regular @.parameter references?
> > >
> > > Thanks!|||John,
I'm a MS SQL Server guy myself, so I'm limited on the Oracle. I have a
Oracle DBA that makes tables for me when I define my sql statement. I've not
had the need to do any processing, I'm been only needing simple selects with
groupings.
>What about the output
> parameter that accepts the cursor back from the stored procedure?
I think you can do with out it, the system may just 'swallow' the return
value (ie a 0 or 1) indicating a success or failure.
>Does it matter where the output parameter is placed in the call
> list, i.e. does it need to appear either first or last?
Yes, parameter order does matter when your calling stored procs, you need to
match the order and the data type.
I hope this helps,
rwiethorn
"John W at Sungard HE" wrote:
> Thanks for the response - the bit with the parameters defintely helps. Do
> you have an example that references a stored procedure instead of a select
> statement? For example, do I have to wrap the procedure call in a PL/SQL
> block like:
> BEGIN
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> END
> or is it sufficient to call the procedure without the block like:
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> And is the above parameter syntax going to work? What about the output
> parameter that accepts the cursor back from the stored procedure? Is that
> just set up as a report parameter like any other, even though it's an output
> parameter? Does it matter where the output parameter is placed in the call
> list, i.e. does it need to appear either first or last?
> Thanks!
> JW
> "rwiethorn" wrote:
> > John,
> > The sql syntax is identical to the that used for MS SQL Server, except for
> > the @.. Switch it to a : (colon)
> > and the parameters will work just fine, example:
> > Select Blah
> > From tblBlah
> > where Blah.tblBlah = :param
> >
> > I like to use the IN Keyword. If you set your parameters as multi-select,
> > the parameters are passed in as an string seperated by commas, so your Where
> > clause would look like this:
> > Where Blah.tblBlah IN (:Param)
> >
> > Hope this helps,
> > rwiethorn
> >
> >
> > "John W at Sungard HE" wrote:
> >
> > > I'd like to write a report with a data set that calls an Oracle data source
> > > for its result set. I have a package with a procedure that returns a ref
> > > cursor as an out variable. What is the syntax for calling this procedure
> > > from the Report Designer? Does the out variable with the cursor need to be
> > > mapped to anything? Are input parameters on the procedure anything other
> > > than regular @.parameter references?
> > >
> > > Thanks!|||Sorry, I did not get it. How I can send an OUT parameter (CURSOR) and use it
to generate report?
Thank you
"rwiethorn" wrote:
> John,
> The sql syntax is identical to the that used for MS SQL Server, except for
> the @.. Switch it to a : (colon)
> and the parameters will work just fine, example:
> Select Blah
> From tblBlah
> where Blah.tblBlah = :param
> I like to use the IN Keyword. If you set your parameters as multi-select,
> the parameters are passed in as an string seperated by commas, so your Where
> clause would look like this:
> Where Blah.tblBlah IN (:Param)
> Hope this helps,
> rwiethorn
>
> "John W at Sungard HE" wrote:
> > I'd like to write a report with a data set that calls an Oracle data source
> > for its result set. I have a package with a procedure that returns a ref
> > cursor as an out variable. What is the syntax for calling this procedure
> > from the Report Designer? Does the out variable with the cursor need to be
> > mapped to anything? Are input parameters on the procedure anything other
> > than regular @.parameter references?
> >
> > Thanks!|||Sorry, can I have more details, please. Like, what driver did you use, did
you use parameters Tab to set parameter and so on.
Thank you
"gene.furibondo@.gmail.com" wrote:
> change the command type to stored procedure.
> The syntax then looks like this
> packagename.procedurename
> no need to put any parameters in here.
> John W at Sungard HE wrote:
> > Thanks for the response - the bit with the parameters defintely helps. Do
> > you have an example that references a stored procedure instead of a select
> > statement? For example, do I have to wrap the procedure call in a PL/SQL
> > block like:
> >
> > BEGIN
> > CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> > END
> >
> > or is it sufficient to call the procedure without the block like:
> >
> > CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> >
> > And is the above parameter syntax going to work? What about the output
> > parameter that accepts the cursor back from the stored procedure? Is that
> > just set up as a report parameter like any other, even though it's an output
> > parameter? Does it matter where the output parameter is placed in the call
> > list, i.e. does it need to appear either first or last?
> >
> > Thanks!
> >
> > JW
> >
> > "rwiethorn" wrote:
> >
> > > John,
> > > The sql syntax is identical to the that used for MS SQL Server, except for
> > > the @.. Switch it to a : (colon)
> > > and the parameters will work just fine, example:
> > > Select Blah
> > > From tblBlah
> > > where Blah.tblBlah = :param
> > >
> > > I like to use the IN Keyword. If you set your parameters as multi-select,
> > > the parameters are passed in as an string seperated by commas, so your Where
> > > clause would look like this:
> > > Where Blah.tblBlah IN (:Param)
> > >
> > > Hope this helps,
> > > rwiethorn
> > >
> > >
> > > "John W at Sungard HE" wrote:
> > >
> > > > I'd like to write a report with a data set that calls an Oracle data source
> > > > for its result set. I have a package with a procedure that returns a ref
> > > > cursor as an out variable. What is the syntax for calling this procedure
> > > > from the Report Designer? Does the out variable with the cursor need to be
> > > > mapped to anything? Are input parameters on the procedure anything other
> > > > than regular @.parameter references?
> > > >
> > > > Thanks!
>

Oracle Stored Procedure for RDLC Dataset

Is it possible to use an Oracle Stored Procedure for an RDLC report. There are posts I've read that deal with RDL reports that use the data tab and command type of "Stored Procedure", but I don't have that installed. I just create a new dataset that the report uses. I can do reports just fine with SQL statements, but I want to be able to call a stored procedure...

Thanks

Another post that seeks to answer this question is at

http://www.codenewsgroups.net/group/microsoft.public.dotnet.framework.aspnet.webcontrols/topic6588.aspx

"Hi, i have used rdlc with the informix database. What is did was add a empty
dataset to the rdlc and i have a business layer (a vb class) which has a
function which returns a dataset. When you add a report to the page add an
objectdatasource and specify the type as the vb class and the select method
as the funtion. This works for me. "

Is this the only way to accomplish this? Does someone have the definitive answer of "No, an

Oracle Stored Procedure cannot be called by a setting in the dataset wizard for a client-side (RDLC) report."

Also, here is a post that deals with just the RDL report:

http://www.developer.com/db/article.php/3524781

Oracle Stored Procedure flakiness...

I'm on a deadline so any help would be much appreciated.
I am building a Reporting Services report that takes a number of
parameters with which to call a stored procedure. I am using the
Microsoft OLEDB provider for Oracle. (For some reason, the Oracle OLEDB
provider that I have manually installed does not show up as a choice,
but that's for another day.)
Ultimately, this report will be rendered from an ASP.NET front-end Web
application. But in order to do the formatting etc. I had it working in
MSRS's HTML Viewer. Thing were fine. I had about 15 parameters working
for a while. A mixture of dates, floats, varchar2s and numbers.
Now, I'm not sure exactly what happened, but to the best of my
recollection:
When calling the report programmtically, it was trying to do a query
for the first parameter and was failing. I didn't need it to do the
query any longer (the front end was handling that). I modified the
report parameters such that it no longer used a Query.
Now I get "PLS-00306 Wrong number or types of arguments..." when I
attempt to render the report either in MSRS HTML Viewer or through my
ASP.NET application. I've tried narrowing it down and it seems when I
add a third parameter, it fails.
I'm sorry for the long post and lack of details, but has anyone else
experienced flakiness with Report Parameters, Stored Procedures and/or
Oracle?
Regards,
PeterMy guess here is that there is a mismatch between your query parameters and
report parameters. They are two different things but it isn't obvious since
RS creates the report parameters for you. Make sure the report work from
Report Manager.
Another possibility is that the report parameter is a string and your stored
procedure is an integer or something like that. When you add a parameter it
defaults to string. Another area to check.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"psparago" <psparago@.yahoo.com> wrote in message
news:1107367305.890651.99870@.o13g2000cwo.googlegroups.com...
> I'm on a deadline so any help would be much appreciated.
> I am building a Reporting Services report that takes a number of
> parameters with which to call a stored procedure. I am using the
> Microsoft OLEDB provider for Oracle. (For some reason, the Oracle OLEDB
> provider that I have manually installed does not show up as a choice,
> but that's for another day.)
> Ultimately, this report will be rendered from an ASP.NET front-end Web
> application. But in order to do the formatting etc. I had it working in
> MSRS's HTML Viewer. Thing were fine. I had about 15 parameters working
> for a while. A mixture of dates, floats, varchar2s and numbers.
> Now, I'm not sure exactly what happened, but to the best of my
> recollection:
> When calling the report programmtically, it was trying to do a query
> for the first parameter and was failing. I didn't need it to do the
> query any longer (the front end was handling that). I modified the
> report parameters such that it no longer used a Query.
> Now I get "PLS-00306 Wrong number or types of arguments..." when I
> attempt to render the report either in MSRS HTML Viewer or through my
> ASP.NET application. I've tried narrowing it down and it seems when I
> add a third parameter, it fails.
> I'm sorry for the long post and lack of details, but has anyone else
> experienced flakiness with Report Parameters, Stored Procedures and/or
> Oracle?
> Regards,
> Peter
>|||Thanks for the quick reply Bruce.
I wish that were the case :-). I've dumbed it down so I'm passing a
VARCHAR2 and two NUMBERS.
Is there any way to see the command generated by MSRS to be sent to
Oracle? That would really help.
I am in class today, more details later. Gotta run.|||I have had some issues with SP going to Sybase. What helped me was to use
the generic query (there is a button to switch to that). Think of this as
passthrough. Put it in the format that you would if you were using a query
tool from Oracle. For instance I put in this for Sybase:
pr_test ?,?,?
Be sure to map it to the report parameters by clicking on the ... and going
to the parameter tab. I sometimes have to put in the ? on the left column
and then put in expression and then select the report parameter.
I'm not sure if ? is the placeholder for the parameter when doing this with
Oracle, it might not be. When you are in the generic query tab you are using
the managed provider for Oracle.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"psparago" <psparago@.yahoo.com> wrote in message
news:1107372894.354513.125000@.o13g2000cwo.googlegroups.com...
> Thanks for the quick reply Bruce.
> I wish that were the case :-). I've dumbed it down so I'm passing a
> VARCHAR2 and two NUMBERS.
> Is there any way to see the command generated by MSRS to be sent to
> Oracle? That would really help.
> I am in class today, more details later. Gotta run.
>|||It won't work through the OleDB provider at design time. Choose "Oracle" as
data source type which will give you the managed provider for Oracle. Also
make sure that you use the text-based generic query designer (2 panes) and
not the visual query designer (4 panes) - you can switch between them
through an icon on the toolbar in the data view of report designer.
In addition, how do you return the data from your stored procedure? Note:
only an out ref cursor is supported. Please follow the guidelines in the
following article on MSDN (scroll down to the section where it talks about
"Oracle REF CURSORs") on how to design the Oracle stored procedure:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
To use a stored procedure with regular out parameters, you should either
remove the parameter (if it is possible) or write a little wrapper around
the original stored procedure which checks the result of the out parameter
and just returns the out ref cursor but no out parameter.
Finally, in the generic query designer, just specify the name of the stored
procedure without arguments and the parameters should get detected
automatically.
BTW: it is possible to switch from the managed Oracle to the OleDB provider
once the entire report is designed and done. E.g. after you published the
report to the production environment you can switch to the OleDB provider
using the steps discussed in detail in this related newsgroup thread:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/782c9647e34cb1f5/8b45a452801e7b77?q=Oracle+REF+CURSORS&_done=%2Fgroup%2Fmicrosoft.public.sqlserver.reportingsvcs%2Fsearch%3Fgroup%3Dmicrosoft.public.sqlserver.reportingsvcs%26q%3DOracle+REF+CURSORS%26qt_g%3D1%26searchnow%3DSearch+this+group%26&_doneTitle=Back+to+Search&&d#8b45a452801e7b77
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"psparago" <psparago@.yahoo.com> wrote in message
news:1107367305.890651.99870@.o13g2000cwo.googlegroups.com...
> I'm on a deadline so any help would be much appreciated.
> I am building a Reporting Services report that takes a number of
> parameters with which to call a stored procedure. I am using the
> Microsoft OLEDB provider for Oracle. (For some reason, the Oracle OLEDB
> provider that I have manually installed does not show up as a choice,
> but that's for another day.)
> Ultimately, this report will be rendered from an ASP.NET front-end Web
> application. But in order to do the formatting etc. I had it working in
> MSRS's HTML Viewer. Thing were fine. I had about 15 parameters working
> for a while. A mixture of dates, floats, varchar2s and numbers.
> Now, I'm not sure exactly what happened, but to the best of my
> recollection:
> When calling the report programmtically, it was trying to do a query
> for the first parameter and was failing. I didn't need it to do the
> query any longer (the front end was handling that). I modified the
> report parameters such that it no longer used a Query.
> Now I get "PLS-00306 Wrong number or types of arguments..." when I
> attempt to render the report either in MSRS HTML Viewer or through my
> ASP.NET application. I've tried narrowing it down and it seems when I
> add a third parameter, it fails.
> I'm sorry for the long post and lack of details, but has anyone else
> experienced flakiness with Report Parameters, Stored Procedures and/or
> Oracle?
> Regards,
> Peter
>|||Thank you for the response Robert.
My stored procedure is being called when I run the Dataset that
references it from the Data pane in the Designer. It's when the stored
procedure is called from the preview pane that I get an error.
Since posting this issue, I've worked around it (somewhat) by passing
my stored procedure only one parameter: an Xml Document containing the
actual 20 or so parameters. I was getting tired of rebuilding the
parameter list (which occasionally fixed the problem).
However, please see my more recent post concerning HTML output caching.
I'm wondering if my single ~1500 character parameter is causing issues
with MSRS determining when to return cached versions of the HTML output
(even though the parameter string differes).
Once again thank you for your help. My company is evaluating moving
from an expensive and really buggy high-end reporting system to MSRS. I
am doing the proof-of-concept work and the HTML issue is causing some
concern about adopting MSRS. I'd really love to whole-heartedly endorse
it! :-)
peter|||Here is an idea. Have a parameter table that you fill with the 20 parameters
and then pass that primary key as the single parameter to RS. The parameter
will definitely be different and your caching problem will go away.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"psparago" <psparago@.yahoo.com> wrote in message
news:1107784198.997079.73660@.c13g2000cwb.googlegroups.com...
> Thank you for the response Robert.
> My stored procedure is being called when I run the Dataset that
> references it from the Data pane in the Designer. It's when the stored
> procedure is called from the preview pane that I get an error.
> Since posting this issue, I've worked around it (somewhat) by passing
> my stored procedure only one parameter: an Xml Document containing the
> actual 20 or so parameters. I was getting tired of rebuilding the
> parameter list (which occasionally fixed the problem).
> However, please see my more recent post concerning HTML output caching.
> I'm wondering if my single ~1500 character parameter is causing issues
> with MSRS determining when to return cached versions of the HTML output
> (even though the parameter string differes).
> Once again thank you for your help. My company is evaluating moving
> from an expensive and really buggy high-end reporting system to MSRS. I
> am doing the proof-of-concept work and the HTML issue is causing some
> concern about adopting MSRS. I'd really love to whole-heartedly endorse
> it! :-)
> peter
>|||Thank you Bruce, I might just give that a try. I am still hoping for a
MSFT solution though.|||My feeling is that this will improve in the next version. I just got an
email from Oracle about additional Oracle design tools for VS beta 2. So it
looks like the next version of VS will have better support for Oracle from
Oracle. Plus MS had to work around some tools limitations for this release.
The design tools were designed around oledb, not dotnet. I expect this to
improve dramatically with the next version (hopefully late summer).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"psparago" <psparago@.yahoo.com> wrote in message
news:1107800057.304345.89930@.z14g2000cwz.googlegroups.com...
> Thank you Bruce, I might just give that a try. I am still hoping for a
> MSFT solution though.
>

Oracle report returning strange data

I have a RS report that is connecting to an Oracle Database (8.1.7), the
8.1.7 oracle client is installed on RS Sever, and the result set is returning
strange data. I'm using a simple view that was created in the Oracle
database, that returns a small record set about 100 records. I'm seeing ASCII
values in place of some data, in the Data tab and the Preview tab and the
Published report.
I have tried using both Oracle Drivers and the Microsoft ODBC drivers for
Oracle, and I get the same results.
Some of the data looks just fine but some of the characters get replaced by
ASCII values.
I have installed RS SP1.
Don't know what I am doing wrong...Hi Gary
We are running SQL Reporting Services (SP1) against an Oracle 9i database
without any problems. In our case 9.2.0.4 client is used to talk with
9.2.0.4 database.
I suspect the problem you are having is related to version of Oracle client
you are using. If you have access to a client 9.2.0.4 or higher you may want
to give that a try.
"garyc" wrote:
> I have a RS report that is connecting to an Oracle Database (8.1.7), the
> 8.1.7 oracle client is installed on RS Sever, and the result set is returning
> strange data. I'm using a simple view that was created in the Oracle
> database, that returns a small record set about 100 records. I'm seeing ASCII
> values in place of some data, in the Data tab and the Preview tab and the
> Published report.
> I have tried using both Oracle Drivers and the Microsoft ODBC drivers for
> Oracle, and I get the same results.
> Some of the data looks just fine but some of the characters get replaced by
> ASCII values.
> I have installed RS SP1.
> Don't know what I am doing wrong...

Oracle Problems...

I get:

An error has occurred during report processing. (rsProcessingAborted) Get Online Help Cannot create a connection to data source 'ODBC'. (rsErrorOpeningConnection) Get Online Help ERROR [HY000] [Oracle][ODBC][Ora]ORA-00604: error occurred at recursive SQL level 1 ORA-12705: invalid or unknown NLS parameter value specified ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR [HY000] [Oracle][ODBC][Ora]ORA-00604: error occurred at recursive SQL level 1 ORA-12705: invalid or unknown NLS parameter value specified

when trying to view a report that runs in Visual Studio .NET. After I deploy the report to the Reporting Services server I get the error. There must be a problem in the server's configuration, right? What can I do?

I seem to have gotten this to work. I found a post somewhere on the Internet about a guy DELETING the NLS registry key. This key can be found by searching the registry on the server for home0. I tried it once without restarting the server and it didn't work. Then I restored the key and continued to look for more information. After not find anything, I decided to delete the key again and bounce the server. PRESTO!

Oracle parameters

I am creating a report using a query where the data source is an Oracle 8i database.

I'm selecting data that is between 2 dates:

"Select * from table where mydate is between :startdate and :stopdate".

To get all of the data I need to use :stopdate + 1 to add a day for oracle SSRS uses dateadd to get the date that oracle requires. SSRS does not like the (+1) and Oracle does not recognize the dateadd function.

How do I solve this problem?

P.S. This is my first SSRS report.

The bottom of this posting contains a simple report that shows how to do this based on a SQL Server data source.

Overall there are several options:

* you can keep your current query commandtext and perform the calculation in the report parameter as shown in the RS 2005 sample below, where the second report parameter is marked as "hidden" and its value is determined based on the first report parameter using an expression: =CDate(Parameters!StartDate.Value).AddDays(1)

* perform the datetime calculation in the commandtext. However in that case you *must* use the Oracle date functions to perform the calculation (lookup your Oracle SQL documentation)

* write a Oracle stored procedure (e.g. in PL/SQL) and call the stored procedure from Reporting Services. The stored procedure has to return the data through one OUT REF cursor (see also this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=356162&SiteID=1)

-- Robert

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>95e33366-62c0-4eea-87f0-94d2534377bd</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="StartDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=Today</Value>
</Values>
</DefaultValue>
<Prompt>StartDate</Prompt>
</ReportParameter>
<ReportParameter Name="EndDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=CDate(Parameters!StartDate.Value).AddDays(1)</Value>
</Values>
</DefaultValue>
<Prompt>EndDate</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Textbox Name="textbox2">
<Left>0.25in</Left>
<Top>0.5in</Top>
<ZIndex>1</ZIndex>
<Width>2.375in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>=Parameters!EndDate.Value</Value>
</Textbox>
<Textbox Name="textbox1">
<Left>0.25in</Left>
<Top>0.125in</Top>
<rd:DefaultName>textbox1</rd:DefaultName>
<Width>2.375in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>=Parameters!StartDate.Value</Value>
</Textbox>
</ReportItems>
<Height>2in</Height>
</Body>
<rd:ReportID>e15b81e3-ab03-4e68-a6b8-0de6d6880ab5</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from orders where OrderDate between @.StartDate and @.EndDate</CommandText>
<QueryParameters>
<QueryParameter Name="@.StartDate">
<Value>=Parameters!StartDate.Value</Value>
</QueryParameter>
<QueryParameter Name="@.EndDate">
<Value>=Parameters!EndDate.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="OrderID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>OrderID</DataField>
</Field>
<Field Name="CustomerID">
<rd:TypeName>System.String</rd:TypeName>
<DataField>CustomerID</DataField>
</Field>
<Field Name="EmployeeID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>EmployeeID</DataField>
</Field>
<Field Name="OrderDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>OrderDate</DataField>
</Field>
<Field Name="RequiredDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>RequiredDate</DataField>
</Field>
<Field Name="ShippedDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>ShippedDate</DataField>
</Field>
<Field Name="ShipVia">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ShipVia</DataField>
</Field>
<Field Name="Freight">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>Freight</DataField>
</Field>
<Field Name="ShipName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipName</DataField>
</Field>
<Field Name="ShipAddress">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipAddress</DataField>
</Field>
<Field Name="ShipCity">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipCity</DataField>
</Field>
<Field Name="ShipRegion">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipRegion</DataField>
</Field>
<Field Name="ShipPostalCode">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipPostalCode</DataField>
</Field>
<Field Name="ShipCountry">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipCountry</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>6.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

|||

Robert,

Thanks for your response. Options 1 & 2 are feasible since we can only 'read' from the Oracle database.

Oracle not supported?

I installed de trial version of SQL Server 2005 Workgroup, then created a
small report using a Oracle Database.
In Visual Studio the report previews fine but after being deployed to SSRS
i't tells me that the data extension is not registered.
Is this a trial version issue or is the Oracle support for SSRS have to be
configured after instalation?
Juan Ignacio Herreralook at this page:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
the workgroup edition support only data source against the same SQL Servre
installation only.
you can't directly access Oracle or any external source.
but... maybe you can create a linked server to your oracle database and
create your queries using the openquery SQL command.
the queries will come from your local server and not from your oracle
database.
"Juan Ignacio Herrera" <nacho(quitar)@.delta.com.gt> wrote in message
news:uQ8sNyvGGHA.3176@.TK2MSFTNGP12.phx.gbl...
>I installed de trial version of SQL Server 2005 Workgroup, then created a
> small report using a Oracle Database.
> In Visual Studio the report previews fine but after being deployed to SSRS
> i't tells me that the data extension is not registered.
> Is this a trial version issue or is the Oracle support for SSRS have to be
> configured after instalation?
> Juan Ignacio Herrera
>

Oracle error witrh Report Builder

Hi
I am doing reports and UDMs from a DW in Oracle and it is great (some
doubt expressed in another post).
I tried Report Builder but the new model wizard comes back with the
error:
ORA 02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ
COMMITTED }
The data source file (.ds) has been configured with most of the
connections to Oracle available (e.g. .NET Providers Oracleclient Data
provider; Microsoft OLE DB Provider for ORacle etc.) with same error.
The strange thing is that I can create the .dsv with no problem, browse
the tables, refresh with no problems.
What I am doing wrong?
cheers
EnzoYou are not doing anything wrong. Oracle is not supported for the Report
Builder (note that it is supported for the Report Designer).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Enzo M" <emartoglio@.gmail.com> wrote in message
news:1141724038.842766.151540@.j33g2000cwa.googlegroups.com...
> Hi
> I am doing reports and UDMs from a DW in Oracle and it is great (some
> doubt expressed in another post).
> I tried Report Builder but the new model wizard comes back with the
> error:
> ORA 02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ
> COMMITTED }
> The data source file (.ds) has been configured with most of the
> connections to Oracle available (e.g. .NET Providers Oracleclient Data
> provider; Microsoft OLE DB Provider for ORacle etc.) with same error.
> The strange thing is that I can create the .dsv with no problem, browse
> the tables, refresh with no problems.
> What I am doing wrong?
> cheers
> Enzo
>|||Work-around for Report Builder:
1. Create linked server connection to Oracle database using the
OraOLEDB.Oracle provider(more up-to-date than Microsoft's).
2. Create a Data Source using the native SQL provider to the SQL Server
where you created in step 1.
3. Create a data source view; do not select objects.
4. Right-click in the DSV designer pane and create a New Named Query. Build
your query against the linked server (i.e. use 4-part names: select * from
linkedservername..schema.object). Repeat step 4 for each object you wish to
add to your model.
5. Add logical keys where applicable.
6. Build your model.
7. Deploy & build reports using Report Builder :-)
X

Oracle DB Link in Reporting Services Query String

I am trying to select for a report some data from an Oracle table through a
DB Link, using a select statement:
SELECT a.filed1, a.field2, dbl.field3, dbl.field4
FROM table_name1 a, table_name2@.db_link_name dbl
WHERE a.filed1=dbl.filed5
However, the SQL Report Wizard returns an error â'Invalid characterâ'
(selected data source is ORACLE type). Apparently, it doesnâ't like â'@.â'
character in the string. To find a way around I have tried to create an
Oracle stored procedure which returns Oracle REF Cursor created using the
same select statement. The procedure was compiled and I changed data type in
the dataset to â'Stored Procedureâ' and query string to the procedure name.
But, again, a failure was received since it canâ't refresh the list of
parameters and fields.
Excluding reference to the DB Link from both the Select statement and the
stored procedure solves the problem, but I need the data from it.
Is there another way to retrieve the data for a report through an Oracle DB
Link?Two things to try. First off, did you do this via the generic query
designer. If not try that. Next, what happens if you put double quotes
around it. I.e.
SELECT a.filed1, a.field2, dbl.field3, dbl.field4 FROM table_name1 a,
"table_name2@.db_link_name" dbl WHERE a.filed1=dbl.filed5
Just a wild guess whether the double quote would work.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anatoliy" <Anatoliy@.discussions.microsoft.com> wrote in message
news:19E95C96-1078-4E89-87D6-95420A8D4184@.microsoft.com...
> I am trying to select for a report some data from an Oracle table through
a
> DB Link, using a select statement:
> SELECT a.filed1, a.field2, dbl.field3, dbl.field4
> FROM table_name1 a, table_name2@.db_link_name dbl
> WHERE a.filed1=dbl.filed5
> However, the SQL Report Wizard returns an error "Invalid character"
> (selected data source is ORACLE type). Apparently, it doesn't like "@."
> character in the string. To find a way around I have tried to create an
> Oracle stored procedure which returns Oracle REF Cursor created using the
> same select statement. The procedure was compiled and I changed data type
in
> the dataset to "Stored Procedure" and query string to the procedure name.
> But, again, a failure was received since it can't refresh the list of
> parameters and fields.
> Excluding reference to the DB Link from both the Select statement and the
> stored procedure solves the problem, but I need the data from it.
> Is there another way to retrieve the data for a report through an Oracle
DB
> Link?
>|||Thanks Bruce,
I am afraid, I canâ't use the generic query designer, since it canâ't access a
table though a db link. I can only add a table or a view in the graphical
schema representation.
After I tried to edit the Select statement and put a double quote the syntax
problem was resolved, however I canâ't even save the query string, because of
an error message:
â'Couldnâ't generate a list of fields for the query.â' A similar message was
received when I was trying to use a stored procedure with ref cursor as OUT
parameter. It looks like because the db link table column list canâ't be
generated a critical run time error is raised and it stops execution of the
procedure.
Is there any way to solve this problem, for example to try to enter the list
of parameters and fields manually?
"Bruce L-C [MVP]" wrote:
> Two things to try. First off, did you do this via the generic query
> designer. If not try that. Next, what happens if you put double quotes
> around it. I.e.
> SELECT a.filed1, a.field2, dbl.field3, dbl.field4 FROM table_name1 a,
> "table_name2@.db_link_name" dbl WHERE a.filed1=dbl.filed5
> Just a wild guess whether the double quote would work.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Anatoliy" <Anatoliy@.discussions.microsoft.com> wrote in message
> news:19E95C96-1078-4E89-87D6-95420A8D4184@.microsoft.com...
> > I am trying to select for a report some data from an Oracle table through
> a
> > DB Link, using a select statement:
> >
> > SELECT a.filed1, a.field2, dbl.field3, dbl.field4
> > FROM table_name1 a, table_name2@.db_link_name dbl
> > WHERE a.filed1=dbl.filed5
> >
> > However, the SQL Report Wizard returns an error "Invalid character"
> > (selected data source is ORACLE type). Apparently, it doesn't like "@."
> > character in the string. To find a way around I have tried to create an
> > Oracle stored procedure which returns Oracle REF Cursor created using the
> > same select statement. The procedure was compiled and I changed data type
> in
> > the dataset to "Stored Procedure" and query string to the procedure name.
> > But, again, a failure was received since it can't refresh the list of
> > parameters and fields.
> >
> > Excluding reference to the DB Link from both the Select statement and the
> > stored procedure solves the problem, but I need the data from it.
> >
> > Is there another way to retrieve the data for a report through an Oracle
> DB
> > Link?
> >
> >
>
>|||There are some unusual things going on with Oracle. When you pick the Oracle
provider it is using Oledb provider when you are in the graphical query
designer. If you are in the generic query designer it is using the dotnet
provider. Plus, when deployed it will be using the dotnet provider. The
reason for all of this is that the graphical query designer component is a
shared component that knows nothing about dotnet. Hopefully with Widbey this
will no longer be true but for now, what I said is try.
Now, the generic query designer should act like a passthrough query. Have
you tried it since you started putting in the double quotes?
You can definitely add the fields manually (right click on the fields list).
The graphical designer does a lot with your query. That is why I suggested
the generic. I would spend some more time in the generic trying to get it to
work (generic plus adding the fields manually is the most likely combination
to work). You can always view the rdl and put the query string in that way.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anatoliy" <Anatoliy@.discussions.microsoft.com> wrote in message
news:F03A20D6-FE5E-479A-93B0-BBCD0B9DAA3C@.microsoft.com...
> Thanks Bruce,
> I am afraid, I can't use the generic query designer, since it can't access
a
> table though a db link. I can only add a table or a view in the graphical
> schema representation.
> After I tried to edit the Select statement and put a double quote the
syntax
> problem was resolved, however I can't even save the query string, because
of
> an error message:
> "Couldn't generate a list of fields for the query." A similar message was
> received when I was trying to use a stored procedure with ref cursor as
OUT
> parameter. It looks like because the db link table column list can't be
> generated a critical run time error is raised and it stops execution of
the
> procedure.
> Is there any way to solve this problem, for example to try to enter the
list
> of parameters and fields manually?
>
> "Bruce L-C [MVP]" wrote:
> > Two things to try. First off, did you do this via the generic query
> > designer. If not try that. Next, what happens if you put double quotes
> > around it. I.e.
> >
> > SELECT a.filed1, a.field2, dbl.field3, dbl.field4 FROM table_name1 a,
> > "table_name2@.db_link_name" dbl WHERE a.filed1=dbl.filed5
> >
> > Just a wild guess whether the double quote would work.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Anatoliy" <Anatoliy@.discussions.microsoft.com> wrote in message
> > news:19E95C96-1078-4E89-87D6-95420A8D4184@.microsoft.com...
> > > I am trying to select for a report some data from an Oracle table
through
> > a
> > > DB Link, using a select statement:
> > >
> > > SELECT a.filed1, a.field2, dbl.field3, dbl.field4
> > > FROM table_name1 a, table_name2@.db_link_name dbl
> > > WHERE a.filed1=dbl.filed5
> > >
> > > However, the SQL Report Wizard returns an error "Invalid character"
> > > (selected data source is ORACLE type). Apparently, it doesn't like
"@."
> > > character in the string. To find a way around I have tried to create
an
> > > Oracle stored procedure which returns Oracle REF Cursor created using
the
> > > same select statement. The procedure was compiled and I changed data
type
> > in
> > > the dataset to "Stored Procedure" and query string to the procedure
name.
> > > But, again, a failure was received since it can't refresh the list of
> > > parameters and fields.
> > >
> > > Excluding reference to the DB Link from both the Select statement and
the
> > > stored procedure solves the problem, but I need the data from it.
> > >
> > > Is there another way to retrieve the data for a report through an
Oracle
> > DB
> > > Link?
> > >
> > >
> >
> >
> >