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.

Paramter Passing

Is it possible to pass variables/parameters to a report other thank through
a url using ASP, as this will compromise our security ?
Thanks in advanceTwo options. 1. Use web services. 2. Pull the sensitive parameters by using
the global user!userid. This value tells you who is running the report and
you can then use that to query the database to find out the sensitive
parameters. You might need to do something like having a table with the
username, report and sensitive parameters that your write to before your
application calls the report. And, as I said, you can also use web services.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Con" <conh@.melbournehosting.com> wrote in message
news:u8z7eiPvEHA.3276@.TK2MSFTNGP15.phx.gbl...
> Is it possible to pass variables/parameters to a report other thank
through
> a url using ASP, as this will compromise our security ?
> Thanks in advance
>

Parametric

Hello,

I'm using SQL 2005 and reporting services.

I have several parameters, the report show two parameters in the same line and I would like to present three parameters in the same line.

how can I do that ?

This is not natively supported.

The closest you can get is to write your own parameter frontend application and then e.g. use the RS report viewer controls to send the parameters to the report server and execute the report in "remote" mode. Other options include to send the parameters via URL-access from your custom frontend application.

-- Robert

Parametres - SqlQuery

Could you pls let me know that when we use the parameters how the sql server ?s able to keep all the datas on the str?ng type?and also how ?t can be prov?de that secur?ty on system(how ?t ?gnore to do sql ?nject?on...).

I did not get your point with the data on the string type ? SQL Injection is not automatically audited in SQL Server you will have to either to secure coding (preferable) or do not use dynamic sql at all.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

ParameterValueClass for setting Parameters Property of ReportViewer

I am trying to set the ReportViewer (the one that runs as a .net control)
Parameters property, which is looking for an array of ParameterValue
objects. Where is the ParameterValue object defined? I cannot find it in
the ReportViewer, or the Web Services. Any ideas how to pass a set of
parameneters to the Parameters property of the ReportViewer?
Thanx, BobAre you referring to the ReportViewer sample control that ships with
Reporting Services?
The Parameters property of the control refers specifically to the parameters
area of the toolbar which can display input fields for report parameters.
This is not a property that can be used to pass report parameters. The
ReportViewer sample utilizes the report server built in parameters support.
--
Bryan Keller
Developer Documentation
SQL Server Reporting Services
A friendly reminder that this posting is provided "AS IS" with no
warranties, and confers no rights.
"Bob Feller" <bob@.nospam.morningdew.net> wrote in message
news:eoz$ml2aEHA.1644@.tk2msftngp13.phx.gbl...
> I am trying to set the ReportViewer (the one that runs as a .net control)
> Parameters property, which is looking for an array of ParameterValue
> objects. Where is the ParameterValue object defined? I cannot find it in
> the ReportViewer, or the Web Services. Any ideas how to pass a set of
> parameneters to the Parameters property of the ReportViewer?
> Thanx, Bob
>

Parameters: Users must choose one or both parameters

I have two date parameters in my report. The user must make a selction on at
least one of the two parameters (either one), but does not have to make a
selection on both.
How do I verify that at least one parameters has a value? I guess this must
be verified on a pre-Execute event on the View Report button.
Thanks in advance for your help.Allow Null on both dates. Then in your sproc (which is essentially what the
query turns into) check both are not null, else raise an error.
--
William Stacey [MVP]
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:0F4F4ED4-CF0F-4955-8265-EC47576F0186@.microsoft.com...
|I have two date parameters in my report. The user must make a selction on
at
| least one of the two parameters (either one), but does not have to make a
| selection on both.
|
| How do I verify that at least one parameters has a value? I guess this
must
| be verified on a pre-Execute event on the View Report button.
|
| Thanks in advance for your help.|||Do I understand you correct when I sum up your answer like this:
In the receiving procedure on the SQL Server I will have to check if at
least one of the two parameters has a value. If not I raise an error in the
stored procedure. This error is automatically caught by Reporting Services
and displayed to the end user.
"William Stacey [MVP]" wrote:
> Allow Null on both dates. Then in your sproc (which is essentially what the
> query turns into) check both are not null, else raise an error.
> --
> William Stacey [MVP]
> "Billy" <Billy@.discussions.microsoft.com> wrote in message
> news:0F4F4ED4-CF0F-4955-8265-EC47576F0186@.microsoft.com...
> |I have two date parameters in my report. The user must make a selction on
> at
> | least one of the two parameters (either one), but does not have to make a
> | selection on both.
> |
> | How do I verify that at least one parameters has a value? I guess this
> must
> | be verified on a pre-Execute event on the View Report button.
> |
> | Thanks in advance for your help.
>
>|||Right. And the report page will display the error text as you entered it.
A dialog box would be better (maybe), but that is what we have.
--
William Stacey [MVP]
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:9A2E1E77-BC99-466C-A856-F4DD48DD2B64@.microsoft.com...
| Do I understand you correct when I sum up your answer like this:
|
| In the receiving procedure on the SQL Server I will have to check if at
| least one of the two parameters has a value. If not I raise an error in
the
| stored procedure. This error is automatically caught by Reporting Services
| and displayed to the end user.
|
|
|
|
| "William Stacey [MVP]" wrote:
|
| > Allow Null on both dates. Then in your sproc (which is essentially what
the
| > query turns into) check both are not null, else raise an error.
| >
| > --
| > William Stacey [MVP]
| >
| > "Billy" <Billy@.discussions.microsoft.com> wrote in message
| > news:0F4F4ED4-CF0F-4955-8265-EC47576F0186@.microsoft.com...
| > |I have two date parameters in my report. The user must make a selction
on
| > at
| > | least one of the two parameters (either one), but does not have to
make a
| > | selection on both.
| > |
| > | How do I verify that at least one parameters has a value? I guess this
| > must
| > | be verified on a pre-Execute event on the View Report button.
| > |
| > | Thanks in advance for your help.
| >
| >
| >

Parameters: How to set null value

Hi, I have a problem.
I have two parameters: Group and Selection

In the first I have 2 choices: Total and Day
And the value of the second parameter is dynamic and is sets to allow null value
The behaviour I want is this: If a user select Total in the first parameter, the second must be set to NULL, if the selection is Day, the second must be set with a data set.

How I can set the value of the second parameter with null, I tryed

Have anyone an idea?

One way I have done this in the past is using '-1' as the Total value, and then letting the stored procedure pass in a null value by setting all '-1' parameters to null.

if @.param1 = '-1' set @.param1=null

System.DbNull.Value may work too.

Yet another option that may be possible is to use an expression to convert the parameter to a null value, and pass in the expression instead of the parameter to the report.

cheers,

Andrew

|||thanks,
I tryed System.DbNull.Value but give me this error : DbNull is a type in system and cannot be used as an expression

Perhaps I wrong something:
I wrote this in the datasets I use for the second parameter

=iif(Parameters!parm1.Value = 2,
"select a11.DATE_ID from Day" ,System.DBNull )

Another question, where I have to place this expression, in the store procedure?

if @.param1 = '-1' set @.param1=null

but I use a dataset.

Thanks in advance

|||

Not sure exactly how you are passing in a dataset as a parameter but looks interesting.

Instead of null perhaps you could use "SELECT null as DateID" instead? Or maybe an empty string?

The syntax for getting dbnull is to use System.DBNull.Value

=iif(Parameters!parm1.Value = 2,
"select a11.DATE_ID from Day" ,System.DBNull.Value )

cheers,

Andrew

