multivalue – The Data Queen

How to use a Delimited String in a Multi-valued Parameter

I’ve seen this issue a lot lately. There is a need to feed a comma delimited string of values into a multi-value parameter in an SSRS report. There is a simple way to do this.

SET UP YOUR MAIN REPORT QUERY

Your main report query should be set up to expect a string of values in the parameter. For example:

SELECT Product_ID
      ,Item_No
      ,Item_Description
  FROM DIM_Product
  WHERE Item_No IN (@ITEM)

SET UP THE PARAMETER

The parameter should be set up as text, but NOT allow multiple values.

You can set this up as you wish to meet your purpose, but for demonstration I will set up two groups of comma delimited strings as available values.

I have set up a second data set to feed these default values to the parameter.

SELECT '11000B,2200,17000' AS ITEMNMBR

SET UP THE PARAMETER ON THE MAIN REPORT DATASET

Now comes the magic. On the Dataset Properties for your main report dataset, on the Parameters tab, edit the expression for the Parameter Value.

Write this in the parameter expression: =split(Parameters!Item.Value,",")

This will take the comma delimited string as input, split it into individual values and it will get used in the IN clause of the main query that we set up at the beginning of this post.

WHERE Item_No IN (@ITEM)

Viola, the report filters on a string of values.

How to Query Multi Value fields in Active Directory using SSIS

Apparently what’s even more difficult than importing data from AD is figuring out how to import multi-value objects from Active Directory. “Description” is an example of a standard AD multi-value field. My client had many custom multi-value fields added to AD and needed to import the data from these fields into tables in a database. You can accomplish this easily this by adding a bit of code to the C# code importing the single value attributes as outlined in my previous post Getting Around AD Paging on SSIS Import.

This C# code is much simpler than trying to import each multi-value field using a Data Flow task. Using Data Flow tasks can be done but it has some tricky problems like importing only those records with values in the multi-value field, working around paging, and how to deal with apparently empty objects that your query returns even though you specified that it only return those objects with values. It’s also quite a bit slower as you need to populate variables and pass those variables to loops to iterate thru the multi-values for one account at a time.

