How to Use a Current User Filter to filter an SSRS Report Web part in SharePoint (using a list of Available Values)

It is not an uncommon requirement to be able to publish an SSRS report to SharePoint and then use it in a Web part filtered by the user viewing it. You include the domainuserid in your main report query and filter the query on this value using a report parameter where the user value can by typed in.

However, in many cases the report does double duty as a report stored in a Report library, where users could choose multiple or ALL users. In that case you want to have a list of Available Values in your parameter so that someone viewing the report in the library can pick from a list of users rather than have to know everyone’s user id. The tricky part is formatting the domainuserid in your parameter so SharePoint can use it, and adding yourself to the list of available values so you can test that the Web part works as expected.

These are easy to do, but it took me a little bit of time to figure it out, so I’m blogging it here.

CREATE THE LIST OF AVAILABLE VALUES FOR YOUR PARAMETER

The SharePoint Current User Filter expects the domainuserid in the parameter list of available values to be all lowercase. So when creating the SQL Query for need to convert them to lower case. You also want to add yourself to the list for testing purposes, otherwise you will get an error when looking at the Web part in SharePoint. You can do this using a UNION clause. Your query for the list of available values should look like this:

SELECT DISTINCT
‘mydomain’ + LOWER(myuserid) AS UserAccount,
myUserName as UserName
FROM myTable

UNION
SELECT
‘ ALL’ AS UserAccount,
‘ ALL’ AS UserName

UNION
SELECT
‘mydomainmydevuserid’ AS UserAccount,
‘mydevusername’ AS UserName

I won’t go into the details here of how to use ALL in your report, but you can read more here http://thedataqueenblog.azurewebsites.net/2011/06/how-to-default-to-all-in-an-ssrs-multi-select-parameter/

Configure your user parameter as follows:

Publish your report to the relevant SharePoint library, and navigate to the library and test that the report and the parameter is working as expected.

ADD THE REPORT TO A WEB PART

Add the report to a web part as you normally would. You would add a web page, and configure a web part to be a SQL Server Reporting Services Report Viewer. Edit the web part and navigate to the report you created. Open the Parameters section and click on the Load Parameters button. You can leave the parameter default as “Use Report Default Value”. Click Apply and OK.

ADD THE CURRENT USER FILTER TO A WEB PART

Add a web part and choose the Current User Filter type from the Filters section. It will say that it is not connected.

CONNECT THE FILTER TO THE SSRS REPORT VIEWER

Go back to your Report Viewer web part and from the drop down choose Connections –> Get Report Parameters From –> Current User Filter

A dialog box will pop up where you can choose your User parameter and click on Finish.

Check in your changes and view the results. You will be able to see your web page with the report filtered on your user name. As a developer, if you do not have any values in this report you should see the report with no values returned, rather than getting an error. This is because you added your userid to the list of available values at the beginning of this exercise. You might want to remove yourself from the list once you have tested that the web part is working correctly.

Dynamically filter a Drop Down on your SharePoint list by another User Selected Value

This is one in a series of posts on using InfoPath. If you are new to SharePoint lists and InfoPath you might want to start with the Best Practices.

Best Practices for Cascading SharePoint Lists using InfoPath Forms

Use a SQL Server data source for an InfoPath pick list

Continuing with the same example I used in the Best Practices post, I’m going to make a change to an existing InfoPath form. Navigate to the SharePoint list called Supply Chain Scorecard Metrics and add a new item. In the Section drop down, notice that I’ve included the Goal as part of the Section description (Goals are Improve, Maintain and Track).

What I could do is have the user choose the goal, and then filter the Section list to show only those Sections which have that Goal. Here’s how we can do that.

ADD THE GOAL TO YOUR LIST

Navigate to your Supply Chain Scorecard Metrics list and in the List menu click on the List Settings. Add a column called Goal_ID with type of Number. Require that the column contains information. When the user selects a Goal we will store the Goal ID in the Metrics list, and then use that to filter the drop down list of available Sections the user can choose from.

ADD THE GOAL TO YOUR FORM

Navigate to your Supply Chain Scorecard Metrics list and in the List menu click on the Customize Form button to open up the InfoPath form.

Click OK and enter a password if necessary. Click Yes when it asks if you want to update the fields from the SharePoint list.

Add a row to your InfoPath form above Section by highlighting the Section row, right click and select Insert –> Rows Above.

From the Fields window, drag the Goal_ID field into the right hand cell in the row you just created.

Delete the label that says Goal_ID:, and type in Goal in the cell to the left of the Goal_ID textbox.

Right click on the Goal_ID text box and select Change Control –> Drop-Down List Box

Right click again and select Drop-Down List Box Properties.

Select Get choices from an external data source and then from the data source drop-down choose Supply Chain Scorecard Section.

Set Value = Goal_ID and Display name = Goal. Check the box that says Show only entries with unique display names. Click OK.

FILTER THE SECTION CHOICES BY THE GOAL SELECTED

Right click on the Section drop down list box and select Drop-Down List Box Properties.Beside the Entries box, click on the tree icon.

Click on the Filter Data button.

Click on the Add button.

Now we will specify on what to filter the Section list. In the first drop down select Goal ID. Leave the second drop down as “is equal to”. In the third drop down select “Select a field or group”

Change the data connection in the drop down to Main. Navigate to dataFields and then to Goal_ID.

Click all of the OK buttons. Publish your InfoPath form.

Add a new item. Select Improve as your Goal. Notice that the Section drop down has only those Sections that have a Goal of Improve.

And that is how you filter a drop down from another value in your list.

Use a SQL Server data source for an InfoPath pick list

This is one in a series of posts on using InfoPath. If you are new to SharePoint lists and InfoPath you might want to start with the Best Practices. The example I will be using here is a continuation of the one started in the Best Practices post.

