Parameters not working

Hello,
I have created a report based on a stored procedure which uses 13
parameters. The 13 parameter values are mapped to the report parameter
selections (for example: Parameter @.CATEGORY, Value
=Parameters!Category.Value).
When the stored procedure is executed from the 'Data' tab of RS and the
parameters are typed in, the result set is correct.
When the report is executed in 'Preview' tab of RS only the first parameter
is evaluated. The rest of the parameters are not evaluated and the result
set brings everything back regardless of the selections.
Is this a bug or is there a way to enforce report parameter selections to be
passed into the stored procedure?
Thanks in Advance,
JohnParameters are case sensitive. If this is happening to you then something is
wrong with your mapping. I always use the expression builder and select the
report parameter just for this reason.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:3E4BF735-9632-4493-AE77-751DB146D5E9@.microsoft.com...
> Hello,
> I have created a report based on a stored procedure which uses 13
> parameters. The 13 parameter values are mapped to the report parameter
> selections (for example: Parameter @.CATEGORY, Value
> =Parameters!Category.Value).
> When the stored procedure is executed from the 'Data' tab of RS and the
> parameters are typed in, the result set is correct.
> When the report is executed in 'Preview' tab of RS only the first
parameter
> is evaluated. The rest of the parameters are not evaluated and the result
> set brings everything back regardless of the selections.
> Is this a bug or is there a way to enforce report parameter selections to
be
> passed into the stored procedure?
> Thanks in Advance,
> John|||Thanks Bruce,
Actually parameters were selected with expression builder and are correct.
The values for some of these parameters are coming from other stored
procedures. I am wondering if all these values are pulled once prior to
report execution and anything that is changed afterwards is not used.
John K.
===
"Bruce L-C [MVP]" wrote:
> Parameters are case sensitive. If this is happening to you then something is
> wrong with your mapping. I always use the expression builder and select the
> report parameter just for this reason.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John K" <JohnK@.discussions.microsoft.com> wrote in message
> news:3E4BF735-9632-4493-AE77-751DB146D5E9@.microsoft.com...
> > Hello,
> >
> > I have created a report based on a stored procedure which uses 13
> > parameters. The 13 parameter values are mapped to the report parameter
> > selections (for example: Parameter @.CATEGORY, Value
> > =Parameters!Category.Value).
> >
> > When the stored procedure is executed from the 'Data' tab of RS and the
> > parameters are typed in, the result set is correct.
> >
> > When the report is executed in 'Preview' tab of RS only the first
> parameter
> > is evaluated. The rest of the parameters are not evaluated and the result
> > set brings everything back regardless of the selections.
> >
> > Is this a bug or is there a way to enforce report parameter selections to
> be
> > passed into the stored procedure?
> >
> > Thanks in Advance,
> > John
>
>|||The parameters that you see in the toolbar prior to clicking on view report
are what should be getting sent to your stored procedure. One thing to keep
in mind, if the parameters are not changed then the data is cached (in the
development environment) and then next time you preview it uses the cached
data (look where you have your rdl files and you will see
reportname.rdl.data, that is what the .data files are.
I am not really sure what you are seeing but it could be a side affect of
that.
If the parameters are supposed to be done in a certain execution order then
you need to make them cascading parameters. Search books on line for
cascading parameter
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:35EA39B3-D88E-4EE4-973B-D162686C48EA@.microsoft.com...
> Thanks Bruce,
> Actually parameters were selected with expression builder and are correct.
> The values for some of these parameters are coming from other stored
> procedures. I am wondering if all these values are pulled once prior to
> report execution and anything that is changed afterwards is not used.
> John K.
> ===> "Bruce L-C [MVP]" wrote:
> > Parameters are case sensitive. If this is happening to you then
something is
> > wrong with your mapping. I always use the expression builder and select
the
> > report parameter just for this reason.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "John K" <JohnK@.discussions.microsoft.com> wrote in message
> > news:3E4BF735-9632-4493-AE77-751DB146D5E9@.microsoft.com...
> > > Hello,
> > >
> > > I have created a report based on a stored procedure which uses 13
> > > parameters. The 13 parameter values are mapped to the report
parameter
> > > selections (for example: Parameter @.CATEGORY, Value
> > > =Parameters!Category.Value).
> > >
> > > When the stored procedure is executed from the 'Data' tab of RS and
the
> > > parameters are typed in, the result set is correct.
> > >
> > > When the report is executed in 'Preview' tab of RS only the first
> > parameter
> > > is evaluated. The rest of the parameters are not evaluated and the
result
> > > set brings everything back regardless of the selections.
> > >
> > > Is this a bug or is there a way to enforce report parameter selections
to
> > be
> > > passed into the stored procedure?
> > >
> > > Thanks in Advance,
> > > John
> >
> >
> >|||Yes, some of the parameters are cascading.
I tried to change the type from Stored Procedure to Text and typed the
parameters out instead of using the paramter tab (like so: exec usp_x
@.Status=1, @.Project='HELLO') which works fine.
I just need the hardcoded 'HELLO' to be the value selected on the report
parameter which has the same name as the expected stored procedure parameter
(@.Project).
I tried exec usp_x @.Status=1, @.Project=Parameters!Project.Value!
which returns incorrect syntax near '!'
Thanks again.
"Bruce L-C [MVP]" wrote:
> The parameters that you see in the toolbar prior to clicking on view report
> are what should be getting sent to your stored procedure. One thing to keep
> in mind, if the parameters are not changed then the data is cached (in the
> development environment) and then next time you preview it uses the cached
> data (look where you have your rdl files and you will see
> reportname.rdl.data, that is what the .data files are.
> I am not really sure what you are seeing but it could be a side affect of
> that.
> If the parameters are supposed to be done in a certain execution order then
> you need to make them cascading parameters. Search books on line for
> cascading parameter
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John K" <JohnK@.discussions.microsoft.com> wrote in message
> news:35EA39B3-D88E-4EE4-973B-D162686C48EA@.microsoft.com...
> > Thanks Bruce,
> >
> > Actually parameters were selected with expression builder and are correct.
> > The values for some of these parameters are coming from other stored
> > procedures. I am wondering if all these values are pulled once prior to
> > report execution and anything that is changed afterwards is not used.
> >
> > John K.
> >
> > ===> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Parameters are case sensitive. If this is happening to you then
> something is
> > > wrong with your mapping. I always use the expression builder and select
> the
> > > report parameter just for this reason.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "John K" <JohnK@.discussions.microsoft.com> wrote in message
> > > news:3E4BF735-9632-4493-AE77-751DB146D5E9@.microsoft.com...
> > > > Hello,
> > > >
> > > > I have created a report based on a stored procedure which uses 13
> > > > parameters. The 13 parameter values are mapped to the report
> parameter
> > > > selections (for example: Parameter @.CATEGORY, Value
> > > > =Parameters!Category.Value).
> > > >
> > > > When the stored procedure is executed from the 'Data' tab of RS and
> the
> > > > parameters are typed in, the result set is correct.
> > > >
> > > > When the report is executed in 'Preview' tab of RS only the first
> > > parameter
> > > > is evaluated. The rest of the parameters are not evaluated and the
> result
> > > > set brings everything back regardless of the selections.
> > > >
> > > > Is this a bug or is there a way to enforce report parameter selections
> to
> > > be
> > > > passed into the stored procedure?
> > > >
> > > > Thanks in Advance,
> > > > John
> > >
> > >
> > >
>
>|||Corrected the syntax to:
exec usp_x @.Status=1, @.Project=[Parameters!Project.Value!]
but it still not used in the reporting window.
Also tried: exec usp_X @.Status, @.Project to reference the parameters
directly. Still having problems.
If there are any examples out there please point me that direction.
Thanks.|||When you do a stored procedure do the following steps. I tend to use command
type as text but that is because I tend to go against Sybase a lot (I am
currently creating a datamart and will be doing most of my reporting against
SQL Server). First thing at work tomorrow I'll give you a stored procedure
example with SQL Server. The format
exec usp_x @.Status=1, @.Project=[Parameters!Project.Value!]
will not work. I am surprised it didn't error out for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:9A94F3C6-54BE-487D-9C43-359F806FE7B9@.microsoft.com...
> Corrected the syntax to:
> exec usp_x @.Status=1, @.Project=[Parameters!Project.Value!]
> but it still not used in the reporting window.
> Also tried: exec usp_X @.Status, @.Project to reference the parameters
> directly. Still having problems.
> If there are any examples out there please point me that direction.
> Thanks.|||Bruce thanks again,
Unfortunately the attachment did not make it, can you please e-mail it to
yiannino@.hotmail.com?
John K.|||In Outlook express the attachment shows as a paperclip. I know some people
use web based readers and seem to not see attachments. I prefer to do all my
responses here but I will email it to you. If you have additional questions
about the report please just respond here so others can benefit as well.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:60B099C4-5A3F-41FD-B130-24226143E8C5@.microsoft.com...
> Bruce thanks again,
> Unfortunately the attachment did not make it, can you please e-mail it to
> yiannino@.hotmail.com?
> John K.|||Hi Bruce,
Sorry it took a while. I was able to recreate the issue and it seems that
the problem lies with the way stored procedures are evaluated by Reporting
Services in terms of what parameters are used in the preview.
Specifically, the original stored procedure declared a cursor (which
required a select from another table) and then called another stored
procedure within the cursor. Seems that Reporting Services evalutated this
first select statement to decide what parameters would be applicable for the
report.
I changed the stored procedure to have as the first statement the select
(dynamically build) with all the parameters that are being passed in, and
that seems to work.
So is this some type of RS limitation where parameters to be evaluated at
execution time are defined by the first sql statement found/returned?
Is there anything coming in SQL Server 2005 that would allow a Cursor based
on another table to be used?
Any other ideas that would allow me to use the stored procedures as they
exist today instead of having to rewrite them?
Thanks again for all your help,
John