Parent Child hierarchy view

Is it possible with a datagrid to display a hierarchy view of a dataset
which is held in a single table that have an ID and Parent ID field
giving multiple levels of a hierarchy?
If it is possible how do we do it?
Regards
<<<Bryan>>Found a very useful url
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_structure_objects_v1_3cok.asp

Parse dataset in report

I am using a "jump to report" that accepts multivalue parameters for fiscal periods. It works fine, returning the correct data. However, I want to add the chosen fiscal periods to the report header in a textbox. When I use

=Join(Parameters!DATEfiscalperiod.Value, ", ")

the results in the textbox show as:

[DATE].[fiscal_period].&[5], [DATE].[fiscal_period].&

When using::

=Parameters!DATEfiscalperiod.Value

results in

[rsInvalidExpressionDataType] The Value expression used in textbox ‘textbox5’ returned a data type that is not valid.

Is there a way to parse the dataset to only return "5, 6" in a string?

TIA

Hi, Takuma,

Try using the parameter label field instead:

=Join(Parameters!DATEfiscalperiod.Label, ", ")

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thank you Mary,

That works.

However, I also have a single value parameter. When I try to use this:

=Parameters!SALESPERSON.Label

no data is returned.

When I use:

=Parameters!SALESPERSONBDM.Value, I get this error:

[rsInvalidExpressionDataType] The Value expression used in textbox ‘textbox9’ returned a data type that is not valid.

Thank you for your help.

|||Is it possible to do this string concatenation in the sql that defines the dataset?
select field1 + ", " + field2 as fieldname
from ...
where ...

otherwise you can do similar things in the expression editor.

Parameters!Report_Parameter_0.Value.ToString() + ", " + Parameters!Report_Parameter_1.Value.ToString()|||

Thanks for the reply killerless,

Unfortunately, when I use

Parameters!Report_Parameter_0.Value.ToString()

it results in the whole dimension hierarchy structure: [SALESMGR].[]SALESPERSON.[NORTH]

I only want to show "NORTH".

Parameters!Report_Parameter_0.Value results in the same [SALESMGR].[SALESPERSON].[NORTH]

Parameters!Report_Parameter_0.:Label returns nothing.

It's also only a single value parameter.

Maybe I need to modify my mdx somehow but as you can see I'm a newbie.

|||So you have a parameter that is

[SALESMGR].[]SALESPERSON.[NORTH]?

|||

The parameter name that is passed is "salesperson".

I pass the parameter using "Jump to report". The parameter itself works fine, the data returned is correct. The only problem is displaying the chosen parameter in a user friendly format, (not in the mdx structure format).

The multivalue fiscal periods parameter works using the join().

|||Using the left or right functions you could reasonably easily filter out the unecessary text that is coming through to that textbox|||

Thank you for the help. That works.

Parameters opening the same datasets over and over again..