Best Practices for Cascading SharePoint Lists using InfoPath Forms

Dynamically filter a drop down on your SharePoint list by another user selected value

Before you even start, be aware that using this method allows the password for the SQL connection to be stored in text format directly in the data connection. Be sure you put security measures in place to disallow others from navigating to the connection file in the SharePoint library, and create a database login with a low level of read-only security to information which is not sensitive. We are using the data connections for simple pick lists, so this shouldn’t be an issue.

Create a SharePoint library to store the data connections for InfoPath. The Type of library is a Data Connection library. Call it DataConnectionsInfoPath. The data connection information for the InfoPath connections will be stored in text format so be sure that security to this folder is limited to only administrators.

Create a SQL login to the database to which you will be connecting to get the data used in the pick lists for SharePoint. The login information will be stored in the data connection as text, so be sure to create a separate login with read only capabilities to only those tables required for the InfoPath pick lists used in SharePoint. Choose a password that is not commonly used by other admin connections. (C’mon, you know you reuse passwords).

Create a view in your database from which the pick list will be fed. The pick list can only be sourced from one table, so by creating a view you can control the values that show up in that table. If you need to filter out, change or add any values in the future, you can use the view to do it without having to change the data connection or the InfoPath form.

Navigate to the SharePoint list which has already been set up, in this case Supply Chain Scorecard Metrics from my previous blog post. Open the InfoPath form by clicking on the Customize Form button in the List menu.

To get the pick list for Site from a SQL Server view do the following.

Right click on the Site text box on the form and change the control to a drop-down list box.

Right click again and edit the properties.

Select Get choices from an external data source. Click the Add button beside Data source.

Create a new connection to Receive data.

Receive data from a Database (Microsoft SQL Server only)

Click on the Select Database button.

Click on the New Source button

Select Microsoft SQL Server

Enter your server name and use the login you created at the beginning of this exercise.

Select the database where your pick list data is stored, and select the view you created at the beginning of this exercise. The InfoPath data connection that you will be sharing in the SharePoint library will have the query imbedded in it, so you can’t just have a connection to the database. You need to choose a specific table.

Save your connection file.

Re-enter you database password. Click Yes to save your password into the InfoPath connection file in plain text.

Click Next.

Click Next, Next, Finish.

Leave the Value as @Site_ID and change the Display name to @Site by clicking on the folder tree to the right and selecting Site.

Click OK.

PUBLISH YOUR INFOPATH DATA CONNECTION TO SHAREPOINT

From the Data menu, select Data Connections.

Select vwSitePicklist and click on the Convert to Connection File button.

Key in the location of your InfoPath Connection library you created earlier, and add a name for the data connection you are creating. Give it a suffix of .udcx. Click OK. It will take a minute or two to create the connection.

Click Close.

Navigate to your SharePoint library that contains the InfoPath data connections. You will see your new data connection here. Notice that the Approval Status is “Pending”. You need to change it to Approved before you can use the data connection.

Click on the dropdown beside your new data connection. Select Approve/Reject.

Select Approved. Click OK.

Now go back to InfoPath and publish your InfoPath form. Navigate to your SharePoint list and Add a new item. You can choose a Site from the Site pick list which is pulling directly from SQL Server.

Pretty cool, right? No need to push data into SharePoint and keep it synchronized, just pull directly from SQL Server. I love it!

Best Practices for Cascading SharePoint Lists using InfoPath Forms

This is the first in a series of posts on using InfoPath. You will find links to the others at the end of this post.

I find on many occasions that users have a need to capture some pieces of information but don’t want to build an application to capture it. SharePoint lists can be a useful way to capture this data, if you plan carefully and use best practices. At times users need to control several different pieces of related information, and would like to use information from one SharePoint list or SQL Server table as a pick list or drop down in another SharePoint list. This can be effective if you put the right controls in place to keep the data in synch and to maintain data integrity.

You could use a SharePoint Lookup column in your list to keep this data in synch, but doing so can have complications. I avoid Lookup columns as a rule, since they store the underlying values as a mash up of SharePoint ID and description, looking something like 12;#Description. When you pull this into your database you have to do some work to get the data into useable form. And if you want to refer to or display any other information from the lookup list into the cascaded list using InfoPath you can’t do it. What I prefer to do store is the ID of the item (sometimes the SharePoint ID and sometimes the underlying ID from the data warehouse), and use InfoPath to get the description of the item and any other relevant fields. I then use the ID for any joins on the data warehouse side of things, and I use SSIS to keep the descriptions updated across the cascading lists. So if Vendor Name got updated in a lookup list, I would join on the ID and use SSIS to update the description in any cascaded lists, since we are not using a SharePoint Lookup column to do this for us.

A combination of SharePoint column settings, InfoPath and SSIS is a great way to put the required controls in place. It takes a bit to get the hang of it, but it has a lot of flexibility to enforce data integrity and to keep the descriptions in synch between lists. The only drawback to using InfoPath forms is that users can no longer use the Datasheet View to enter and update data. With large lists this can be a problem for users, so you need to decide if Datasheet View is a requirement for your users before using InfoPath forms.

When I design a cascading list scenario, I like to build the underlying SQL tables and report first, get the structures exactly as needed, and then build the SharePoint lists based on the existing table structures. Without doing this, a lot of changes can be discovered along the way, and having to go back and change the SP List, SSIS Package, SQL tables and reports is not an efficient use of time. Best to build the tables and report, hammer out the correct structures and build the SharePoint lists and InfoPath forms on top of that.

For the step-by-step I will build 4 lists where users can enter data to control a department scorecard. These 4 lists will be used to populate these 5 tables which have already been defined:

DIM_Supply_Chain_Goal

DIM_Supply_Chain_Section

