UPDATE (8/11/2012): updated the link for the sample download files to point to my file on SkyDrive.
UPDATE (6/18/2011): added a PDF download option of the blog post.
UPDATE (6/15/2011): added file download which includes the Excel source file used to create SharePoint list and also two RDL files, the one in the demo and additional one that was created with multi value modification for the parameter.
When creating reports with Reporting Services (SSRS) you might find yourself having to reference data that is stored in SharePoint lists. Another task that you might need to do is to leverage that same data that is stored in the list to filter the results that are displayed to the user in the report. Let’s take a look at a scenario for this type of a report request. In order to get setup for this scenario we are going to do a little prep work first though to get the data created for the example. We will do this right after we go over the scenario.
Scenario
A request comes in to create a report that provides a list of the stores and to provide a way for the users to filter the stores that they see on the report based on the location of the store; in this case they would like to filter the records by country. The request states that the information needed for the report is currently being stored and maintained in a SharePoint list.
Just a heads up, in this example we will be using SharePoint 2010 along with Reporting Services (SSRS) 2008 R2. SSRS 2008 R2 includes a new SharePoint List Connection Type. You will see screenshots of this down below in the Solution part.
Prep Work
Okay, first we are going to generate the list in SharePoint that is going to be the source for our report where the store information is entered and maintained so that we can do this example. We are going to do the following task:
- Create custom Store Information SharePoint List by importing an Excel file as a source for the structure and initial data load
The Excel file that we are going to use for this list was originally created from the AdventureWorks2008R2 database by exporting the view [Sales].[vStoreWithAddresses]. You can download the AdventureWorks sample databases from CodePlex here.
 |
 |
| From the SharePoint 2010 site select Site Actions->View All Site Content |
From the All Site Content page click on the Create option at the top of the page to display the popup window for creating new content. Under the All Categories section on the left-hand side select Blank & Custom, then select Import Spreadsheet, and finally click the Create button. |
 |
 |
| Enter a Name for the new list to maintain the store information, click Browse to locate the Excel file that contains the store information, click Open, and then click Import |
A Excel window will open up so that you can specify what you want to import. Select the range of cells and then click Import. |
 |
 |
| The range of cells will be converted to a Table and you will see a progress meter on the bottom of the file stating ‘Adding Records’ |
Once the import process is complete you will have a new list in your site with all of the store addresses from the Adventure Works database. |
Solution
We will now create the report that will provide a list of stores and also provide a way to filter the records displayed to the end-user. All of this data will be generated from the SharePoint list that we just setup in the Prep Work section above.
 |
 |
| First step is to simply create a Report Server Project that will contain our report and to add a new Report item called Store List. |
Next step is to add a new Data Source to the report with the Type of Microsoft SharePoint List with the URL address of the site (in my case it is http://mybi10/) and then click OK. |
 |
 |
| Now add a new Dataset to the Data Source you just created, provide a name, click Query Designer, select the desired fields from the SharePoint list you created in the Prep Work, and then click OK twice to close out the Query Designer and the new Dataset dialog boxes. |
Repeat the previous step, but this time only select the CountryRegionName field from the SharePoint List. This is going to be the source for the Parameter we are going to setup next. |
 |
 |
| Add a new Parameter to the report and on the General page make sure you click on the checkbox for ‘Allow multiple values’ and also click the radio button for Hidden. |
On the Available Values page click the radio button for the ‘Get values from a query’, select the Dataset you created that contains just the CountryRegionName field, and then select the CountryRegionName for the Value and Label field drop-downs. |
 |
 |
| On the Default Values repeat the process that you just did for the Available Values with the exception that there is no Label field option. Once you have done this click OK to add the new Parameter. |
Now if we go back and run the query for the CountryNames dataset we created we will see that this is not a distinct list of values of Countries. Since this is coming from a SharePoint list we need to do a bit of extra work to generate this distinct list.Here is where the magic comes in and a little help comes in from the community and the Microsoft forums – “How to get distinct values of sharepoint column using SSRS”. |
 |
 |
| In the Report Properties we need to go to the Code page and add some code that will be referenced to create an ArrayList of values that will contain our distinct list of values, that will be sorted, and will also contain an “All” option as well. In the code above which is also provided below in the posting to reference there is some additional code to handle ampersands (&) in names as well, but in this case we do not have those, and I use a Vertical Par (Pipe) for a delimiter – need to be careful if your values had commas, so I go with the bar instead. Once you have the code in place click Ok to exit the Report Properties window. |
Now we need to setup an additional Parameter. Add a new Parameter and on the General page enter a name and prompt text. |
 |
 |
| On the Available Values page click the radio button for ‘Specify values’, click on the Add button, click on the fx button to enter an expression for the value and use the following=Split(Code.RemoveDups(JOIN(Parameters!CountryNames.Value, “|”)), “|”)Now click the Ok and go to the next step. |
Similar to the previous step, on the Default Values page click on the ‘Specify values’ radio button, click Add, click on the fx button to enter an expression for the value and use the following=Split(Code.RemoveDups(JOIN(Parameters!CountryNames.Value, “|”)), “|”)(0)This will return the first value in the ArrayList. Now click OK twice to close the expression window and to add the new Parameter to the report. |
 |
 |
| Next step is to wire the Parameter into the main dataset for the report. In order to do that we need to access the properties of the dataset and add a filter. |
Now we need to setup the Expression and we will use a formula for this so that we can handle the use of the “All” value.=IIF(Parameters!CountryName.Value=”All”, “All”, Fields!CountryRegionName.Value) |
 |
 |
| Next move down to the Value and enter another expression in order to account for the modification we did in the Code to handle ampersand ( & ) logic.=Replace(Parameters!CountryName.Value,”&”,”&”) |
Now that part is done, just need to create the desired report layout. In this case I use a List data region, but you could use a Table. I also included some additional logic to alter the background color (green bar effect).Don’t forget to add a sort to the data region as well. |
 |
 |
| Run the report and test out the filter. Here is the report for Canada.One thing to note is that we would want to update each field that might contain an ampersand with the REPLACE logic. |
After that logic is added into the expression for the field we are in business. Another option is to modify the placeholder for the data and enable the option for HTML support.You could simply create another function in the Code to handle the ampersand logic for reusability as well.Nice job;) |
Code Snippet used to remove duplicates from the dataset to present to the second Parameter defined in the report. I also added in the Sort after the ArrayList is fully populated along with the additional All value added to the beginning of the pipe delimited string.
Public Shared Function RemoveDups(ByVal items As String) As String
Dim noDups As New System.Collections.ArrayList()
Dim SpStr as String()
SpStr = Split(items ,"|")
For i As Integer=0 To Ubound(Spstr)
If Not noDups.Contains(SpStr(i).Replace("&","&").Trim()) Then
noDups.Add(SpStr(i).Replace("&","&").Trim())
End If
Next
Dim uniqueItems As String() = New String(noDups.Count-1){}
noDups.CopyTo(uniqueItems)
uniqueItems.Sort(uniqueItems)
Dim strRetValue AS String = String.Join("|", uniqueItems)
strRetValue = "All|" + strRetValue
Return strRetValue
End Function
Conclusion
The challenge with this solution was to get the parameter setup the way we wanted as a distinct list of values. Since there can be more than one store in each country the values were duplicated. Luckily we can handle this with some code and an additional hidden parameter. The other thing is dealing with SharePoint data and handling additional HTML markup.
Pretty fun and interesting huh? Little challenges like this are always fun to work with and it is great once you figure out the workaround to get this all to display the way you want.
Enjoy.
File Downloads:
Subscribe to Blog feed
Dan English's BI Blog 