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

Data based, Row Level Security in Reporting

Overview

Last month I promised an article on data based security. Well, after I ignored it for a month, someone asked for it. So here is what I am currently implementing in a project for a client.

I import several transactional systems into a data mart of my own design. It is a star schema, and utilizes 4 fact tables and about 20 dimensions. One of the dimensions handles the primary focus of our reports. Let’s call it Orders. Each individual order is assigned an OrderKey that ties all of the fact tables together.

To implement security, I assign OrderKeys to report user groups. The assignment is based on rules setup thru a front end .NET web application, and can be used to filter orders to users based on any field in the data mart. Then the report SQL checks to make sure that every order is in that security table for the user pulling the report.

The Details

I have setup a data dictionary containing all of the metadata that I need to build a query for the fields. The admin application displays the fields to the admin user, and the admin user selects a field, an operator (=, <, >, <>, Like, etc) and then enters a value.

So for instance, the admin user would select the CostCenter field, select the = operator, and then type in 1234. Stored in the database is the line: CostCenter = 1234.

Once the admin user is done, they select a menu item to “build” the rules. Using the data dictionary, the application builds SQL statements to filter the OrderKeys. The statements look like this:

Insert into Security
Select Distinct a1.*
From
(Select 1 as ProfileID, f.OrderKey as KeyVal
From factOrderLine f (NOLOCK)
inner join dimProduct d (NOLOCK)
on f.ProductKey = d.ProductKey

Where d.ProductID <> '555666') a1
Inner Join
(Select 1 as ProfileID, f.OrderKey as KeyVal
From factOrderLine f (NOLOCK)
inner join dimMFG d (NOLOCK)
on f.MFGKey = d.MFGKey

Where d.MFGID = 'PG') a2
On a1.KeyVal = a2.KeyVal

Because I am doing inner joins, this acts as an AND clause. So in the above query, I would get all orders that have a product ID not equal to 555666 and everything where the manufacturer ID is PG.

The resultant list is a profile ID, and then order key itself.

I put this into the Security table.

When I code the SQL in the report, I add an EXISTS statement to the WHERE clause:

and OrderKey in (Select OrderKey from Security where ProfileID = @ProfileID)

The @ProfileID parameter is being passed in by the app calling the reports. It is set when the user logs into the web front end.

That in a nutshell is how I do data based row level security.

This is a huge topic, and I have only scratched the surface in this blog. As I work on it some more, and add new features, I will blog them as well.

In the meantime, if you have any questions or suggestions, please email me!

peace

Data Based Security in Reporting

I sometimes wonder if anyone actually reads these blogs :-P
Well, here is my chance to find out.

I am going to ask for feedback.

In my position as a Business Intelligence Consultant, I have been asked before how to implement data based security (DBS). By DBS I mean the ability for an admin user to restrict a row of data from a user based on a value in the database.

For example, I, as an end user of a report, can only see transactions that occurred within my region: Georgia; whereas my peer across the country can only see transactions in Colorado.

I could add a where clause to every report, but what if the admin wants the ability to filter by ANY field in the database.

I have a solution that works quite well, but I am interested to know if anyone else has run into this request, and how they have solved it.

I am going to post my solution later on this week.

Peace