Is there a way to pass the "select all" option to a multi-select parameter from a URL string?
Thanks, Eva
Hi,
In the case of multi-select with Select All let's assume 2 cases, a text param and an INT param.
Assuming no default values are set
INT param.
Set the report parameter like that
Param Value, Label = Select All, Value = 2147483647
Set the procedure like that, parameter declaration
@.Value as int = 2147483647
Set the procedure Limit like that
AND ( @.Value = 2147483647 or i.Value <= @.Value)
Then call the URL Like that
http://myname.mydomain.com/ReportServer/Pages/ReportViewer.aspx?%2fNew+Product+Market+Synergy+Reports%2fNP+Market+Synergy&rs%3aCommand=Render&Division=%23%23&project=1763570219&Value=2147483647&OptyDetailsTop=10&rs:format=EXCEL
For Strings use something like
Param Division, Label = All, Value = '##'
or use a dataset for your param like that
Select '##' as Value, '*All' As label
union all
Select distinct
Division as Value, Division_Description as Label from tb_Division
order by Label
in the proc use this param declaration
@.Division as Varchar(85) = '##'
And the limit like that
where ( @.Division = '##' or i2.Division = @.Division)
Then call it like that from the URL
http://myname.mydomain.com/ReportServer/Pages/ReportViewer.aspx?%2fNew+Product+Market+Synergy+Reports%2fNP+Market+Synergy&rs%3aCommand=Render&Division=%23%23&project=1763570219&Value=2147483647&OptyDetailsTop=10&rs:format=PDF
Where %23%23 will pass the ## to the procedure parameter.
Note that if you simply want to display the report, remove the &rs:format tag from the URL.
Since the procedure limit start with @.Division = '##' the i2.Division = @.Division will not even be part of the query sent to the server.
Regards,
Philippe
|||If I do something like you suggest RS puts the Select ALL option at the top of the multi-select parameter. Selecting both the RS select all option and the one I built in makes the queries not work. Therefore, I really just want to leave the datasets clean and the parameters in my main query TableField in (@.param) and just be able to send the URL something that tells it I want that default RS Select ALL option as if I had set that thing as the default value of the parameter.|||
Oops sorry, the example where I craft the select all was for a single select or a pre-SP2 install.
For a multi-select with built-in Select all you would actually just use the standard list of values. here the trick to avoid a huge Where Item is IN ('a', 'b', 'c',......) would be to do it like this
Procedure parameter
, @.Item varchar(Max) = '##'
Procedure handling of ALL option.
declare @.NKeyCount as int
declare @.NKeyMax as int
set @.NKeyCount = onglobals.dbo.fn_CountChar(@.Item, ',')
set @.NKeyMax = (
Select Count(Distinct b.Item_Cd) as Value
from meta_NewProductBaseline b
)
if @.NKeyCount + 1 = @.NKeyMax begin set @.Item = '##' end
If @.Item is null begin set @.Item = '##' end
Procedure limit
And (@.Item = '##' or b.Item_Cd in (select ltrim(SQLstr) from ONGlobals.dbo.clrfn_SplitCommaDelimitedString(@.Item) ) )
Unfortunately, you need a bunch of functions to get it to work. I hope you already have those functions. If not, you can try to find it from this forum or I can give you the code.
Philippe
|||OK, Here is the code for the 2 functions. one is SQL the other is CLR.
You do not have to use CLR, there are multiple other ways to do this.
See
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=399&messageid=386759
Also, note that the CLR I show here is limited to 4000 chars and miss a build-in LTRIM...
Count chars
Code Snippet
CREATE FUNCTION dbo.fn_countchar (@.source varchar(max), @.charval varchar(255))
returns int
as
BEGIN
DECLARE @.len int, @.icount int, @.count int
SET @.len = len(@.source)
SET @.icount = 1
SET @.count = 0
WHILE @.icount<= @.len
BEGIN
IF substring(@.source, @.icount, 1) = @.charval
SET @.count = @.count + 1
SET @.icount = @.icount +1
END
RETURN @.count
end
Split multi-param
Code Snippet
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true, TableDefinition = "SQLStr nvarchar(4000)", FillRowMethodName = "FillSplitCommaDelimitedStringToStr")]
public static System.Collections.IEnumerable clrfn_SplitCommaDelimitedString(SqlString str)
{
string x = str.Value;
if (!string.IsNullOrEmpty(x))
{
return x.Split(',');
}
else
{
return null;
}
}
private static void FillSplitCommaDelimitedStringToStr(object obj, out SqlString str)
{
if (obj != null)
str = (String)(obj);
else
str = String.Empty;
}
};
|||How do I pass this any values from a URL string?
|||SAme as previously posted. You call the report via URL and the report will call the procedure.
http://myname.mydomain.com/ReportServer/Pages/ReportViewer.aspx?%2fNew+Product+Market+Synergy+Reports%2fNP+Market+Synergy&rs%3aCommand=Render&Division=%23%23&project=1763570219&Value=2147483647&OptyDetailsTop=10&rs:format=EXCEL
Use %23%23 to pass ## meaning ALL otherwhise pass the standard list of values for a multiselect or just select all in the multiselect.
Philippe
ar_pub=%WW07%