|||If I use this expression "System.DBNull.Value" something don't work but I don't understand what.
but following your example and this link forums.microsoft.com/MSDN/ShowPost.aspx?PostID=766574&SiteID=1
it works

thanks

Parameters: Calculating Months

What I am trying to do seems simple and I have most of it figured out. I am
in RS2000, using Oracle DB. I am getting the the CreatedMonth and
CurrReportMonth...no issues. I need help with deriving PrevReportMonth
because it crosses years. In this example that the CreatedMonth = 200802
If there is a better way to do this maybe in T-SQL I am definitely open to
that also.
select to_number(to_char(sysdate, 'YYYYMM')) as CreatedMonth,
to_number(to_char(sysdate, 'YYYYMM') - 1) as CurrReportMonth,
'200712' as PrevReportMonth
from dual
Thnx in advance
OSWhy dont you try using the add_months() dunction. it will solve the
cross year problem it self. But the datatype have to be date.
On Mar 8, 4:40 am, OriginalStealth
<OriginalStea...@.discussions.microsoft.com> wrote:
> What I am trying to do seems simple and I have most of it figured out. I am
> in RS2000, using Oracle DB. I am getting the the CreatedMonth and
> CurrReportMonth...no issues. I need help with deriving PrevReportMonth
> because it crosses years. In this example that the CreatedMonth = 200802
> If there is a better way to do this maybe in T-SQL I am definitely open to
> that also.
> select to_number(to_char(sysdate, 'YYYYMM')) as CreatedMonth,
> to_number(to_char(sysdate, 'YYYYMM') - 1) as CurrReportMonth,
> '200712' as PrevReportMonth
> from dual
> Thnx in advance
> OS

parameters.refresh bug

It seems MS have not fixed the parameters.refresh bug yet.If I use this
code, it would throw out an exception of 'AV in module SQLOLEDB.dll'. By the
way, my SQL is 2000 + SP4 under Win2000 +SP4.
Jack ZhongJack Zhong (invalid@.email.com) writes:
> It seems MS have not fixed the parameters.refresh bug yet.If I use this
> code, it would throw out an exception of 'AV in module SQLOLEDB.dll'. By
> the way, my SQL is 2000 + SP4 under Win2000 +SP4.
Which code?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>
> Which code?
>
(in Delphi 7)
sp.Parameters.Refresh;
.....
sp.ExecProc;
sp is a stored procedure component wrapped in datamodule. There would be an
AV exception if fire sp.Parameters.Refresh; If this line is disabled, it
works perfectly well. I knew there was a Parameters.Refresh bug in previous
SQLOLEDB.dll before, but my SQL might be the latest, it is SP4 patched.
The following is snippet of my codes in Delphi 7.
function TBiz.TransData(FGuest: TGuest; FIndex: Byte): Boolean;
begin
begin
with dm.prTransGuest do
begin
try
Parameters.Refresh;
Parameters.ParamByName('@.ref').Value := Ref ;
..
Parameters.ParamByName('@.position').Value := FIndex;
Prepared:=True ;
ExecProc;
Result := True;
except
Result := False;
end;
end;
end;
end;
Jack Zhong|||Jack Zhong (invalid@.email.com) writes:
> (in Delphi 7)
> sp.Parameters.Refresh;
> .....
> sp.ExecProc;
> sp is a stored procedure component wrapped in datamodule. There would be
> an AV exception if fire sp.Parameters.Refresh; If this line is
> disabled, it works perfectly well. I knew there was a Parameters.Refresh
> bug in previous SQLOLEDB.dll before, but my SQL might be the latest, it
> is SP4 patched.
I don't know Delphi or have any access to it, so I cannot repro. But I
know we use the .Refresh method in our code (ADO + VB6), and I have not
heard about any access violation.
Besides, the .Refresh method is not in SQLOLEDB proper, but is part of
ADO. True, though, that ADO uses SQLOLEDB (or whichever provider you
use).
Is Delphi 7 a recent version of Delphi?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Merry Christmas, Erland.
I am a betrayer to VB, ^_*. Yes, there are a great many AVs in Delphi than
in VB, VB is much friendly to programmers sometimes.
Actually, the AV is somewhat strange. There are two function routines of
similar structure in the same loop to insert data into different tables, the
other function would be OK even though there is also a Parameters.Refresh.
The only diffrence is the underlying tables are slightly different. One has
primary key and the other one contains no primary keys. Inserting data into
the latter table, *without primary key*, might throw out AV if execute
Parameters.Refresh before insertion. I am not sure whether this might be the
point in which the problem lies. However, if I put a TADOStoredProcedure
component on the form and set the values in the properties inspector, then
no matter how many times I fire it, there is no AV at all.
By the way, for there are some differences between Pascal data types and SQL
server, I modified some parameters data types, for instance from
ftWideString to ftString, I got an AV in module mo15.dll if I enable
Parameters.Refresh.
Delphi 7 is an Adult in the Delphi family, Delphi 2006 is the latest one. D7
might be the latest one for Win32 enviroment.
Following is the snippet of my codes, I have added some comments to it. BTW,
the TBiz is a class to implement business rules.
procedure TBiz.ButtonApplyClick(FMbr: tstrings; FContact: TContact);
//class routine respond to ButtonApply click event in host application
var
i : Integer;
begin
if Assigned(FMbr) then
begin
for i := 0 to FMbr.Count - 1 do //repeatedly insert data into tables
with two stored procedure
begin
if TGuest(FMbr.Objects[i]).Rounds <> 0 then
begin
if ( AddNew( TGuest(FMbr.Objects[i]) ) <> 0) then
TransData(TGuest(FMbr.Objects[i]), i + 1);
end; //end if
end; //end for
case Operation of
0: New; //Operation create new.
1: Append; //Operation append
end;
end;
end;
function TBiz.TransData(FGuest: TGuest; FIndex: Byte): Boolean;
begin
begin
with dm.prTransGuest do //prTransGuest is a stored procedure in dm
(TDataMudle);
begin
try
Parameters.Refresh;
Parameters.ParamByName('@.ref').Value := Ref ;
..
Parameters.ParamByName('@.position').Value := FIndex;
Prepared;
ExecProc; //throw out AV exception on the second try, if
Parameters.Refresh enabled
Result := True;
except
Result := False;
end;
end;
end;
end;
function TBiz.AddNew(FGuest: TGuest): LongWord;
begin
with dm.prAddNew do
begin
with Parameters do
begin
try
try
Parameters.Refresh;
ParamByName('@.FirstName').Value := FGuest.FirstName;
..
Prepared;
ExecProc; //No AV even enable the above Parameters.Refresh
Result := ParamByName('@.Return_Value').Value;
except
result := 0;
end;
finally
end;
end;
end;
end;|||Jack Zhong (invalid@.email.com) writes:
> Actually, the AV is somewhat strange. There are two function routines
> of similar structure in the same loop to insert data into different
> tables, the other function would be OK even though there is also a
> Parameters.Refresh. The only diffrence is the underlying tables are
> slightly different. One has primary key and the other one contains no
> primary keys. Inserting data into the latter table, *without primary
> key*, might throw out AV if execute Parameters.Refresh before
> insertion. I am not sure whether this might be the point in which the
> problem lies. However, if I put a TADOStoredProcedure component on the
> form and set the values in the properties inspector, then no matter how
> many times I fire it, there is no AV at all.
There is one thing that I find strange in your code. You are refreshing
the parameters for each time in the loop, but you never set up any
command object. Or this is implicit in some way.
Anyway, calling .Refresh for every call to the procedure is hardly good
for performance, since that is a roundtrip to the server. Ideally, you
could call .Refresh once, and the reuse the parameter collection. Un-
fortunately, when I have tried to reuse command objects in ADO, it has
started doing things I don't like at all.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> '
news:Xns9735F04F06514Yazorman@.127.0.0.1...
> There is one thing that I find strange in your code. You are refreshing
> the parameters for each time in the loop, but you never set up any
> command object. Or this is implicit in some way.
>
This might be the difference between Delphi and VB, I am afraid. Delphi
could add parameter list in the property inspector, so it is not needed to
add parameters.refresh in the routines again. But If there were some
parameters added to the procedure and the programmer forgot to add them to
the inspector box, Delphi would throw out 'parameter not found' error.