Hello:
I have a report with multiple tables, one table per page. On the page header
I need to show some information that is coming from a dataset, several
records like first name, last name, file#, etc..
I've a single dataset in my report, which has the data for all the tables,
and it includes the information I need in the header.
My page header expression is the sum of the values of some parameters that
gets their values from the dataset.
The problem is that the stored procedure I'm using to get the data is
executed once for each parameter, instead of executing it only once and get
the values for all the parameters locally.
Is there any way to improve this behavior?
Thanks,
Daniel Bello Urizarri.On Mar 26, 4:09 pm, "Daniel Bello" <dburiza...@.yahoo.es> wrote:
> Hello:
> I have a report with multiple tables, one table per page. On the page header
> I need to show some information that is coming from a dataset, several
> records like first name, last name, file#, etc..
> I've a single dataset in my report, which has the data for all the tables,
> and it includes the information I need in the header.
> My page header expression is the sum of the values of some parameters that
> gets their values from the dataset.
> The problem is that the stored procedure I'm using to get the data is
> executed once for each parameter, instead of executing it only once and get
> the values for all the parameters locally.
> Is there any way to improve this behavior?
> Thanks,
> Daniel Bello Urizarri.
You might be able to avoid the multiple parameters, if I understand
you correctly, by using aggregates to reference values in the dataset.
Something like this might help.
=Max(Fields!FirstName.Value, "DataSetName")
=Max(Fields!LastName.Value, "DataSetName")
=Max(Fields!FileNum.Value, "DataSetName")
Regards,
Enrique Martinez
Sr. Software Consultant|||"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1174959834.202639.33660@.p77g2000hsh.googlegroups.com...
> On Mar 26, 4:09 pm, "Daniel Bello" <dburiza...@.yahoo.es> wrote:
>> Hello:
>> I have a report with multiple tables, one table per page. On the page
>> header
>> I need to show some information that is coming from a dataset, several
>> records like first name, last name, file#, etc..
>> I've a single dataset in my report, which has the data for all the
>> tables,
>> and it includes the information I need in the header.
>> My page header expression is the sum of the values of some parameters
>> that
>> gets their values from the dataset.
>> The problem is that the stored procedure I'm using to get the data is
>> executed once for each parameter, instead of executing it only once and
>> get
>> the values for all the parameters locally.
>> Is there any way to improve this behavior?
>> Thanks,
>> Daniel Bello Urizarri.
>
> You might be able to avoid the multiple parameters, if I understand
> you correctly, by using aggregates to reference values in the dataset.
> Something like this might help.
> =Max(Fields!FirstName.Value, "DataSetName")
> =Max(Fields!LastName.Value, "DataSetName")
> =Max(Fields!FileNum.Value, "DataSetName")
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>
Enrique:
You can not use fields on the headers, that is why I'm using the parameters.
This part of the reporting services is so poorly coded, I can't see a single
reason to open the datasets several times, and I see a lot of reasons to
open the dataset only once and then use it for all the parameters, the lists
of available values and the default values.
Thanks,
Daniel Bello Urizarri.

Parameters

Attempting to use parameters for report pulling from Teradata. Appears TD
does not like parameters. Thinking of custom code for generate dataset query
dynamically.. Is there function/method to generate / replace sql string?
Other ideas?
Thxin place of your query, have a vb-type expresssion like:
="select * from tablename where col1 = " & Parameters!Parm1.value
Let the report create your fields for you by running a hard-coded version of
the query, then change the query string to the vb expression. It will
generate the sql string expression, then execute it. As long as the
expression generates a sql string whose fields match those pre-generated in
the report, it will work.
Steve
"DJC" wrote:
> Attempting to use parameters for report pulling from Teradata. Appears TD
> does not like parameters. Thinking of custom code for generate dataset query
> dynamically.. Is there function/method to generate / replace sql string?
> Other ideas?
> Thx|||What error are you geting with the Parameters you are useing ?
In so cases TD does not let you name your Parameters. so what i did was this
--
(DT_COM BETWEEN TO_DATE(?, 'MM/DD/YYYY') AND TO_DATE(?, 'MM/DD/YYYY'))
"SteveIrwin" wrote:
> in place of your query, have a vb-type expresssion like:
> ="select * from tablename where col1 = " & Parameters!Parm1.value
> Let the report create your fields for you by running a hard-coded version of
> the query, then change the query string to the vb expression. It will
> generate the sql string expression, then execute it. As long as the
> expression generates a sql string whose fields match those pre-generated in
> the report, it will work.
> Steve
> "DJC" wrote:
> > Attempting to use parameters for report pulling from Teradata. Appears TD
> > does not like parameters. Thinking of custom code for generate dataset query
> > dynamically.. Is there function/method to generate / replace sql string?
> > Other ideas?
> > Thx|||Thanks Steve.. works like a charm.
"SteveIrwin" wrote:
> in place of your query, have a vb-type expresssion like:
> ="select * from tablename where col1 = " & Parameters!Parm1.value
> Let the report create your fields for you by running a hard-coded version of
> the query, then change the query string to the vb expression. It will
> generate the sql string expression, then execute it. As long as the
> expression generates a sql string whose fields match those pre-generated in
> the report, it will work.
> Steve
> "DJC" wrote:
> > Attempting to use parameters for report pulling from Teradata. Appears TD
> > does not like parameters. Thinking of custom code for generate dataset query
> > dynamically.. Is there function/method to generate / replace sql string?
> > Other ideas?
> > Thx|||Thanke for the response. Steve's respone resolved...
BTW, Most of the errors were I think were related to OLE DB vs ODBC on the
TD side...
"C.M" wrote:
> What error are you geting with the Parameters you are useing ?
> In so cases TD does not let you name your Parameters. so what i did was this
> --
> (DT_COM BETWEEN TO_DATE(?, 'MM/DD/YYYY') AND TO_DATE(?, 'MM/DD/YYYY'))
>
>
> "SteveIrwin" wrote:
> > in place of your query, have a vb-type expresssion like:
> >
> > ="select * from tablename where col1 = " & Parameters!Parm1.value
> >
> > Let the report create your fields for you by running a hard-coded version of
> > the query, then change the query string to the vb expression. It will
> > generate the sql string expression, then execute it. As long as the
> > expression generates a sql string whose fields match those pre-generated in
> > the report, it will work.
> >
> > Steve
> >
> > "DJC" wrote:
> >
> > > Attempting to use parameters for report pulling from Teradata. Appears TD
> > > does not like parameters. Thinking of custom code for generate dataset query
> > > dynamically.. Is there function/method to generate / replace sql string?
> > > Other ideas?
> > > Thx

