I have a stored procedure which accepts a CustomerID parameter of type
char(3). If this parameter is null or blank, then data for all customers is
returned, otherwise just for the selected customer. This works as exepected
in query analyser.
pr_CustomerReport NULL OR
pr_CustomerReport ''
both return data for all customers
pr_CustomerReport 'FDR' returns data for selected customer.
I am trying to get a report to pass a parameter to this stored procedure
from a drop down list. If I select a parameter value in the drop down list I
get the data for the selected customer, but if I leave the parameter with no
selection I get nothing, but I KNOW the sp works correctly with blank or
null parameters.
In report parameters I have ticked both allow null value and allow blank
value. Why won't the report display anything? How can I find out if the
report is even attempting to run the stored procedure (I suspect it isn't)?
Is there anything else I need to do to get the report to run with an
unselected parameter?
Thanks for any help
KenYour drop down list is populated from the query. That query should return
null (dbnull) as one of the values so that you can select it later. You
can't just select nothing and assume it is null.
By selecting "allow null" and "allow blank" you say that your stored proc
(or query) can potentially accepts nulls (will not crash). By _not_
providing null as one of the valid values you say that null is _not_ valid
in the current situation and therefore can't be selected.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
"Ken Cooper" <_k.a.cooper_@._shu.ac.uk_> wrote in message
news:ulHvEjrYEHA.2812@.TK2MSFTNGP11.phx.gbl...
> I have a stored procedure which accepts a CustomerID parameter of type
> char(3). If this parameter is null or blank, then data for all customers
is
> returned, otherwise just for the selected customer. This works as
exepected
> in query analyser.
> pr_CustomerReport NULL OR
> pr_CustomerReport ''
> both return data for all customers
> pr_CustomerReport 'FDR' returns data for selected customer.
> I am trying to get a report to pass a parameter to this stored procedure
> from a drop down list. If I select a parameter value in the drop down list
I
> get the data for the selected customer, but if I leave the parameter with
no
> selection I get nothing, but I KNOW the sp works correctly with blank or
> null parameters.
> In report parameters I have ticked both allow null value and allow blank
> value. Why won't the report display anything? How can I find out if the
> report is even attempting to run the stored procedure (I suspect it
isn't)?
> Is there anything else I need to do to get the report to run with an
> unselected parameter?
> Thanks for any help
> Ken
>
Showing posts with label procedure. Show all posts
Showing posts with label procedure. Show all posts
Friday, March 30, 2012
Report Parameters
I am using stored procedures and I would like an input parameter to use a
different stored procedure from the report. Query access seems to be on the
report stored proc. I also don't want to run the report untill the user
selects parameters since it is long.
Thanks,
maaOn Nov 4, 7:30 am, maa <m...@.discussions.microsoft.com> wrote:
> I am using stored procedures and I would like an input parameter to use a
> different stored procedure from the report. Query access seems to be on the
> report stored proc. I also don't want to run the report untill the user
> selects parameters since it is long.
> Thanks,
> maa
In the Data tab of the BIDS environment, select the drop-down list box
to the right of 'Dataset:' and select '<New Dataset...>' change
'Command type:' to StoredProcedure and below 'Query string:' enter in
the new stored procedure that you want to use. Then select the
'Report' drop-down tab and select 'Report Parameters...' >> select the
'Add' button and below 'Available values:' select the dataset you just
created below 'Dataset:' The 'Value field:' will be what is used in
the report or elsewhere in another stored procedure (sometimes an
identity field in a table or primary key of sorts: but not always) and
the 'Label field:' is what is shown to the user to select from. To
delay the report running until after the parameter is selected, below
the 'Default values:' during the parameter creation process select
'Null.' Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
different stored procedure from the report. Query access seems to be on the
report stored proc. I also don't want to run the report untill the user
selects parameters since it is long.
Thanks,
maaOn Nov 4, 7:30 am, maa <m...@.discussions.microsoft.com> wrote:
> I am using stored procedures and I would like an input parameter to use a
> different stored procedure from the report. Query access seems to be on the
> report stored proc. I also don't want to run the report untill the user
> selects parameters since it is long.
> Thanks,
> maa
In the Data tab of the BIDS environment, select the drop-down list box
to the right of 'Dataset:' and select '<New Dataset...>' change
'Command type:' to StoredProcedure and below 'Query string:' enter in
the new stored procedure that you want to use. Then select the
'Report' drop-down tab and select 'Report Parameters...' >> select the
'Add' button and below 'Available values:' select the dataset you just
created below 'Dataset:' The 'Value field:' will be what is used in
the report or elsewhere in another stored procedure (sometimes an
identity field in a table or primary key of sorts: but not always) and
the 'Label field:' is what is shown to the user to select from. To
delay the report running until after the parameter is selected, below
the 'Default values:' during the parameter creation process select
'Null.' Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
Report parameter won't show values with stored procedure results
Hi,
I wrote a stored procedure in C# to return results that I could use to
populate a report parameter list. The problem occurs when I set up the
parameter. Under "Report Parameters -> Available Values -> From Query", I
can set the dataset fine but no entries appear under the Value field or the
Label field. When I run the query (whether it's thru the data tab or in SQL
server management Studio or the SQL Server project), I get the correct
results. If I try to type in the name of the field, I get an
rsInvalidDataSetReferenceField error when I preview the report.
Below is the steps I took to get to where I'm at:
1.
The stored procedure was supposed to extract certain areas from the area
path, with the TFSWarehouse as my data source. The areas of interest were
top-level areas, i.e. \\TeamProject\TopLevelArea\SubAreas... The
CommandText attribute of my SqlCommand instance is as follows:
@."SELECT DISTINCT Area.[Area Path]
FROM Area INNER JOIN
[Current Work Item] ON Area.__ID = [Current Work
Item].Area";
2.
The results are returned in an SqlDataReader. I read thru each record of
the reader and manipulate some of the data and send it back using
SqlContext.Pipe.SendResultsStart/Row/End when appropriate. The structure of
the record consists of one column, called "TopLevelArea".
3.
I compiled it into an assembly using the Visual Studio command prompt, ran a
T-SQL query to create the procedure, so that it appears in the Stored
Procedures list. And then I created a dataset whose command type is
StoredProcedure, and it contains the name of the procedure. Then I set up
the parameter and that's where everything went all wrong.
Please help.Hi Winkles,
Would you please try to check the DataSet Properties?
Make sure the Field have set a proper name in the data tab of the Report
Project.
Also, please make sure you have set the correct dataset name when you
config the report parameter.
If this does not help, would you please send the sample stored procedure to
me for troubleshooting? Thanks!
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hello Winkles,
I have tested on my side with your code.
You need to set the Field tab in the DataSet properties.
In the Field Name, type TopLevelArea, and type TopLevelArea in the Value.
(Remove the equal mark). And then, you could get the Field name in the
Parameter.
Please do the above and let me know the result.
Sincerely,
Wei Lu
Microsoft Online Community Support|||Hi Wei,
It worked!!! Thank you very much! I had been including the equal sign
before.
The 'TopLevelArea' in Field Name...does that correspond to the metadata
column in the stored procedure? What does the TopLevelArea in the 'Value'
column match up with? Or do they both refer to the same thing?
Thanks again.
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:1BHmMqBwGHA.5976@.TK2MSFTNGXA01.phx.gbl...
> Hello Winkles,
> I have tested on my side with your code.
> You need to set the Field tab in the DataSet properties.
> In the Field Name, type TopLevelArea, and type TopLevelArea in the Value.
> (Remove the equal mark). And then, you could get the Field name in the
> Parameter.
> Please do the above and let me know the result.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
>|||Hi Winkles,
Yes, it correspond to the metadata column in the stored procedure.
The TopLevelArea in the Value column is the Friendly Name you could use in
the Report. You could modify it with other name.
Here is the article for your reference:
Fields
Each dataset in a report contains a list of fields. Typically, the fields
refer to columns or fields returned by the query in the dataset. Fields
that refer to database fields contain a pointer to the database field and a
name property. You can use the name property to provide a friendly name in
place of the name of the database field. In addition to database fields,
the fields list can contain calculated fields. Calculated fields contain a
name and an expression. The expression can be as simple as a concatenation
of two database fields (for example, first name and last name), or it can
be used to perform complex calculations.
Some query languages are flexible enough so that a query can be written to
return friendly field names and perform calculations, making changes to the
fields list unnecessary. The fields list is especially useful when using a
database or query language that does not provide this flexibility.
http://msdn2.microsoft.com/en-us/library/ms160324.aspx
If you have any questions or concerns, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Supportsql
I wrote a stored procedure in C# to return results that I could use to
populate a report parameter list. The problem occurs when I set up the
parameter. Under "Report Parameters -> Available Values -> From Query", I
can set the dataset fine but no entries appear under the Value field or the
Label field. When I run the query (whether it's thru the data tab or in SQL
server management Studio or the SQL Server project), I get the correct
results. If I try to type in the name of the field, I get an
rsInvalidDataSetReferenceField error when I preview the report.
Below is the steps I took to get to where I'm at:
1.
The stored procedure was supposed to extract certain areas from the area
path, with the TFSWarehouse as my data source. The areas of interest were
top-level areas, i.e. \\TeamProject\TopLevelArea\SubAreas... The
CommandText attribute of my SqlCommand instance is as follows:
@."SELECT DISTINCT Area.[Area Path]
FROM Area INNER JOIN
[Current Work Item] ON Area.__ID = [Current Work
Item].Area";
2.
The results are returned in an SqlDataReader. I read thru each record of
the reader and manipulate some of the data and send it back using
SqlContext.Pipe.SendResultsStart/Row/End when appropriate. The structure of
the record consists of one column, called "TopLevelArea".
3.
I compiled it into an assembly using the Visual Studio command prompt, ran a
T-SQL query to create the procedure, so that it appears in the Stored
Procedures list. And then I created a dataset whose command type is
StoredProcedure, and it contains the name of the procedure. Then I set up
the parameter and that's where everything went all wrong.
Please help.Hi Winkles,
Would you please try to check the DataSet Properties?
Make sure the Field have set a proper name in the data tab of the Report
Project.
Also, please make sure you have set the correct dataset name when you
config the report parameter.
If this does not help, would you please send the sample stored procedure to
me for troubleshooting? Thanks!
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hello Winkles,
I have tested on my side with your code.
You need to set the Field tab in the DataSet properties.
In the Field Name, type TopLevelArea, and type TopLevelArea in the Value.
(Remove the equal mark). And then, you could get the Field name in the
Parameter.
Please do the above and let me know the result.
Sincerely,
Wei Lu
Microsoft Online Community Support|||Hi Wei,
It worked!!! Thank you very much! I had been including the equal sign
before.
The 'TopLevelArea' in Field Name...does that correspond to the metadata
column in the stored procedure? What does the TopLevelArea in the 'Value'
column match up with? Or do they both refer to the same thing?
Thanks again.
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:1BHmMqBwGHA.5976@.TK2MSFTNGXA01.phx.gbl...
> Hello Winkles,
> I have tested on my side with your code.
> You need to set the Field tab in the DataSet properties.
> In the Field Name, type TopLevelArea, and type TopLevelArea in the Value.
> (Remove the equal mark). And then, you could get the Field name in the
> Parameter.
> Please do the above and let me know the result.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
>|||Hi Winkles,
Yes, it correspond to the metadata column in the stored procedure.
The TopLevelArea in the Value column is the Friendly Name you could use in
the Report. You could modify it with other name.
Here is the article for your reference:
Fields
Each dataset in a report contains a list of fields. Typically, the fields
refer to columns or fields returned by the query in the dataset. Fields
that refer to database fields contain a pointer to the database field and a
name property. You can use the name property to provide a friendly name in
place of the name of the database field. In addition to database fields,
the fields list can contain calculated fields. Calculated fields contain a
name and an expression. The expression can be as simple as a concatenation
of two database fields (for example, first name and last name), or it can
be used to perform complex calculations.
Some query languages are flexible enough so that a query can be written to
return friendly field names and perform calculations, making changes to the
fields list unnecessary. The fields list is especially useful when using a
database or query language that does not provide this flexibility.
http://msdn2.microsoft.com/en-us/library/ms160324.aspx
If you have any questions or concerns, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Supportsql
Report Parameter Problem
I have a report based on a stored procedure with three parameters: Start
Date, End Date, and Salesperson.
The first two are required, and the Salesperson is not. In the report, the
Salesperson is based on a query which provides a drop down for choosing a
name. It is set to allow nulls and blanks.
When I run the report in Visual Studio preview, if no Salesperson is chosen,
and both dates are entered, no data is returned. Doing the same thing via
debug in a browser, (the drop down is not blank, but says <Selelct a value>),
I get prompted to supply the Salesperson parameter.
Supplying a value for Salesperson in both cases works fine, but I want the
user to have the option to get all the data, not just for a particular
person. I've covered this case in the stored procedure, so I'm not sure what
is getting passed in the previewer when nothing is entered and no data is
returned.
If I run the query (sp) via the Data tab, and provide exactly the same
values for the parameters, I get the data I expect, and no prompt for the
Salesperson.
If I put a default value in for the Salesperson, (non-queried), e.g. "All",
the preview in VS goes crazy, and locks up.
I would have thought with allowing nulls and/or blanks, this should work. Am
I missing something?
Thanks for any help,
TomIf Salesperson parameter uses available values from query, then the query
must return null or blank as one of available values.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"TomT" <tomt@.newsgroup.nospam> wrote in message
news:87D78917-4603-4339-AD5B-C4C3377BA9AA@.microsoft.com...
>I have a report based on a stored procedure with three parameters: Start
> Date, End Date, and Salesperson.
> The first two are required, and the Salesperson is not. In the report, the
> Salesperson is based on a query which provides a drop down for choosing a
> name. It is set to allow nulls and blanks.
> When I run the report in Visual Studio preview, if no Salesperson is
> chosen,
> and both dates are entered, no data is returned. Doing the same thing via
> debug in a browser, (the drop down is not blank, but says <Selelct a
> value>),
> I get prompted to supply the Salesperson parameter.
> Supplying a value for Salesperson in both cases works fine, but I want the
> user to have the option to get all the data, not just for a particular
> person. I've covered this case in the stored procedure, so I'm not sure
> what
> is getting passed in the previewer when nothing is entered and no data is
> returned.
> If I run the query (sp) via the Data tab, and provide exactly the same
> values for the parameters, I get the data I expect, and no prompt for the
> Salesperson.
> If I put a default value in for the Salesperson, (non-queried), e.g.
> "All",
> the preview in VS goes crazy, and locks up.
> I would have thought with allowing nulls and/or blanks, this should work.
> Am
> I missing something?
> Thanks for any help,
> Tom|||Lev, thanks, I was assuming if no choice was made (drop down blank) a null
would be passed. I fixed it up to provide a value simulating a null
situation, and it now works fine.
Thanks for your quick response...
Tom
"Lev Semenets [MSFT]" wrote:
> If Salesperson parameter uses available values from query, then the query
> must return null or blank as one of available values.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "TomT" <tomt@.newsgroup.nospam> wrote in message
> news:87D78917-4603-4339-AD5B-C4C3377BA9AA@.microsoft.com...
> >I have a report based on a stored procedure with three parameters: Start
> > Date, End Date, and Salesperson.
> >
> > The first two are required, and the Salesperson is not. In the report, the
> > Salesperson is based on a query which provides a drop down for choosing a
> > name. It is set to allow nulls and blanks.
> >
> > When I run the report in Visual Studio preview, if no Salesperson is
> > chosen,
> > and both dates are entered, no data is returned. Doing the same thing via
> > debug in a browser, (the drop down is not blank, but says <Selelct a
> > value>),
> > I get prompted to supply the Salesperson parameter.
> >
> > Supplying a value for Salesperson in both cases works fine, but I want the
> > user to have the option to get all the data, not just for a particular
> > person. I've covered this case in the stored procedure, so I'm not sure
> > what
> > is getting passed in the previewer when nothing is entered and no data is
> > returned.
> >
> > If I run the query (sp) via the Data tab, and provide exactly the same
> > values for the parameters, I get the data I expect, and no prompt for the
> > Salesperson.
> >
> > If I put a default value in for the Salesperson, (non-queried), e.g.
> > "All",
> > the preview in VS goes crazy, and locks up.
> >
> > I would have thought with allowing nulls and/or blanks, this should work.
> > Am
> > I missing something?
> >
> > Thanks for any help,
> >
> > Tom
>
>|||TomT
Could you please tell me how did you simulate a value of null. I am running
into exactly same problem as yours where I cant make paramter optional as
Report Manager forces me to choose a value although I have defined null and
blank as options for the parameter.
TIA
"TomT" wrote:
> Lev, thanks, I was assuming if no choice was made (drop down blank) a null
> would be passed. I fixed it up to provide a value simulating a null
> situation, and it now works fine.
> Thanks for your quick response...
> Tom
> "Lev Semenets [MSFT]" wrote:
> > If Salesperson parameter uses available values from query, then the query
> > must return null or blank as one of available values.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "TomT" <tomt@.newsgroup.nospam> wrote in message
> > news:87D78917-4603-4339-AD5B-C4C3377BA9AA@.microsoft.com...
> > >I have a report based on a stored procedure with three parameters: Start
> > > Date, End Date, and Salesperson.
> > >
> > > The first two are required, and the Salesperson is not. In the report, the
> > > Salesperson is based on a query which provides a drop down for choosing a
> > > name. It is set to allow nulls and blanks.
> > >
> > > When I run the report in Visual Studio preview, if no Salesperson is
> > > chosen,
> > > and both dates are entered, no data is returned. Doing the same thing via
> > > debug in a browser, (the drop down is not blank, but says <Selelct a
> > > value>),
> > > I get prompted to supply the Salesperson parameter.
> > >
> > > Supplying a value for Salesperson in both cases works fine, but I want the
> > > user to have the option to get all the data, not just for a particular
> > > person. I've covered this case in the stored procedure, so I'm not sure
> > > what
> > > is getting passed in the previewer when nothing is entered and no data is
> > > returned.
> > >
> > > If I run the query (sp) via the Data tab, and provide exactly the same
> > > values for the parameters, I get the data I expect, and no prompt for the
> > > Salesperson.
> > >
> > > If I put a default value in for the Salesperson, (non-queried), e.g.
> > > "All",
> > > the preview in VS goes crazy, and locks up.
> > >
> > > I would have thought with allowing nulls and/or blanks, this should work.
> > > Am
> > > I missing something?
> > >
> > > Thanks for any help,
> > >
> > > Tom
> >
> >
> >|||TomT
Could you please tell me how did you simulate a value of null. I am running
into exactly same problem as yours where I cant make paramter optional as
Report Manager forces me to choose a value although I have defined null and
blank as options for the parameter.
TIA
"TomT" wrote:
> Lev, thanks, I was assuming if no choice was made (drop down blank) a null
> would be passed. I fixed it up to provide a value simulating a null
> situation, and it now works fine.
> Thanks for your quick response...
> Tom
> "Lev Semenets [MSFT]" wrote:
> > If Salesperson parameter uses available values from query, then the query
> > must return null or blank as one of available values.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "TomT" <tomt@.newsgroup.nospam> wrote in message
> > news:87D78917-4603-4339-AD5B-C4C3377BA9AA@.microsoft.com...
> > >I have a report based on a stored procedure with three parameters: Start
> > > Date, End Date, and Salesperson.
> > >
> > > The first two are required, and the Salesperson is not. In the report, the
> > > Salesperson is based on a query which provides a drop down for choosing a
> > > name. It is set to allow nulls and blanks.
> > >
> > > When I run the report in Visual Studio preview, if no Salesperson is
> > > chosen,
> > > and both dates are entered, no data is returned. Doing the same thing via
> > > debug in a browser, (the drop down is not blank, but says <Selelct a
> > > value>),
> > > I get prompted to supply the Salesperson parameter.
> > >
> > > Supplying a value for Salesperson in both cases works fine, but I want the
> > > user to have the option to get all the data, not just for a particular
> > > person. I've covered this case in the stored procedure, so I'm not sure
> > > what
> > > is getting passed in the previewer when nothing is entered and no data is
> > > returned.
> > >
> > > If I run the query (sp) via the Data tab, and provide exactly the same
> > > values for the parameters, I get the data I expect, and no prompt for the
> > > Salesperson.
> > >
> > > If I put a default value in for the Salesperson, (non-queried), e.g.
> > > "All",
> > > the preview in VS goes crazy, and locks up.
> > >
> > > I would have thought with allowing nulls and/or blanks, this should work.
> > > Am
> > > I missing something?
> > >
> > > Thanks for any help,
> > >
> > > Tom
> >
> >
> >|||TomT
Could you please tell me how did you simulate a value of null. I am running
into exactly same problem as yours where I cant make paramter optional as
Report Manager forces me to choose a value although I have defined null and
blank as options for the parameter.
TIA
"TomT" wrote:
> I have a report based on a stored procedure with three parameters: Start
> Date, End Date, and Salesperson.
> The first two are required, and the Salesperson is not. In the report, the
> Salesperson is based on a query which provides a drop down for choosing a
> name. It is set to allow nulls and blanks.
> When I run the report in Visual Studio preview, if no Salesperson is chosen,
> and both dates are entered, no data is returned. Doing the same thing via
> debug in a browser, (the drop down is not blank, but says <Selelct a value>),
> I get prompted to supply the Salesperson parameter.
> Supplying a value for Salesperson in both cases works fine, but I want the
> user to have the option to get all the data, not just for a particular
> person. I've covered this case in the stored procedure, so I'm not sure what
> is getting passed in the previewer when nothing is entered and no data is
> returned.
> If I run the query (sp) via the Data tab, and provide exactly the same
> values for the parameters, I get the data I expect, and no prompt for the
> Salesperson.
> If I put a default value in for the Salesperson, (non-queried), e.g. "All",
> the preview in VS goes crazy, and locks up.
> I would have thought with allowing nulls and/or blanks, this should work. Am
> I missing something?
> Thanks for any help,
> Tom|||Did you set default value for the parameter?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"tiwanap" <tiwanap@.discussions.microsoft.com> wrote in message
news:7C84F377-9ECA-4A87-AC8F-E936353B917A@.microsoft.com...
> TomT
> Could you please tell me how did you simulate a value of null. I am
> running
> into exactly same problem as yours where I cant make paramter optional as
> Report Manager forces me to choose a value although I have defined null
> and
> blank as options for the parameter.
> TIA
> "TomT" wrote:
>> I have a report based on a stored procedure with three parameters: Start
>> Date, End Date, and Salesperson.
>> The first two are required, and the Salesperson is not. In the report,
>> the
>> Salesperson is based on a query which provides a drop down for choosing a
>> name. It is set to allow nulls and blanks.
>> When I run the report in Visual Studio preview, if no Salesperson is
>> chosen,
>> and both dates are entered, no data is returned. Doing the same thing via
>> debug in a browser, (the drop down is not blank, but says <Selelct a
>> value>),
>> I get prompted to supply the Salesperson parameter.
>> Supplying a value for Salesperson in both cases works fine, but I want
>> the
>> user to have the option to get all the data, not just for a particular
>> person. I've covered this case in the stored procedure, so I'm not sure
>> what
>> is getting passed in the previewer when nothing is entered and no data is
>> returned.
>> If I run the query (sp) via the Data tab, and provide exactly the same
>> values for the parameters, I get the data I expect, and no prompt for the
>> Salesperson.
>> If I put a default value in for the Salesperson, (non-queried), e.g.
>> "All",
>> the preview in VS goes crazy, and locks up.
>> I would have thought with allowing nulls and/or blanks, this should work.
>> Am
>> I missing something?
>> Thanks for any help,
>> Tom
Date, End Date, and Salesperson.
The first two are required, and the Salesperson is not. In the report, the
Salesperson is based on a query which provides a drop down for choosing a
name. It is set to allow nulls and blanks.
When I run the report in Visual Studio preview, if no Salesperson is chosen,
and both dates are entered, no data is returned. Doing the same thing via
debug in a browser, (the drop down is not blank, but says <Selelct a value>),
I get prompted to supply the Salesperson parameter.
Supplying a value for Salesperson in both cases works fine, but I want the
user to have the option to get all the data, not just for a particular
person. I've covered this case in the stored procedure, so I'm not sure what
is getting passed in the previewer when nothing is entered and no data is
returned.
If I run the query (sp) via the Data tab, and provide exactly the same
values for the parameters, I get the data I expect, and no prompt for the
Salesperson.
If I put a default value in for the Salesperson, (non-queried), e.g. "All",
the preview in VS goes crazy, and locks up.
I would have thought with allowing nulls and/or blanks, this should work. Am
I missing something?
Thanks for any help,
TomIf Salesperson parameter uses available values from query, then the query
must return null or blank as one of available values.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"TomT" <tomt@.newsgroup.nospam> wrote in message
news:87D78917-4603-4339-AD5B-C4C3377BA9AA@.microsoft.com...
>I have a report based on a stored procedure with three parameters: Start
> Date, End Date, and Salesperson.
> The first two are required, and the Salesperson is not. In the report, the
> Salesperson is based on a query which provides a drop down for choosing a
> name. It is set to allow nulls and blanks.
> When I run the report in Visual Studio preview, if no Salesperson is
> chosen,
> and both dates are entered, no data is returned. Doing the same thing via
> debug in a browser, (the drop down is not blank, but says <Selelct a
> value>),
> I get prompted to supply the Salesperson parameter.
> Supplying a value for Salesperson in both cases works fine, but I want the
> user to have the option to get all the data, not just for a particular
> person. I've covered this case in the stored procedure, so I'm not sure
> what
> is getting passed in the previewer when nothing is entered and no data is
> returned.
> If I run the query (sp) via the Data tab, and provide exactly the same
> values for the parameters, I get the data I expect, and no prompt for the
> Salesperson.
> If I put a default value in for the Salesperson, (non-queried), e.g.
> "All",
> the preview in VS goes crazy, and locks up.
> I would have thought with allowing nulls and/or blanks, this should work.
> Am
> I missing something?
> Thanks for any help,
> Tom|||Lev, thanks, I was assuming if no choice was made (drop down blank) a null
would be passed. I fixed it up to provide a value simulating a null
situation, and it now works fine.
Thanks for your quick response...
Tom
"Lev Semenets [MSFT]" wrote:
> If Salesperson parameter uses available values from query, then the query
> must return null or blank as one of available values.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "TomT" <tomt@.newsgroup.nospam> wrote in message
> news:87D78917-4603-4339-AD5B-C4C3377BA9AA@.microsoft.com...
> >I have a report based on a stored procedure with three parameters: Start
> > Date, End Date, and Salesperson.
> >
> > The first two are required, and the Salesperson is not. In the report, the
> > Salesperson is based on a query which provides a drop down for choosing a
> > name. It is set to allow nulls and blanks.
> >
> > When I run the report in Visual Studio preview, if no Salesperson is
> > chosen,
> > and both dates are entered, no data is returned. Doing the same thing via
> > debug in a browser, (the drop down is not blank, but says <Selelct a
> > value>),
> > I get prompted to supply the Salesperson parameter.
> >
> > Supplying a value for Salesperson in both cases works fine, but I want the
> > user to have the option to get all the data, not just for a particular
> > person. I've covered this case in the stored procedure, so I'm not sure
> > what
> > is getting passed in the previewer when nothing is entered and no data is
> > returned.
> >
> > If I run the query (sp) via the Data tab, and provide exactly the same
> > values for the parameters, I get the data I expect, and no prompt for the
> > Salesperson.
> >
> > If I put a default value in for the Salesperson, (non-queried), e.g.
> > "All",
> > the preview in VS goes crazy, and locks up.
> >
> > I would have thought with allowing nulls and/or blanks, this should work.
> > Am
> > I missing something?
> >
> > Thanks for any help,
> >
> > Tom
>
>|||TomT
Could you please tell me how did you simulate a value of null. I am running
into exactly same problem as yours where I cant make paramter optional as
Report Manager forces me to choose a value although I have defined null and
blank as options for the parameter.
TIA
"TomT" wrote:
> Lev, thanks, I was assuming if no choice was made (drop down blank) a null
> would be passed. I fixed it up to provide a value simulating a null
> situation, and it now works fine.
> Thanks for your quick response...
> Tom
> "Lev Semenets [MSFT]" wrote:
> > If Salesperson parameter uses available values from query, then the query
> > must return null or blank as one of available values.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "TomT" <tomt@.newsgroup.nospam> wrote in message
> > news:87D78917-4603-4339-AD5B-C4C3377BA9AA@.microsoft.com...
> > >I have a report based on a stored procedure with three parameters: Start
> > > Date, End Date, and Salesperson.
> > >
> > > The first two are required, and the Salesperson is not. In the report, the
> > > Salesperson is based on a query which provides a drop down for choosing a
> > > name. It is set to allow nulls and blanks.
> > >
> > > When I run the report in Visual Studio preview, if no Salesperson is
> > > chosen,
> > > and both dates are entered, no data is returned. Doing the same thing via
> > > debug in a browser, (the drop down is not blank, but says <Selelct a
> > > value>),
> > > I get prompted to supply the Salesperson parameter.
> > >
> > > Supplying a value for Salesperson in both cases works fine, but I want the
> > > user to have the option to get all the data, not just for a particular
> > > person. I've covered this case in the stored procedure, so I'm not sure
> > > what
> > > is getting passed in the previewer when nothing is entered and no data is
> > > returned.
> > >
> > > If I run the query (sp) via the Data tab, and provide exactly the same
> > > values for the parameters, I get the data I expect, and no prompt for the
> > > Salesperson.
> > >
> > > If I put a default value in for the Salesperson, (non-queried), e.g.
> > > "All",
> > > the preview in VS goes crazy, and locks up.
> > >
> > > I would have thought with allowing nulls and/or blanks, this should work.
> > > Am
> > > I missing something?
> > >
> > > Thanks for any help,
> > >
> > > Tom
> >
> >
> >|||TomT
Could you please tell me how did you simulate a value of null. I am running
into exactly same problem as yours where I cant make paramter optional as
Report Manager forces me to choose a value although I have defined null and
blank as options for the parameter.
TIA
"TomT" wrote:
> Lev, thanks, I was assuming if no choice was made (drop down blank) a null
> would be passed. I fixed it up to provide a value simulating a null
> situation, and it now works fine.
> Thanks for your quick response...
> Tom
> "Lev Semenets [MSFT]" wrote:
> > If Salesperson parameter uses available values from query, then the query
> > must return null or blank as one of available values.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "TomT" <tomt@.newsgroup.nospam> wrote in message
> > news:87D78917-4603-4339-AD5B-C4C3377BA9AA@.microsoft.com...
> > >I have a report based on a stored procedure with three parameters: Start
> > > Date, End Date, and Salesperson.
> > >
> > > The first two are required, and the Salesperson is not. In the report, the
> > > Salesperson is based on a query which provides a drop down for choosing a
> > > name. It is set to allow nulls and blanks.
> > >
> > > When I run the report in Visual Studio preview, if no Salesperson is
> > > chosen,
> > > and both dates are entered, no data is returned. Doing the same thing via
> > > debug in a browser, (the drop down is not blank, but says <Selelct a
> > > value>),
> > > I get prompted to supply the Salesperson parameter.
> > >
> > > Supplying a value for Salesperson in both cases works fine, but I want the
> > > user to have the option to get all the data, not just for a particular
> > > person. I've covered this case in the stored procedure, so I'm not sure
> > > what
> > > is getting passed in the previewer when nothing is entered and no data is
> > > returned.
> > >
> > > If I run the query (sp) via the Data tab, and provide exactly the same
> > > values for the parameters, I get the data I expect, and no prompt for the
> > > Salesperson.
> > >
> > > If I put a default value in for the Salesperson, (non-queried), e.g.
> > > "All",
> > > the preview in VS goes crazy, and locks up.
> > >
> > > I would have thought with allowing nulls and/or blanks, this should work.
> > > Am
> > > I missing something?
> > >
> > > Thanks for any help,
> > >
> > > Tom
> >
> >
> >|||TomT
Could you please tell me how did you simulate a value of null. I am running
into exactly same problem as yours where I cant make paramter optional as
Report Manager forces me to choose a value although I have defined null and
blank as options for the parameter.
TIA
"TomT" wrote:
> I have a report based on a stored procedure with three parameters: Start
> Date, End Date, and Salesperson.
> The first two are required, and the Salesperson is not. In the report, the
> Salesperson is based on a query which provides a drop down for choosing a
> name. It is set to allow nulls and blanks.
> When I run the report in Visual Studio preview, if no Salesperson is chosen,
> and both dates are entered, no data is returned. Doing the same thing via
> debug in a browser, (the drop down is not blank, but says <Selelct a value>),
> I get prompted to supply the Salesperson parameter.
> Supplying a value for Salesperson in both cases works fine, but I want the
> user to have the option to get all the data, not just for a particular
> person. I've covered this case in the stored procedure, so I'm not sure what
> is getting passed in the previewer when nothing is entered and no data is
> returned.
> If I run the query (sp) via the Data tab, and provide exactly the same
> values for the parameters, I get the data I expect, and no prompt for the
> Salesperson.
> If I put a default value in for the Salesperson, (non-queried), e.g. "All",
> the preview in VS goes crazy, and locks up.
> I would have thought with allowing nulls and/or blanks, this should work. Am
> I missing something?
> Thanks for any help,
> Tom|||Did you set default value for the parameter?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"tiwanap" <tiwanap@.discussions.microsoft.com> wrote in message
news:7C84F377-9ECA-4A87-AC8F-E936353B917A@.microsoft.com...
> TomT
> Could you please tell me how did you simulate a value of null. I am
> running
> into exactly same problem as yours where I cant make paramter optional as
> Report Manager forces me to choose a value although I have defined null
> and
> blank as options for the parameter.
> TIA
> "TomT" wrote:
>> I have a report based on a stored procedure with three parameters: Start
>> Date, End Date, and Salesperson.
>> The first two are required, and the Salesperson is not. In the report,
>> the
>> Salesperson is based on a query which provides a drop down for choosing a
>> name. It is set to allow nulls and blanks.
>> When I run the report in Visual Studio preview, if no Salesperson is
>> chosen,
>> and both dates are entered, no data is returned. Doing the same thing via
>> debug in a browser, (the drop down is not blank, but says <Selelct a
>> value>),
>> I get prompted to supply the Salesperson parameter.
>> Supplying a value for Salesperson in both cases works fine, but I want
>> the
>> user to have the option to get all the data, not just for a particular
>> person. I've covered this case in the stored procedure, so I'm not sure
>> what
>> is getting passed in the previewer when nothing is entered and no data is
>> returned.
>> If I run the query (sp) via the Data tab, and provide exactly the same
>> values for the parameters, I get the data I expect, and no prompt for the
>> Salesperson.
>> If I put a default value in for the Salesperson, (non-queried), e.g.
>> "All",
>> the preview in VS goes crazy, and locks up.
>> I would have thought with allowing nulls and/or blanks, this should work.
>> Am
>> I missing something?
>> Thanks for any help,
>> Tom
Report Parameter help
In am trying to create a report using a stored procedure having two
parameters. The Avalilable values for the parameters come from queries. The
user while running the report may not select one of the parameters i.e. leave
it blank. When I leave one of the parameters blank the report does not run, I
have to select some value for both parameters for report to run.
Can Someone let me know how to run a report without passing a value in
parameter.
Thanks
AnoopHi Anoop
You will always have to pass the parameter to the report because the
stored procedure is expecting it and won't run unless it has it.
The way round this is to pass a null value and allow for it in your
stored procedure.
Wherever you use the parameter in the stored procedure, you could use
the following syntax:
(@.QueueId IS NULL OR QueueId = @.QueueId)
eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
This would mean that if the @.QueueId parameter is null, it will ignore
it and return all the queues.
Hope this helps?
Regards
Dave|||Hi Dave,
Thanks for the response.
I am already handling NULLs in the Stored procedure. The problem that I am
having is that my report parameters are populated using queries which do not
have null, also user may not select all report parameters. When user does not
select a parameter the report does not pass NULL to sp infact it does not run
at all. Is there a way around this.
Thanks and Regards,
Anoop
"Bungle" wrote:
> Hi Anoop
> You will always have to pass the parameter to the report because the
> stored procedure is expecting it and won't run unless it has it.
> The way round this is to pass a null value and allow for it in your
> stored procedure.
> Wherever you use the parameter in the stored procedure, you could use
> the following syntax:
> (@.QueueId IS NULL OR QueueId = @.QueueId)
> eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
> This would mean that if the @.QueueId parameter is null, it will ignore
> it and return all the queues.
> Hope this helps?
> Regards
> Dave
>|||Unless you allow null or blank in the report, or have a default value , the
user MUST select a value for each parameter or the report will not run...
If your issue is that you wish to account for Null or blanks, then the prior
respondant's method is commonly used, although it may perform badly..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Anoop" <Anoop@.discussions.microsoft.com> wrote in message
news:260B6167-9D77-40DC-83AE-1D172820E0E0@.microsoft.com...
> Hi Dave,
> Thanks for the response.
> I am already handling NULLs in the Stored procedure. The problem that I am
> having is that my report parameters are populated using queries which do
not
> have null, also user may not select all report parameters. When user does
not
> select a parameter the report does not pass NULL to sp infact it does not
run
> at all. Is there a way around this.
> Thanks and Regards,
> Anoop
>
> "Bungle" wrote:
> > Hi Anoop
> >
> > You will always have to pass the parameter to the report because the
> > stored procedure is expecting it and won't run unless it has it.
> >
> > The way round this is to pass a null value and allow for it in your
> > stored procedure.
> >
> > Wherever you use the parameter in the stored procedure, you could use
> > the following syntax:
> >
> > (@.QueueId IS NULL OR QueueId = @.QueueId)
> >
> > eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
> >
> > This would mean that if the @.QueueId parameter is null, it will ignore
> > it and return all the queues.
> >
> > Hope this helps?
> >
> > Regards
> >
> > Dave
> >
> >|||I think I cannot do what I am trying to do through reporting services as each
parameter must be supplied with a value for a report to run.
Thanks a lot for your help.
"Wayne Snyder" wrote:
> Unless you allow null or blank in the report, or have a default value , the
> user MUST select a value for each parameter or the report will not run...
> If your issue is that you wish to account for Null or blanks, then the prior
> respondant's method is commonly used, although it may perform badly..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Anoop" <Anoop@.discussions.microsoft.com> wrote in message
> news:260B6167-9D77-40DC-83AE-1D172820E0E0@.microsoft.com...
> > Hi Dave,
> > Thanks for the response.
> >
> > I am already handling NULLs in the Stored procedure. The problem that I am
> > having is that my report parameters are populated using queries which do
> not
> > have null, also user may not select all report parameters. When user does
> not
> > select a parameter the report does not pass NULL to sp infact it does not
> run
> > at all. Is there a way around this.
> >
> > Thanks and Regards,
> >
> > Anoop
> >
> >
> > "Bungle" wrote:
> >
> > > Hi Anoop
> > >
> > > You will always have to pass the parameter to the report because the
> > > stored procedure is expecting it and won't run unless it has it.
> > >
> > > The way round this is to pass a null value and allow for it in your
> > > stored procedure.
> > >
> > > Wherever you use the parameter in the stored procedure, you could use
> > > the following syntax:
> > >
> > > (@.QueueId IS NULL OR QueueId = @.QueueId)
> > >
> > > eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
> > >
> > > This would mean that if the @.QueueId parameter is null, it will ignore
> > > it and return all the queues.
> > >
> > > Hope this helps?
> > >
> > > Regards
> > >
> > > Dave
> > >
> > >
>
>|||Can you modify the query that loads the parameters to add another line like
'None' that has a value of NULL? This would be similar to the method used to
add an 'All' selection.
Actually, I may need to try this myself for 1 of my reports.
Neil
"Anoop" wrote:
> I think I cannot do what I am trying to do through reporting services as each
> parameter must be supplied with a value for a report to run.
> Thanks a lot for your help.
> "Wayne Snyder" wrote:
> > Unless you allow null or blank in the report, or have a default value , the
> > user MUST select a value for each parameter or the report will not run...
> >
> > If your issue is that you wish to account for Null or blanks, then the prior
> > respondant's method is commonly used, although it may perform badly..
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Anoop" <Anoop@.discussions.microsoft.com> wrote in message
> > news:260B6167-9D77-40DC-83AE-1D172820E0E0@.microsoft.com...
> > > Hi Dave,
> > > Thanks for the response.
> > >
> > > I am already handling NULLs in the Stored procedure. The problem that I am
> > > having is that my report parameters are populated using queries which do
> > not
> > > have null, also user may not select all report parameters. When user does
> > not
> > > select a parameter the report does not pass NULL to sp infact it does not
> > run
> > > at all. Is there a way around this.
> > >
> > > Thanks and Regards,
> > >
> > > Anoop
> > >
> > >
> > > "Bungle" wrote:
> > >
> > > > Hi Anoop
> > > >
> > > > You will always have to pass the parameter to the report because the
> > > > stored procedure is expecting it and won't run unless it has it.
> > > >
> > > > The way round this is to pass a null value and allow for it in your
> > > > stored procedure.
> > > >
> > > > Wherever you use the parameter in the stored procedure, you could use
> > > > the following syntax:
> > > >
> > > > (@.QueueId IS NULL OR QueueId = @.QueueId)
> > > >
> > > > eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
> > > >
> > > > This would mean that if the @.QueueId parameter is null, it will ignore
> > > > it and return all the queues.
> > > >
> > > > Hope this helps?
> > > >
> > > > Regards
> > > >
> > > > Dave
> > > >
> > > >
> >
> >
> >|||Hi,
I will try to be as clear as possible, excuse my english
What you will need to do is for the SP's that are used to generate the
parameter list add a value that will mean "All Possible value" like so:
create procedure <ParamList>
AS
declare @.paramtable TABLE (ordersequence TINYINT,
id INT,
VALUE VARCHAR(100))
insert into @.paramtable values (1,0,"All Possible values")
insert into @.paramtable
select 1,id,value
from table1
select id,value
from @.paramtable
order by ordersequence,value
Then you will need to setup your parameter in RS and define the default
value of this parameter to 0.
You will also need to modify your report SP to manage the case of the "All
Possible values" param value...
Something like this should work
Create Procedure <Report>
(
@.ParamValue INT
)
AS
select col1,col2,col3,col4
from table1
where col1 = case when @.ParamValue = 0 then col1
else @.ParamValue
end
HTH,
Aiwa
"Anoop" wrote:
> In am trying to create a report using a stored procedure having two
> parameters. The Avalilable values for the parameters come from queries. The
> user while running the report may not select one of the parameters i.e. leave
> it blank. When I leave one of the parameters blank the report does not run, I
> have to select some value for both parameters for report to run.
> Can Someone let me know how to run a report without passing a value in
> parameter.
> Thanks
> Anoop|||Hi,
Thanks for your help.
As I have 10 parameters for my report. I have now created tables which have
0 or blank as a value. These tables are used to populate the Lists for report
parameters. It is a long process but it is working.
Hope there was a simpler solution. Anyways..
Thanks
Anoop
"Aiwa" wrote:
> Hi,
> I will try to be as clear as possible, excuse my english
> What you will need to do is for the SP's that are used to generate the
> parameter list add a value that will mean "All Possible value" like so:
> create procedure <ParamList>
> AS
> declare @.paramtable TABLE (ordersequence TINYINT,
> id INT,
> VALUE VARCHAR(100))
> insert into @.paramtable values (1,0,"All Possible values")
> insert into @.paramtable
> select 1,id,value
> from table1
> select id,value
> from @.paramtable
> order by ordersequence,value
>
> Then you will need to setup your parameter in RS and define the default
> value of this parameter to 0.
> You will also need to modify your report SP to manage the case of the "All
> Possible values" param value...
> Something like this should work
> Create Procedure <Report>
> (
> @.ParamValue INT
> )
> AS
> select col1,col2,col3,col4
> from table1
> where col1 = case when @.ParamValue = 0 then col1
> else @.ParamValue
> end
> HTH,
> Aiwa
> "Anoop" wrote:
> > In am trying to create a report using a stored procedure having two
> > parameters. The Avalilable values for the parameters come from queries. The
> > user while running the report may not select one of the parameters i.e. leave
> > it blank. When I leave one of the parameters blank the report does not run, I
> > have to select some value for both parameters for report to run.
> >
> > Can Someone let me know how to run a report without passing a value in
> > parameter.
> >
> > Thanks
> > Anoopsql
parameters. The Avalilable values for the parameters come from queries. The
user while running the report may not select one of the parameters i.e. leave
it blank. When I leave one of the parameters blank the report does not run, I
have to select some value for both parameters for report to run.
Can Someone let me know how to run a report without passing a value in
parameter.
Thanks
AnoopHi Anoop
You will always have to pass the parameter to the report because the
stored procedure is expecting it and won't run unless it has it.
The way round this is to pass a null value and allow for it in your
stored procedure.
Wherever you use the parameter in the stored procedure, you could use
the following syntax:
(@.QueueId IS NULL OR QueueId = @.QueueId)
eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
This would mean that if the @.QueueId parameter is null, it will ignore
it and return all the queues.
Hope this helps?
Regards
Dave|||Hi Dave,
Thanks for the response.
I am already handling NULLs in the Stored procedure. The problem that I am
having is that my report parameters are populated using queries which do not
have null, also user may not select all report parameters. When user does not
select a parameter the report does not pass NULL to sp infact it does not run
at all. Is there a way around this.
Thanks and Regards,
Anoop
"Bungle" wrote:
> Hi Anoop
> You will always have to pass the parameter to the report because the
> stored procedure is expecting it and won't run unless it has it.
> The way round this is to pass a null value and allow for it in your
> stored procedure.
> Wherever you use the parameter in the stored procedure, you could use
> the following syntax:
> (@.QueueId IS NULL OR QueueId = @.QueueId)
> eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
> This would mean that if the @.QueueId parameter is null, it will ignore
> it and return all the queues.
> Hope this helps?
> Regards
> Dave
>|||Unless you allow null or blank in the report, or have a default value , the
user MUST select a value for each parameter or the report will not run...
If your issue is that you wish to account for Null or blanks, then the prior
respondant's method is commonly used, although it may perform badly..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Anoop" <Anoop@.discussions.microsoft.com> wrote in message
news:260B6167-9D77-40DC-83AE-1D172820E0E0@.microsoft.com...
> Hi Dave,
> Thanks for the response.
> I am already handling NULLs in the Stored procedure. The problem that I am
> having is that my report parameters are populated using queries which do
not
> have null, also user may not select all report parameters. When user does
not
> select a parameter the report does not pass NULL to sp infact it does not
run
> at all. Is there a way around this.
> Thanks and Regards,
> Anoop
>
> "Bungle" wrote:
> > Hi Anoop
> >
> > You will always have to pass the parameter to the report because the
> > stored procedure is expecting it and won't run unless it has it.
> >
> > The way round this is to pass a null value and allow for it in your
> > stored procedure.
> >
> > Wherever you use the parameter in the stored procedure, you could use
> > the following syntax:
> >
> > (@.QueueId IS NULL OR QueueId = @.QueueId)
> >
> > eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
> >
> > This would mean that if the @.QueueId parameter is null, it will ignore
> > it and return all the queues.
> >
> > Hope this helps?
> >
> > Regards
> >
> > Dave
> >
> >|||I think I cannot do what I am trying to do through reporting services as each
parameter must be supplied with a value for a report to run.
Thanks a lot for your help.
"Wayne Snyder" wrote:
> Unless you allow null or blank in the report, or have a default value , the
> user MUST select a value for each parameter or the report will not run...
> If your issue is that you wish to account for Null or blanks, then the prior
> respondant's method is commonly used, although it may perform badly..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Anoop" <Anoop@.discussions.microsoft.com> wrote in message
> news:260B6167-9D77-40DC-83AE-1D172820E0E0@.microsoft.com...
> > Hi Dave,
> > Thanks for the response.
> >
> > I am already handling NULLs in the Stored procedure. The problem that I am
> > having is that my report parameters are populated using queries which do
> not
> > have null, also user may not select all report parameters. When user does
> not
> > select a parameter the report does not pass NULL to sp infact it does not
> run
> > at all. Is there a way around this.
> >
> > Thanks and Regards,
> >
> > Anoop
> >
> >
> > "Bungle" wrote:
> >
> > > Hi Anoop
> > >
> > > You will always have to pass the parameter to the report because the
> > > stored procedure is expecting it and won't run unless it has it.
> > >
> > > The way round this is to pass a null value and allow for it in your
> > > stored procedure.
> > >
> > > Wherever you use the parameter in the stored procedure, you could use
> > > the following syntax:
> > >
> > > (@.QueueId IS NULL OR QueueId = @.QueueId)
> > >
> > > eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
> > >
> > > This would mean that if the @.QueueId parameter is null, it will ignore
> > > it and return all the queues.
> > >
> > > Hope this helps?
> > >
> > > Regards
> > >
> > > Dave
> > >
> > >
>
>|||Can you modify the query that loads the parameters to add another line like
'None' that has a value of NULL? This would be similar to the method used to
add an 'All' selection.
Actually, I may need to try this myself for 1 of my reports.
Neil
"Anoop" wrote:
> I think I cannot do what I am trying to do through reporting services as each
> parameter must be supplied with a value for a report to run.
> Thanks a lot for your help.
> "Wayne Snyder" wrote:
> > Unless you allow null or blank in the report, or have a default value , the
> > user MUST select a value for each parameter or the report will not run...
> >
> > If your issue is that you wish to account for Null or blanks, then the prior
> > respondant's method is commonly used, although it may perform badly..
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Anoop" <Anoop@.discussions.microsoft.com> wrote in message
> > news:260B6167-9D77-40DC-83AE-1D172820E0E0@.microsoft.com...
> > > Hi Dave,
> > > Thanks for the response.
> > >
> > > I am already handling NULLs in the Stored procedure. The problem that I am
> > > having is that my report parameters are populated using queries which do
> > not
> > > have null, also user may not select all report parameters. When user does
> > not
> > > select a parameter the report does not pass NULL to sp infact it does not
> > run
> > > at all. Is there a way around this.
> > >
> > > Thanks and Regards,
> > >
> > > Anoop
> > >
> > >
> > > "Bungle" wrote:
> > >
> > > > Hi Anoop
> > > >
> > > > You will always have to pass the parameter to the report because the
> > > > stored procedure is expecting it and won't run unless it has it.
> > > >
> > > > The way round this is to pass a null value and allow for it in your
> > > > stored procedure.
> > > >
> > > > Wherever you use the parameter in the stored procedure, you could use
> > > > the following syntax:
> > > >
> > > > (@.QueueId IS NULL OR QueueId = @.QueueId)
> > > >
> > > > eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
> > > >
> > > > This would mean that if the @.QueueId parameter is null, it will ignore
> > > > it and return all the queues.
> > > >
> > > > Hope this helps?
> > > >
> > > > Regards
> > > >
> > > > Dave
> > > >
> > > >
> >
> >
> >|||Hi,
I will try to be as clear as possible, excuse my english
What you will need to do is for the SP's that are used to generate the
parameter list add a value that will mean "All Possible value" like so:
create procedure <ParamList>
AS
declare @.paramtable TABLE (ordersequence TINYINT,
id INT,
VALUE VARCHAR(100))
insert into @.paramtable values (1,0,"All Possible values")
insert into @.paramtable
select 1,id,value
from table1
select id,value
from @.paramtable
order by ordersequence,value
Then you will need to setup your parameter in RS and define the default
value of this parameter to 0.
You will also need to modify your report SP to manage the case of the "All
Possible values" param value...
Something like this should work
Create Procedure <Report>
(
@.ParamValue INT
)
AS
select col1,col2,col3,col4
from table1
where col1 = case when @.ParamValue = 0 then col1
else @.ParamValue
end
HTH,
Aiwa
"Anoop" wrote:
> In am trying to create a report using a stored procedure having two
> parameters. The Avalilable values for the parameters come from queries. The
> user while running the report may not select one of the parameters i.e. leave
> it blank. When I leave one of the parameters blank the report does not run, I
> have to select some value for both parameters for report to run.
> Can Someone let me know how to run a report without passing a value in
> parameter.
> Thanks
> Anoop|||Hi,
Thanks for your help.
As I have 10 parameters for my report. I have now created tables which have
0 or blank as a value. These tables are used to populate the Lists for report
parameters. It is a long process but it is working.
Hope there was a simpler solution. Anyways..
Thanks
Anoop
"Aiwa" wrote:
> Hi,
> I will try to be as clear as possible, excuse my english
> What you will need to do is for the SP's that are used to generate the
> parameter list add a value that will mean "All Possible value" like so:
> create procedure <ParamList>
> AS
> declare @.paramtable TABLE (ordersequence TINYINT,
> id INT,
> VALUE VARCHAR(100))
> insert into @.paramtable values (1,0,"All Possible values")
> insert into @.paramtable
> select 1,id,value
> from table1
> select id,value
> from @.paramtable
> order by ordersequence,value
>
> Then you will need to setup your parameter in RS and define the default
> value of this parameter to 0.
> You will also need to modify your report SP to manage the case of the "All
> Possible values" param value...
> Something like this should work
> Create Procedure <Report>
> (
> @.ParamValue INT
> )
> AS
> select col1,col2,col3,col4
> from table1
> where col1 = case when @.ParamValue = 0 then col1
> else @.ParamValue
> end
> HTH,
> Aiwa
> "Anoop" wrote:
> > In am trying to create a report using a stored procedure having two
> > parameters. The Avalilable values for the parameters come from queries. The
> > user while running the report may not select one of the parameters i.e. leave
> > it blank. When I leave one of the parameters blank the report does not run, I
> > have to select some value for both parameters for report to run.
> >
> > Can Someone let me know how to run a report without passing a value in
> > parameter.
> >
> > Thanks
> > Anoopsql
Wednesday, March 28, 2012
Report parameter dropdown
Hello,
I need to create a MyPar parameter dropdown list, that is why I created a new dataset that gets data through a stored procedure. I also add @.MyPar into the query string of MyMainDataSet . I defined this parameter in Parameter screen for both available and default values.
When I run the report, I get the following error.
Query execution failed for data set MyMainDataSet.
Must declare the variable @.MyPar
What is problem?
Hello:
If I understand correctly:
The first stored procedure that displays the parameters for MyPar is a straight SQL Select Statement - So this SQL Select just gets the data for the available parameters to display in the dropdown list.
The next step the user selects one value from all of the available parameters in the dropdown list - so once this parameter is selected you need to pass this selected parameter to your next SQL Select statement so you only get the data based on the parameter the user selected!
Include the following in the Stored Procedure (if your are using one for the selection of data based on the parameter the user selected)
CREATE PROCEDURE "Whatever the Procedure name is"
@.MyPar nvarchar(10) or whatever length the parameter is
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
Hope this helps!
Best Regards - Joe
Report parameter
I have a stored procedure that works fine in reporting services. It grabs the total of Yes's and No's by dates . But then i went ahead and added 2 more parameters to the proc, and now the totals are all wrong. I dont understand how that can mess everything up. Here is the previous stored proc, that gives the correct sum. I just want to know what im doing wrong, that the totals are completely off now. Did i set up the parameter wrong in reporting services. I have the 3 parameters list in the report parameter section, and even have them cascading off of each other. That seems to work fine. but for the first matrix in my report, but the second matrix with this stored proc, is way off. Please help!!.
Code Snippet
ALTER PROCEDURE [dbo].[Testing_Questions_ALL_YESOrNO]
@.Question char(80)
AS
BEGIN
SELECT
Qry_Questions.Question
, Qry_Questions.Date
, Qry_Questions.response
, B.Total
FROM Qry_Questions
INNER JOIN Qry_Sales_Group
ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
INNER JOIN
( Select COUNT(qq.response)as Total, Question, Date, response
FROM Qry_Questions qq
Where qq.response in ('Yes','No')
GROUP by qq.[Question] , qq.Date,qq.response ) B
ON Qry_Questions.Date = B.Date AND
Qry_Questions.Question =B.Question and
Qry_Questions.response=B.response
WHERE Qry_Questions.[Response Type]='YesNo' and Qry_Questions.Question=@.Question
GROUP BY Qry_Questions.question,Qry_Questions.Date,Qry_questions.Response,B.Total
ORDER BY Qry_Questions.Question, Qry_Questions.Date
END
SET NOCOUNT OFF
Here is the edited version which only has two new parameters added to the proc. The edits are highlighted.
Code Snippet
ALTER PROCEDURE [dbo].[Testing_Questions_ALL_YESOrNO_Totals]
(@.Region_Key int=null,@.QuestionCode char(5),@.Question char(80))
AS
BEGIN
SELECT
Qry_Questions.Question
, Qry_Questions.Date
, Qry_Questions.response
, B.Total
FROM Qry_Questions
INNER JOIN Qry_Sales_Group
ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
INNER JOIN
( Select COUNT(qq.response)as Total, Question, Date, response
FROM Qry_Questions qq
Where qq.response in ('Yes','No')
GROUP by qq.[Question] , qq.Date,qq.response ) B
ON Qry_Questions.Date = B.Date AND
Qry_Questions.Question =B.Question and
Qry_Questions.response=B.response
WHERE Qry_Questions.[Response Type]='YesNo'
AND REGION_KEY=@.Region_Key
AND LEFT(Qry_Questions.[Question Code],2)IN (@.QuestionCode)
AND Qry_Questions.Question=@.Question
GROUP BY Qry_Questions.question,Qry_Questions.Date,Qry_questions.Response,B.Total
ORDER BY Qry_Questions.Question, Qry_Questions.Date
END
SET NOCOUNT OFF
Try this:
AND (@.Region_Key is null or REGION_KEY=@.Region_Key)
BobP
Tuesday, February 21, 2012
Report from prcedure that return multiple recordsets
Hi there,
I am creating a report from a stored procedure that returns
multiples record sets. For an example, my stored procedure is as follows
CREATE PROCEDURE MYPROCEDURE
AS
BEGIN
SELECT * FROM TABLE1
SELECT * FROM TABLE2
SELECT * FROM TABLE3
END
Now lets say I want to create a report that will display the
result of the query no 2 (which is SELECT * FROM TABLE2 in this example). How
can I do this? Is there any way to bind the second record set to the report
dataset with out changing the stored procedure?
I will appreciate any kind of suggestions on this
Thanks
Moim
yes,
you can do this in ur procedure itself. based upon what criteria you decide which query to run, pass that criteria to procedure as a parameter, and then use if condition to run the query
|||Suresh,
Thanks for your reply. But I am sorry I dont have a chance to modify the Stored procesdure. Those are written for a legacy software and they are running good for couple of years.
So can you or any one else find me an alternate to do this?
Anyway, thanks shuresh.
Thanks
Moim|||
Moim,
Unfortunately, SSRS doesn't support multiple recordsets being returned from a stored procedure. It will use the first resultset returned only.
Can you break up your master stored proc into 3 smaller stored procs, each called by separate reports with it's own resultset?
Jarret
|||Hmmm..seems I have to do that Jarret. Anyway thanks for your reply.|||Can you mark this one as answered so others can see that multiple recordsets aren't supported?
Thanks.
Jarret
|||Sure. Thanks|||Thanks Jarret for the answer. I did that earlier , but couldn't get my procedure to work by passing parameters to get the resulting resultset. Insted I have created different procedures and made it to work.
I didn't know that this is a constraint with SSRS.
Subscribe to:
Posts (Atom)