> Anyway, calling .Refresh for every call to the procedure is hardly good
> for performance, since that is a roundtrip to the server. Ideally, you
> could call .Refresh once, and the reuse the parameter collection. Un-
> fortunately, when I have tried to reuse command objects in ADO, it has
> started doing things I don't like at all.
>
I would move Refresh command to the form.create routine, this might avoid
refreshing SP for many times.
Thanks.

> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

parameters.Add with SqlDataSource

Does anybody knows how to use parameters.add with SqlDataSource?

Hi,

in theSelecting event you can play around with the parameters of the DataSource control just before the actual call to the database is made.

Grz, Kris.

|||Could you please tell me about it, briefly? I'm not an advanced one in dotnet.|||

Hi,

please take a look at the code that's provided by Klaus in this post:http://forums.asp.net/t/1187969.aspx.

Grz, Kris.

|||

Thanks a lot. This will help me to solve the problem.

Parameters...HELP!

I am using 6 parameters in a report. If I set the Where clauses to OR,
the report will return everything (understood) but it doesnt work when
I set it to AND (how it should be). By themselves, each parameter
works. But when you combine all of them or even 2, nothing returns. Am
I missing a simple step?
Also, I wanted some parameters to be "optional"-dont have to have a
value in them (is this possible with report designer?). I've tried
setting the "optional" ones to null.
I even tried to use SQL profiler to try and view the sql being executed
but to no avail.
Any suggestions would be greatly appreciated!
Thanks!gte401:
Does the query work correct in Query Designer? Typically, I like to design
my SQL code there and then paste it into the report query after it's been
debugged. If it's working correctly then you should get the same results.
As for six parameters, I have a few with up to four with no problems.
Regarding your question about leaving parameters NULL. If you do, then
you'll definitely want to make sure you've accommodated the NULL in your
WHERE statement. You could use an ISNULL function to convert it to '%' and
do a LIKE. For example:
WHERE myField LIKE '%' + RTRIM(LTRIM(ISNULL(@.TEST,''))) + '%'
Hope this helps! I'm sure someone may have an easier way to do this, but
for now it works for me.
Patrick|||Open the report up in Visual Studio.
Go to Report => parameters.
You can specify if the value of a parameter may be NULL/Empty.
hope this helps
Regards,
Stas K.|||When you select the Null Function for say Last Name, does the query
skips this parameter or does it look for Last Names with Null?
Thanks
Kevin|||Patrick,
My SQL runs. Its just when I add the Where clause, it does not work.
This is what Business Intelligence Studio generated:
WHERE
(Person.Status LIKE @.Status) AND (PersonType.PersonType_Desc =PersonType.PersonType_Desc) AND (Methodology.Methodology_Desc IN
(@.Methodology_Desc)) AND (Certification.Certification_Desc IN
(@.Certification_Desc)) AND (Person.Last_Name = @.Last_Name) AND
(Skill.Skill_Desc IN (@.Skill_Desc)) AND (Person.First_Name =@.First_Name)
When you run it, it will prompt "Parameters for Query" I set everything
to null.
I then go to the preview tab. Enter the desired values in my search
page, and nothing appears.|||This also works:
and myfield = ISNULL(@.myparameter, myfield)
... if @.myparameter isnull then statement is myfield = myfield, which is
always true
"gte401e" wrote:
> I am using 6 parameters in a report. If I set the Where clauses to OR,
> the report will return everything (understood) but it doesnt work when
> I set it to AND (how it should be). By themselves, each parameter
> works. But when you combine all of them or even 2, nothing returns. Am
> I missing a simple step?
> Also, I wanted some parameters to be "optional"-dont have to have a
> value in them (is this possible with report designer?). I've tried
> setting the "optional" ones to null.
> I even tried to use SQL profiler to try and view the sql being executed
> but to no avail.
> Any suggestions would be greatly appreciated!
> Thanks!
>|||Dterrie,
Thanks for the help!
But one more question....I have several multi value parameters
=IN(@.Example) . I know you cant set them to Null. Is there a way to
make them optional though? Or can I incorporate =IN(@.Example) into the
statement you gave me? If so, how?
Thanks in advance!
Kevin

Parameters with ODBC server

