Passing SELECT ALL from URL

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