Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ZMike
Posting Yak Master
110 Posts |
 Posted - 2008-10-16 : 11:40:32
|
| I have several fields that I'd like to do this for but once I do one of these then the others should all follow suitWHERE ([WORDMPF (Desc)].[Account Number] IN (@Account_Number)In the Report Parameters I have the Account Number setup. However what I'd like the report to do is to show everything but leave the drop down with multi value if they want to change it. So basically I have several Parms that I'd like the report to initially show everything and they can filter if need be. I'd also like that Account field to show Blank until they enter something but the SQL to still show everything.Any Ideas ?Thanks ! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-16 : 13:07:59
|
| WHERE (','+@Account_Number+',' LIKE '%,'+ CAST([WORDMPF (Desc)].[Account Number] AS varchar(10)) + ',%'OR @Account_Number IS NULL)make null as default value for parameter. also remember to check allow null value option in parameters tab. |
 |
|
|
ZMike
Posting Yak Master
110 Posts |
Posted - 2008-10-16 : 15:16:01
|
| visakh16,Thank you ! That seems to work. My only follow up question is that I do notice that it won't let me do the multi value if I want to specify muliple account numbers. However it will let me type multiple in on a single line seperated by a comma but it doesn't seem user friendly for the end user. Is there a way to keep the multi-value box while still letting the Null Value stay ?Actually it looks like I didnt need to do all of the Varchar stuff. I just had to add OR FIELD IS NULL and uncheck the multi field and put the null back in instead. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-18 : 02:15:46
|
quote:
Originally posted by ZMike
visakh16,Thank you ! That seems to work. My only follow up question is that I do notice that it won't let me do the multi value if I want to specify muliple account numbers. However it will let me type multiple in on a single line seperated by a comma but it doesn't seem user friendly for the end user. Is there a way to keep the multi-value box while still letting the Null Value stay ?Actually it looks like I didnt need to do all of the Varchar stuff. I just had to add OR FIELD IS NULL and uncheck the multi field and put the null back in instead.
for that you need to uncheck allow null value option and then add an explicit value to your dataset which populates parameter values (some thing like (All)). when (All) is selected pass the value internally as some invalid account number say -1). then write filter as belowWHERE (','+@Account_Number+',' LIKE '%,'+ CAST([WORDMPF (Desc)].[Account Number] AS varchar(10)) + ',%'OR @Account_Number =-1) |
|
|
|
|
|
|
Report Parameters Question