Hi all,
I have to build a report from SQL tables thru an ODBC server. When I
try to pass (nonnamed) parameters to the report, the SQL request runs
but returns nothing, as if the value was not understood. I have put
"?" in the SQL request in place of the parameter.
Do I need to do something else ? What is the format of the capture in
the run-time parameter dialog box : are quotes needed, double quotes,
or other character ?
Thanks ODBC users !
Jean-MarcI'm not sure what you mean by non-named parameters. RS uses the @. format
for parameters in queries, like so:
... WHERE FieldName = @.ParameterName
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
news:ab591a74.0412011054.6ef96a76@.posting.google.com...
> Hi all,
> I have to build a report from SQL tables thru an ODBC server. When I
> try to pass (nonnamed) parameters to the report, the SQL request runs
> but returns nothing, as if the value was not understood. I have put
> "?" in the SQL request in place of the parameter.
> Do I need to do something else ? What is the format of the capture in
> the run-time parameter dialog box : are quotes needed, double quotes,
> or other character ?
> Thanks ODBC users !
> Jean-Marc|||Umm, no it does not. It uses that for SQL Server but otherwise it needs
unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
It sounds to me like you are trying to put in the values in the dialog box
that pops up with you hit the exclamation point. When prompted by the dialog
box do not put in any quotes (single or double) just put the value in.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
> I'm not sure what you mean by non-named parameters. RS uses the @. format
> for parameters in queries, like so:
> ... WHERE FieldName = @.ParameterName
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
> > Hi all,
> >
> > I have to build a report from SQL tables thru an ODBC server. When I
> > try to pass (nonnamed) parameters to the report, the SQL request runs
> > but returns nothing, as if the value was not understood. I have put
> > "?" in the SQL request in place of the parameter.
> > Do I need to do something else ? What is the format of the capture in
> > the run-time parameter dialog box : are quotes needed, double quotes,
> > or other character ?
> >
> > Thanks ODBC users !
> >
> > Jean-Marc
>|||Thanks for the correction, Bruce.
Rats! Now I've got more work to do when we port to Oracle.
...sigh...
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OJf0gI%231EHA.3000@.TK2MSFTNGP15.phx.gbl...
> Umm, no it does not. It uses that for SQL Server but otherwise it needs
> unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
> It sounds to me like you are trying to put in the values in the dialog box
> that pops up with you hit the exclamation point. When prompted by the
> dialog
> box do not put in any quotes (single or double) just put the value in.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
>> I'm not sure what you mean by non-named parameters. RS uses the @. format
>> for parameters in queries, like so:
>> ... WHERE FieldName = @.ParameterName
>> --
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
>> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
>> > Hi all,
>> >
>> > I have to build a report from SQL tables thru an ODBC server. When I
>> > try to pass (nonnamed) parameters to the report, the SQL request runs
>> > but returns nothing, as if the value was not understood. I have put
>> > "?" in the SQL request in place of the parameter.
>> > Do I need to do something else ? What is the format of the capture in
>> > the run-time parameter dialog box : are quotes needed, double quotes,
>> > or other character ?
>> >
>> > Thanks ODBC users !
>> >
>> > Jean-Marc
>>
>|||I'm not sure about Oracle. Oracle might be the same and it might not. The
reason is that Oracle is a special case. Here is a tidbit for you. Only
Oracle and SQL Server use the dotnet managed provider in RS. But, wait, it
is not that simple. When you use the GUI query designer it uses the OLEDB
provider because the GUI query designer knows nothing about dotnet. But, if
you go to the generic designer it uses the managed provider. At runtime it
uses the managed provider. The reason I mention this is two fold. First, so
all testing is done with the same provider as used at runtime I would
recommend always going to the generic query designer. Second, I'm not sure
whether managed provider uses named parameters or not. If it does then you
will not have to change this.
One last point, the next version should have a query designer that knows
about dotnet (V2, not SP2).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:%233X2QO%231EHA.1264@.TK2MSFTNGP12.phx.gbl...
> Thanks for the correction, Bruce.
> Rats! Now I've got more work to do when we port to Oracle.
> ...sigh...
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OJf0gI%231EHA.3000@.TK2MSFTNGP15.phx.gbl...
> > Umm, no it does not. It uses that for SQL Server but otherwise it needs
> > unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
> >
> > It sounds to me like you are trying to put in the values in the dialog
box
> > that pops up with you hit the exclamation point. When prompted by the
> > dialog
> > box do not put in any quotes (single or double) just put the value in.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
> >> I'm not sure what you mean by non-named parameters. RS uses the @.
format
> >> for parameters in queries, like so:
> >>
> >> ... WHERE FieldName = @.ParameterName
> >>
> >> --
> >> '(' Jeff A. Stucker
> >> \
> >>
> >> Business Intelligence
> >> www.criadvantage.com
> >> ---
> >> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
> >> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
> >> > Hi all,
> >> >
> >> > I have to build a report from SQL tables thru an ODBC server. When I
> >> > try to pass (nonnamed) parameters to the report, the SQL request runs
> >> > but returns nothing, as if the value was not understood. I have put
> >> > "?" in the SQL request in place of the parameter.
> >> > Do I need to do something else ? What is the format of the capture in
> >> > the run-time parameter dialog box : are quotes needed, double quotes,
> >> > or other character ?
> >> >
> >> > Thanks ODBC users !
> >> >
> >> > Jean-Marc
> >>
> >>
> >
> >
>|||The syntax used by the managed Oracle provider to mark named parameters is a
colon (":") instead of @.. This is nothing specific to RS, this is just how
the data provider works.
E.g., select * from emp where deptno = :Dept
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eMlzgX%231EHA.2676@.TK2MSFTNGP12.phx.gbl...
> I'm not sure about Oracle. Oracle might be the same and it might not. The
> reason is that Oracle is a special case. Here is a tidbit for you. Only
> Oracle and SQL Server use the dotnet managed provider in RS. But, wait, it
> is not that simple. When you use the GUI query designer it uses the OLEDB
> provider because the GUI query designer knows nothing about dotnet. But,
if
> you go to the generic designer it uses the managed provider. At runtime
it
> uses the managed provider. The reason I mention this is two fold. First,
so
> all testing is done with the same provider as used at runtime I would
> recommend always going to the generic query designer. Second, I'm not sure
> whether managed provider uses named parameters or not. If it does then you
> will not have to change this.
> One last point, the next version should have a query designer that knows
> about dotnet (V2, not SP2).
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:%233X2QO%231EHA.1264@.TK2MSFTNGP12.phx.gbl...
> > Thanks for the correction, Bruce.
> >
> > Rats! Now I've got more work to do when we port to Oracle.
> >
> > ...sigh...
> > --
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:OJf0gI%231EHA.3000@.TK2MSFTNGP15.phx.gbl...
> > > Umm, no it does not. It uses that for SQL Server but otherwise it
needs
> > > unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
> > >
> > > It sounds to me like you are trying to put in the values in the dialog
> box
> > > that pops up with you hit the exclamation point. When prompted by the
> > > dialog
> > > box do not put in any quotes (single or double) just put the value in.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > >
> > > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > > news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
> > >> I'm not sure what you mean by non-named parameters. RS uses the @.
> format
> > >> for parameters in queries, like so:
> > >>
> > >> ... WHERE FieldName = @.ParameterName
> > >>
> > >> --
> > >> '(' Jeff A. Stucker
> > >> \
> > >>
> > >> Business Intelligence
> > >> www.criadvantage.com
> > >> ---
> > >> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
> > >> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
> > >> > Hi all,
> > >> >
> > >> > I have to build a report from SQL tables thru an ODBC server. When
I
> > >> > try to pass (nonnamed) parameters to the report, the SQL request
runs
> > >> > but returns nothing, as if the value was not understood. I have put
> > >> > "?" in the SQL request in place of the parameter.
> > >> > Do I need to do something else ? What is the format of the capture
in
> > >> > the run-time parameter dialog box : are quotes needed, double
quotes,
> > >> > or other character ?
> > >> >
> > >> > Thanks ODBC users !
> > >> >
> > >> > Jean-Marc
> > >>
> > >>
> > >
> > >
> >
> >
>|||So the big question is: What's the best way to manage reports that need to
work against both SQL Server and Oracle databases, depending where they are
deployed? (The database structure will be identical, but the platform is
customer-specific.)
Thanks,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eiNx1TC2EHA.2012@.TK2MSFTNGP15.phx.gbl...
> The syntax used by the managed Oracle provider to mark named parameters is
> a
> colon (":") instead of @.. This is nothing specific to RS, this is just how
> the data provider works.
> E.g., select * from emp where deptno = :Dept
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:eMlzgX%231EHA.2676@.TK2MSFTNGP12.phx.gbl...
>> I'm not sure about Oracle. Oracle might be the same and it might not. The
>> reason is that Oracle is a special case. Here is a tidbit for you. Only
>> Oracle and SQL Server use the dotnet managed provider in RS. But, wait,
>> it
>> is not that simple. When you use the GUI query designer it uses the OLEDB
>> provider because the GUI query designer knows nothing about dotnet. But,
> if
>> you go to the generic designer it uses the managed provider. At runtime
> it
>> uses the managed provider. The reason I mention this is two fold. First,
> so
>> all testing is done with the same provider as used at runtime I would
>> recommend always going to the generic query designer. Second, I'm not
>> sure
>> whether managed provider uses named parameters or not. If it does then
>> you
>> will not have to change this.
>> One last point, the next version should have a query designer that knows
>> about dotnet (V2, not SP2).
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
>> news:%233X2QO%231EHA.1264@.TK2MSFTNGP12.phx.gbl...
>> > Thanks for the correction, Bruce.
>> >
>> > Rats! Now I've got more work to do when we port to Oracle.
>> >
>> > ...sigh...
>> > --
>> > '(' Jeff A. Stucker
>> > \
>> >
>> > Business Intelligence
>> > www.criadvantage.com
>> > ---
>> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> > news:OJf0gI%231EHA.3000@.TK2MSFTNGP15.phx.gbl...
>> > > Umm, no it does not. It uses that for SQL Server but otherwise it
> needs
>> > > unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
>> > >
>> > > It sounds to me like you are trying to put in the values in the
>> > > dialog
>> box
>> > > that pops up with you hit the exclamation point. When prompted by the
>> > > dialog
>> > > box do not put in any quotes (single or double) just put the value
>> > > in.
>> > >
>> > > --
>> > > Bruce Loehle-Conger
>> > > MVP SQL Server Reporting Services
>> > >
>> > >
>> > > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
>> > > news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
>> > >> I'm not sure what you mean by non-named parameters. RS uses the @.
>> format
>> > >> for parameters in queries, like so:
>> > >>
>> > >> ... WHERE FieldName = @.ParameterName
>> > >>
>> > >> --
>> > >> '(' Jeff A. Stucker
>> > >> \
>> > >>
>> > >> Business Intelligence
>> > >> www.criadvantage.com
>> > >> ---
>> > >> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
>> > >> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
>> > >> > Hi all,
>> > >> >
>> > >> > I have to build a report from SQL tables thru an ODBC server. When
> I
>> > >> > try to pass (nonnamed) parameters to the report, the SQL request
> runs
>> > >> > but returns nothing, as if the value was not understood. I have
>> > >> > put
>> > >> > "?" in the SQL request in place of the parameter.
>> > >> > Do I need to do something else ? What is the format of the capture
> in
>> > >> > the run-time parameter dialog box : are quotes needed, double
> quotes,
>> > >> > or other character ?
>> > >> >
>> > >> > Thanks ODBC users !
>> > >> >
>> > >> > Jean-Marc
>> > >>
>> > >>
>> > >
>> > >
>> >
>> >
>>
>|||Hi Jeff,
Did you get any answers to your big question? I find myself in the exact
same situation (except its either Sql Server or Sybase)..I was just curious
what you did to solve this problem...
Thanks...
--Aparna.
"Jeff A. Stucker" wrote:
> So the big question is: What's the best way to manage reports that need to
> work against both SQL Server and Oracle databases, depending where they are
> deployed? (The database structure will be identical, but the platform is
> customer-specific.)
> Thanks,
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:eiNx1TC2EHA.2012@.TK2MSFTNGP15.phx.gbl...
> > The syntax used by the managed Oracle provider to mark named parameters is
> > a
> > colon (":") instead of @.. This is nothing specific to RS, this is just how
> > the data provider works.
> > E.g., select * from emp where deptno = :Dept
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:eMlzgX%231EHA.2676@.TK2MSFTNGP12.phx.gbl...
> >> I'm not sure about Oracle. Oracle might be the same and it might not. The
> >> reason is that Oracle is a special case. Here is a tidbit for you. Only
> >> Oracle and SQL Server use the dotnet managed provider in RS. But, wait,
> >> it
> >> is not that simple. When you use the GUI query designer it uses the OLEDB
> >> provider because the GUI query designer knows nothing about dotnet. But,
> > if
> >> you go to the generic designer it uses the managed provider. At runtime
> > it
> >> uses the managed provider. The reason I mention this is two fold. First,
> > so
> >> all testing is done with the same provider as used at runtime I would
> >> recommend always going to the generic query designer. Second, I'm not
> >> sure
> >> whether managed provider uses named parameters or not. If it does then
> >> you
> >> will not have to change this.
> >>
> >> One last point, the next version should have a query designer that knows
> >> about dotnet (V2, not SP2).
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> >> news:%233X2QO%231EHA.1264@.TK2MSFTNGP12.phx.gbl...
> >> > Thanks for the correction, Bruce.
> >> >
> >> > Rats! Now I've got more work to do when we port to Oracle.
> >> >
> >> > ...sigh...
> >> > --
> >> > '(' Jeff A. Stucker
> >> > \
> >> >
> >> > Business Intelligence
> >> > www.criadvantage.com
> >> > ---
> >> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> >> > news:OJf0gI%231EHA.3000@.TK2MSFTNGP15.phx.gbl...
> >> > > Umm, no it does not. It uses that for SQL Server but otherwise it
> > needs
> >> > > unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
> >> > >
> >> > > It sounds to me like you are trying to put in the values in the
> >> > > dialog
> >> box
> >> > > that pops up with you hit the exclamation point. When prompted by the
> >> > > dialog
> >> > > box do not put in any quotes (single or double) just put the value
> >> > > in.
> >> > >
> >> > > --
> >> > > Bruce Loehle-Conger
> >> > > MVP SQL Server Reporting Services
> >> > >
> >> > >
> >> > > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> >> > > news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
> >> > >> I'm not sure what you mean by non-named parameters. RS uses the @.
> >> format
> >> > >> for parameters in queries, like so:
> >> > >>
> >> > >> ... WHERE FieldName = @.ParameterName
> >> > >>
> >> > >> --
> >> > >> '(' Jeff A. Stucker
> >> > >> \
> >> > >>
> >> > >> Business Intelligence
> >> > >> www.criadvantage.com
> >> > >> ---
> >> > >> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
> >> > >> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
> >> > >> > Hi all,
> >> > >> >
> >> > >> > I have to build a report from SQL tables thru an ODBC server. When
> > I
> >> > >> > try to pass (nonnamed) parameters to the report, the SQL request
> > runs
> >> > >> > but returns nothing, as if the value was not understood. I have
> >> > >> > put
> >> > >> > "?" in the SQL request in place of the parameter.
> >> > >> > Do I need to do something else ? What is the format of the capture
> > in
> >> > >> > the run-time parameter dialog box : are quotes needed, double
> > quotes,
> >> > >> > or other character ?
> >> > >> >
> >> > >> > Thanks ODBC users !
> >> > >> >
> >> > >> > Jean-Marc
> >> > >>
> >> > >>
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
>
>|||I have the same issue. I have a whole lot of reports that are going against
a process control database in Sybase and will be switched to go against a
datamart in SQL Server. I would love to have an easy way to switch it back
if I needed to. Putting aside having compatible SQL (Sybase does not have
Top and some other SQL) I don't see an easy way to do this. I plan on
manually changing the reports by going into the RDL and changing it (rather
than doing it from the IDE). The only way I can see this would work is if
you make your dataset sql be based on an expression. You can use the generic
query screen and do this:
= "Select * from something where somefield = " & Parameters!ParamName.value
But if you have a date or character field you have to do this:
= "Select * from something where somefield = '" & Parameters!ParamName.value
& "'" (that is a double quote, a single quote, a double quote)
So it is slow and potentially buggy. When done though if the SQL created is
compatible this will work against any databases.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aparna" <Aparna@.discussions.microsoft.com> wrote in message
news:C4540199-F9D9-4D37-A491-BC5EE4AEE91A@.microsoft.com...
> Hi Jeff,
> Did you get any answers to your big question? I find myself in the exact
> same situation (except its either Sql Server or Sybase)..I was just
curious
> what you did to solve this problem...
> Thanks...
> --Aparna.
> "Jeff A. Stucker" wrote:
> > So the big question is: What's the best way to manage reports that need
to
> > work against both SQL Server and Oracle databases, depending where they
are
> > deployed? (The database structure will be identical, but the platform
is
> > customer-specific.)
> >
> > Thanks,
> >
> > --
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> > news:eiNx1TC2EHA.2012@.TK2MSFTNGP15.phx.gbl...
> > > The syntax used by the managed Oracle provider to mark named
parameters is
> > > a
> > > colon (":") instead of @.. This is nothing specific to RS, this is just
how
> > > the data provider works.
> > > E.g., select * from emp where deptno = :Dept
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > >
> > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > > news:eMlzgX%231EHA.2676@.TK2MSFTNGP12.phx.gbl...
> > >> I'm not sure about Oracle. Oracle might be the same and it might not.
The
> > >> reason is that Oracle is a special case. Here is a tidbit for you.
Only
> > >> Oracle and SQL Server use the dotnet managed provider in RS. But,
wait,
> > >> it
> > >> is not that simple. When you use the GUI query designer it uses the
OLEDB
> > >> provider because the GUI query designer knows nothing about dotnet.
But,
> > > if
> > >> you go to the generic designer it uses the managed provider. At
runtime
> > > it
> > >> uses the managed provider. The reason I mention this is two fold.
First,
> > > so
> > >> all testing is done with the same provider as used at runtime I would
> > >> recommend always going to the generic query designer. Second, I'm not
> > >> sure
> > >> whether managed provider uses named parameters or not. If it does
then
> > >> you
> > >> will not have to change this.
> > >>
> > >> One last point, the next version should have a query designer that
knows
> > >> about dotnet (V2, not SP2).
> > >>
> > >> --
> > >> Bruce Loehle-Conger
> > >> MVP SQL Server Reporting Services
> > >>
> > >>
> > >> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > >> news:%233X2QO%231EHA.1264@.TK2MSFTNGP12.phx.gbl...
> > >> > Thanks for the correction, Bruce.
> > >> >
> > >> > Rats! Now I've got more work to do when we port to Oracle.
> > >> >
> > >> > ...sigh...
> > >> > --
> > >> > '(' Jeff A. Stucker
> > >> > \
> > >> >
> > >> > Business Intelligence
> > >> > www.criadvantage.com
> > >> > ---
> > >> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > >> > news:OJf0gI%231EHA.3000@.TK2MSFTNGP15.phx.gbl...
> > >> > > Umm, no it does not. It uses that for SQL Server but otherwise it
> > > needs
> > >> > > unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
> > >> > >
> > >> > > It sounds to me like you are trying to put in the values in the
> > >> > > dialog
> > >> box
> > >> > > that pops up with you hit the exclamation point. When prompted by
the
> > >> > > dialog
> > >> > > box do not put in any quotes (single or double) just put the
value
> > >> > > in.
> > >> > >
> > >> > > --
> > >> > > Bruce Loehle-Conger
> > >> > > MVP SQL Server Reporting Services
> > >> > >
> > >> > >
> > >> > > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > >> > > news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
> > >> > >> I'm not sure what you mean by non-named parameters. RS uses the
@.
> > >> format
> > >> > >> for parameters in queries, like so:
> > >> > >>
> > >> > >> ... WHERE FieldName = @.ParameterName
> > >> > >>
> > >> > >> --
> > >> > >> '(' Jeff A. Stucker
> > >> > >> \
> > >> > >>
> > >> > >> Business Intelligence
> > >> > >> www.criadvantage.com
> > >> > >> ---
> > >> > >> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
> > >> > >> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
> > >> > >> > Hi all,
> > >> > >> >
> > >> > >> > I have to build a report from SQL tables thru an ODBC server.
When
> > > I
> > >> > >> > try to pass (nonnamed) parameters to the report, the SQL
request
> > > runs
> > >> > >> > but returns nothing, as if the value was not understood. I
have
> > >> > >> > put
> > >> > >> > "?" in the SQL request in place of the parameter.
> > >> > >> > Do I need to do something else ? What is the format of the
capture
> > > in
> > >> > >> > the run-time parameter dialog box : are quotes needed, double
> > > quotes,
> > >> > >> > or other character ?
> > >> > >> >
> > >> > >> > Thanks ODBC users !
> > >> > >> >
> > >> > >> > Jean-Marc
> > >> > >>
> > >> > >>
> > >> > >
> > >> > >
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
> >|||Hey Bruce, Thanks so much! You know, I never even thought of using
expressions within the Query Designer... You are right that this approach is
not very elegant, but its good to know that there is a last resort...I think
I am going to investigate using a custom data extension to just read a
dataset produced by an external proram....and see if that can help in
anyway...
But once again, thanks for the tip...
"Bruce L-C [MVP]" wrote:
> I have the same issue. I have a whole lot of reports that are going against
> a process control database in Sybase and will be switched to go against a
> datamart in SQL Server. I would love to have an easy way to switch it back
> if I needed to. Putting aside having compatible SQL (Sybase does not have
> Top and some other SQL) I don't see an easy way to do this. I plan on
> manually changing the reports by going into the RDL and changing it (rather
> than doing it from the IDE). The only way I can see this would work is if
> you make your dataset sql be based on an expression. You can use the generic
> query screen and do this:
> = "Select * from something where somefield = " & Parameters!ParamName.value
> But if you have a date or character field you have to do this:
> = "Select * from something where somefield = '" & Parameters!ParamName.value
> & "'" (that is a double quote, a single quote, a double quote)
> So it is slow and potentially buggy. When done though if the SQL created is
> compatible this will work against any databases.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
> news:C4540199-F9D9-4D37-A491-BC5EE4AEE91A@.microsoft.com...
> > Hi Jeff,
> > Did you get any answers to your big question? I find myself in the exact
> > same situation (except its either Sql Server or Sybase)..I was just
> curious
> > what you did to solve this problem...
> > Thanks...
> > --Aparna.
> >
> > "Jeff A. Stucker" wrote:
> >
> > > So the big question is: What's the best way to manage reports that need
> to
> > > work against both SQL Server and Oracle databases, depending where they
> are
> > > deployed? (The database structure will be identical, but the platform
> is
> > > customer-specific.)
> > >
> > > Thanks,
> > >
> > > --
> > > '(' Jeff A. Stucker
> > > \
> > >
> > > Business Intelligence
> > > www.criadvantage.com
> > > ---
> > > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> > > news:eiNx1TC2EHA.2012@.TK2MSFTNGP15.phx.gbl...
> > > > The syntax used by the managed Oracle provider to mark named
> parameters is
> > > > a
> > > > colon (":") instead of @.. This is nothing specific to RS, this is just
> how
> > > > the data provider works.
> > > > E.g., select * from emp where deptno = :Dept
> > > >
> > > > --
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > > > rights.
> > > >
> > > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > > > news:eMlzgX%231EHA.2676@.TK2MSFTNGP12.phx.gbl...
> > > >> I'm not sure about Oracle. Oracle might be the same and it might not.
> The
> > > >> reason is that Oracle is a special case. Here is a tidbit for you.
> Only
> > > >> Oracle and SQL Server use the dotnet managed provider in RS. But,
> wait,
> > > >> it
> > > >> is not that simple. When you use the GUI query designer it uses the
> OLEDB
> > > >> provider because the GUI query designer knows nothing about dotnet.
> But,
> > > > if
> > > >> you go to the generic designer it uses the managed provider. At
> runtime
> > > > it
> > > >> uses the managed provider. The reason I mention this is two fold.
> First,
> > > > so
> > > >> all testing is done with the same provider as used at runtime I would
> > > >> recommend always going to the generic query designer. Second, I'm not
> > > >> sure
> > > >> whether managed provider uses named parameters or not. If it does
> then
> > > >> you
> > > >> will not have to change this.
> > > >>
> > > >> One last point, the next version should have a query designer that
> knows
> > > >> about dotnet (V2, not SP2).
> > > >>
> > > >> --
> > > >> Bruce Loehle-Conger
> > > >> MVP SQL Server Reporting Services
> > > >>
> > > >>
> > > >> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > > >> news:%233X2QO%231EHA.1264@.TK2MSFTNGP12.phx.gbl...
> > > >> > Thanks for the correction, Bruce.
> > > >> >
> > > >> > Rats! Now I've got more work to do when we port to Oracle.
> > > >> >
> > > >> > ...sigh...
> > > >> > --
> > > >> > '(' Jeff A. Stucker
> > > >> > \
> > > >> >
> > > >> > Business Intelligence
> > > >> > www.criadvantage.com
> > > >> > ---
> > > >> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > > >> > news:OJf0gI%231EHA.3000@.TK2MSFTNGP15.phx.gbl...
> > > >> > > Umm, no it does not. It uses that for SQL Server but otherwise it
> > > > needs
> > > >> > > unnamed (i.e. ?) for both OLEDB and OLDBC data sources.
> > > >> > >
> > > >> > > It sounds to me like you are trying to put in the values in the
> > > >> > > dialog
> > > >> box
> > > >> > > that pops up with you hit the exclamation point. When prompted by
> the
> > > >> > > dialog
> > > >> > > box do not put in any quotes (single or double) just put the
> value
> > > >> > > in.
> > > >> > >
> > > >> > > --
> > > >> > > Bruce Loehle-Conger
> > > >> > > MVP SQL Server Reporting Services
> > > >> > >
> > > >> > >
> > > >> > > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > > >> > > news:OjXCNA%231EHA.3336@.TK2MSFTNGP11.phx.gbl...
> > > >> > >> I'm not sure what you mean by non-named parameters. RS uses the
> @.
> > > >> format
> > > >> > >> for parameters in queries, like so:
> > > >> > >>
> > > >> > >> ... WHERE FieldName = @.ParameterName
> > > >> > >>
> > > >> > >> --
> > > >> > >> '(' Jeff A. Stucker
> > > >> > >> \
> > > >> > >>
> > > >> > >> Business Intelligence
> > > >> > >> www.criadvantage.com
> > > >> > >> ---
> > > >> > >> "Jean-Marc Audrin" <jean-marc.audrin@.future.ca> wrote in message
> > > >> > >> news:ab591a74.0412011054.6ef96a76@.posting.google.com...
> > > >> > >> > Hi all,
> > > >> > >> >
> > > >> > >> > I have to build a report from SQL tables thru an ODBC server.
> When
> > > > I
> > > >> > >> > try to pass (nonnamed) parameters to the report, the SQL
> request
> > > > runs
> > > >> > >> > but returns nothing, as if the value was not understood. I
> have
> > > >> > >> > put
> > > >> > >> > "?" in the SQL request in place of the parameter.
> > > >> > >> > Do I need to do something else ? What is the format of the
> capture
> > > > in
> > > >> > >> > the run-time parameter dialog box : are quotes needed, double
> > > > quotes,
> > > >> > >> > or other character ?
> > > >> > >> >
> > > >> > >> > Thanks ODBC users !
> > > >> > >> >
> > > >> > >> > Jean-Marc
> > > >> > >>
> > > >> > >>
> > > >> > >
> > > >> > >
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >
> > > >
> > >
> > >
> > >
>
>

