Friday, November 1, 2013

Data Driven Subscription using SharePoint List

In this article I will walk through the steps to create a data-driven subscription using a SharePoint list as a data source.  I'm using SSRS 2012 integrated in SharePoint 2010.

In the data driven subscription pane, choose a Description, set the Data Source Type to Microsoft SharePoint List, enter the url to the SharePoint site (obviously replacing the {SharepointDomain}/{SPSite}/ with the appropriate values, and entering in the credentials to be used by the subscription to access the list and execute the report.


The Next part is building the Query to be used.  I find it easiest to build the query by opening up SSRS Report Builder or Visual Studio and using the Query Designer inside of SSRS to help me.  In the example below, I've set up a SharePoint Data Source in my report called "SharePoint" which has the same same connection string as above:  http://{SharepointDomain}/{SPSite}


Opening up the Query Designer will take you to a page that looks like this.  In this example, I'm going to use the "subscription test" list for my data-driven values for the subscription.  I select the fields I need, and in this case, I put a filter in it to restrict my list to only list items that have a BeginDate less than 10/1/2013.  You may test your data by pressing the "Run Query" button as shown in the green box below.  This allow you to see exactly how the data will be returned.


Pressing "OK" takes me back to the original window and gives me my query syntax.

I copy the selected syntax and then go back to the data-driven subscription window and paste that code into the Query section.  Pressing the "Validate" button will check to make sure the syntax is correct.  At this point, the hard part is done...the rest of the subscription should be pretty straight forward.


The next page lets us wire up the parameters with the BeginDate and EndDate we retrieved from the SharePoint list query.


The next page lets us wire up the email address to the one in our SharePoint list.  It also requires us to specify a rendering type and subject.


The remainder of the subscription is setting the schedule just like any other subscription, so I won't provide any details on that screen.  Pressing "Finish" gives you a data driven subscription using a SharePoint list as your data source!

The hardest part for me is getting that query.  I've found that using the SSRS query designer to help me write the query is much easier than typing it all in myself.  The query designer is usually where I go to get the "stub" of my query, and then I manually change the bits and pieces in it that I need for the particular situation.  Good luck in your next SharePoint driven Data-Driven subscription!

4 comments:

  1. Hi Jayson, I see you have had more luck than me with accomplishing this. I have followed all the steps you list, but when I send out the subscription it returns with an error... and if I check the database it shows that instead of taking the values from the fields it takes the field names instead... any ideas? (From example To gets the field name of To)

    ReplyDelete
    Replies
    1. Hi Emery--have you run your query inside of Visual Studio or Report Builder to verify that the query is returning the expected values from the list? (See the 3rd image in the article for a reference). If it is, then make sure you have your "To" parameter mapped correctly to the values from the database (see the last image in the article for a reference).

      Delete
    2. Hi there:

      We are using the same sort of approach. However just wondering if there is any way not to send the email if report is empty.

      The SSRS reports are deployed within SharePoint that connect to external data source which is a database.

      Regards,
      ASC

      Delete
  2. I have this same thing setup but the report can return several rows for the same person and subsequently that person gets an email for each time they appear in a row in the report. I used to do this just fine with a regular SQL query by doing Select Distinct on the query that drove the subscription but that is not an option with this xml based querying. Any ideas?

    ReplyDelete