More About Multi-Value Parameters

Just for fun, I am going to talk about multi value parameters again. In this blog, Shortcomings of Multi-Value Parameters, I discuss two of the shortcomings. Now I am going to talk about how to use them in detail.

OverviewMany times you will need to have a parameter that allows the user to select more than one value. In this instance, when you are creating the parameter, check on the Multi-value check box in the Report Parameters window.


Available Values… Or Not!If you have a set list of items that you want the user to be able to select from, you can establish this list in the Available Values section of the Report Parameters window. You can either use a dataset, or type in static values.

To use a dataset to populate the available value, create the dataset in the data tab, making sure that you assign field names. Then in the Report Parameters window, click on your parameter, click on the From Query radio button in the available values section. Then select your dataset name, value column and label column. The value is what will be returned when referencing @Parameter in your SQL query, and the label is what will be shown in the drop down parameter. You can access both of these values in expressions:

Parameters!Parm1.Value or Parameters!Parm1.Label

You can have a multi-value parameter with or without available values. For example, you may have a parameter for Color, with the available values being red, green and blue. In this case, when you preview the report, you will have a drop down check list, with Select All, Red, Green and Blue listed. Users can click on one or more checkboxes, and select the colors they want to filter on. (If you have SP1, you will not get the Select All check box. The fix for this is SP2.)

If your multi-value parameter does not have any available values assigned to it when it is set up, your users are presented with a drop down multi line text box. The users can enter values, separated by a carriage return, and SSRS automatically formats it for an IN clause in SQL. The great thing about this: Users can copy a column of data from Excel and paste into the textbox! I had a client that worked with loans. They always needed information based on a list of loan numbers that someone had in an Excel spreadsheet. I developed a report that have a multi value parameter, they would copy the loan number column out of the spreadsheet, paste it into the parameter, run the report, export to Excel. It saved them from having to copy, look up, format, etc.

When using a Multi-value parameter, you cannot have a null value returned. This means that the user will have to select or enter at least 1 value from the parameter list. When setting up the parameter, you will get an error if you try to select the Allow null value check box. If there are no available values, the user must enter at least 1 value into the textbox.

Using in SQL
If you are going to be passing a multi-value parameter to a stored procedure, you will have to pass it as 1 string, and parse it in the stored procedure. See the blog referenced above for more information about this.

However, to use your multi-value parameter in a dataset SQL statement, just use the IN clause:

Where ItemID in (@Item)

SSRS will automatically add the commas and format it for you.

There is no way for you to tell if all of the values have been selected, or if the Select All was clicked on. This is an issue if you have a lot of values because the IN clause is worthless, but SQL still executes it, cost precious time and resources. If you expect to have a long list of values, or if you think that all of them will be selected most of the time, there is a workaround documented in the blog referenced at the top of this article.

Using in ExpressionsTo determine how many values have been selected or entered, use the Count property:

=Parameters!Parm1.Count

To reference a value directly, use:

=Parameters!Parm1.Value(arrayindex)

where the array index is less than the count.
If you want to display the list of values selected/entered, use the Join function:

=Join(Parameters!Parm1.Label, “, “)

The second argument is the delimiter; what character string is displayed between each parameter entry. You could also reference the parameter value.

Using in Code
You can reference multi-value parameters in embedded code as well. See my article about multi language report labels for a look at how that is used.

Passing in URL
To pass the multi-value parameter values in an URL to report manager, repeat the parameter name. For example:

http://server/reportserver?/dir/Report&rs:Command=Render&Parm1=VALUE1&Parm1=VALUE2&Parm1=VALUE3

That’s all for now. I think this gives a lot of information. Hope it helps!

peace

CodeProject

Report Layout Basics

I thought I would go over some of the basics of report layouts since I have seen a few forum posts about basic layouts. This is by no means a comprehensive overview, but a starting place.

The first thing to realize about the report layout is that the only sections you have to work with are the page header, page footer and the report body. There is no concept of a report header/footer, etc. But also, unlike Crystal Reports, the report sections are NOT tied to data.

Instead, the report body holds the controls that can act as report sections. Here are some ideas:

Report Header

If you need a report header, you can place any control, like a text box, on the body of the report, and populate it with data, static text, etc. If you place these controls inside a rectangle, you can set the PageBreakAtEnd property to true, and then those text boxes will appear on a page by themselves. If you dont use a rectangle in this manner, your report header will be on the same page as the start of your data area.

Report Footer

As with the report header, if you place controls at the bottom of the body section, in a rectangle, and set the PageBreakAtStart to true, you will have these on the last page of the report.

Multiple Body Sections

You can have multiple body sections just by adding multiple controls to the body section of the report. Say that you have 2 datasets in your report and you want to show them in separate sections of the report. Add a table for the first dataset. Set the PageBreakAtEnd property to true.
Then add another table below that for the second dataset. When you render the report, you will have the first table displayed, a page break, and then the second table. Add the report header and footer from above, and you now have a 4 section report!

Using the List Control

The list control is used to repeat a set of controls within it. You can place a list control on your report, set the dataset and the grouping on the list. Then place some text boxes in the list control and populate them with the fields from the dataset. The list will group the data as you set up, and populate the text boxes, repeating for every group.

You can also put multiple tables in the list control. I will do this often when there is a lot of detail data to display, and I do not want to have to worry about the column layouts.

Well, that was some basic layout ideas. I will add more soon

peace

BobP