Parameters with no prompt

Can I have a parameter that wouldn't have a text for the prompt?
Thanksno
but you could use a colon or a period as a prompt - next best thing :)
"Mark Goldin" wrote:
> Can I have a parameter that wouldn't have a text for the prompt?
> Thanks
>
>

parameters with multi value and navigation

Hello.

I have a report with parameters include multiple value.

I configure "Jamp To Report" under navigation and I would like to forward to a new reports the same parameters user select in the source report undel multiple value parameters.

in the text box I use =join(Parameters!Ds.Label ,", ") but how can I forward the selected parameters to the new report

Thanks

Idan

Please read this related posting: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=163803&SiteID=1

-- Robert

parameters with multi value and navigation

Hello.
I have a report with parameters include multiple value.
I configure "Jamp To Report" under navigation and I would like to
forward to a new reports the same parameters user select in the source
report undel multiple value parameters.
in the text box I use =join(Parameters!Ds.Label ,", ") but how can I
forward the selected parameters to the new report
Thanks
IdanHi,
The parameter used in both the query should be same.
Amarnath
"Idan" wrote:
> Hello.
> I have a report with parameters include multiple value.
> I configure "Jamp To Report" under navigation and I would like to
> forward to a new reports the same parameters user select in the source
> report undel multiple value parameters.
>
> in the text box I use =join(Parameters!Ds.Label ,", ") but how can I
> forward the selected parameters to the new report
> Thanks
> Idan
>

