|
|
You're in the
sql server reporting services
group:Stored procedure parameters and default values
sql server reporting services:
stored procedure parameters. Later I add the query parameter. Not sure what the problem is that you are seeing. Bruce L-C "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message news:%23QFQ6ixfEHA.1092@TK2MSFTNGP11.phx.gbl... [quoted text, click to view] > Again, seeking confirmation that I'm on the right track: > > Say I have a stored procedure that I want to re-use in several different reports. For some, I want to > hard-wire the parameters without modifying the procedure code. What options do I have? > > Am I correct in thinking that if I use command type "stored procedure", I should *only* have the procedure > name as "command text". I.e., I cannot specify parameter values in the proc call? > > And if I use command type as "query string", RD will parse the string and figure out the parameters, so the > same goes here? > > If above is correct, what options do I have? > I can specify a default value in "Report", "Report Parameters", but that parameter will still be there when I > execute it from the "Preview" tab. I understand I can pass it in the URL when I deploy the report, but that > seems to be messy way for such a simple task. Any other options, am I missing the obvious? > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > >
From SQL Books online, not sure if this will help. The code below creates
default parameters for a sproc if none is supplied when called. CREATE PROCEDURE au_info2 @lastname varchar(30) = 'D%', @firstname varchar(18) = '%' AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname LIKE @firstname AND au_lname LIKE @lastname GO "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message news:uqcjBAyfEHA.3048@TK2MSFTNGP09.phx.gbl... [quoted text, click to view] > Hmm, perhaps I'm missing something. The environment creates one report parameter for each parameter to the > stored procedure. This is regardless of whether I hard code the proc parameter and use command type "SQL text" > instead of "stored procedure". And from there' there just don't seem to be a way to get rid of this parameter > when I execute the procedure (as the top, where RD ask for the parameter values). > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > "Bruce Loehle-Conger" <bruce_lcNOSPAM@hotmail.com> wrote in message > news:O4zEI7xfEHA.2812@tk2msftngp13.phx.gbl... > > When I use a stored procedure I usually test it first by hard coding the > > stored procedure parameters. Later I add the query parameter. Not sure what > > the problem is that you are seeing. > > > > Bruce L-C > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in > > message news:%23QFQ6ixfEHA.1092@TK2MSFTNGP11.phx.gbl... > > > Again, seeking confirmation that I'm on the right track: > > > > > > Say I have a stored procedure that I want to re-use in several different > > reports. For some, I want to > > > hard-wire the parameters without modifying the procedure code. What > > options do I have? > > > > > > Am I correct in thinking that if I use command type "stored procedure", I > > should *only* have the procedure > > > name as "command text". I.e., I cannot specify parameter values in the > > proc call? > > > > > > And if I use command type as "query string", RD will parse the string and > > figure out the parameters, so the > > > same goes here? > > > > > > If above is correct, what options do I have? > > > I can specify a default value in "Report", "Report Parameters", but that > > parameter will still be there when I > > > execute it from the "Preview" tab. I understand I can pass it in the URL > > when I deploy the report, but that > > > seems to be messy way for such a simple task. Any other options, am I > > missing the obvious? > > > > > > -- > > > Tibor Karaszi, SQL Server MVP > > > http://www.karaszi.com/sqlserver/default.asp > > > http://www.solidqualitylearning.com/ > > > > > > > > > > > > > > >
Again, seeking confirmation that I'm on the right track:
Say I have a stored procedure that I want to re-use in several different reports. For some, I want to hard-wire the parameters without modifying the procedure code. What options do I have? Am I correct in thinking that if I use command type "stored procedure", I should *only* have the procedure name as "command text". I.e., I cannot specify parameter values in the proc call? And if I use command type as "query string", RD will parse the string and figure out the parameters, so the same goes here? If above is correct, what options do I have? I can specify a default value in "Report", "Report Parameters", but that parameter will still be there when I execute it from the "Preview" tab. I understand I can pass it in the URL when I deploy the report, but that seems to be messy way for such a simple task. Any other options, am I missing the obvious? -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/
As you have noticed, the report designer will automatically detect "query"
parameters, generate corresponding report parameters, and link them together. E.g. the generated RDL file would contain something similar to this: <Query> <DataSourceName>DataSource1</DataSourceName> <CommandType>StoredProcedure</CommandType> <CommandText>get_report_viewing_frequency</CommandText> <QueryParameters> <QueryParameter Name="company_id"> <Value>=Parameters!company_id.Value</Value> </QueryParameter> </QueryParameters> </Query> Note: The query parameter value is an expression! You can always modify the linking between report parameters and query parameters (edit the dataset -> in the dataset dialog click on the Parameters tab). You could change the query parameter value expression to a constant value or an expression which does not depend on a report parameter. You can even remove automatically generated report parameters (VS menu: Report - Report Parameters). -- This posting is provided "AS IS" with no warranties, and confers no rights. "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message news:eMr9mw1fEHA.3556@TK2MSFTNGP12.phx.gbl... [quoted text, click to view] > Not what I'm looking for, I'm afraid. IT doesn't mater whether the proc has default values for the parameters > or not. The parameters will still be visible when you run the report. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > "Jeff Dillon" <jeff@removeemergencyreporting.com> wrote in message > news:ebWYIMzfEHA.2416@TK2MSFTNGP09.phx.gbl... > > From SQL Books online, not sure if this will help. The code below creates > > default parameters for a sproc if none is supplied when called. > > > > CREATE PROCEDURE au_info2 > > @lastname varchar(30) = 'D%', > > @firstname varchar(18) = '%' > > AS > > SELECT au_lname, au_fname, title, pub_name > > FROM authors a INNER JOIN titleauthor ta > > ON a.au_id = ta.au_id INNER JOIN titles t > > ON t.title_id = ta.title_id INNER JOIN publishers p > > ON t.pub_id = p.pub_id > > WHERE au_fname LIKE @firstname > > AND au_lname LIKE @lastname > > GO > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in > > message news:uqcjBAyfEHA.3048@TK2MSFTNGP09.phx.gbl... > > > Hmm, perhaps I'm missing something. The environment creates one report > > parameter for each parameter to the > > > stored procedure. This is regardless of whether I hard code the proc > > parameter and use command type "SQL text" > > > instead of "stored procedure". And from there' there just don't seem to be > > a way to get rid of this parameter > > > when I execute the procedure (as the top, where RD ask for the parameter > > values). > > > > > > -- > > > Tibor Karaszi, SQL Server MVP > > > http://www.karaszi.com/sqlserver/default.asp > > > http://www.solidqualitylearning.com/ > > > > > > > > > "Bruce Loehle-Conger" <bruce_lcNOSPAM@hotmail.com> wrote in message > > > news:O4zEI7xfEHA.2812@tk2msftngp13.phx.gbl... > > > > When I use a stored procedure I usually test it first by hard coding the > > > > stored procedure parameters. Later I add the query parameter. Not sure > > what > > > > the problem is that you are seeing. > > > > > > > > Bruce L-C > > > > > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote > > in > > > > message news:%23QFQ6ixfEHA.1092@TK2MSFTNGP11.phx.gbl... > > > > > Again, seeking confirmation that I'm on the right track: > > > > > > > > > > Say I have a stored procedure that I want to re-use in several > > different > > > > reports. For some, I want to > > > > > hard-wire the parameters without modifying the procedure code. What > > > > options do I have? > > > > > > > > > > Am I correct in thinking that if I use command type "stored > > procedure", I > > > > should *only* have the procedure > > > > > name as "command text". I.e., I cannot specify parameter values in the > > > > proc call? > > > > > > > > > > And if I use command type as "query string", RD will parse the string > > and > > > > figure out the parameters, so the > > > > > same goes here? > > > > > > > > > > If above is correct, what options do I have? > > > > > I can specify a default value in "Report", "Report Parameters", but > > that > > > > parameter will still be there when I > > > > > execute it from the "Preview" tab. I understand I can pass it in the > > URL > > > > when I deploy the report, but that > > > > > seems to be messy way for such a simple task. Any other options, am I > > > > missing the obvious? > > > > > > > > > > -- > > > > > Tibor Karaszi, SQL Server MVP > > > > > http://www.karaszi.com/sqlserver/default.asp > > > > > http://www.solidqualitylearning.com/ > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
Hmm, perhaps I'm missing something. The environment creates one report parameter for each parameter to the
stored procedure. This is regardless of whether I hard code the proc parameter and use command type "SQL text" instead of "stored procedure". And from there' there just don't seem to be a way to get rid of this parameter when I execute the procedure (as the top, where RD ask for the parameter values). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ [quoted text, click to view] "Bruce Loehle-Conger" <bruce_lcNOSPAM@hotmail.com> wrote in message news:O4zEI7xfEHA.2812@tk2msftngp13.phx.gbl... > When I use a stored procedure I usually test it first by hard coding the > stored procedure parameters. Later I add the query parameter. Not sure what > the problem is that you are seeing. > > Bruce L-C > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in > message news:%23QFQ6ixfEHA.1092@TK2MSFTNGP11.phx.gbl... > > Again, seeking confirmation that I'm on the right track: > > > > Say I have a stored procedure that I want to re-use in several different > reports. For some, I want to > > hard-wire the parameters without modifying the procedure code. What > options do I have? > > > > Am I correct in thinking that if I use command type "stored procedure", I > should *only* have the procedure > > name as "command text". I.e., I cannot specify parameter values in the > proc call? > > > > And if I use command type as "query string", RD will parse the string and > figure out the parameters, so the > > same goes here? > > > > If above is correct, what options do I have? > > I can specify a default value in "Report", "Report Parameters", but that > parameter will still be there when I > > execute it from the "Preview" tab. I understand I can pass it in the URL > when I deploy the report, but that > > seems to be messy way for such a simple task. Any other options, am I > missing the obvious? > > > > -- > > Tibor Karaszi, SQL Server MVP > > http://www.karaszi.com/sqlserver/default.asp > > http://www.solidqualitylearning.com/ > > > > > > > >
Not what I'm looking for, I'm afraid. IT doesn't mater whether the proc has default values for the parameters
or not. The parameters will still be visible when you run the report. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ [quoted text, click to view] "Jeff Dillon" <jeff@removeemergencyreporting.com> wrote in message news:ebWYIMzfEHA.2416@TK2MSFTNGP09.phx.gbl... > From SQL Books online, not sure if this will help. The code below creates > default parameters for a sproc if none is supplied when called. > > CREATE PROCEDURE au_info2 > @lastname varchar(30) = 'D%', > @firstname varchar(18) = '%' > AS > SELECT au_lname, au_fname, title, pub_name > FROM authors a INNER JOIN titleauthor ta > ON a.au_id = ta.au_id INNER JOIN titles t > ON t.title_id = ta.title_id INNER JOIN publishers p > ON t.pub_id = p.pub_id > WHERE au_fname LIKE @firstname > AND au_lname LIKE @lastname > GO > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in > message news:uqcjBAyfEHA.3048@TK2MSFTNGP09.phx.gbl... > > Hmm, perhaps I'm missing something. The environment creates one report > parameter for each parameter to the > > stored procedure. This is regardless of whether I hard code the proc > parameter and use command type "SQL text" > > instead of "stored procedure". And from there' there just don't seem to be > a way to get rid of this parameter > > when I execute the procedure (as the top, where RD ask for the parameter > values). > > > > -- > > Tibor Karaszi, SQL Server MVP > > http://www.karaszi.com/sqlserver/default.asp > > http://www.solidqualitylearning.com/ > > > > > > "Bruce Loehle-Conger" <bruce_lcNOSPAM@hotmail.com> wrote in message > > news:O4zEI7xfEHA.2812@tk2msftngp13.phx.gbl... > > > When I use a stored procedure I usually test it first by hard coding the > > > stored procedure parameters. Later I add the query parameter. Not sure > what > > > the problem is that you are seeing. > > > > > > Bruce L-C > > > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote > in > > > message news:%23QFQ6ixfEHA.1092@TK2MSFTNGP11.phx.gbl... > > > > Again, seeking confirmation that I'm on the right track: > > > > > > > > Say I have a stored procedure that I want to re-use in several > different > > > reports. For some, I want to > > > > hard-wire the parameters without modifying the procedure code. What > > > options do I have? > > > > > > > > Am I correct in thinking that if I use command type "stored > procedure", I > > > should *only* have the procedure > > > > name as "command text". I.e., I cannot specify parameter values in the > > > proc call? > > > > > > > > And if I use command type as "query string", RD will parse the string > and > > > figure out the parameters, so the > > > > same goes here? > > > > > > > > If above is correct, what options do I have? > > > > I can specify a default value in "Report", "Report Parameters", but > that > > > parameter will still be there when I > > > > execute it from the "Preview" tab. I understand I can pass it in the > URL > > > when I deploy the report, but that > > > > seems to be messy way for such a simple task. Any other options, am I > > > missing the obvious? > > > > > > > > -- > > > > Tibor Karaszi, SQL Server MVP > > > > http://www.karaszi.com/sqlserver/default.asp > > > > http://www.solidqualitylearning.com/ > > > > > > > > > > > > > > > > > > > > > > > >
This is probably the most common conceptual part of RS that people miss. The
difference between query parameters and report parameters (and in your case stored procedure parameters). RS is being nice and creating these things for you. Not just when you use stored procedures but also when you create any query with a parameter, it then creates a matching report parameter. But the point still is that they are two different things (three in the case of stored procedure). How they are defined, called, tied together is totally up to you. In report->report parameters you can rename them, remove them, change the order etc. If renamed, however, they do not fix it up with the query and you have to go to the dataset, click on the ... and then parameters tab and map the query parameter to the stored procedure parameter. Bruce L-C "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message news:eBBjJB3fEHA.3632@TK2MSFTNGP09.phx.gbl... [quoted text, click to view] > Thanks Robert. Will check it out.
> Being a SQL person, I guess I always try to do things at the SQL level first. :-) > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > "Robert Bruckner [MSFT]" <robruc@online.microsoft.com> wrote in message > news:eHhjvd2fEHA.3048@TK2MSFTNGP09.phx.gbl... > > As you have noticed, the report designer will automatically detect "query" > > parameters, generate corresponding report parameters, and link them > > together. E.g. the generated RDL file would contain something similar to > > this: > > > > <Query> > > <DataSourceName>DataSource1</DataSourceName> > > <CommandType>StoredProcedure</CommandType> > > <CommandText>get_report_viewing_frequency</CommandText> > > <QueryParameters> > > <QueryParameter Name="company_id"> > > <Value>=Parameters!company_id.Value</Value> > > </QueryParameter> > > </QueryParameters> > > </Query> > > > > Note: The query parameter value is an expression! > > You can always modify the linking between report parameters and query > > parameters (edit the dataset -> in the dataset dialog click on the > > Parameters tab). You could change the query parameter value expression to a > > constant value or an expression which does not depend on a report parameter. > > > > You can even remove automatically generated report parameters (VS menu: > > Report - Report Parameters). > > > > -- > > This posting is provided "AS IS" with no warranties, and confers no rights. > > > > > > > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in > > message news:eMr9mw1fEHA.3556@TK2MSFTNGP12.phx.gbl... > > > Not what I'm looking for, I'm afraid. IT doesn't mater whether the proc > > has default values for the parameters > > > or not. The parameters will still be visible when you run the report. > > > > > > -- > > > Tibor Karaszi, SQL Server MVP > > > http://www.karaszi.com/sqlserver/default.asp > > > http://www.solidqualitylearning.com/ > > > > > > > > > "Jeff Dillon" <jeff@removeemergencyreporting.com> wrote in message > > > news:ebWYIMzfEHA.2416@TK2MSFTNGP09.phx.gbl... > > > > From SQL Books online, not sure if this will help. The code below > > creates > > > > default parameters for a sproc if none is supplied when called. > > > > > > > > CREATE PROCEDURE au_info2 > > > > @lastname varchar(30) = 'D%', > > > > @firstname varchar(18) = '%' > > > > AS > > > > SELECT au_lname, au_fname, title, pub_name > > > > FROM authors a INNER JOIN titleauthor ta > > > > ON a.au_id = ta.au_id INNER JOIN titles t > > > > ON t.title_id = ta.title_id INNER JOIN publishers p > > > > ON t.pub_id = p.pub_id > > > > WHERE au_fname LIKE @firstname > > > > AND au_lname LIKE @lastname > > > > GO > > > > > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote > > in > > > > message news:uqcjBAyfEHA.3048@TK2MSFTNGP09.phx.gbl... > > > > > Hmm, perhaps I'm missing something. The environment creates one report > > > > parameter for each parameter to the > > > > > stored procedure. This is regardless of whether I hard code the proc > > > > parameter and use command type "SQL text" > > > > > instead of "stored procedure". And from there' there just don't seem > > to be > > > > a way to get rid of this parameter > > > > > when I execute the procedure (as the top, where RD ask for the > > parameter > > > > values). > > > > > > > > > > -- > > > > > Tibor Karaszi, SQL Server MVP > > > > > http://www.karaszi.com/sqlserver/default.asp > > > > > http://www.solidqualitylearning.com/ > > > > > > > > > > > > > > > "Bruce Loehle-Conger" <bruce_lcNOSPAM@hotmail.com> wrote in message > > > > > news:O4zEI7xfEHA.2812@tk2msftngp13.phx.gbl... > > > > > > When I use a stored procedure I usually test it first by hard coding > > the > > > > > > stored procedure parameters. Later I add the query parameter. Not > > sure > > > > what > > > > > > the problem is that you are seeing. > > > > > > > > > > > > Bruce L-C > > > > > > > > > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> > > wrote > > > > in > > > > > > message news:%23QFQ6ixfEHA.1092@TK2MSFTNGP11.phx.gbl... > > > > > > > Again, seeking confirmation that I'm on the right track: > > > > > > > > > > > > > > Say I have a stored procedure that I want to re-use in several > > > > different > > > > > > reports. For some, I want to > > > > > > > hard-wire the parameters without modifying the procedure code. > > What > > > > > > options do I have? > > > > > > > > > > > > > > Am I correct in thinking that if I use command type "stored > > > > procedure", I > > > > > > should *only* have the procedure > > > > > > > name as "command text". I.e., I cannot specify parameter values in > > the > > > > > > proc call? > > > > > > > > > > > > > > And if I use command type as "query string", RD will parse the > > string > > > > and > > > > > > figure out the parameters, so the > > > > > > > same goes here? > > > > > > > > > > > > > > If above is correct, what options do I have? > > > > > > > I can specify a default value in "Report", "Report Parameters", > > but > > > > that > > > > > > parameter will still be there when I > > > > > > > execute it from the "Preview" tab. I understand I can pass it in > > the > > > > URL > > > > > > when I deploy the report, but that > > > > > > > seems to be messy way for such a simple task. Any other options, > > am I > > > > > > missing the obvious? > > > > > > > > > > > > > > -- > > > > > > > Tibor Karaszi, SQL Server MVP > > > > > > > http://www.karaszi.com/sqlserver/default.asp > > > > > > > http://www.solidqualitylearning.com/ > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
Thanks Robert. Will check it out.
Being a SQL person, I guess I always try to do things at the SQL level first. :-) -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ [quoted text, click to view] "Robert Bruckner [MSFT]" <robruc@online.microsoft.com> wrote in message news:eHhjvd2fEHA.3048@TK2MSFTNGP09.phx.gbl... > As you have noticed, the report designer will automatically detect "query" > parameters, generate corresponding report parameters, and link them > together. E.g. the generated RDL file would contain something similar to > this: > > <Query> > <DataSourceName>DataSource1</DataSourceName> > <CommandType>StoredProcedure</CommandType> > <CommandText>get_report_viewing_frequency</CommandText> > <QueryParameters> > <QueryParameter Name="company_id"> > <Value>=Parameters!company_id.Value</Value> > </QueryParameter> > </QueryParameters> > </Query> > > Note: The query parameter value is an expression! > You can always modify the linking between report parameters and query > parameters (edit the dataset -> in the dataset dialog click on the > Parameters tab). You could change the query parameter value expression to a > constant value or an expression which does not depend on a report parameter. > > You can even remove automatically generated report parameters (VS menu: > Report - Report Parameters). > > -- > This posting is provided "AS IS" with no warranties, and confers no rights. > > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in > message news:eMr9mw1fEHA.3556@TK2MSFTNGP12.phx.gbl... > > Not what I'm looking for, I'm afraid. IT doesn't mater whether the proc > has default values for the parameters > > or not. The parameters will still be visible when you run the report. > > > > -- > > Tibor Karaszi, SQL Server MVP > > http://www.karaszi.com/sqlserver/default.asp > > http://www.solidqualitylearning.com/ > > > > > > "Jeff Dillon" <jeff@removeemergencyreporting.com> wrote in message > > news:ebWYIMzfEHA.2416@TK2MSFTNGP09.phx.gbl... > > > From SQL Books online, not sure if this will help. The code below > creates > > > default parameters for a sproc if none is supplied when called. > > > > > > CREATE PROCEDURE au_info2 > > > @lastname varchar(30) = 'D%', > > > @firstname varchar(18) = '%' > > > AS > > > SELECT au_lname, au_fname, title, pub_name > > > FROM authors a INNER JOIN titleauthor ta > > > ON a.au_id = ta.au_id INNER JOIN titles t > > > ON t.title_id = ta.title_id INNER JOIN publishers p > > > ON t.pub_id = p.pub_id > > > WHERE au_fname LIKE @firstname > > > AND au_lname LIKE @lastname > > > GO > > > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote > in > > > message news:uqcjBAyfEHA.3048@TK2MSFTNGP09.phx.gbl... > > > > Hmm, perhaps I'm missing something. The environment creates one report > > > parameter for each parameter to the > > > > stored procedure. This is regardless of whether I hard code the proc > > > parameter and use command type "SQL text" > > > > instead of "stored procedure". And from there' there just don't seem > to be > > > a way to get rid of this parameter > > > > when I execute the procedure (as the top, where RD ask for the > parameter > > > values). > > > > > > > > -- > > > > Tibor Karaszi, SQL Server MVP > > > > http://www.karaszi.com/sqlserver/default.asp > > > > http://www.solidqualitylearning.com/ > > > > > > > > > > > > "Bruce Loehle-Conger" <bruce_lcNOSPAM@hotmail.com> wrote in message > > > > news:O4zEI7xfEHA.2812@tk2msftngp13.phx.gbl... > > > > > When I use a stored procedure I usually test it first by hard coding > the > > > > > stored procedure parameters. Later I add the query parameter. Not > sure > > > what > > > > > the problem is that you are seeing. > > > > > > > > > > Bruce L-C > > > > > > > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> > wrote > > > in > > > > > message news:%23QFQ6ixfEHA.1092@TK2MSFTNGP11.phx.gbl... > > > > > > Again, seeking confirmation that I'm on the right track: > > > > > > > > > > > > Say I have a stored procedure that I want to re-use in several > > > different > > > > > reports. For some, I want to > > > > > > hard-wire the parameters without modifying the procedure code. > What > > > > > options do I have? > > > > > > > > > > > > Am I correct in thinking that if I use command type "stored > > > procedure", I > > > > > should *only* have the procedure > > > > > > name as "command text". I.e., I cannot specify parameter values in > the > > > > > proc call? > > > > > > > > > > > > And if I use command type as "query string", RD will parse the > string > > > and > > > > > figure out the parameters, so the > > > > > > same goes here? > > > > > > > > > > > > If above is correct, what options do I have? > > > > > > I can specify a default value in "Report", "Report Parameters", > but > > > that > > > > > parameter will still be there when I > > > > > > execute it from the "Preview" tab. I understand I can pass it in > the > > > URL > > > > > when I deploy the report, but that > > > > > > seems to be messy way for such a simple task. Any other options, > am I > > > > > missing the obvious? > > > > > > > > > > > > -- > > > > > > Tibor Karaszi, SQL Server MVP > > > > > > http://www.karaszi.com/sqlserver/default.asp > > > > > > http://www.solidqualitylearning.com/ > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
Yep, I fully understand what you mean, after this day worth of playing with DS parameters vs. Report
parameter. I removed the DS parameters, couldn't run the DS. Added it back on, setting it to a constant value, DS executed. Report still showed the parameter (didn't make a difference what I put there), until I removed the Report parameter. All makes sense now. :-) -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ [quoted text, click to view] "Bruce Loehle-Conger" <bruce_lcNOSPAM@hotmail.com> wrote in message
news:ep8veW6fEHA.3016@tk2msftngp13.phx.gbl... > This is probably the most common conceptual part of RS that people miss. The > difference between query parameters and report parameters (and in your case > stored procedure parameters). RS is being nice and creating these things for > you. Not just when you use stored procedures but also when you create any > query with a parameter, it then creates a matching report parameter. But the > point still is that they are two different things (three in the case of > stored procedure). How they are defined, called, tied together is totally up > to you. In report->report parameters you can rename them, remove them, > change the order etc. If renamed, however, they do not fix it up with the > query and you have to go to the dataset, click on the ... and then > parameters tab and map the query parameter to the stored procedure > parameter. > > Bruce L-C > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in > message news:eBBjJB3fEHA.3632@TK2MSFTNGP09.phx.gbl... > > Thanks Robert. Will check it out. > > Being a SQL person, I guess I always try to do things at the SQL level > first. :-) > > > > -- > > Tibor Karaszi, SQL Server MVP > > http://www.karaszi.com/sqlserver/default.asp > > http://www.solidqualitylearning.com/ > > > > > > "Robert Bruckner [MSFT]" <robruc@online.microsoft.com> wrote in message > > news:eHhjvd2fEHA.3048@TK2MSFTNGP09.phx.gbl... > > > As you have noticed, the report designer will automatically detect > "query" > > > parameters, generate corresponding report parameters, and link them > > > together. E.g. the generated RDL file would contain something similar to > > > this: > > > > > > <Query> > > > <DataSourceName>DataSource1</DataSourceName> > > > <CommandType>StoredProcedure</CommandType> > > > <CommandText>get_report_viewing_frequency</CommandText> > > > <QueryParameters> > > > <QueryParameter Name="company_id"> > > > <Value>=Parameters!company_id.Value</Value> > > > </QueryParameter> > > > </QueryParameters> > > > </Query> > > > > > > Note: The query parameter value is an expression! > > > You can always modify the linking between report parameters and query > > > parameters (edit the dataset -> in the dataset dialog click on the > > > Parameters tab). You could change the query parameter value expression > to a > > > constant value or an expression which does not depend on a report > parameter. > > > > > > You can even remove automatically generated report parameters (VS menu: > > > Report - Report Parameters). > > > > > > -- > > > This posting is provided "AS IS" with no warranties, and confers no > rights. > > > > > > > > > > > > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote > in > > > message news:eMr9mw1fEHA.3556@TK2MSFTNGP12.phx.gbl... > > > > Not what I'm looking for, I'm afraid. IT doesn't mater whether the > proc > > > has default values for the parameters > > > > or not. The parameters will still be visible when you run the report. > > > > > > > > -- > > > > Tibor Karaszi, SQL Server MVP > > > > http://www.karaszi.com/sqlserver/default.asp > > > > http://www.solidqualitylearning.com/ > > > > > > > > > > > > "Jeff Dillon" <jeff@removeemergencyreporting.com> wrote in message > > > > news:ebWYIMzfEHA.2416@TK2MSFTNGP09.phx.gbl... > > > > > From SQL Books online, not sure if this will help. The code below > > > creates > > > > > default parameters for a sproc if none is supplied when called. > > > > > > > > > > CREATE PROCEDURE au_info2 > > > > > @lastname varchar(30) = 'D%', > > > > > @firstname varchar(18) = '%' > > > > > AS > > > > > SELECT au_lname, au_fname, title, pub_name > > > > > FROM authors a INNER JOIN titleauthor ta > > > > > ON a.au_id = ta.au_id INNER JOIN titles t > > > > > ON t.title_id = ta.title_id INNER JOIN publishers p > > > > > ON t.pub_id = p.pub_id > > > > > WHERE au_fname LIKE @firstname > > > > > AND au_lname LIKE @lastname > > > > > GO > > > > > > > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> > wrote > > > in > > > > > message news:uqcjBAyfEHA.3048@TK2MSFTNGP09.phx.gbl... > > > > > > Hmm, perhaps I'm missing something. The environment creates one > report > > > > > parameter for each parameter to the > > > > > > stored procedure. This is regardless of whether I hard code the > proc > > > > > parameter and use command type "SQL text" > > > > > > instead of "stored procedure". And from there' there just don't > seem > > > to be > > > > > a way to get rid of this parameter > > > > > > when I execute the procedure (as the top, where RD ask for the > > > parameter > > > > > values). > > > > > > > > > > > > -- > > > > > > Tibor Karaszi, SQL Server MVP > > > > > > http://www.karaszi.com/sqlserver/default.asp > > > > > > http://www.solidqualitylearning.com/ > > > > > > > > > > > > > > > > > > "Bruce Loehle-Conger" <bruce_lcNOSPAM@hotmail.com> wrote in > message > > > > > > news:O4zEI7xfEHA.2812@tk2msftngp13.phx.gbl... > > > > > > > When I use a stored procedure I usually test it first by hard > coding > > > the > > > > > > > stored procedure parameters. Later I add the query parameter. > Not > > > sure > > > > > what > > > > > > > the problem is that you are seeing. > > > > > > > > > > > > > > Bruce L-C > > > > > > > > > > > > > > "Tibor Karaszi" > <tibor_please.no.email_karaszi@hotmail.nomail.com> > > > wrote > > > > > in > > > > > > > message news:%23QFQ6ixfEHA.1092@TK2MSFTNGP11.phx.gbl... > > > > > > > > Again, seeking confirmation that I'm on the right track: > > > > > > > > > > > > > > > > Say I have a stored procedure that I want to re-use in several > > > > > different > > > > > > > reports. For some, I want to > > > > > > > > hard-wire the parameters without modifying the procedure code. > > > What > > > > > > > options do I have? > > > > > > > > > > > > > > > > Am I correct in thinking that if I use command type "stored > > > > > procedure", I > > > > > > > should *only* have the procedure > > > > > > > > name as "command text". I.e., I cannot specify parameter > values in > > > the > > > > > > > proc call? > > > > > > > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |