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
>
Showing posts with label runs. Show all posts
Showing posts with label runs. Show all posts
Tuesday, March 20, 2012
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
> > > >> > >>
> > > >> > >>
> > > >> > >
> > > >> > >
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >
> > > >
> > >
> > >
> > >
>
>
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
> > > >> > >>
> > > >> > >>
> > > >> > >
> > > >> > >
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >
> > > >
> > >
> > >
> > >
>
>
Monday, March 12, 2012
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
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 parmaeters...
Hi all,
did a search on parameters and could not find the answer to my ask, so here
goes..
I have a report that runs on a SP. Originally, the SP had only one
parameter (ID). The main report, however has and should prompt for 3
additional parameters -- (ShowThis..Yes/No, ShowThat..Yes/No,
ShowOther..Yes/No). The report is broken out into lists (bands) that utilize
7 additional datasets. Thay are not one within the other(s), but laid out
sequentially down the report body - depending on the 'Show' parm values,
certain bands -or lists- will be shown or not. All data(sets) are run and
all data is returned to the report.
I have a SP that I have set up for a dataset that returns the available
values for the 'Show' parameters - values are Yes and No. I have default
values set up for the 'Show' parameters ( ="No" ). The problem is that I
cannot get the report to prompt me for the show partameters. The boxes are
greyed out and I have no option of making a selection...The main parameter,
ID, also has a default value and a dataset that returns available values -
once I select an available ID, the Show parms are enabled, use the default
value and the report runs. I thought that maybe this was happening because
the 'Show' parms were not included in the SP, so I added them to the SP and
made the link from the dataset parm to the report parm - no change. I feel
like I'm missing something right in front of my face - what do I have to do
so the user can make these 'Show' selections?
thanks,What is happening is that the system thinks your other parameters are
cascading (it is possible to have one parameter dependent on another). That
is why it is greying it out. Although your default is working when you put
an equal sign this means it expects an expression. An expression can refere
to other parameters and that is why it thought it was cascading. Instead of
doing the default this way:
= "No"
Just put this in (no quote marks)
No
This is like what you can do with a textbox. You can put in the text like a
tile
This is the title of my report
Or you can have an expression
="My Report with a date range of: " & Parameters!Startdate.value & " - " &
Parameters!Enddate.value
It definitely is not clear on how you put in defaults.
One other point, if all parameters have defaults then the report runs
immediately without waiting for the View button to be pressed. If you want
to wait until user decides then you need to leave one parameter without a
default.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Myles" <Myles@.discussions.microsoft.com> wrote in message
news:67790BA2-8213-4568-866F-2CEFEF356A2C@.microsoft.com...
> Hi all,
> did a search on parameters and could not find the answer to my ask, so
> here
> goes..
> I have a report that runs on a SP. Originally, the SP had only one
> parameter (ID). The main report, however has and should prompt for 3
> additional parameters -- (ShowThis..Yes/No, ShowThat..Yes/No,
> ShowOther..Yes/No). The report is broken out into lists (bands) that
> utilize
> 7 additional datasets. Thay are not one within the other(s), but laid out
> sequentially down the report body - depending on the 'Show' parm values,
> certain bands -or lists- will be shown or not. All data(sets) are run and
> all data is returned to the report.
> I have a SP that I have set up for a dataset that returns the available
> values for the 'Show' parameters - values are Yes and No. I have default
> values set up for the 'Show' parameters ( ="No" ). The problem is that I
> cannot get the report to prompt me for the show partameters. The boxes
> are
> greyed out and I have no option of making a selection...The main
> parameter,
> ID, also has a default value and a dataset that returns available values -
> once I select an available ID, the Show parms are enabled, use the default
> value and the report runs. I thought that maybe this was happening
> because
> the 'Show' parms were not included in the SP, so I added them to the SP
> and
> made the link from the dataset parm to the report parm - no change. I
> feel
> like I'm missing something right in front of my face - what do I have to
> do
> so the user can make these 'Show' selections?
> thanks,
>|||Thank you Bruce,
I knew it was something simple I was overlooking...a fella can only look at
this stuff for so long!
"Bruce L-C [MVP]" wrote:
> What is happening is that the system thinks your other parameters are
> cascading (it is possible to have one parameter dependent on another). That
> is why it is greying it out. Although your default is working when you put
> an equal sign this means it expects an expression. An expression can refere
> to other parameters and that is why it thought it was cascading. Instead of
> doing the default this way:
> = "No"
> Just put this in (no quote marks)
> No
> This is like what you can do with a textbox. You can put in the text like a
> tile
> This is the title of my report
> Or you can have an expression
> ="My Report with a date range of: " & Parameters!Startdate.value & " - " &
> Parameters!Enddate.value
> It definitely is not clear on how you put in defaults.
> One other point, if all parameters have defaults then the report runs
> immediately without waiting for the View button to be pressed. If you want
> to wait until user decides then you need to leave one parameter without a
> default.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Myles" <Myles@.discussions.microsoft.com> wrote in message
> news:67790BA2-8213-4568-866F-2CEFEF356A2C@.microsoft.com...
> > Hi all,
> >
> > did a search on parameters and could not find the answer to my ask, so
> > here
> > goes..
> >
> > I have a report that runs on a SP. Originally, the SP had only one
> > parameter (ID). The main report, however has and should prompt for 3
> > additional parameters -- (ShowThis..Yes/No, ShowThat..Yes/No,
> > ShowOther..Yes/No). The report is broken out into lists (bands) that
> > utilize
> > 7 additional datasets. Thay are not one within the other(s), but laid out
> > sequentially down the report body - depending on the 'Show' parm values,
> > certain bands -or lists- will be shown or not. All data(sets) are run and
> > all data is returned to the report.
> >
> > I have a SP that I have set up for a dataset that returns the available
> > values for the 'Show' parameters - values are Yes and No. I have default
> > values set up for the 'Show' parameters ( ="No" ). The problem is that I
> > cannot get the report to prompt me for the show partameters. The boxes
> > are
> > greyed out and I have no option of making a selection...The main
> > parameter,
> > ID, also has a default value and a dataset that returns available values -
> > once I select an available ID, the Show parms are enabled, use the default
> > value and the report runs. I thought that maybe this was happening
> > because
> > the 'Show' parms were not included in the SP, so I added them to the SP
> > and
> > made the link from the dataset parm to the report parm - no change. I
> > feel
> > like I'm missing something right in front of my face - what do I have to
> > do
> > so the user can make these 'Show' selections?
> >
> > thanks,
> >
> >
>
>
did a search on parameters and could not find the answer to my ask, so here
goes..
I have a report that runs on a SP. Originally, the SP had only one
parameter (ID). The main report, however has and should prompt for 3
additional parameters -- (ShowThis..Yes/No, ShowThat..Yes/No,
ShowOther..Yes/No). The report is broken out into lists (bands) that utilize
7 additional datasets. Thay are not one within the other(s), but laid out
sequentially down the report body - depending on the 'Show' parm values,
certain bands -or lists- will be shown or not. All data(sets) are run and
all data is returned to the report.
I have a SP that I have set up for a dataset that returns the available
values for the 'Show' parameters - values are Yes and No. I have default
values set up for the 'Show' parameters ( ="No" ). The problem is that I
cannot get the report to prompt me for the show partameters. The boxes are
greyed out and I have no option of making a selection...The main parameter,
ID, also has a default value and a dataset that returns available values -
once I select an available ID, the Show parms are enabled, use the default
value and the report runs. I thought that maybe this was happening because
the 'Show' parms were not included in the SP, so I added them to the SP and
made the link from the dataset parm to the report parm - no change. I feel
like I'm missing something right in front of my face - what do I have to do
so the user can make these 'Show' selections?
thanks,What is happening is that the system thinks your other parameters are
cascading (it is possible to have one parameter dependent on another). That
is why it is greying it out. Although your default is working when you put
an equal sign this means it expects an expression. An expression can refere
to other parameters and that is why it thought it was cascading. Instead of
doing the default this way:
= "No"
Just put this in (no quote marks)
No
This is like what you can do with a textbox. You can put in the text like a
tile
This is the title of my report
Or you can have an expression
="My Report with a date range of: " & Parameters!Startdate.value & " - " &
Parameters!Enddate.value
It definitely is not clear on how you put in defaults.
One other point, if all parameters have defaults then the report runs
immediately without waiting for the View button to be pressed. If you want
to wait until user decides then you need to leave one parameter without a
default.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Myles" <Myles@.discussions.microsoft.com> wrote in message
news:67790BA2-8213-4568-866F-2CEFEF356A2C@.microsoft.com...
> Hi all,
> did a search on parameters and could not find the answer to my ask, so
> here
> goes..
> I have a report that runs on a SP. Originally, the SP had only one
> parameter (ID). The main report, however has and should prompt for 3
> additional parameters -- (ShowThis..Yes/No, ShowThat..Yes/No,
> ShowOther..Yes/No). The report is broken out into lists (bands) that
> utilize
> 7 additional datasets. Thay are not one within the other(s), but laid out
> sequentially down the report body - depending on the 'Show' parm values,
> certain bands -or lists- will be shown or not. All data(sets) are run and
> all data is returned to the report.
> I have a SP that I have set up for a dataset that returns the available
> values for the 'Show' parameters - values are Yes and No. I have default
> values set up for the 'Show' parameters ( ="No" ). The problem is that I
> cannot get the report to prompt me for the show partameters. The boxes
> are
> greyed out and I have no option of making a selection...The main
> parameter,
> ID, also has a default value and a dataset that returns available values -
> once I select an available ID, the Show parms are enabled, use the default
> value and the report runs. I thought that maybe this was happening
> because
> the 'Show' parms were not included in the SP, so I added them to the SP
> and
> made the link from the dataset parm to the report parm - no change. I
> feel
> like I'm missing something right in front of my face - what do I have to
> do
> so the user can make these 'Show' selections?
> thanks,
>|||Thank you Bruce,
I knew it was something simple I was overlooking...a fella can only look at
this stuff for so long!
"Bruce L-C [MVP]" wrote:
> What is happening is that the system thinks your other parameters are
> cascading (it is possible to have one parameter dependent on another). That
> is why it is greying it out. Although your default is working when you put
> an equal sign this means it expects an expression. An expression can refere
> to other parameters and that is why it thought it was cascading. Instead of
> doing the default this way:
> = "No"
> Just put this in (no quote marks)
> No
> This is like what you can do with a textbox. You can put in the text like a
> tile
> This is the title of my report
> Or you can have an expression
> ="My Report with a date range of: " & Parameters!Startdate.value & " - " &
> Parameters!Enddate.value
> It definitely is not clear on how you put in defaults.
> One other point, if all parameters have defaults then the report runs
> immediately without waiting for the View button to be pressed. If you want
> to wait until user decides then you need to leave one parameter without a
> default.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Myles" <Myles@.discussions.microsoft.com> wrote in message
> news:67790BA2-8213-4568-866F-2CEFEF356A2C@.microsoft.com...
> > Hi all,
> >
> > did a search on parameters and could not find the answer to my ask, so
> > here
> > goes..
> >
> > I have a report that runs on a SP. Originally, the SP had only one
> > parameter (ID). The main report, however has and should prompt for 3
> > additional parameters -- (ShowThis..Yes/No, ShowThat..Yes/No,
> > ShowOther..Yes/No). The report is broken out into lists (bands) that
> > utilize
> > 7 additional datasets. Thay are not one within the other(s), but laid out
> > sequentially down the report body - depending on the 'Show' parm values,
> > certain bands -or lists- will be shown or not. All data(sets) are run and
> > all data is returned to the report.
> >
> > I have a SP that I have set up for a dataset that returns the available
> > values for the 'Show' parameters - values are Yes and No. I have default
> > values set up for the 'Show' parameters ( ="No" ). The problem is that I
> > cannot get the report to prompt me for the show partameters. The boxes
> > are
> > greyed out and I have no option of making a selection...The main
> > parameter,
> > ID, also has a default value and a dataset that returns available values -
> > once I select an available ID, the Show parms are enabled, use the default
> > value and the report runs. I thought that maybe this was happening
> > because
> > the 'Show' parms were not included in the SP, so I added them to the SP
> > and
> > made the link from the dataset parm to the report parm - no change. I
> > feel
> > like I'm missing something right in front of my face - what do I have to
> > do
> > so the user can make these 'Show' selections?
> >
> > thanks,
> >
> >
>
>
Friday, March 9, 2012
Parameters in Stored Procedures problem
I have 2 stored procedures, both are listed below. The
first Stored Procedure takes 8 parameters and runs and
update query using those parameters. This procedure
takes about 3 minutes to run. The second procedure
doesn't take any parameters, but declares the same 8
variables at the start of the procedure and sets them to
same valuse that I used when I ran the first procedure
and this one takes less than a second to run.
Just a little more information here is the size of the
tables:
aggQuotaAtt 135194
txoQuota: 135194
mstrsCustomer: 19741
So my question is why does the same procedure take
radically different when I use parameters.
Here is the first procedure which takes 3 minutes:
ALTER PROCEDURE [dbo].[Paul'sJunk]
@.PrdID varchar(18),
@.ShipToID int,
@.PoolYearPeriodID int,
@.AorID int,
@.Aor0ID int,
@.Aor1ID int,
@.Aor2ID int,
@.Aor3ID int
as
print getDate()
UPDATE aggQuotaAtt SET
OrderQty = txoQuota.OrderQty,
Revenue = txoQuota.Revenue,
HistoricalPercentage = txoQuota.HistoricalPercentage,
CurrentPercentage = txoQuota.CurrentPercentage,
ModifiedDate = GetDate(),
AorID = mstrsCustomer.AorID,
Aor0ID = mstrsCustomer.Aor0ID,
Aor1ID = mstrsCustomer.Aor1ID,
Aor2ID = mstrsCustomer.Aor2ID,
Aor3ID = mstrsCustomer.Aor3ID,
Aor4ID = mstrsCustomer.Aor4ID
FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
QuotaBase.ShipToID = txoQuota.ShipToID
AND QuotaBase.PrdID = txoQuota.PrdID
AND QuotaBase.PoolYearPeriodID = txoQuota.PoolYearPeriodID
INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
mstrsCustomer.ShipToID = txoQuota.ShipToID
WHERE txoQuota.PrdID = @.PrdID
AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
AND (txoQuota.ShipToID = @.ShipToID OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))
and here is the second procedure. This is the one that
takes less than a second:
ALTER PROCEDURE [dbo].[Paul'sJunk2]
as
DECLARE @.PrdID varchar(18)
DECLARE @.ShipToID int
DECLARE @.PoolYearPeriodID int
DECLARE @.AorID int
DECLARE @.Aor0ID int
DECLARE @.Aor1ID int
DECLARE @.Aor2ID int
DECLARE @.Aor3ID int
SET @.PrdID = '20027-933'
SET @.ShipToID = 0
SET @.PoolYearPeriodID = 73
SET @.AorID = 1
SET @.Aor0ID = NULL
SET @.Aor1ID = NULL
SET @.Aor2ID = NULL
SET @.Aor3ID = NULL
UPDATE aggQuotaAtt SET
OrderQty = txoQuota.OrderQty,
Revenue = txoQuota.Revenue,
HistoricalPercentage = txoQuota.HistoricalPercentage,
CurrentPercentage = txoQuota.CurrentPercentage,
ModifiedDate = GetDate(),
AorID = mstrsCustomer.AorID,
Aor0ID = mstrsCustomer.Aor0ID,
Aor1ID = mstrsCustomer.Aor1ID,
Aor2ID = mstrsCustomer.Aor2ID,
Aor3ID = mstrsCustomer.Aor3ID,
Aor4ID = mstrsCustomer.Aor4ID
FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
QuotaBase.ShipToID = txoQuota.ShipToID
AND QuotaBase.PrdID = txoQuota.PrdID
AND QuotaBase.PoolYearPeriodID = txoQuota.PoolYearPeriodID
INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
mstrsCustomer.ShipToID = txoQuota.ShipToID
WHERE txoQuota.PrdID = @.PrdID
AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
AND (txoQuota.ShipToID = @.ShipToID OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))Jory asked effectively the same question yesterday in the .programming
group - the same responses apply.
"Paul Cavacas" <pcavacas@.oceanspray.com> wrote in message
news:08f301c36327$a1e66090$a601280a@.phx.gbl...
> I have 2 stored procedures, both are listed below. The
> first Stored Procedure takes 8 parameters and runs and
> update query using those parameters. This procedure
> takes about 3 minutes to run. The second procedure
> doesn't take any parameters, but declares the same 8
> variables at the start of the procedure and sets them to
> same valuse that I used when I ran the first procedure
> and this one takes less than a second to run.
> Just a little more information here is the size of the
> tables:
> aggQuotaAtt 135194
> txoQuota: 135194
> mstrsCustomer: 19741
> So my question is why does the same procedure take
> radically different when I use parameters.
> Here is the first procedure which takes 3 minutes:
> ALTER PROCEDURE [dbo].[Paul'sJunk]
> @.PrdID varchar(18),
> @.ShipToID int,
> @.PoolYearPeriodID int,
> @.AorID int,
> @.Aor0ID int,
> @.Aor1ID int,
> @.Aor2ID int,
> @.Aor3ID int
> as
> print getDate()
> UPDATE aggQuotaAtt SET
> OrderQty = txoQuota.OrderQty,
> Revenue = txoQuota.Revenue,
> HistoricalPercentage => txoQuota.HistoricalPercentage,
> CurrentPercentage => txoQuota.CurrentPercentage,
> ModifiedDate = GetDate(),
> AorID = mstrsCustomer.AorID,
> Aor0ID = mstrsCustomer.Aor0ID,
> Aor1ID = mstrsCustomer.Aor1ID,
> Aor2ID = mstrsCustomer.Aor2ID,
> Aor3ID = mstrsCustomer.Aor3ID,
> Aor4ID = mstrsCustomer.Aor4ID
> FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
> QuotaBase.ShipToID = txoQuota.ShipToID
> AND QuotaBase.PrdID = txoQuota.PrdID
> AND QuotaBase.PoolYearPeriodID => txoQuota.PoolYearPeriodID
> INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
> mstrsCustomer.ShipToID = txoQuota.ShipToID
> WHERE txoQuota.PrdID = @.PrdID
> AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
> AND (txoQuota.ShipToID = @.ShipToID OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))
>
>
>
> and here is the second procedure. This is the one that
> takes less than a second:
> ALTER PROCEDURE [dbo].[Paul'sJunk2]
> as
> DECLARE @.PrdID varchar(18)
> DECLARE @.ShipToID int
> DECLARE @.PoolYearPeriodID int
> DECLARE @.AorID int
> DECLARE @.Aor0ID int
> DECLARE @.Aor1ID int
> DECLARE @.Aor2ID int
> DECLARE @.Aor3ID int
> SET @.PrdID = '20027-933'
> SET @.ShipToID = 0
> SET @.PoolYearPeriodID = 73
> SET @.AorID = 1
> SET @.Aor0ID = NULL
> SET @.Aor1ID = NULL
> SET @.Aor2ID = NULL
> SET @.Aor3ID = NULL
> UPDATE aggQuotaAtt SET
> OrderQty = txoQuota.OrderQty,
> Revenue = txoQuota.Revenue,
> HistoricalPercentage => txoQuota.HistoricalPercentage,
> CurrentPercentage => txoQuota.CurrentPercentage,
> ModifiedDate = GetDate(),
> AorID = mstrsCustomer.AorID,
> Aor0ID = mstrsCustomer.Aor0ID,
> Aor1ID = mstrsCustomer.Aor1ID,
> Aor2ID = mstrsCustomer.Aor2ID,
> Aor3ID = mstrsCustomer.Aor3ID,
> Aor4ID = mstrsCustomer.Aor4ID
> FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
> QuotaBase.ShipToID = txoQuota.ShipToID
> AND QuotaBase.PrdID = txoQuota.PrdID
> AND QuotaBase.PoolYearPeriodID => txoQuota.PoolYearPeriodID
> INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
> mstrsCustomer.ShipToID = txoQuota.ShipToID
> WHERE txoQuota.PrdID = @.PrdID
> AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
> AND (txoQuota.ShipToID = @.ShipToID OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))|||can't say about the whole thing, but
what are the differences in the execution plan?
i have concern the select queries:
SELECT ShipToID FROM WaveAgg.dbo.mstrsCustomer WHERE
Aor0ID = @.Aor0ID
etc, where @.AorxID value is NULL,
this query should be a simple index lookup
but i have situations where the NULL value in the where
clause resulting in a table scan
>--Original Message--
> I have 2 stored procedures, both are listed below. The
>first Stored Procedure takes 8 parameters and runs and
>update query using those parameters. This procedure
>takes about 3 minutes to run. The second procedure
>doesn't take any parameters, but declares the same 8
>variables at the start of the procedure and sets them to
>same valuse that I used when I ran the first procedure
>and this one takes less than a second to run.
> Just a little more information here is the size of the
>tables:
>aggQuotaAtt 135194
>txoQuota: 135194
>mstrsCustomer: 19741
> So my question is why does the same procedure take
>radically different when I use parameters.
>Here is the first procedure which takes 3 minutes:
>ALTER PROCEDURE [dbo].[Paul'sJunk]
> @.PrdID varchar(18),
> @.ShipToID int,
> @.PoolYearPeriodID int,
> @.AorID int,
> @.Aor0ID int,
> @.Aor1ID int,
> @.Aor2ID int,
> @.Aor3ID int
>as
>print getDate()
>UPDATE aggQuotaAtt SET
> OrderQty = txoQuota.OrderQty,
> Revenue = txoQuota.Revenue,
> HistoricalPercentage =>txoQuota.HistoricalPercentage,
> CurrentPercentage =>txoQuota.CurrentPercentage,
> ModifiedDate = GetDate(),
> AorID = mstrsCustomer.AorID,
> Aor0ID = mstrsCustomer.Aor0ID,
> Aor1ID = mstrsCustomer.Aor1ID,
> Aor2ID = mstrsCustomer.Aor2ID,
> Aor3ID = mstrsCustomer.Aor3ID,
> Aor4ID = mstrsCustomer.Aor4ID
>FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
>QuotaBase.ShipToID = txoQuota.ShipToID
> AND QuotaBase.PrdID = txoQuota.PrdID
> AND QuotaBase.PoolYearPeriodID =>txoQuota.PoolYearPeriodID
>INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
>mstrsCustomer.ShipToID = txoQuota.ShipToID
>WHERE txoQuota.PrdID = @.PrdID
> AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
> AND (txoQuota.ShipToID = @.ShipToID OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))
>
>
>
>and here is the second procedure. This is the one that
>takes less than a second:
>ALTER PROCEDURE [dbo].[Paul'sJunk2]
>as
>DECLARE @.PrdID varchar(18)
>DECLARE @.ShipToID int
>DECLARE @.PoolYearPeriodID int
>DECLARE @.AorID int
>DECLARE @.Aor0ID int
>DECLARE @.Aor1ID int
>DECLARE @.Aor2ID int
>DECLARE @.Aor3ID int
>SET @.PrdID = '20027-933'
>SET @.ShipToID = 0
>SET @.PoolYearPeriodID = 73
>SET @.AorID = 1
>SET @.Aor0ID = NULL
>SET @.Aor1ID = NULL
>SET @.Aor2ID = NULL
>SET @.Aor3ID = NULL
>UPDATE aggQuotaAtt SET
> OrderQty = txoQuota.OrderQty,
> Revenue = txoQuota.Revenue,
> HistoricalPercentage =>txoQuota.HistoricalPercentage,
> CurrentPercentage =>txoQuota.CurrentPercentage,
> ModifiedDate = GetDate(),
> AorID = mstrsCustomer.AorID,
> Aor0ID = mstrsCustomer.Aor0ID,
> Aor1ID = mstrsCustomer.Aor1ID,
> Aor2ID = mstrsCustomer.Aor2ID,
> Aor3ID = mstrsCustomer.Aor3ID,
> Aor4ID = mstrsCustomer.Aor4ID
>FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
>QuotaBase.ShipToID = txoQuota.ShipToID
> AND QuotaBase.PrdID = txoQuota.PrdID
> AND QuotaBase.PoolYearPeriodID =>txoQuota.PoolYearPeriodID
>INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
>mstrsCustomer.ShipToID = txoQuota.ShipToID
>WHERE txoQuota.PrdID = @.PrdID
> AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
> AND (txoQuota.ShipToID = @.ShipToID OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))
>.
>
first Stored Procedure takes 8 parameters and runs and
update query using those parameters. This procedure
takes about 3 minutes to run. The second procedure
doesn't take any parameters, but declares the same 8
variables at the start of the procedure and sets them to
same valuse that I used when I ran the first procedure
and this one takes less than a second to run.
Just a little more information here is the size of the
tables:
aggQuotaAtt 135194
txoQuota: 135194
mstrsCustomer: 19741
So my question is why does the same procedure take
radically different when I use parameters.
Here is the first procedure which takes 3 minutes:
ALTER PROCEDURE [dbo].[Paul'sJunk]
@.PrdID varchar(18),
@.ShipToID int,
@.PoolYearPeriodID int,
@.AorID int,
@.Aor0ID int,
@.Aor1ID int,
@.Aor2ID int,
@.Aor3ID int
as
print getDate()
UPDATE aggQuotaAtt SET
OrderQty = txoQuota.OrderQty,
Revenue = txoQuota.Revenue,
HistoricalPercentage = txoQuota.HistoricalPercentage,
CurrentPercentage = txoQuota.CurrentPercentage,
ModifiedDate = GetDate(),
AorID = mstrsCustomer.AorID,
Aor0ID = mstrsCustomer.Aor0ID,
Aor1ID = mstrsCustomer.Aor1ID,
Aor2ID = mstrsCustomer.Aor2ID,
Aor3ID = mstrsCustomer.Aor3ID,
Aor4ID = mstrsCustomer.Aor4ID
FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
QuotaBase.ShipToID = txoQuota.ShipToID
AND QuotaBase.PrdID = txoQuota.PrdID
AND QuotaBase.PoolYearPeriodID = txoQuota.PoolYearPeriodID
INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
mstrsCustomer.ShipToID = txoQuota.ShipToID
WHERE txoQuota.PrdID = @.PrdID
AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
AND (txoQuota.ShipToID = @.ShipToID OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))
and here is the second procedure. This is the one that
takes less than a second:
ALTER PROCEDURE [dbo].[Paul'sJunk2]
as
DECLARE @.PrdID varchar(18)
DECLARE @.ShipToID int
DECLARE @.PoolYearPeriodID int
DECLARE @.AorID int
DECLARE @.Aor0ID int
DECLARE @.Aor1ID int
DECLARE @.Aor2ID int
DECLARE @.Aor3ID int
SET @.PrdID = '20027-933'
SET @.ShipToID = 0
SET @.PoolYearPeriodID = 73
SET @.AorID = 1
SET @.Aor0ID = NULL
SET @.Aor1ID = NULL
SET @.Aor2ID = NULL
SET @.Aor3ID = NULL
UPDATE aggQuotaAtt SET
OrderQty = txoQuota.OrderQty,
Revenue = txoQuota.Revenue,
HistoricalPercentage = txoQuota.HistoricalPercentage,
CurrentPercentage = txoQuota.CurrentPercentage,
ModifiedDate = GetDate(),
AorID = mstrsCustomer.AorID,
Aor0ID = mstrsCustomer.Aor0ID,
Aor1ID = mstrsCustomer.Aor1ID,
Aor2ID = mstrsCustomer.Aor2ID,
Aor3ID = mstrsCustomer.Aor3ID,
Aor4ID = mstrsCustomer.Aor4ID
FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
QuotaBase.ShipToID = txoQuota.ShipToID
AND QuotaBase.PrdID = txoQuota.PrdID
AND QuotaBase.PoolYearPeriodID = txoQuota.PoolYearPeriodID
INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
mstrsCustomer.ShipToID = txoQuota.ShipToID
WHERE txoQuota.PrdID = @.PrdID
AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
AND (txoQuota.ShipToID = @.ShipToID OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))Jory asked effectively the same question yesterday in the .programming
group - the same responses apply.
"Paul Cavacas" <pcavacas@.oceanspray.com> wrote in message
news:08f301c36327$a1e66090$a601280a@.phx.gbl...
> I have 2 stored procedures, both are listed below. The
> first Stored Procedure takes 8 parameters and runs and
> update query using those parameters. This procedure
> takes about 3 minutes to run. The second procedure
> doesn't take any parameters, but declares the same 8
> variables at the start of the procedure and sets them to
> same valuse that I used when I ran the first procedure
> and this one takes less than a second to run.
> Just a little more information here is the size of the
> tables:
> aggQuotaAtt 135194
> txoQuota: 135194
> mstrsCustomer: 19741
> So my question is why does the same procedure take
> radically different when I use parameters.
> Here is the first procedure which takes 3 minutes:
> ALTER PROCEDURE [dbo].[Paul'sJunk]
> @.PrdID varchar(18),
> @.ShipToID int,
> @.PoolYearPeriodID int,
> @.AorID int,
> @.Aor0ID int,
> @.Aor1ID int,
> @.Aor2ID int,
> @.Aor3ID int
> as
> print getDate()
> UPDATE aggQuotaAtt SET
> OrderQty = txoQuota.OrderQty,
> Revenue = txoQuota.Revenue,
> HistoricalPercentage => txoQuota.HistoricalPercentage,
> CurrentPercentage => txoQuota.CurrentPercentage,
> ModifiedDate = GetDate(),
> AorID = mstrsCustomer.AorID,
> Aor0ID = mstrsCustomer.Aor0ID,
> Aor1ID = mstrsCustomer.Aor1ID,
> Aor2ID = mstrsCustomer.Aor2ID,
> Aor3ID = mstrsCustomer.Aor3ID,
> Aor4ID = mstrsCustomer.Aor4ID
> FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
> QuotaBase.ShipToID = txoQuota.ShipToID
> AND QuotaBase.PrdID = txoQuota.PrdID
> AND QuotaBase.PoolYearPeriodID => txoQuota.PoolYearPeriodID
> INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
> mstrsCustomer.ShipToID = txoQuota.ShipToID
> WHERE txoQuota.PrdID = @.PrdID
> AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
> AND (txoQuota.ShipToID = @.ShipToID OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))
>
>
>
> and here is the second procedure. This is the one that
> takes less than a second:
> ALTER PROCEDURE [dbo].[Paul'sJunk2]
> as
> DECLARE @.PrdID varchar(18)
> DECLARE @.ShipToID int
> DECLARE @.PoolYearPeriodID int
> DECLARE @.AorID int
> DECLARE @.Aor0ID int
> DECLARE @.Aor1ID int
> DECLARE @.Aor2ID int
> DECLARE @.Aor3ID int
> SET @.PrdID = '20027-933'
> SET @.ShipToID = 0
> SET @.PoolYearPeriodID = 73
> SET @.AorID = 1
> SET @.Aor0ID = NULL
> SET @.Aor1ID = NULL
> SET @.Aor2ID = NULL
> SET @.Aor3ID = NULL
> UPDATE aggQuotaAtt SET
> OrderQty = txoQuota.OrderQty,
> Revenue = txoQuota.Revenue,
> HistoricalPercentage => txoQuota.HistoricalPercentage,
> CurrentPercentage => txoQuota.CurrentPercentage,
> ModifiedDate = GetDate(),
> AorID = mstrsCustomer.AorID,
> Aor0ID = mstrsCustomer.Aor0ID,
> Aor1ID = mstrsCustomer.Aor1ID,
> Aor2ID = mstrsCustomer.Aor2ID,
> Aor3ID = mstrsCustomer.Aor3ID,
> Aor4ID = mstrsCustomer.Aor4ID
> FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
> QuotaBase.ShipToID = txoQuota.ShipToID
> AND QuotaBase.PrdID = txoQuota.PrdID
> AND QuotaBase.PoolYearPeriodID => txoQuota.PoolYearPeriodID
> INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
> mstrsCustomer.ShipToID = txoQuota.ShipToID
> WHERE txoQuota.PrdID = @.PrdID
> AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
> AND (txoQuota.ShipToID = @.ShipToID OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))|||can't say about the whole thing, but
what are the differences in the execution plan?
i have concern the select queries:
SELECT ShipToID FROM WaveAgg.dbo.mstrsCustomer WHERE
Aor0ID = @.Aor0ID
etc, where @.AorxID value is NULL,
this query should be a simple index lookup
but i have situations where the NULL value in the where
clause resulting in a table scan
>--Original Message--
> I have 2 stored procedures, both are listed below. The
>first Stored Procedure takes 8 parameters and runs and
>update query using those parameters. This procedure
>takes about 3 minutes to run. The second procedure
>doesn't take any parameters, but declares the same 8
>variables at the start of the procedure and sets them to
>same valuse that I used when I ran the first procedure
>and this one takes less than a second to run.
> Just a little more information here is the size of the
>tables:
>aggQuotaAtt 135194
>txoQuota: 135194
>mstrsCustomer: 19741
> So my question is why does the same procedure take
>radically different when I use parameters.
>Here is the first procedure which takes 3 minutes:
>ALTER PROCEDURE [dbo].[Paul'sJunk]
> @.PrdID varchar(18),
> @.ShipToID int,
> @.PoolYearPeriodID int,
> @.AorID int,
> @.Aor0ID int,
> @.Aor1ID int,
> @.Aor2ID int,
> @.Aor3ID int
>as
>print getDate()
>UPDATE aggQuotaAtt SET
> OrderQty = txoQuota.OrderQty,
> Revenue = txoQuota.Revenue,
> HistoricalPercentage =>txoQuota.HistoricalPercentage,
> CurrentPercentage =>txoQuota.CurrentPercentage,
> ModifiedDate = GetDate(),
> AorID = mstrsCustomer.AorID,
> Aor0ID = mstrsCustomer.Aor0ID,
> Aor1ID = mstrsCustomer.Aor1ID,
> Aor2ID = mstrsCustomer.Aor2ID,
> Aor3ID = mstrsCustomer.Aor3ID,
> Aor4ID = mstrsCustomer.Aor4ID
>FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
>QuotaBase.ShipToID = txoQuota.ShipToID
> AND QuotaBase.PrdID = txoQuota.PrdID
> AND QuotaBase.PoolYearPeriodID =>txoQuota.PoolYearPeriodID
>INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
>mstrsCustomer.ShipToID = txoQuota.ShipToID
>WHERE txoQuota.PrdID = @.PrdID
> AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
> AND (txoQuota.ShipToID = @.ShipToID OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))
>
>
>
>and here is the second procedure. This is the one that
>takes less than a second:
>ALTER PROCEDURE [dbo].[Paul'sJunk2]
>as
>DECLARE @.PrdID varchar(18)
>DECLARE @.ShipToID int
>DECLARE @.PoolYearPeriodID int
>DECLARE @.AorID int
>DECLARE @.Aor0ID int
>DECLARE @.Aor1ID int
>DECLARE @.Aor2ID int
>DECLARE @.Aor3ID int
>SET @.PrdID = '20027-933'
>SET @.ShipToID = 0
>SET @.PoolYearPeriodID = 73
>SET @.AorID = 1
>SET @.Aor0ID = NULL
>SET @.Aor1ID = NULL
>SET @.Aor2ID = NULL
>SET @.Aor3ID = NULL
>UPDATE aggQuotaAtt SET
> OrderQty = txoQuota.OrderQty,
> Revenue = txoQuota.Revenue,
> HistoricalPercentage =>txoQuota.HistoricalPercentage,
> CurrentPercentage =>txoQuota.CurrentPercentage,
> ModifiedDate = GetDate(),
> AorID = mstrsCustomer.AorID,
> Aor0ID = mstrsCustomer.Aor0ID,
> Aor1ID = mstrsCustomer.Aor1ID,
> Aor2ID = mstrsCustomer.Aor2ID,
> Aor3ID = mstrsCustomer.Aor3ID,
> Aor4ID = mstrsCustomer.Aor4ID
>FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
>QuotaBase.ShipToID = txoQuota.ShipToID
> AND QuotaBase.PrdID = txoQuota.PrdID
> AND QuotaBase.PoolYearPeriodID =>txoQuota.PoolYearPeriodID
>INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
>mstrsCustomer.ShipToID = txoQuota.ShipToID
>WHERE txoQuota.PrdID = @.PrdID
> AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
> AND (txoQuota.ShipToID = @.ShipToID OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))
>.
>
parameters in Reporting Services
When you are using a number of parameters in a report, how do you set it so that if a parameter is selected the query runs against that parameter, but if a parameter isn't selected, the query runs against all values for that parameter?
you should should give a default value to your parm(0 if it's int),then add to your sql script the condition...|||Yes, if you are using dropdownlist you can add one more item which will redirect you to span all records. You can modify your parameter listing query like below
SELECT LanguageId, Code FROM Languages UNION SELECT 0, 'ALL'
If you are using textboxes then if you trim the value and get nothing then you can code your datasource code not to filter on that column.
Eralper
http://www.kodyaz.com
Subscribe to:
Posts (Atom)