Parameters with LIKE statement

I have a sql that I want to execute with LIKE and parameters:
I tried several options outlined that I found athttp://aspnet101.com/aspnet101/tutorials.aspx?id=10%20
but they all seem to return 0 records. When I try and execute my statement in Enterprise manager, it works fine.
Code Snippet:
...
Dim sql as string
sql = "SELECT * FROM tblName WHERE First LIKE '%' + @.fname + '%' AND Last LIKE '%' + @.lname + '%'"
Dim param(1) as sqlParameter
sqlParams(0) =New SqlParameter("@.fname", SqlDbType.VarChar, 50)
sqlParams(0).Value = Trim(fname.text)
sqlParams(1) =New SqlParameter("@.lname", SqlDbType.VarChar, 50)
sqlParams(1).Value = Trim(lname.text)
...
Can anyone tell me if there is anything wrong with my code above?Do you call Command.Parameters.Add after you set the values?
If not you must add the parameters to the Command after their values are set for example::

sqlParams(0) =New SqlParameter("@.fname", SqlDbType.VarChar, 50)
Command.Parameters.Add(sqlParams(0))

Sam

|||

Yes. I actually pass my parameter collection to a class that adds it to the command. It's a bit more complicated and so I don't want to post that part. I'm just wondering if the code section that I had posted was correct.
All my sqls work fine with parameters except when I use the LIKE clause.
Thanks.