DIM_Supply_Chain_Metrics

FACT_Supply_Chain_Target

FACT_Supply_Chain_Actual

I won’t be showing you the SSIS packages to populate these tables, as that is another subject. I am simply demonstrating how to can use SharePoint lists to capture data with a good user experience. You can then use these lists to populate underlying tables of a different structure using SSIS. For more details on pulling the data out of SharePoint lists using SSIS please see my previous post – How to use a SharePoint list as a data source in your SSIS package.

Users will be able to update the values for these tables from SharePoint using InfoPath forms. SSIS will be used to import the data into SQL Server and populate these tables. The end result will be a department scorecard where users can control the values for actuals and targets, as well as the sort order of all of the goals, sections and metrics. Some of the actuals will be calculated by SSIS using data from an ERP system, and some will be user controlled. The targets are all user controlled. And the descriptions and sort order of Goals, Sections and Metrics are user controlled.

The lists are designed with the best user experience in mind, rather than building them to match the underlying tables. Some data transformation will be done by SSIS to extract the data from the lists and load it in the correct format into the tables.

SharePoint List creation, best practices:

Column Settings: The first step is to create the SharePoint lists which will feed the tables above. It is important to match the data types in the list to those in your underlying tables as closely as possible, but only for those columns that the user will be editing. For example if you are expecting the Goal field to have no more than 50 characters, then put that restriction on the column in your list.

For any calculated columns which are concatenations of fields, leave the default of 255 characters, since you may not know exactly which fields you will be concatenating yet.

For user Sort Order fields, make them Number type with 0 decimal places and choose a maximum value so you know how many leading zeros you will need to add to the ListSort column. More on that later.

When it comes to storing something like Year, you’ll want to make this a text field, since the display of Number will put a comma for thousands. So 2012 would look like 2,012. You could either have 2 fields for year in your list, one for display and one for the value to be sent to the data warehouse which makes sense in the case where a user could key in the number, or you could use a dropdown to control what the user can choose – store the value as a string, and then convert it on the way into the data warehouse.

Column naming convention: When naming your fields don’t put any spaces, The underlying name for that column captured behind the scenes will have special characters added in place of the spaces. When you get to SSIS this can be annoying. Avoid spaces when first naming the field, and then go back and rename the field with the space. The original value will be retained underneath the hood, but users will to see the space on the List View making the user experience better. for example, name the column Sort_Order when you first create it, and then go back and change it to Sort Order. You only need to rename those columns which will be visible in the List View, since this is strictly for user experience.

Built-in columns for display descriptions, sort order, item uniqueness and item title: Some additional best practices I have adopted for data integrity and for flexibility in changing logic without changing underlying structures is to add some calculated columns.

ItemDescription – The description stored in this column can be pulled into cascading lists to get the full description displaying in the list downstream. I have InfoPath populate it with whatever combination of descriptors make the most complete description of that list item to be displayed downstream.

UniqueItem – Be sure to check off Enforce Unique Values when creating the UniqueItem column. Since we are using InfoPath to control the user experience it makes sense to put all the logic to populate these fields in InfoPath, rather than creating calculated columns in our list. We will concatenate the appropriate combination of data to define a unique list item, and the Enforce Unique Values property will throw an error if a user tries to create a duplicate item.

ListSort – I also add a column called ListSort in which I can place the combination of values by which the list view should be sorted. SharePoint only allows you to sort a list view by up to two values. By concatenating the combination of values into one field it makes it very easy to sort your list. If you will also be including a numeric Sort_Order field which users can control, be sure to set a maximum value that they can enter. This will be important when you use this Sort_Order to populate your ListSort fields downstream in the cascading lists. For example if you are sorting your Section list by the Goal sort and then the Section name, you will need to concatenate these fields. SharePoint sorts numbers stored in a text field as text, so the numbers 1,50 and 100 would be sorted as 1, 100, 50. Since we are using the ListSort text field as our best practice for all lists, we will want to add some leading zeros to the Sort_Order when populating the ListSort. The maximum value will tell you how many leading zeros to add. If your maximum value is 999, you will need to add up to 2 leading zeros. As a best practice make the maximum value for your Sort_Order 999,999 and concatenate 6 leading zeros. This will become clear in our example.

Title – I also prefer to leave the Title column as a calculated field, to be a combination of one or more of the values a user will enter into the list. This gives me complete flexibility to have the Title reflect a good description of the list item without restricting that description to only one field in the list which a user may key in. Keep this at default size of 255 since it may be a concatenation of fields.

You end up with 4 additional calculated columns in your list, and although in some cases they may all contain the same information, you have built in the flexibility to change display descriptions, sort order, item uniqueness and item Title without have to change the structure of your list or the SSIS packages. You can change the logic in the InfoPath form without having to touch the underlying structures.

Create the SharePoint Lists:

List 1. Supply Chain Scorecard Goal

Change the Title field to Goal, and add a column called Sort_Order of type Number with 0 decimal places. To enforce the data integrity, require this field to have information and Enforce unique values.

After you save the Sort_Order column, go back and rename it to Sort Order (with a space). The end result is a list with 4 columns, Item (title), Goal, Sort Order, and UniqueItem. While this particular list may not need the addition of Item and UniqueItem, since Item, Goal and UniqueItem may all end up being the same value – it is good design to include all three columns to give you the flexibility to change it later.

List 2. Supply Chain Scorecard Section

List 3. Supply Chain Scorecard Metrics

List 4. Supply Chain Scorecard Actuals

Modify the List View:

Go back and remove any columns from the List View which you do not want the users to see – including UniqueItem and ListSort, reorder the columns as required, and set the view to sort by ListSort.

Do this by selecting your list, clicking on the List tab in the SharePoint menu and selecting Modify View.

