Using javascript to encode a URL with an ampersand

javascript escape function:


="javascript:void(window.open('http://xxx/reportserver?/Folder A/Report X" & "&rs:Command=Render" & "&rc:Parameters=true" & "&param1="
& replace(
Fields!description.Value, "&", "'+escape('&')+'"
)
& "'));" 

Using SQL commands, parameters and subreports - CR

My report contains a number of subreports, each comprising a SQL command and using a parameter value passed from the main report.

1) Set up the main report parameter, in this instance called ?School

2) Add a subreport, Add Command with the Where clause as follows:

WHERE (School.SchoolKey = {?School})

3) For the Parameter list, Click Create:

Parameter Name = School
Value Type = Number

It will then prompt for a parameter value, enter 1 (anything will suffice at this stage)

NOTE: When the database field is a uniqueidentifier, use 'String' type and for the prompt use a value in the uniqueidenfier format: {29D2B899-393A-4592-9B00-D289884A1F94}

4) Back in the main report, right click the subreport and Change Subreport Links:

Available Fields = ?School
Subreport parameter field to use = ?School (make sure to select the user-generated one and not the automatically generated one named similar to ?PM-?School)

5) Voila! Main report parameters should now be passed to SQL Commands in subreports.

Display parameter Label field - SSRS

I was familiar with displaying a parameter's value field by using the expression:
 =Parameters!parameterfield.Value

But in many cases this value will be a key field and pointless to display. The 'Label' field is required and is obtained by simply changing the expression to:
=Parameters!parameterfield.Label

Simple and obvious really!

Select All option with multi-valued parameters (Part 2) - SSRS

Following on from my previous post on the shortcomings of the 'Select All' option with multi-valued parameters, another workaround is to assess whether the user actually Selects All. If so, then deal with this in the main query code.
Firstly, create an internal parameter (called EstabCount in this case) that counts the number of values held in multi-valued parameter list, with the dataset query simply getting a count of values.

The EstabCount parameter is given the following properties:

The below code simply populates the mult-valued parameter list:

Once the two parameters are set up the next stage is to alter the main dataset query. Select the Parameters tab and create an IIF statement that returns a predetermined string , "Select All" if the count of the number of parameters selected equals or is greater than EstabCount parameter value, i.e. if the 'Select All' option is checked in the drop down box.

=iif(Parameters!EstablishmentParameter.Count>=Parameters!EstabCount.Value, "Select All", Parameters!EstablishmentParameter.Value)

Then we need to alter the WHERE clause in the main dataset query:

WHERE (('Select All' IN (@EstablishmentParameter)) OR (View_ActiveEstablishment.EstablishmentName IN (@EstablishmentParameter)))

So, if the user checks 'Select All' in the drop down the IIF statement returns the string 'Select All' and the WHERE clause evaluates to true and returns a full selection thus negating the need for a potentially massive IN clause being used in the query. Likewise, if the user selects multiple parameter values then these are treated as expected. Unfortunately this may still result in a cumbersome IN clause if the user decides to select all but a few of the parameter values!

A quick play with SQL Server Profiler shows the 'Select All' database query before and after the alteration:

Select All option with multi-valued parameters - SSRS

A major short coming with multi-valued parameters in SQL Server Reporting Services is that there is no way to disable the 'Select All' option. This may have massive performance issues if 'Select All' is checked and large numbers of records are pulled from the DB. A work-around is to create another parameter and by using the IIF function limit the number of parameters available for selection.
















The first stage is to create a new parameter, in this case, called LimitEstablishmentParameter.















I've decided to limit the number of parameters available for selection using the following code in Default Value for the LimitEstablishmentParameter.



=iif(Parameters!EstablishmentParameter.count<=20,Parameters!EstablishmentParameter.value,"xxx")












Where xxx is a value that will return zero records from the dataset.

Change your dataset to refer to your new 'limiting' multi-valued parameter (LimitEstablishmentParameter in this case).






It's also worth putting a textbox near the top of the report with an expression similar to the following, informing the user of the selection limit:

=iif(Parameters!LimitEstablishmentParameter.Value(0)="xxx","The selection is limited to 20 establishments.",(Join(Parameters!EstablishmentParameter.Value, ", ")))

Display a list of all multivalued parameter values - SSRS

To display a list of all the selected multivalued parameters in a report use the Join function:

=Join(Parameters!Field.Value, ", ")

Multivalued parameter drop down width - SSRS

I was working on a SSRS project where I was using multivalued parameters. This creates a drop down list whereby the user can select the relevant values. Unfortunately in SSRS 2005 (SP3) the width of the dropdown is fixed, which seems to me as a bit of an oversight.

I believe that this may be corrected by altering the HtmlViewer.CSS file or by programmatically fixing the width of the available parameter values.

Neither is ideal.

















I will look into whether this has been rectified in SSRS 2008.

Select All option - SSRS

It's often necessary to have a 'select all' option when using parameters in Reporting Services.
Here's a way to do it:

For my report I've created three datasets.

1) The first dataset, named 'Package' creates the parameter values, using UNION command to adjoin a '** SELECT ALL **' value to those obtained from a database.

SELECT '** SELECT ALL **' as PACKAGE

UNION

SELECT PACKAGE.PACKAGE as PACKAGE

FROM PACKAGE

order by PACKAGE.PACKAGE

2) The second dataset, named 'SelectAll' is used to populate the Default Value for the parameter.

SELECT '** SELECT ALL **' as PACKAGE

These two datasets are then used to populate the Report Parameters window as below:

3) The third parameter, in this case named 'ChangedRequest', provides the select logic.

SELECT .....

FROM .....

WHERE PACKAGE.PACKAGE = @Package or '** SELECT ALL **' in (@Package)

order by PACKAGE.PACKAGE

Which results in something like this:

SSRS URL Parameters - SSRS

To pass a report parameter through to another report via a new browser:

On the Navigation tab for the report objects properties add the following javascript command to the Jump to URL expression box:

="javascript:void(window.open('http://SERVER NAME/ReportServer/Pages/ReportViewer.aspx?%FOLDER NAME%REPORT NAME%&rs:Command=Render&PARAMETER NAME="+Fields!Pass value.Value+"'))"

For example:

="javascript:void(window.open('http://dom-pc/ReportServer/Pages/ReportViewer.aspx?%2fCAD+GIS+DB+reports%2fJobs+By+Establishment&rs:Command=Render&EstablishmentParameter="+Fields!SITE_NAME.Value+"'))"

Adding Null Values for Multi-Valued Parameters - SSRS

When we use multi-valued parameters, we would want to have null values as an option as we may want it to be selected by default. The problem is, there is no null value for multi-valued parameters. One possible solution could be to create a view that has a null value. To do this, we can make use of the UNION ALL command present in SQL.

CREATE VIEW viewDomainWithNull
AS SELECT -1 AS [Domain Id], 'NULL' AS [Domain Name]
UNION ALL
SELECT [Domain Id],[Domain Name] FROM Domain

Taken from
http://www.codeproject.com/KB/reporting-services/SSRSaaka3.aspx