Parameterized query that will work for both Oracle and SQL Server

Hi, I have a dataset that I am using the following SQL DML to gather the data
select * from hermes.vwsurveyprepostresults where ssn = ? and surveydate = ?
This works fine when the Shared Data Source is Oracle. However when I am
using a SQL Server Shared Data Source I need to change it to the following
with the Native SQL Client
select * from hermes.vwsurveyprepostresults where ssn = @.SSN and surveydate
= @.SURVEYDATE
However when I change it to use OLEDB for SQL Server as a shared data source
and revert to the ? as parameter place holders I get the following error -
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'HRAII'. (rsErrorExecutingCommand)
Incorrect syntax near '?'. Incorrect syntax near '?'.
Is there a parameterized query that will work for both Oracle and SQL Server?
--
Thank you,
JohnTry to set Oracle a linked server and using store procedure as the query
string.
You can generate you SQL synatx in store procedure depending on the input
parameter.
HTH
> Hi, I have a dataset that I am using the following SQL DML to gather the
> data
> select * from hermes.vwsurveyprepostresults where ssn = ? and surveydate => ?
> This works fine when the Shared Data Source is Oracle. However when I am
> using a SQL Server Shared Data Source I need to change it to the following
> with the Native SQL Client
> select * from hermes.vwsurveyprepostresults where ssn = @.SSN and
> surveydate
> = @.SURVEYDATE
> However when I change it to use OLEDB for SQL Server as a shared data
> source
> and revert to the ? as parameter place holders I get the following error -
> An error has occurred during report processing. (rsProcessingAborted)
> Query execution failed for data set 'HRAII'. (rsErrorExecutingCommand)
> Incorrect syntax near '?'. Incorrect syntax near '?'.
> Is there a parameterized query that will work for both Oracle and SQL
> Server?
>
> --
> Thank you,
> John|||Is this the easiest way to do it? Seems like a hack, inelegant and not
appealing.
Currently I am trying to figure out how to use a Web Service as my data
source - XML. That way I can control via a config file which database to
pull from and the paremeters will be the same.
Both these solutions seem like a convulusion of what should be a relatively
easy thing to do. Pass an ANSI-SQL query a parameter using OLEDB as the
provider and have the parameter signature always be the same. I cannot
believe this is such a difficult thing. If I did not know better I would
swear that this is a bug.
--
Thank you,
John
"Steffi" wrote:
> Try to set Oracle a linked server and using store procedure as the query
> string.
> You can generate you SQL synatx in store procedure depending on the input
> parameter.
> HTH
> > Hi, I have a dataset that I am using the following SQL DML to gather the
> > data
> > select * from hermes.vwsurveyprepostresults where ssn = ? and surveydate => > ?
> >
> > This works fine when the Shared Data Source is Oracle. However when I am
> > using a SQL Server Shared Data Source I need to change it to the following
> > with the Native SQL Client
> > select * from hermes.vwsurveyprepostresults where ssn = @.SSN and
> > surveydate
> > = @.SURVEYDATE
> >
> > However when I change it to use OLEDB for SQL Server as a shared data
> > source
> > and revert to the ? as parameter place holders I get the following error -
> >
> > An error has occurred during report processing. (rsProcessingAborted)
> > Query execution failed for data set 'HRAII'. (rsErrorExecutingCommand)
> > Incorrect syntax near '?'. Incorrect syntax near '?'.
> >
> > Is there a parameterized query that will work for both Oracle and SQL
> > Server?
> >
> >
> > --
> > Thank you,
> > John
>
>|||If you use ODBC against both then you will be able to use the same query.
But, keep in mind that Oracle and SQL Server can have different syntax. But,
if you are using the more recent Oracle (like 9i or 10i ... not sure what
the latest version is) then Oracle has started to support join syntax (inner
join, left join etc). Otherwise, if you get complicated at all then the
syntax can vary.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John A" <i-code4food@.newsgroups.nospam> wrote in message
news:3EF087E3-3F8D-4415-9B9D-CAA912A55A13@.microsoft.com...
> Hi, I have a dataset that I am using the following SQL DML to gather the
> data
> select * from hermes.vwsurveyprepostresults where ssn = ? and surveydate => ?
> This works fine when the Shared Data Source is Oracle. However when I am
> using a SQL Server Shared Data Source I need to change it to the following
> with the Native SQL Client
> select * from hermes.vwsurveyprepostresults where ssn = @.SSN and
> surveydate
> = @.SURVEYDATE
> However when I change it to use OLEDB for SQL Server as a shared data
> source
> and revert to the ? as parameter place holders I get the following error -
> An error has occurred during report processing. (rsProcessingAborted)
> Query execution failed for data set 'HRAII'. (rsErrorExecutingCommand)
> Incorrect syntax near '?'. Incorrect syntax near '?'.
> Is there a parameterized query that will work for both Oracle and SQL
> Server?
>
> --
> Thank you,
> John|||Hi John,
Thank you for your post.
OLE DB is a Microsoft's strategic low-level application program interface
(API) for access to different data sources.
An application using OLE DB would use this request sequence:
Initialize OLE.
Connect to a data source.
Issue a command.
Process the results.
Release the data source object and uninitialize OLE.
The Oledb provide just pass the sql command to the database engine to
execute and get the recordset from the engine.
Since the syntax of SQL Server only support the variable marked with @., you
could not use other mark to specify it's a parameter.
I think Steffi's suggestion is a great suggest for you. You could add a
linked server in the sql server and use the stored procedure to get the
result.
Configuring Linked Servers
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
_1_server_4uuq.asp
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for all the help guys but I am leaning more toward using a Web Service
AKA XML as my data source. Played around with it over the weekend and got it
working. This actually has a number of benefits for me. The main one being
greater control over what is happening from a programatic stand point. I
have nothing against using Stored Procedures per se but I think that I would
run into problems linking the databases as I do not have much control over
our Oracle databases.
--
Thank you,
John
"Wei Lu" wrote:
> Hi John,
> Thank you for your post.
> OLE DB is a Microsoft's strategic low-level application program interface
> (API) for access to different data sources.
> An application using OLE DB would use this request sequence:
> Initialize OLE.
> Connect to a data source.
> Issue a command.
> Process the results.
> Release the data source object and uninitialize OLE.
> The Oledb provide just pass the sql command to the database engine to
> execute and get the recordset from the engine.
> Since the syntax of SQL Server only support the variable marked with @., you
> could not use other mark to specify it's a parameter.
> I think Steffi's suggestion is a great suggest for you. You could add a
> linked server in the sql server and use the stored procedure to get the
> result.
> Configuring Linked Servers
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
> _1_server_4uuq.asp
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Thanks Bruce, this seems like the easiest and best short term solution.
--
Thank you,
John
"Bruce L-C [MVP]" wrote:
> If you use ODBC against both then you will be able to use the same query.
> But, keep in mind that Oracle and SQL Server can have different syntax. But,
> if you are using the more recent Oracle (like 9i or 10i ... not sure what
> the latest version is) then Oracle has started to support join syntax (inner
> join, left join etc). Otherwise, if you get complicated at all then the
> syntax can vary.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John A" <i-code4food@.newsgroups.nospam> wrote in message
> news:3EF087E3-3F8D-4415-9B9D-CAA912A55A13@.microsoft.com...
> > Hi, I have a dataset that I am using the following SQL DML to gather the
> > data
> > select * from hermes.vwsurveyprepostresults where ssn = ? and surveydate => > ?
> >
> > This works fine when the Shared Data Source is Oracle. However when I am
> > using a SQL Server Shared Data Source I need to change it to the following
> > with the Native SQL Client
> > select * from hermes.vwsurveyprepostresults where ssn = @.SSN and
> > surveydate
> > = @.SURVEYDATE
> >
> > However when I change it to use OLEDB for SQL Server as a shared data
> > source
> > and revert to the ? as parameter place holders I get the following error -
> >
> > An error has occurred during report processing. (rsProcessingAborted)
> > Query execution failed for data set 'HRAII'. (rsErrorExecutingCommand)
> > Incorrect syntax near '?'. Incorrect syntax near '?'.
> >
> > Is there a parameterized query that will work for both Oracle and SQL
> > Server?
> >
> >
> > --
> > Thank you,
> > John
>
>|||John,
I wanted to put this post because I was trying to do the same thing that you
are, using parameters with Reporting Services to hit an Oracle DB.
The only way that I found out how to get this to work is by using a ":"
instead of the "@.".
So, for example:
SELECT *
FROM sometable x
WHERE x.auth_date >= to_date(:param_start, 'mm/dd/yyyy')
Then I set my Report Parameter(param_start) as a string value.
It works fine for me right now, I do wonder when I try to roll it up into a
stored proc that if it will work or not. I hope this helps others out there.
Rob Cuscaden
"John A" wrote:
> Thanks Bruce, this seems like the easiest and best short term solution.
> --
> Thank you,
> John
>
> "Bruce L-C [MVP]" wrote:
> > If you use ODBC against both then you will be able to use the same query.
> > But, keep in mind that Oracle and SQL Server can have different syntax. But,
> > if you are using the more recent Oracle (like 9i or 10i ... not sure what
> > the latest version is) then Oracle has started to support join syntax (inner
> > join, left join etc). Otherwise, if you get complicated at all then the
> > syntax can vary.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "John A" <i-code4food@.newsgroups.nospam> wrote in message
> > news:3EF087E3-3F8D-4415-9B9D-CAA912A55A13@.microsoft.com...
> > > Hi, I have a dataset that I am using the following SQL DML to gather the
> > > data
> > > select * from hermes.vwsurveyprepostresults where ssn = ? and surveydate => > > ?
> > >
> > > This works fine when the Shared Data Source is Oracle. However when I am
> > > using a SQL Server Shared Data Source I need to change it to the following
> > > with the Native SQL Client
> > > select * from hermes.vwsurveyprepostresults where ssn = @.SSN and
> > > surveydate
> > > = @.SURVEYDATE
> > >
> > > However when I change it to use OLEDB for SQL Server as a shared data
> > > source
> > > and revert to the ? as parameter place holders I get the following error -
> > >
> > > An error has occurred during report processing. (rsProcessingAborted)
> > > Query execution failed for data set 'HRAII'. (rsErrorExecutingCommand)
> > > Incorrect syntax near '?'. Incorrect syntax near '?'.
> > >
> > > Is there a parameterized query that will work for both Oracle and SQL
> > > Server?
> > >
> > >
> > > --
> > > Thank you,
> > > John
> >
> >
> >|||Bruce -- I am just trying your suggestion, since I have the same issue. In
my query, how do I write the parameters (with a '?', ':' or '@.')?
I have tried all 3 with no success.
When I try '?' - I get an error saying that "Cannot add multi value query
parameter '?' for data set 'Report_main' because it is not supported by the
data extension."
When I try ':' - it says incorrect syntax near ':'
When I try '@.' - it says "The data extension ODBC does not support named
parameters. Use unnamed parameters instead."
Thanks in advance for your help.
--
LaurieT
"Bruce L-C [MVP]" wrote:
> If you use ODBC against both then you will be able to use the same query.
> But, keep in mind that Oracle and SQL Server can have different syntax. But,
> if you are using the more recent Oracle (like 9i or 10i ... not sure what
> the latest version is) then Oracle has started to support join syntax (inner
> join, left join etc). Otherwise, if you get complicated at all then the
> syntax can vary.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John A" <i-code4food@.newsgroups.nospam> wrote in message
> news:3EF087E3-3F8D-4415-9B9D-CAA912A55A13@.microsoft.com...
> > Hi, I have a dataset that I am using the following SQL DML to gather the
> > data
> > select * from hermes.vwsurveyprepostresults where ssn = ? and surveydate => > ?
> >
> > This works fine when the Shared Data Source is Oracle. However when I am
> > using a SQL Server Shared Data Source I need to change it to the following
> > with the Native SQL Client
> > select * from hermes.vwsurveyprepostresults where ssn = @.SSN and
> > surveydate
> > = @.SURVEYDATE
> >
> > However when I change it to use OLEDB for SQL Server as a shared data
> > source
> > and revert to the ? as parameter place holders I get the following error -
> >
> > An error has occurred during report processing. (rsProcessingAborted)
> > Query execution failed for data set 'HRAII'. (rsErrorExecutingCommand)
> > Incorrect syntax near '?'. Incorrect syntax near '?'.
> >
> > Is there a parameterized query that will work for both Oracle and SQL
> > Server?
> >
> >
> > --
> > Thank you,
> > John
>
>|||Hello Laurie,
Have you tried the suggestion Rob Provided?
===========================From: =?Utf-8?B?Um9i?= <Rob@.discussions.microsoft.com>
Subject: Re: Parameterized query that will work for both Oracle and SQL Ser
Date: Fri, 21 Jul 2006 09:39:01 -0700
Newsgroups: microsoft.public.sqlserver.reportingsvcs
John,
I wanted to put this post because I was trying to do the same thing that
you
are, using parameters with Reporting Services to hit an Oracle DB.
The only way that I found out how to get this to work is by using a ":"
instead of the "@.".
So, for example:
SELECT *
FROM sometable x
WHERE x.auth_date >= to_date(:param_start, 'mm/dd/yyyy')
Then I set my Report Parameter(param_start) as a string value.
It works fine for me right now, I do wonder when I try to roll it up into a
stored proc that if it will work or not. I hope this helps others out
there.
Rob Cuscaden
===========================
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Yes -- I think so.
I was already using ":" variables in my query. But I was connecting to the
database using an Oracle driver. So I set up a ODBC connection to my SQL
Server database, then I created a new datasource using ODBC and pointed to
the SQL Server ODBC connection. I get the following error: incorrect syntax
near the ':'.
I must be missing something?
--
LaurieT
"Wei Lu [MSFT]" wrote:
> Hello Laurie,
> Have you tried the suggestion Rob Provided?
> ===========================> From: =?Utf-8?B?Um9i?= <Rob@.discussions.microsoft.com>
> Subject: Re: Parameterized query that will work for both Oracle and SQL Ser
> Date: Fri, 21 Jul 2006 09:39:01 -0700
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> John,
> I wanted to put this post because I was trying to do the same thing that
> you
> are, using parameters with Reporting Services to hit an Oracle DB.
> The only way that I found out how to get this to work is by using a ":"
> instead of the "@.".
> So, for example:
> SELECT *
> FROM sometable x
> WHERE x.auth_date >= to_date(:param_start, 'mm/dd/yyyy')
> Then I set my Report Parameter(param_start) as a string value.
> It works fine for me right now, I do wonder when I try to roll it up into a
> stored proc that if it will work or not. I hope this helps others out
> there.
> Rob Cuscaden
> ===========================> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||I've been working with some of the same issues and found that you can't
really blur the like between the Oracle and SQL Server data.
You have to query the Oracle db with Oracle syntax. If you want to roll
everything up to a SQL Server OLTP or OLAP db, design an SSIS package
and import your Oracle & SQL data into a single source.
I know it's not the answer you wanted, but in the end it will be faster.
Garth H
webdev511@.spamcop.net
Microsoft Certified Professional
Macromedia Certified Developer|||Hello Laurie,
I agree with Garth that design a SSIS package(in SQL 2005) or a DTS package
(in SQL 2000) will be a faster way to do this.
Also, you may try to build up a Linked server in SQL Server which point to
the Oracle database.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Parameterized part of the query?