Deselect any items you don’t want to show up in the view. Reorder by changing the Position from Left dropdown.

In the “First Sort by the column:” drop down select the ListSort column.

Do this for all of the lists.

Edit the First InfoPath Form:

Start with the first pick list in the cascade of lists. The one that does not depend on any of the other lists. It would be the highest level in the hierarchy of the cascade, in this case Supply Chain Scorecard Goal. This list does not choose a value from any other list, but the Supply Chain Scorecard Section list will use the Goal list as a pick list.

InfoPath Form 1. Supply Chain Scorecard Goal

Although this list does not depend on any of the others, we would like to use InfoPath to allow users to edit the two editable fields – Goal and Sort Order, and have InfoPath populate the underlying calculated fields of Title, ItemDescription, UniqueItem and ListSort. In this case the list is a very simple one, so each of these 4 calculated fields will contain just one value – Goal. The list will be sorted by Goal, the uniqueness of the list will allow only one row for each Goal description, the title will display the Goal and the description passed on to the cascading lists will be Goal.

Open your list. In the SharePoint menu go to the List tab and select Customize Form.

A dialog box will ask if you would like to open the form in Microsoft InfoPath. Click OK. InfoPath will open, and the default form for your list will appear with all of the underlying fields on it.

For the calculated fields which the user does not need to see, highlight the row

and click delete.

Now only the two fields remain, which the user will populate.

UNIQUEITEM

To populate the calculated fields look at the Fields pane on the right hand side of the screen. Right click on the UniqueItem field and select Field Properties.

Beside the Default Value click on the expression editor button

Click on the Insert Field or Group button

Select the Goal field and click OK.

Your formula now looks like this:

Click OK. Make sure the checkbox is checked which says “Refresh value when formula is recalculated”. This will ensure that each time the Goal description is changed, the UniqueItem value will also be updated to the new Goal description.

Click OK. For now, repeat this for each of ItemDescription, Title and ListSort. Be sure that you populate all four calculated fields correctly before going to the next step, since we have required that these fields be populated. If they are blank you will not be able to add any items to your list.

Click on the InfoPath File menu and click the Quick Publish button. This will publish your changes back to SharePoint.

Now you can add some items to your list. You will need to do this to have some data for the next step, which is to edit the InfoPath form for the cascading SharePoint list. You will want to test that it is working, so you need to have at least one item in the Goal list. Add some items – add Goal = Improve, Sort Order = 1. Now add Goal = Improve, Sort Order = 2. Notice that you get an error message stating that there is a duplicate value for UniqueItem. We selected Enforce unique values on the UniqueItem column to ensure users do not accidentally enter duplicate items. UniqueItem lets you control what constitutes a unique value.

Click OK and change the Goal = Maintain. Save the item. Add one more item – Goal = Track, Sort Order = 3. Your list is now complete.

TITLE

Take a look at the List View. Notice how the Title is aligned to the left and Sort Order is to the extreme right. This doesn’t seem like the best user experience.

Let’s change the logic in our InfoPath form to make the Title calculation a concatenation of Sort Order and Goal. Then we can remove the Sort Order field from our List View.

Go back to the InfoPath form you edited. It should still be open. In the Fields pane on the right hand side of the screen right click on the Title field. Select Field Properties. Click on the expression editor button for the Default Value. Delete the current formula which says Goal. Click on the Insert Function Button.

Select the Text Category and then select the concat Function.