Here is the code for importing one multi-value attribute into a table. This code should be placed at an appropriate spot within the “foreach (SearchResults” loop outlined in the Getting Around AD Paging on SSIS Import post.

string propertyName = “Description”; //or whichever multi-value field you are importing

ResultPropertyValueCollection valueCollection = results.Properties[propertyName];

//Iterate thru the collection for the user and insert each value from the multi-value field into a table

foreach (String sMultiValueField in valueCollection)

{

string sValue = sMultiValueField.Replace(“‘”, “””); //Replace any single quotes with double quotes

sqlConnection1.Open();

cmd.CommandText =

“INSERT INTO User_Descriptions (sAMAccountName,Description) VALUES (‘” + sAMAccountName + “‘,'” + sValue + “‘)”;

reader = cmd.ExecuteReader();

sqlConnection1.Close();

}

The nice thing about this code is that you can iterate through any records, even if the multi-value field is empty. It won’t fail, it just won’t return a record. This means you can add this same chunk of code multiple times edited for several different multi-value fields within the same script task, and have all your tables updated using the same script. The package is very easy to maintain, with no package variables, no complex package logic, just a simple script. Very elegant!

Get around Active Directory Paging on SSIS import

I have a client who is importing certain users from Active Directory. The paging on their AD is set to 20,000 records. When trying to pull data using a SQL statement, the query fails because it hits the maximum number of records and is unable to return more. You could work around a problem like this by editing your query filter to ensure that you always retrieve fewer than 20,000 records at a time, for example using the whenCreated field. However, there is no guarantee that whatever filter you use will always limit your return value to a maximum of 20,000 records. And you now need to build a loop construct to retrieve all the records since you want more than 20,000 records.

This is much easier to solve than you might think, judging from the number of forum questions out there on the subject (and how long it took me to piece it together). Here are the steps.

Create an SSIS package.

Add a string variable, scoped to the package, called SqlConn. Populate it with the connection string to the database you want to populate with the AD records.

Add a script task to your package. Open the script task, making sure that the ScriptLanguage is C# and not VB.

Click on the Edit Script button. On the right hand side you should see the Project Explorer window. Right click on the name of the Project File at the top of the tree and select Add Reference.

On the .NET tab scroll down and find System.DirectoryServices. Select it and click OK.

Make sure you see the reference appear in the References folder in the Project Explorer window.

Add these statements at the beginning of your script.

using System.DirectoryServices;

using System.Data.SqlClient;

Paste this script to replace the public void Main(). Edit the ds.Filter and Insert string values to meet your table requirements. Be sure to only select single value attributes of the object. If you try to use this method to import multi-value attributes such as “Description” from AD it won’t work. I’ll be writing about that next.

public void Main()

{

//Set up the AD connection;

using (DirectorySearcher ds = new DirectorySearcher())

{

//Edit the filter for your purposes;

ds.Filter = “(&(objectClass=user)(|(sAMAccountName=A*)(sAMAccountName=D0*)))”;

ds.SearchScope = SearchScope.Subtree;

ds.PageSize = 1000;

//This will page through the records 1000 at a time;

//Set up SQL Connection

string sSqlConn = Dts.Variables[“SqlConn”].Value.ToString();

SqlConnection sqlConnection1 = new SqlConnection(sSqlConn);

SqlCommand cmd = new SqlCommand();

SqlDataReader reader;

cmd.CommandType = CommandType.Text;

cmd.Connection = sqlConnection1;

//Read all records in AD that meet the search criteria into a Collection

using (SearchResultCollection src = ds.FindAll())

{

//For each record object in the Collection, insert a record into the SQL table

foreach (SearchResult results in src)

{

string sAMAccountName = results.Properties[“sAMAccountName”][0].ToString();

string objectCategory = results.Properties[“objectCategory”][0].ToString();

//Replace any single quotes in the string with two single quotes for sql INSERT statement

objectCategory = objectCategory.Replace(“‘”, “””);

sqlConnection1.Open();

cmd.CommandText = “INSERT INTO Users (sAMAccountName, objectCategory) VALUES (‘” + sAMAccountName + “‘,'” + objectCategory + “‘)”;

reader = cmd.ExecuteReader();

sqlConnection1.Close();

} } } }

That’s it. This will iterate through all of the objects in Active Directory, regardless of paging size set on Active Directory.

To learn how to import multi-value fields from AD, read this post:

How to Query Multi-Value Fields from Active Directory using SSIS

How to Default to ALL in an SSRS Multi-select Parameter

This seems like it should be easy, but SSRS has nothing built in to allow you to do this. I searched an couldn’t come up with a solution that fit all my requirements for a report. There are ways to default the selection list to every value in the list, but I needed to add the option of ALL to my parameter list, default to ALL, and then most importantly have ALL show up in my header instead of showing every item in the list. I was determined to find a way, and here is how I did it.

In this example I would like to allow the report to default to ALL Layout Codes, rather than selecting every Layout Code in the list. there is already a multi-select parameter in the report called Layout_Code, and the main query filters where Layout_Code IN (@Layout_Code)

1. ADD ‘ ALL’ TO YOUR PARAMETER QUERY

The first thing to do is add the value ‘ALL’ to your parameter query. You’ll want it show up at the top of your select list. A simple way to do this is to put a space before the A to make it sort to the top.

SELECT DISTINCT Layout_Code
FROM Your_Table
UNION
SELECT ‘ ALL’ AS Layout_Code
ORDER BY Layout_Code

2. EDIT YOUR MAIN QUERY TO USE BOTH PARAMETERS

Edit the where clause in your main query. If you are using Query Designer in Text mode, simply add this statement. Be sure to use outer brackets to contain both statements if you have other items in your where clause.

WHERE (‘ ALL’ IN (@Layout_Code)) OR (Layout_Code IN (@Layout_Code)) )

If you have multiple parameters in your main query are using Query Designer in the View mode rather than Edit Text mode, your WHERE clause will have to return all combinations using AND and OR. Let Query Designer build your query for you by using outer brackets and replace:

(Layout_Code IN (@Layout_Code))

with

( (‘ ALL’ IN (@Layout_Code)) OR (Layout_Code IN (@Layout_Code)) )

I suggest you have your WHERE clause already complete before adding the OR, since the results can get confusing.

3. CASCADING PARAMETERS WITH ‘ ALL’

You can use this method for cascading parameters where multiple parameters have the ALL value added. You’ll need to add the OR clause to your underlying parameter queries where applicable.

For example, if you have a parameter preceding the Layout_Code parameter called Layout_Group you will follow the same steps above, plus an additional step to edit the Layout_Code dataset query.

Add ‘ ALL’ to your Layout_Group parameter with a Union clause and add an order by
Edit your main query where clause to use @TopLayoutCode . Adjust the AND/OR in the WHERE clause accordingly.
Edit your cascaded parameter query, in this case Layout_Code, to OR in the where clause. Adjust the AND/OR accordingly.

Your main query where clause will look like this in the Query Designer View mode:

WHERE �
(‘ ALL’ IN (@Current_Layout_Group)) AND (‘ ALL’ IN (@Layout_Code)) OR
(‘ ALL’ IN (@Current_Layout_Group)) AND (Layout_Code IN (@Layout_Code)) OR
(‘ ALL’ IN (@Layout_Code)) AND (Layout_Group_Current IN (@Current_Layout_Group)) OR
(Layout_Code IN (@Layout_Code)) AND (Layout_Group_Current IN (@Current_Layout_Group))

Your Layout_Code query will look like this

SELECT DISTINCT Layout_Code
FROM         Your_Table
WHERE     (Layout_Group_Current IN (@Current_Layout_Group)) OR (‘ ALL’ IN (@Current_Layout_Group))UNION
SELECT     ‘ ALL’ AS Layout_Code
ORDER BY Layout_Code

4. SET YOUR PARAMETER DEFAULTS TO ALL

Set the default value for each of your parameters, Layout_Code and Layout_Group, to the value ALL. Be sure to put a space before the A if you are using this method to sort your list in Step 1.