| Groups | Blog | Home |
sql server reporting services : Default Parameter Values
How can i have the user run a report in which the user may or may not select
a value for the parameter?I dont want to set a default value for the parameter because that would limit the results of the query to whatever value you set as the default. I tried Robert Bruckner of [MSFT] suggestion below,it doesnt work.You get the first value returned by the query as the default value. TIA, Vinny Make the valid values list and the default value both query-based and use the same dataset column. -- Robert This posting is provided "AS IS" with no warranties, and confers no rights. [quoted text, click to view] "isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
news:F1744AE8-27D3-4AA9-A622-7F19DED503BD@microsoft.com... >I want the default value for a muti-value parameter to be the equivalent of > <Select All>. How can this be accomplished? Thanks in advance >
We accomplished this by using something similiar to what Robert said.
Quering for a list of valid entries and adding ' All' to it and making ' ALL' the default for that parameter. (="select ' ALL' as BUSINESS_UNIT UNION select B.BUSINESS_UNIT from BUSINESS_UNIT_TABLE B ") This is then used as the basis for your parameter and in your report data you add a conditional line to the where clause like below. IIF( Parameters!Category.Value = " ALL", "", " and tbl.Business_Unit = '" & Parameters!Business_unit.Value & "'") Just make sure of all your single and double quotes :) We put a space in front of ALL so that it would always go to the top of the list as well.
I a problem usings your approach .
I am going against DB2.IIF Syntax is not valid.i tried using a IF Statment (which is DB2 compliant) but i got an error message stating OLEDB doesnt allow named parameters (i usually use ? to denote a parameter). Any suggestions would be appreciated. Vinny [quoted text, click to view] "Ches" wrote:
> We accomplished this by using something similiar to what Robert said. > Quering for a list of valid entries and adding ' All' to it and making > ' ALL' the default for that parameter. > > (="select ' ALL' as BUSINESS_UNIT UNION select B.BUSINESS_UNIT from > BUSINESS_UNIT_TABLE B ") > > This is then used as the basis for your parameter and in your report > data you add a conditional line to the where clause like below. > > IIF( Parameters!Category.Value = " ALL", "", " and tbl.Business_Unit = > '" & Parameters!Business_unit.Value & "'") > > Just make sure of all your single and double quotes :) We put a space > in front of ALL so that it would always go to the top of the list as > well. >
the iif is part of RS not sql. It doesnt' come through to the
database. Let me give you a bigger snippet. ="select field1, field2, field3 from table where field1 = '44444'" & iif(Parameters!Category.Value = ' ALL', '', ' and category = '" & Parameters!Category.Value & "') The entire statement is an expression not a query. The actual query would depend on the parameter. If it were ' ALL' (the default) the query would look like this. select field1, field2, field3 from table where field1 = '44444' If it were anything else the actual query would end up looking like this... select field1, field2, field3 from table where fied1 = '44444' and category = 'Parameter' The iif evaluates where to put nothing in ('') or to put in the and clause. That make anymore sense?
Yes,it make alot of sense,However i have my Sql in a dataset not in an
expression. I appreciate you trying to help. [quoted text, click to view] "Ches" wrote:
> the iif is part of RS not sql. It doesnt' come through to the > database. Let me give you a bigger snippet. > > ="select field1, field2, field3 from table > where field1 = '44444'" > & iif(Parameters!Category.Value = ' ALL', '', ' and category = '" & > Parameters!Category.Value & "') > > > The entire statement is an expression not a query. The actual query > would depend on the parameter. If it were ' ALL' (the default) the > query would look like this. > > select field1, field2, field3 from table where field1 = '44444' > > If it were anything else the actual query would end up looking like > this... > > select field1, field2, field3 from table where fied1 = '44444' and > category = 'Parameter' > > > > The iif evaluates where to put nothing in ('') or to put in the and > clause. > > That make anymore sense? >
That is the point. He is suggesting using an expression for your query
definition for the dataset. You go to the generic query designer and put in the = sign and the rest of the expression. My suggestion if you are going to do this is to first develop the expression in a textbox so you can see the resultant query string. Then put it in as the source of the dataset. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Vinny Vinn" <VinnyVinn@discussions.microsoft.com> wrote in message news:333BE3C8-1631-47DA-B1D5-F7E88364B07D@microsoft.com... > Yes,it make alot of sense,However i have my Sql in a dataset not in an > expression. > I appreciate you trying to help. > > "Ches" wrote: > >> the iif is part of RS not sql. It doesnt' come through to the >> database. Let me give you a bigger snippet. >> >> ="select field1, field2, field3 from table >> where field1 = '44444'" >> & iif(Parameters!Category.Value = ' ALL', '', ' and category = '" & >> Parameters!Category.Value & "') >> >> >> The entire statement is an expression not a query. The actual query >> would depend on the parameter. If it were ' ALL' (the default) the >> query would look like this. >> >> select field1, field2, field3 from table where field1 = '44444' >> >> If it were anything else the actual query would end up looking like >> this... >> >> select field1, field2, field3 from table where fied1 = '44444' and >> category = 'Parameter' >> >> >> >> The iif evaluates where to put nothing in ('') or to put in the and >> clause. >> >> That make anymore sense? >> >>
Can you point me to an article that demonstrates how to write an expression
for my query definition? Thanks [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> That is the point. He is suggesting using an expression for your query > definition for the dataset. You go to the generic query designer and put in > the = sign and the rest of the expression. My suggestion if you are going to > do this is to first develop the expression in a textbox so you can see the > resultant query string. Then put it in as the source of the dataset. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Vinny Vinn" <VinnyVinn@discussions.microsoft.com> wrote in message > news:333BE3C8-1631-47DA-B1D5-F7E88364B07D@microsoft.com... > > Yes,it make alot of sense,However i have my Sql in a dataset not in an > > expression. > > I appreciate you trying to help. > > > > "Ches" wrote: > > > >> the iif is part of RS not sql. It doesnt' come through to the > >> database. Let me give you a bigger snippet. > >> > >> ="select field1, field2, field3 from table > >> where field1 = '44444'" > >> & iif(Parameters!Category.Value = ' ALL', '', ' and category = '" & > >> Parameters!Category.Value & "') > >> > >> > >> The entire statement is an expression not a query. The actual query > >> would depend on the parameter. If it were ' ALL' (the default) the > >> query would look like this. > >> > >> select field1, field2, field3 from table where field1 = '44444' > >> > >> If it were anything else the actual query would end up looking like > >> this... > >> > >> select field1, field2, field3 from table where fied1 = '44444' and > >> category = 'Parameter' > >> > >> > >> > >> The iif evaluates where to put nothing in ('') or to put in the and > >> clause. > >> > >> That make anymore sense? > >> > >> > >
Hi Vinny,
the way I do this is to fill all parameters from a stored procedure and I add the value '*' to the list of parameters and use it as the default. Then in the SP that performed the query I use where .... and (parm1 = @parm1 or @parm1 = '*') ..... If the @parm coming into the SP is a '*' the optimise recognised that the or condition is seen to be true.....I am not sure if it ignors it all together as it should do but one day it will. In this case, if the user wants to see all rows for the particular parameter he/she does nothing....if he/she wants to constrain rows he/she must select a parameter.. Note, this means I do insist on parameters being characters. I picked * for no better reason than it's pretty obvious...I could have picked 'ALL' . Good luck.. Peter
Thank you,Pete
Ii agree with you stored pocedures is the way to go ,however i am going against DB2 and unfortunetly we have issues with sp against DB2 (we use sp for Oracle and SQL Server). Vinny [quoted text, click to view] "Peter Nolan" wrote:
> Hi Vinny, > the way I do this is to fill all parameters from a stored procedure and > I add the value '*' to the list of parameters and use it as the > default. > > Then in the SP that performed the query I use > where > .... > and (parm1 = @parm1 or @parm1 = '*') > ..... > > If the @parm coming into the SP is a '*' the optimise recognised that > the or condition is seen to be true.....I am not sure if it ignors it > all together as it should do but one day it will. > > In this case, if the user wants to see all rows for the particular > parameter he/she does nothing....if he/she wants to constrain rows > he/she must select a parameter.. > > Note, this means I do insist on parameters being characters. > > I picked * for no better reason than it's pretty obvious...I could have > picked 'ALL' . > > Good luck.. > > Peter >
Vinny,
just out of interest, what issues with SPs in DB2...I used to be a DB2 person but have not had the chance to use it for years now... Peter
I dont Know,I am relatively new here.When i asked if i could write an SP,i
was told there were issues. Vinny [quoted text, click to view] "Peter Nolan" wrote:
> Vinny, > just out of interest, what issues with SPs in DB2...I used to be a DB2 > person but have not had the chance to use it for years now... > Peter >
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 |