|||Your SQL statement is fine.
Check out the way you pass parameters to ur Statement
regards|||Alternatively you could also do like this :
Dim sql as string
sql = "SELECT * FROM tblName WHERE First LIKE @.fname AND Last LIKE @.lname "
when you define the parameters and pass the value you could say:
sqlParams(0).Value = "%" & Trim(fname.text) & "%"
|||Use SQL Profile, and make sure "statement starting" is on, and see what SQL is actually being sent...also, what error are you seeing?|||

I don't see any error messages. It just returns 0 records when it should be return all records.

I simplified my code:

Dim strSQLTextAsString
strSQLText = "SELECT * FROM tbl WHERE Name LIKE @.name AND Email LIKE @.email"

Dim _conStringAsString
_conString = configurationSettings.appSettings("conString")

Dim _conSQLAs SqlConnection

Dim _SQLCommandAs SqlCommand

Try

_conSQL=New SqlConnection(_conString)
_SQLCommand =New SqlCommand
_SQLCommand.CommandText = strSQLText
_SQLCommand.Connection = _conSQL
_SQLCommand.Parameters.Add("@.name",String.Format("%{0}%", name.text))
_SQLCommand.Parameters.Add("@.email",String.Format("%{0}%", email.text))
_conSQL.Open()

Dim Data =New DataSet
Dim dataAdapter =New SqlDataAdapter(_SQLCommand)
dataAdapter.Fill(Data)

