Passing SELECT ALL from URL

evaleah

Is there a way to pass the "select all" option to a multi-select parameter from a URL string

Thanks, Eva

Philippe Cand

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

evaleah

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.

Philippe Cand

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

Philippe Cand

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;

}

};

evaleah

How do I pass this any values from a URL string

Philippe Cand

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

Passing SELECT ALL from URL

Re: Passing SELECT ALL from URL

Re: Passing SELECT ALL from URL

Re: Passing SELECT ALL from URL

Re: Passing SELECT ALL from URL

Re: Passing SELECT ALL from URL

Re: Passing SELECT ALL from URL