I have a standard report that I would like to use over and over. The
difference for each version is the WHERE clause on the dataset SQL. I tried
using a parameter and sending in the information but it doesn't seem to work.
Perhaps I am formatting it incorrectly. Any suggestions? Can I do this?
StephanieStephanie,
I hope, you do not want to pass T-SQL as a parameter. If you do, you should
have sql injection attack.
Try to do the following:
Open BIDS and create new Report project from file menu.
Add a new report item to your project.
Navigate to the data tab in the designer, then create a new dataset.
Choose command type - I recommend to use stored procedures, because of
security reason.
Type stored procedure name to the query string.
All of the stored procedure's parameters will be added automatically to your
parameter list in the report.
If you use command type text, then you should add manually the parameters if
BIDS does not do automatically.
You should find a really good step by step guide in the BOL under the SQL
Server 2005 Tutorials --> Reporting Services Tutorials --> Using a Dynamic
Query in a Report section.
I hope it will help for you.
Kind Regards,
Janos
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:4AE93DB5-D7B0-4C04-AA61-399062840200@.microsoft.com...
>I have a standard report that I would like to use over and over. The
> difference for each version is the WHERE clause on the dataset SQL. I
> tried
> using a parameter and sending in the information but it doesn't seem to
> work.
> Perhaps I am formatting it incorrectly. Any suggestions? Can I do this?
> Stephanie|||Janos,
Thanks for the help. I was able to find what I needed using the tutorial
you identified.
Stephanie
"BERKE Janos" wrote:
> Stephanie,
> I hope, you do not want to pass T-SQL as a parameter. If you do, you should
> have sql injection attack.
> Try to do the following:
> Open BIDS and create new Report project from file menu.
> Add a new report item to your project.
> Navigate to the data tab in the designer, then create a new dataset.
> Choose command type - I recommend to use stored procedures, because of
> security reason.
> Type stored procedure name to the query string.
> All of the stored procedure's parameters will be added automatically to your
> parameter list in the report.
> If you use command type text, then you should add manually the parameters if
> BIDS does not do automatically.
> You should find a really good step by step guide in the BOL under the SQL
> Server 2005 Tutorials --> Reporting Services Tutorials --> Using a Dynamic
> Query in a Report section.
> I hope it will help for you.
> Kind Regards,
> Janos
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:4AE93DB5-D7B0-4C04-AA61-399062840200@.microsoft.com...
> >I have a standard report that I would like to use over and over. The
> > difference for each version is the WHERE clause on the dataset SQL. I
> > tried
> > using a parameter and sending in the information but it doesn't seem to
> > work.
> > Perhaps I am formatting it incorrectly. Any suggestions? Can I do this?
> >
> > Stephanie
>