Dim dtAsNew DataTable
dt = Data.Tables(0)

Finally

_conSQL.Dispose()
_SQLCommand.Dispose()

EndTry

--
Can anyone tell me what's wrong with the code?
Thanks!!
|||Its a good idea to add the size of each of the parameters.|||

I figured out what was not working. I had created a sqlParameter array and was passing that to my data access funtion... (code in original posting). That I think was screwing up the %.

Anyhow, found a work around and is working fine now

Thanks much for everyone's pointers.

Parameters with Crystal Reports.

I have set up some (three to be exact) parameters in a report that I want to
be able to print. Before I set the parameters everything worked just fine. I
set the datasource and the printout worked great. Now I add parameters to the
report and it seems that no matter what I do, I get an exception indicating
that I have not set the parameters. Here is the latest try (this is Winform
code):
Dim rpt As New QuoteDetail
Dim disVal As New CrystalDecisions.Shared.ParameterDiscreteValue()
disVal.Value = "Net 30"
rpt.Parameter_Terms.CurrentValues.Add(disVal)
disVal = New CrystalDecisions.Shared.ParameterDiscreteValue()
disVal.Value = q.FileName
rpt.Parameter_QuoteFile.CurrentValues.Add(disVal)
disVal = New CrystalDecisions.Shared.ParameterDiscreteValue()
disVal.Value = "Me"
rpt.Parameter_PreparerName.CurrentValues.Add(disVal)
rpt.SetDataSource(ds)
rpt.PrintToPrinter(1, False, 0, 0)
rpt.Dispose()
One interesting thing that I learned from several sessions in the debugger.
Before the SetDataSource there are current values for each of the parameters.
After the call to SetDataSource it seems that the current values go away.
I would like to just be able to set values for these parameters and print,
like a function call. Any suggestions on what I am doing wrong?
Thank you.
KevinThis forum is for Reporting Services, the reporting product from MS that
competes with Crystal. I know Crystal is still bundled with VS. I would
suggest a Visual Studio newsgroup. Not really sure where the best place is
for you to go for an answer.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:F3EA2C93-0DB0-4CB4-9A65-815EBAD105C4@.microsoft.com...
>I have set up some (three to be exact) parameters in a report that I want
>to
> be able to print. Before I set the parameters everything worked just fine.
> I
> set the datasource and the printout worked great. Now I add parameters to
> the
> report and it seems that no matter what I do, I get an exception
> indicating
> that I have not set the parameters. Here is the latest try (this is
> Winform
> code):
> Dim rpt As New QuoteDetail
> Dim disVal As New
> CrystalDecisions.Shared.ParameterDiscreteValue()
> disVal.Value = "Net 30"
> rpt.Parameter_Terms.CurrentValues.Add(disVal)
> disVal = New CrystalDecisions.Shared.ParameterDiscreteValue()
> disVal.Value = q.FileName
> rpt.Parameter_QuoteFile.CurrentValues.Add(disVal)
> disVal = New CrystalDecisions.Shared.ParameterDiscreteValue()
> disVal.Value = "Me"
> rpt.Parameter_PreparerName.CurrentValues.Add(disVal)
> rpt.SetDataSource(ds)
> rpt.PrintToPrinter(1, False, 0, 0)
> rpt.Dispose()
> One interesting thing that I learned from several sessions in the
> debugger.
> Before the SetDataSource there are current values for each of the
> parameters.
> After the call to SetDataSource it seems that the current values go away.
> I would like to just be able to set values for these parameters and print,
> like a function call. Any suggestions on what I am doing wrong?
> Thank you.
>
> Kevin

Parameters with all values as default

Hi all.
Have a report with 2 parameters StartDate and SessionName. Both are non-query
based. So, the user runs the report and has to enter values in both fields in
order to run the report. I would like to have by default to have the report
run all values like as in (select * from etc) and then if user decides to
hone in on any StartDate/Session then they can used the parameter fields on
the report and then click view report.
Thanks
James
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1You'll need to allow Null values for each parameter. There's an example on
www.msbicentral.com. If you login and go to Downloads, Reporting Services,
RDL files, the example is Matrix.Param.RDL. There are other useful examples
there, also.
"James Woo via SQLMonster.com" wrote:
> Hi all.
> Have a report with 2 parameters StartDate and SessionName. Both are non-query
> based. So, the user runs the report and has to enter values in both fields in
> order to run the report. I would like to have by default to have the report
> run all values like as in (select * from etc) and then if user decides to
> hone in on any StartDate/Session then they can used the parameter fields on
> the report and then click view report.
> Thanks
> James
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1
>|||COOL SITE.. Thanks. I will have to spend hours there.
daw wrote:
>You'll need to allow Null values for each parameter. There's an example on
>www.msbicentral.com. If you login and go to Downloads, Reporting Services,
>RDL files, the example is Matrix.Param.RDL. There are other useful examples
>there, also.
>> Hi all.
>[quoted text clipped - 7 lines]
>> Thanks
>> James
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1|||Easiest way to do this...
Create a new dataset for your Parameter:
e.g
SELECT Product,NAME
FROM Products
UNION
SELECT '***' AS Product,'All Managers' AS Name
This creates a *** as the ALL for products...
Then in your main report dataset...
Make sure you are using the generic query designer (button above the query)
Then as your statement:
WHERE ( Product = @.ProductParam or @.ProductParam='***')
Good luck
"James Woo via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:52B3D2CE3710C@.SQLMonster.com...
> COOL SITE.. Thanks. I will have to spend hours there.
> daw wrote:
> >You'll need to allow Null values for each parameter. There's an example
on
> >www.msbicentral.com. If you login and go to Downloads, Reporting
Services,
> >RDL files, the example is Matrix.Param.RDL. There are other useful
examples
> >there, also.
> >
> >> Hi all.
> >>
> >[quoted text clipped - 7 lines]
> >> Thanks
> >> James
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1

Parameters with a union statement

I have a report that is using a union statement to pull in data from two identical tables except that one is for current month, the other for archived data.

What I want to do is prompt the user once for a date and use the value to select from the right table. Since a sales date can only exist in one of the tables, one union will work, the other not.

But the report in prompting me for a parameter for each query....which is in Informix and the prompt is this: "?"

Is there anyway to force both halves of the query to see this as one parameter so the user is only prompted once?

Thanks

Have you tried to use this sql structure

="select * from table_1 where Sale_date = '" & format(Parameters!date,"MM-dd-yyyy") & "'"
union
select * from table_2 where Sale_date = '" & format(Parameters!date,"MM-dd-yyyy") & "'"

|||

Can I do this in the data set?

Thanks

Parameters will not Default

Greetings

I am using SQL 2005 Reporting Services and I have a problem with parameters. If I leave 3 of the 4 values as non queried in the Available values section and mark them as a Default Values: of NULL, then the report works fine. However if I set the Available values to a query I have written and leave the Default values as NULL, when I view the report in either preview mode or on the reports server, it wants me to specify a value from the boxes and the tick box for NULL has disappeared. Allow NULL value is ticked inthe first section, Properties.

Anybody got any suggestions?

If you have available values, then the allowable values of the parameter are limited to those returned by your query. If you want to allow the parameter to be null, then null must be one of the available values returned by your query.

-Albert