Click OK. You can double click where it tells you, but we are going to delete everything after concat( and then click on the Insert Field or Group button. Select the Sort Order field. Type a comma. Type “ – “. Type a comma. Click the Insert Field or Group button. Select the Goal field. Type a close bracket. You should end up with this formula.

Click OK.

Publish the Form. File –> Info –> Quick Publish.

Go back to your SharePoint list. The changes to the title will not take effect until the Goals are edited. Open the Edit window for each of the three items in your list and save the items. The Title has changed to the new value.

Modify the view and remove the Sort Order field from the View, since it is now part of the Title.

ITEMDESCRIPTION

In your downstream cascading lists you will want a good description of your item. It may be the same as Title, but it may not. By having this field in place as a best practice, you can control how it will look.

For now we will leave the ItemDescription = Goal.

LISTSORT

Since we want the List to sort by the numeric value of Sort Order, and want to keep with our best practices of using ListSort as our standard sorting mechanism which will also be used downstream, we need to edit the calculation for ListSort. If we leave the view to sort by ListSort as it is now, it will sort alphabetically by Goal.

Open your InfoPath form if it is not already open. Right click on the ListSort field and select Field Properties. Click on the expression editor button for the Default Value. Click on the Insert Field or Group button and choose Sort Order. Click OK. Your formula should look like this.

Click OK, OK and publish the form. Go to your list and for each item in turn select Edit Item, and then Save. The ListSort values will not get updated with the new value until the items are edited, since the value is only refreshed when the formula gets recalculated. Add a new item to your list, Goal = Test Sort Order = 11. Since ListSort is a text field the list is sorting alphabetically by the Sort Order value we put in the ListSort field. This does not give the desired results since numbers stored in a text field get sorted like this:

At this point you might decide to just change the sort order on your list to use the Sort Order field, but when you want to use this downstream in your cascading lists you are going to want to be able to sort by a concatenation of Goal sort order and other sort orders which may not be numeric. For example you might want to sort the Section list by the Goal sort order and then the Section description. We can do this by adding some leading zeros to the Sort Order value when storing it in the ListSort.

Go back to your InfoPath form. Right click on the ListSort field and select Field Properties. Click on the expression editor button for the Default Value. Create the following formula using the Insert Function button and the Insert Field or Group button. You can type in the functions, but must select the Sort Order field using the Insert Field or Group button.

substring(concat("000000", Sort Order), string-length(string(Sort Order)) + 1, 6)

Notice that we are adding 6 leading zeros to the Sort Order value and then choosing the last 6 characters of the resulting value. Your formula should look like this.

Notice that we didn’t have to change the data type or structure to implement this change to the value in ListSort, but simply changed the logic. Now go back and Edit and Save each of the items in the list. Notice that they sort correctly now.

Use the First SharePoint list as a Pick List in the Second Cascaded List:

Close InfoPath if it is still open, and navigate to your second list – Supply Chain Scorecard Section. This list will use the Goal SharePoint list as a pick list.

Open the list, navigate to the List tab and click on the Customize Form button on the SharePoint menu. Click OK on the message. Highlight and delete the rows in the form for Title, Goal, ItemDescription, UniqueItem and ListSort. Edit the label on the form for Goal ID to Goal, and Display Toggle to Display. You should end up with a form that looks like this:

LOOKUP AND STORE ID FROM ANOTHER LIST

In the text box for Goal ID on the form, right click and select Change Control –> Drop-Down List Box.

Right click on it again and select Drop-Down List Box Properties.

Click on the radio button Get choices from an external data source. Click on the Add button beside the Data Source.

Leave the default values of Create a new connection to Receive data.

Click Next. Select SharePoint library or list.

Click Next. Using Internet Explorer navigate to your Goal list. Copy the URL and scrape out anything to the right of and including Lists. This is the SharePoint site where the list is stored. Enter this value in the Data Connection box.

Click Next. Select the Supply Chain Scorecard Goal list.

Click Next. Select all of the fields you created, and sort by ListSort. You will be using more than one field from this list, and it is easiest if you have already included the fields in your data connection.

Click Next, Next and Finish. In the Value field of the Drop-Down List Box Properties window click on the Tree button. Select ID. You will be storing the SharePoint ID of the Goal in the Goal_ID field of your Section list. Change the Display name to ItemDescription.

Click OK. This will store the ID, but display the ItemDescription to the user in the InfoPath form.

LOOK UP DESCRIPTIONS FROM THAT LIST

We would also like to store the ItemDescription so we can display it in our list, and for that we need a calculated field to go and fetch the description matching the ID now stored in the Goal_ID field.

In the Fields pane right click on the Goal field and select Field Properties. Click on the expression editor beside the Default Value. Click on the Insert Field or Group button. Click on the Show advanced view link.

In the Fields drop down change the data connection to the Supply Chain Scorecard Goal connection you created when populating the Goal ID.

Open up the dataFields toggle and the SharePointListItem_RW toggle. Select ItemDescription. This is where the best practices come in handy, since we already know that this field was created specifically to display a useful description in cascaded lists. Now, DON’T CLICK OK. This is something you have to get used to. You need to filter the data first to get the corresponding Goal description which goes with the Goal ID already stored in your list. If you make a mistake and click okay now, you will have to go back and navigate the data connections again. It doesn’t remember where you were in the tree. Click on the Filter Data button.

Click on the Add button to add a filter. In the first drop down select the ID column. You are building an expression that will find the ItemDescription that corresponds to the ID stored in your main data connection. Leave the middle drop down as ‘is equal to’. Select the third drop down. Click on ‘Select a field or group’.

In the Fields drop down navigate to the Main data connection.

Open up the dataFields toggle and the SharePointListItem_RW toggle. Select the Goal ID field.

Click OK. Your filter condition looks like this:

Click OK. Your Filter Data looks like this:

Click OK and OK again. Your formula looks like this.

Please note that you couldn’t just type this expression into the box because the context of the data connection is stored within the filter. If you are interested you can select the Edit Xpath checkbox to can see the underlying code.

Click OK. The field properties looks like this.

Be sure that the Refresh value checkbox is selected. Click OK.

POPULATE THE CALCULATED FIELDS

Just as we did for the Goal SharePoint list, we also need to populate the calculated fields in this list.

A UniqueItem for this list will be a combination of Goal ID and Section name, since we could potentially have a Section of the same name in more than one Goal.

Title will be just Section

ItemDescription, to be used downstream, will be Goal plus Section

ListSort needs to incorporate the ListSort of Goal as well as the ListSort for Section. If a user were to change the sort order of the goals, we would want the Section list to be reordered accordingly. So we need to go and get the ListSort for Goal, and then concatenate it with our formula ListSort.

Right click on the ListSort field and paste our formula into the Formula box.

substring(concat("000000", Sort Order), string-length(string(Sort Order)) + 1, 6)

Delete the first reference to Sort Order and replace it by clicking on the Insert Field or Group and selecting Sort Order. This will put the XPath context of Sort Order into the formula.

Do the same with the second instance of Sort Order. Your formula should look like this.

We want to concatenate the ListSort value from our Goal in front of this formula. Type “concat( , “ at the beginning of the Formula window and add a “)” bracket at the end of the formula. Put the cursor after the “(“ . You are about to insert the ListSort value for Goal into the formula so the cursor must be in the right position. Your formula looks like this – cursor is highlighted in red.

Click on Insert Field or Group. We are going to follow the same steps we used to get the Goal description. In the Select a Field or Group window select Show advanced view.

In the Fields drop down change the data connection to the Supply Chain Scorecard Goal connection you created earlier.

Open up the dataFields toggle and the SharePointListItem_RW toggle. Select ListSort. This is another spot where the best practices come in handy, since we already know that this field was created specifically to sort the goal list. Now remember, DON’T CLICK OK. You need to filter the data first to get the corresponding Goal description which goes with the Goal ID already stored in your list. If you make a mistake and click okay now, you will have to go back and navigate the data connections again. It doesn’t remember where you were in the tree. Click on the Filter Data button.

Click on the Add button to add a filter. In the first drop down select the ID column. You are building an expression that will find the ListSort that corresponds to the Goal ID stored in your main data connection. Leave the middle drop down as ‘is equal to’. Select the third drop down. Click on ‘Select a field or group’.

In the Fields drop down navigate to the Main data connection.

Open up the dataFields toggle and the SharePointListItem_RW toggle. Select the Goal ID field.

Click OK. Your filter condition looks like this:

Click OK. Your Filter Data looks like this:

Click OK and OK again. Your formula looks like this.

Click OK and OK again. Publish your form.

Go to your Section list and add a few items. Notice that even if you put a lower Sort Order with a Maintain goal it gets sorted after the Improve goal.

Use the Second SharePoint list as a Pick List in the Third Cascaded List:

I’m not going to do as much detail on the screenshots for this one, since a lot of it is rinse and repeat. I’ll tell you the steps.

Navigate to the third SharePoint list – Supply Chain Scorecard Metrics.
Open the InfoPath form by clicking on the Customize Form button in the List menu.
Remove all the rows in the form except for Metric, Section ID, Site, UOM and Sort Order.
Set up the Section ID lookup:
1. Edit the Section ID label to say “Section” and right click on the textbox and Change the Control to a Drop-Down List Box.
2. Right click again and select Drop-Down List Box Properties. Select Get choices from an external data source
3. Click the Add button beside the Data source.
4. Create a new connection to Receive data.
5. From a SharePoint library or list
6. Enter your SharePoint site URL
7. Select the Supply Chain Scorecard Section list.
8. Select all of the fields that you created. Sort by ListSort.
9. Next and Finish.
10. Change the Value field to ID and the Display name to ItemDescription. Click OK.
Edit the Section field properties.
1. Right click Section, Field Properties.
2. Click on the expression builder button beside Value.
3. Click on Insert Field or Group. Click on Show Advanced View.
4. Select Supply Chain Scorecard Section. Navigate to dataFields and then to ItemDescription.
5. Click on Filter Data.
6. Click on Add.
7. Select ID in the first drop down, leave “is equal to” in the second drop down.
8. In the third drop down choose Select a field or group. Change the data connection in the drop down to Main. Navigate to Section ID. Click all of the OKs.
Edit the Title field properties. Concatenate the Metric and Site field.

concat(Metric, " ",Site)

Edit the ItemDescription field properties. Concatenate Section and Metric and Site.
- concat(Section, " – ", Metric, " ", Site)
Edit the UniqueItem field properties. Concatenate the Metric and Site field.

concat(Metric, " ",Site)

Edit the ListSort field properties. It will end up looking like this: concat( ListSort[ID = Section_ID] ,substring(concat("000000", Sort Order), string-length(string(Sort Order)) + 1, 6))
1. Enter the formula for the List Sort and set up to concatenate the Section List Sort field to the beginning of it.
  - concat( <insert Section List Sort here> ,substring(concat("000000", Sort Order), string-length(string(Sort Order)) + 1, 6))
2. Show advanced view. Select the Supply Chain Scorecard Section data connection.
3. Navigate to dataFields and select ListSort.
4. Select the Filter Data button. Click Add.
5. Select ID in the first drop down, leave “is equal to” in the second drop down.
6. In the third drop down choose Select a field or group. Change the data connection in the drop down to Main. Navigate to Section ID. Click all of the OKs.
Publish the InfoPath form.

Navigate to your list and add some items. All should be working as expected.

Use the Third SharePoint list as a Pick List in the Fourth Cascaded List.

This is almost a repeat of the last section. The difference is that the user will not be choosing a sort order. The Supply Chain Scorecard Actuals list will contain the Metrics for each year. This means the ListSort will be based on Year and Metric ListSort. UniqueItem will be based on Year and Metric ID. ItemDescription won’t be important because there will be no other lists downstream from this one. I’ll write out the steps just as a refresher.

Navigate to the fourth SharePoint list – Supply Chain Scorecard Actuals.
Open the InfoPath form by clicking on the Customize Form button in the List menu.
Remove the following rows from the form: Title, Metric, UOM, ItemDescription, UniqueItem, ListSort.
Set up the Metric ID lookup:
1. Edit the Metric ID label to say “Section” and right click on the textbox and Change the Control to a Drop-Down List Box.
2. Right click again and select Drop-Down List Box Properties. Select Get choices from an external data source
3. Click the Add button beside the Data source.
4. Create a new connection to Receive data.
5. From a SharePoint library or list
6. Enter your SharePoint site URL
7. Select the Supply Chain Scorecard Metrics list.
8. Select all of the fields that you created. Sort by ListSort.
9. Next and Finish.
10. Change the Value field to ID and the Display name to ItemDescription. Click OK.
Edit the Metric field properties.
1. Right click Metric, Field Properties.
2. Click on the expression builder button beside Value.
3. Click on Insert Field or Group. Click on Show Advanced View.
4. Select Supply Chain Scorecard Metrics. Navigate to dataFields and then to ItemDescription.
5. Click on Filter Data.
6. Click on Add.
7. Select ID in the first drop down, leave “is equal to” in the second drop down.
8. In the third drop down choose Select a field or group. Change the data connection in the drop down to Main. Navigate to dataFields and then to Metric ID. Click all of the OKs.
Edit the Title field properties. Set it to equal Metric.
Edit the ItemDescription field properties. Set it to equal Metric.
- concat(Section, " – " , Metric)
Edit the UniqueItem field properties. Set it equal to the Year + Metric ID field.
Edit the ListSort field properties. It will end up looking like this: concat(Year, ListSort[ID = Metric_ID])
1. Enter the formula “concat (“, click on the Insert Field or Group button and select the Year field. Key in a comma some space and a “)”. Place the cursor right before the close bracket. You will insert the Metrics ListSort field here.
  - concat( Year, <insert Metric ListSort here> )
2. Show advanced view. Select the Supply Chain Scorecard Metrics data connection.
3. Navigate to dataFields and select ListSort.
4. Select the Filter Data button. Click Add.
5. Select ID in the first drop down, leave “is equal to” in the second drop down.
6. In the third drop down choose Select a field or group. Change the data connection in the drop down to Main. Navigate to Metric ID. Click all of the OKs.
Publish the InfoPath form.

Navigate to the list. Add an item.

Display a Look up field on the Form:

You notice that it would enhance the user experience to be able to see the UOM beside the Target, Thresholds and Monthly Actuals on the form when editing or adding an item. We will look up the UOM and keep it on the form as a ‘display only’ so when the user is entering the targets and actuals they will be aware of the UOM for the Metric.

Highlight the entire right hand column, right click and select Insert –> Columns to the Right.

Drag the left edge of the new column to make it smaller. Drag the UOM field from your Fields list onto the form, in the cell beside Target. Delete the Label that says UOM beside the textbox you dragged onto the form.

Right click on the textbox and select Text Box Properties. On the Data tab click on the expression editor button.

Click on the Insert Field or Group button. Click on Show advanced view, and in the dropdown at the top select the Supply Chain Scorecard Metrics data connection.

Navigate to dataFields and then to UOM. Click on Filter Data. Click on Add.

Select ID in the first drop down, leave “is equal to” in the second drop down. In the third drop down choose Select a field or group.

Change the data connection in the drop down to Main. Navigate to Metric ID.

Click all of the OKs until you get back to the Text Box Properties window. Click on the Display tab. Check off the Read-only box. Click OK.

Right click on the UOM text box you created and select Borders and Shading. Change Borders to None. Change the shading to No Color. This will make it look like part of the form, instead of as a text box which might be editable.

Right click on the UOM text box you created, and select Copy. Paste it into each of the cells where you would like to display the UOM on the form.

Publish the InfoPath form. Navigate to the list and add an item. Notice that as soon as you select a Metric the UOM appears beside all the textboxes to be populated with that metric by the user.

There are lots of other things you can do with InfoPath, but I think this is more than enough to get you started.

I have also blogged a few other related subjects while writing this post, since this one already covered so much. These two are a continuation of the example started here.

Use a SQL Server data source for an InfoPath pick list

Dynamically filter a drop down on your SharePoint list by another user selected value

You might also find this interesting:

How to use a SharePoint list as a Data Source in your SSIS Package

Maintaining Data Integrity of a SharePoint list with SSIS

Maintaining Data Integrity of a SharePoint List with SSIS

I like using InfoPath to maintain data integrity as it has a much finer control than SSIS. I will blog about that later. But it can be very useful to stick with SSIS to maintain the data integrity on a simple list which synchronizes data between a database and a SharePoint list. For example if users do not have an available interface to define groups for their customers, they can use a SharePoint list to house the customers and define the groups.

In this example the customers are coming from an ERP system and so the account names and ids must be correctly maintained in the SharePoint list. It’s the typical three tasks required for maintenance: insert, update and delete. Any new customers should automatically be added to the list, any descriptions which have changed should be updated, and any customers who have been incorrectly added to the list should be deleted. I find the simplest way to manage this is to have a separate table in the data warehouse strictly for the data coming from the SharePoint list. This way you can match up to your ERP tables to see what data management is needed.

Create your SharePoint list

Have the field sizes match the ERP field sizes, to ensure you don’t run into any size conflicts. Best practices for creating a SharePoint list is to name your list and your field names without any spaces in them. Then go back and change them to add any spaces that you might want for readability. SharePoint doesn’t like spaces and will add strange characters internally to fill the gaps. When you go to do your SSIS package it makes things less readable. So, in this example a column in the list will be called “Account Status”. So I first name it “AccountStatus”, and then go back and change it to “Account Status”. Internally it will still be called “AccountStatus” without the space.

Here is my list for this example. It has 4 columns, Debtor Name, Debtor ID, Account Status and Debtor Group. All of these columns except Debtor Group will be maintained by the SSIS package. Users will key in the Debtor Group.

Populate the data warehouse with the most current data from business system

This example assumes a data warehouse situation. Create a package in your SSIS project. Keep in mind where in your project you need to run this package. It should come after the Debtor table has been updated from your ERP system. It’s best to put the two related items in the same package, to ensure that the order of operations does not get lost in a master package somewhere. So first step is to add the SSIS tasks which populate your Debtor table. Put them in a sequence container.

Insert, Update and Delete data in SharePoint list

There are series of 8 SSIS tasks which will “synchronize”, at a particular point in time, your SharePoint list with the data warehouse. To do this create a Sequence Container called “Update Debtor Group SharePoint list, and add and connect these 8 consecutive tasks.

1. Delete from SP_Debtor_Group: Add an Execute SQL taks to your container called “Delete from SP_Debtor_Group”. I always prefix any working SharePoint tables with SP for ease of identification. We haven’t created this table yet, but it will be automatically created in the next step. For now just add the task which will execute the sql statement “Delete from SP_Debtor_Group”

2. Populate SP_Debtor_Group: Add a Data Flow task to your container called “Populate SP_Debtor_Group”. This will populate your new table with whatever data currently exists in the list in SharePoint. Right now there is probably no data in the list, but let’s create the step. Add a SharePoint List Source to the Data Flow work surface, Edit the Properties of the source for SiteUrl and SiteListName to connect to your Debtor Group SharePoint list. (For instructions on how to do this, view my blog post here.) Add an OLE DB Destination and connect to your data warehouse, set Data Access mode to “Table or view – fast load”, and click on the NEW button beside the “Name of the table or the view” to create the new table. The initial statement will look like this, but edit it to give the table the chosen name, and move or delete fields as required.

Keep in mind that you need to keep the ID field in order to do any updates in SharePoint, and the Title field is whatever is the field which shows up as a hyperlink in your SharePoint list, in this case Debtor Name. I prefer to keep the field types as whatever SharePoint has allowed, since when we are sending data back up to SharePoint it’s easiest to have it already in the correct data type. If I need the data type to be something different in the data warehouse I will do the conversion before populating the data warehouse table. Your Create Table script should look like this.

Click on the Mappings tab and map the Input Columns from the SharePoint list to the Destination Columns in your new table.

3. Delete duplicate records from SharePoint: A business decision was made to delete the newest record from SharePoint in cases where duplicates exist. The only reason duplicates might exist is if a user added it, and this SharePoint List is only supposed to be updated by the SSIS packages. Add a Data Flow Task to your container, called “Delete duplicates from SharePoint”. Add an OLE DB Source to your Data Flow work surface.

SELECT MAX(ID) as ID FROM SP_Debtor_Group
WHERE DebtorID IN
(
SELECT DebtorID
FROM SP_Debtor_Group
GROUP BY DebtorID
HAVING COUNT(*)>1)
GROUP BY DebtorID

We only need the ID in order to identify which records to delete. Write the appropriate query to identify those records which are duplicates. Add the query to your OLE DB Source. Place a SharePoint List Destination on your work surface. Edit the Properties of the destination for SiteUrl and SiteListName. Be sure to change the BatchType to “Deletion”. Click the Refresh button. Go to the Column Mappings tab and ensure that the ID is mapped on the Input Column and the Destination Column. Click OK.

4. Delete invalid records from SharePoint: Add a Data Flow Task to your container, called “Delete from SharePoint”. Add an OLE DB Source to your Data Flow work surface.

SELECT SP.ID
FROM SP_Debtor_Group as SP LEFT OUTER JOIN
DIM_Debtor as DB ON SP.DebtorID = DB.Debtor_ID
WHERE DB.Debtor_ID IS NULL

We only need the ID in order to identify which records to delete. Write the appropriate query to identify those records. Add the query to your OLE DB Source. Place a SharePoint List Destination on your work surface. Edit the Properties of the destination for SiteUrl and SiteListName. Be sure to change the BatchType to “Deletion”. Click the Refresh button. Go to the Column Mappings tab and ensure that the ID is mapped on the Input Column and the Destination Column. Click OK.

5. Update descriptions in SharePoint: Add a Data Flow task to your container. Call it “Update SharePoint descriptions”. Add an OLE DB Source to your Data Flow surface. Write the SQL statement that you would like to use to ensure that any descriptions are update appropriately. Remember that DebtorGroup is owned by the users, so we don’t want to overwrite anything in SharePoint. We won’t update that field. And we need the SharePoint ID field in our query in order to identify which SharePoint record to update. The fields we would like to update are Debtor and Account Status, to ensure that any changes are uploaded to SharePoint. Here is the query.

SELECT SP.ID,
DB.Account_Name as Debtor,
DB.Account_Status as AccountStatus
FROM SP_Debtor_Group as SP INNER JOIN
DIM_Debtor as DB ON SP.DebtorID = DB.Debtor_ID

Drag a SharePoint List Destination onto your Data Flow surface. Connect the OLE DB Source to the destination. Edit the destination with the appropriate SiteUrl and SiteListName and be sure the BatchType is Modification, since we will be modifying the SharePoint records. Click the Refresh button. Then select the Column Mappings tab. Map the Input and Destination columns. Remember that Title is whichever field has a hyperlink in your list (Debtor) and that you have to include the ID column in order to identify which record to update.

6. Insert new records into SharePoint: Add a Data Flow Task to your container, called “Add records to SharePoint”. Add an OLE DB Source to your Data Flow work surface.

SELECT
DB.Debtor_ID as DebtorID,
DB.Account_Name as Debtor,
DB.Account_Status as AccountStatus,
DB.Account_Name as DebtorGroup

FROM SP_Debtor_Group as SP RIGHT OUTER JOIN
DIM_Debtor as DB ON SP.DebtorID = DB.Debtor_ID
WHERE SP.DebtorID IS NULL

Write a query to populate new records into SharePoint. In this case the Debtor Group should default to the Debtor Name until the users can update it. Add the query to your OLE DB Source. Place a SharePoint List Destination on your work surface. Edit the Properties of the destination for SiteUrl and SiteListName. Be sure that the BatchType is “Modification”. Click the Refresh button. Go to the Column Mappings tab and ensure that the fields are correctly mapped on the Input Column and the Destination Column, especially Title. Click OK.

7. Delete from SP_Debtor_Group again: Add another Execute SQL taks to your container called “Delete from SP_Debtor_Group again”, to refresh the data in your data warehouse table with the newly cleansed data from SharePoint. Add the task which will execute the sql statement “Delete from SP_Debtor_Group”

8. Populate SP_Debtor_Group again: Add a Data Flow task to your container called “Populate SP_Debtor_Group”. This will populate your table with the cleansed data from SharePoint. Add a SharePoint List Source to the Data Flow work surface, Edit the Properties of the source for SiteUrl and SiteListName to connect to your Debtor Group SharePoint list.Click Refresh. Add an OLE DB Destination to the Data Flow surface. Select the SP_Debtor_Group table as the destination.

Click on the Mappings tab and map the Input Columns from the SharePoint list to the Destination Columns in your new table.

These 8 steps will periodically clean the data in your SharePoint list. It will depend how often you run the package, how clean the data will stay. And of course how careful the users are. It may seem cumbersome, but these steps can save a lot of headaches on bad data when using the end result in reporting.