Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Using SharePoint List data for Reporting Services (SSRS) parameter source

Posted by denglishbi on April 13, 2011

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.

image image
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.
image image
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.
image image
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.

image image
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.
image image
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.
image image
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.
image image
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”.
image image
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.
image image
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.
image image
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)
image image
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.
image image
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:

imageimage

About these ads

29 Responses to “Using SharePoint List data for Reporting Services (SSRS) parameter source”

  1. Cody said

    Could you possibly please move the images out of the table… they make it so the images on the right get cut off, and there’s no horizontal scrolling (Firefox and Safari).

    • denglishbi said

      it isn’t a browser related issue it is because of your resolution. not sure what you have setup. i have 1920×1080 and still have about 300px or so to spare to the right of the tables when viewing the posting. i modified the layout to be only two columns and removed about 300px from the width. LiveSpaces used to allow for a separate blog layout that would remove the sidebars for a posting if you clicked on the title, doesn’t look like WordPress has that. i added a div to get horizontal scrolling setup around the tables, but it flattened the text cells and looked really odd, so i removed that setup. hope the two column layout works for you.

  2. Cody said

    Thanks for doing that, it’s a bit better. While I use 1920×1200 and multiple screens at work, at home I do everything on a 13″ MBP where the standard res is 1280×800.

    • denglishbi said

      Because of the resolution issue I am going to look at creating PDF versions of some of my posts so that people can download them for references. Hopefully I can start doing that sometime next week and start adding that option as well. Thanks for bringing this issue to my attention.

  3. Bruce Patin said

    Here is an example of how to include a parameter in a CAML query of a SharePoint list as a report dataset: (I had to convert the first XML bracket to < for this comment. Hopefully, it displays correctly as a left angle bracket)

    <RSSharePointList xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>
    <ListName>Sample</ListName>
    <ViewFields>
    <FieldRef Name=”ID” />
    <FieldRef Name=”sampleString” />
    </ViewFields>
    <Query>
    <Where>
    <Contains>
    <FieldRef Name=”sampleString” />
    <Value Type=”Text”>
    <Parameter Name=”searchString” />
    </Value>
    </Contains>
    </Where>
    </Query>
    </RSSharePointList>

  4. gab said

    Could the CountryName parameter be a multi value parameter? I keep trying to set it up that way and running into trouble

    • denglishbi said

      This can be done, you would need to do some modifications to the setup of the filters section of the dataset. If you go with a multi-valued parameter setup you probably would remove the ‘All’ option. If you did that you can then set the filter expression to just the CountryRegionName, update the operator from = to In and then set the value of the filter to just the Parameters!CountryName.Value

  5. [...] Top Posts SSAS MDX Round = Banker’s RoundingUsing Reporting Services Report (SSRS) with PerformancePoint Services (PPS)Free Online PerformancePoint Services 2010 Training VideosInformatica PowerCenter vs. Integration Services (SSIS) ComparisonUsing SharePoint List data for Reporting Services (SSRS) parameter source [...]

  6. Suzanne Kriefall said

    Dan, Thank you. The section on creating distinct parameter values from a SharePoint list was incredibly helpful. We had no idea how to create a unique set of values for a parameter from SharePoint (SSRS 2008 R2) but the step by step instructions you provided did the trick. Thank you again, and I will be following this blog looking forward to additional very helpful information. Thanks for the good work.

  7. veethia said

    Hi sir, please advise for my question in the link below:

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/9f8d4dfa-9e30-4b47-bafa-6f39cfb41dd2/

    Really appreciate if there is a solution from the reply.

    • denglishbi said

      If you take a look at my example, if I changed the CountryName parameter to Allow Multiple Values, the next thing I would need to do is change the logic on the Filter setup on the dataset. I would first change my expression to be =IIF(Parameters!CountryName.Value(0)=”All”, “All”, Fields!CountryRegionName.Value) and then I would alter the Operator to be In instead of =. That is all you need to do. You do not need to change anything on the value side, SSRS will pass in the multivalue parameter properly, no additional JOIN logic is needed.

  8. veethia said

    Hi Dan, could uld you please give a solution for the link below.
    Really need your assistance.
    Thanks.

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/e4be0cc9-8e60-49a6-a512-04ab0e85efbb/#e4be0cc9-8e60-49a6-a512-04ab0e85efbb

  9. Michal said

    Hi, great how-to, however I still can’t get it work. I am doing this excercise on WSS 3.0 list in SQL Server 2012 R0 Express on Person or Group type of field, is that an issue or should it work?

  10. wingsbox said

    how can i get a carlander data of SharePoint 2010 in reporting service?

  11. Johanna said

    Hi! I would like to change the labels of the Available values in the Parameter. Showing the actual values in the parameter drop-down is not necessarily what the user needs to see.
    Can you help me with this please?

  12. nandhakumar said

    hi

  13. nandhakumar said

    Hi,

    how to dynamically update data from sql to ssas(Analysis service)

  14. Links doesnt work, please post the links where I can download….thanks in advance :)

    • denglishbi said

      Both links for the downloads at the end of the file go into my public SkyDrive area and are working for me. The first one you will select the download option from the top or the context menu and the second one should load the document in your browser.

  15. Ange said

    Fantastic, I have been searching for something like this for weeks…. There is not very much info out there on how to do this. Thank you so much.

    Can you tell me if I can do the same with a date ? Start Date and End date parameters ?
    Cheers

  16. Ange said

    Ok, got the date working fine and all else works from my VS 2008, but when I deploy my report to reporting services the dummyparameter is greyed out.

    I have changed the order etc to test and then I unhid the hidden parameter and found that it was not showing any data. hence the other one being greyed out.

    Can you tell me why I would not be pulling the data from my sharepoint list?
    I have tested the data source and it connects fine. It works from my machine beautifully but once deployed I cannot seem to see the data in my dataset for those parameters linked to my sharepoint list.

    Would appreciate any feedback on this……
    Thanks

  17. Ange said

    Hi there, don’t worry about my previous comments. I have got it all sorted. Working beautifully. The parameter greying out was a permissions problem….. argh – security, who’d have it. :-)

  18. Nice solution :) When I downloaded your file, the parameter work well. But when I tried to replicate it to my report, my parameter was grayed out and cannot enable select a value. Hope you help me figure out my problem. Thank You!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 89 other followers

%d bloggers like this: