Creating Reports From SharePoint Lists Using SQL Server Reporting Services (SSRS)

Now that you have collected all your information in your SharePoint lists, your business users will want to see reports off them.

You may want to leverage SQL Server Reporting Services for this (I would! I’d love to use SSRS for all the reports I need to create!), but we know it’s not an easy task.
Your options are:

  • Query the SharePoint database. Although I say it’s an option, this is NOT AT ALL recommended. Don’t touch your SharePoint content databases directly! Not only that it’s complicated to query it, it also adds extra load to the server that is outside of SharePoint’s control. MS also places no guarantee that the structure of the db will not change when patches or service packs are applied. Your changes may get overwritten, and you will have to re-do them, or re-architect your solution
     
  • Duplicate your data. You can either create an event that inserts into a parallel database when a record is inserted/updated/deleted from your SharePoint list, or dump the data to a database using a batch job that runs on a regular basis. If you choose the latter, you can go with either a console application, or PowerShell (yes, yes, I’m a big PowerShell fanatic). The advantage here is you gain awesome speed in your reports (which could very well be readonly, indexed to the max, and “written” only during the bulk updates). It also makes your report creation a lot easier! The big disadvantage is the latency, which may or may not be tolerable depending on your business.
     
  • Use Web Services But it is not great in terms of performance, and you cannot query and correlate multiple lists (for example if you need to “join” lists together in one report)
     
  • Use a reliable, third party tool
     

I’ve tried all of the above, and so far, my best bet is using a third party tool called Enesys RS Data Extension.

Enesys has made it easy to create data sources from SharePoint lists. And since I’m a database person, I am happy to report that they support a “limited” version of SQL syntax called SQLLite. Doing basic operations like aggregates, JOINs, UNIONs is fairly easy.

The best part is it plugs into Reporting Services with no issues! You just need to install the Enesys Server component, and away you go.

Interested to learn more? Have a look at these links:

Advertisements

9 comments

  • Hye,

    i am searching for reporting tools for SharePoint2007. Could you show us the steps and demo

    Like

  • Hi,

    I too have run through all these options, I created quit a few report with the Ensys tool, but found it took to long to craft the XML, and the report performance wasn’t too good either.

    I ended up creating utility that went down the Data duplication path, but not on an over night batch, instead I used Item Event Handlers to make the data replication occur in real-time.

    It has been hugely sucessful and it’s now become a fully bonfied product (i-PMO’s SharePoint Data Miner).

    Adrian

    Like

  • We’ve recently launched a commercial product called SQList that makes this process much easier, and should be relatively cost effective given the time and complexity of the custom development approach.

    It takes the pain out of creating clean, normalized SQL Server tables from your SharePoint lists and libraries. SQList is a Windows service; all it needs to access your SharePoint data is a user with at least read permissions and it will keep your SQL tables constantly up to date.

    Find out more here: http://www.axioworks.com/sqlist.aspx

    Like

  • Enesys RS Data Extension has a visual Query Designer (for quite some times now) that makes it unnecessary to deal with the XML Query syntax. A few hours is enough to understand the query construction concepts.

    Though reporting directly over SharePoint data has some performance impact it has the important advantage to be permission aware. You can run reports with the credentials of the user running the report and retrieve only items for which the user has permissions. This may be even more important when rolling up list’s items into an entire site collection.
    We believe that this approach is best suited for many scenarios (permission aware, report server centric, no sync management). However we realize that in other scenarios, when dealing with large lists for example, it may not necessary be the best fit.
    That’s why we have also released “Enesys IS Data Extension”, a set of Integration Services components that lets you integrate SharePoint data using Integration Services packages. Because we believe Enesys IS Data Extension is a complementary product (and not a replacement product), it is included with Standard and Enterprise Edition of our extension for Reporting Services.
    Of course, this is not to say that the other products mentioned here are not valid options to get the job done depending on its needs.

    Like

  • you can find a how to here
    http://ifixations.weebly.com/how-tos.html
    This has the XML and query to use SSRS to do your reporting. You can use the filter option inside your tables and charts to filter out data you don’t want. You can also use the query function in the XML. I need to post the how to for that still.

    Like

  • Hey, I am looking for the basics of how to install, connect and configure SQL Server with SSRS.
    I would be highly thankful if anyone can help please.

    Awaiting your response.

    Thanks and Regards
    Amit Mathur

    Like

  • @Adrian Bear – We are using your product, it must be one of the early versions as I don’t believe it is a complete product. For one I often see special characters like “1;#” before a value in a database field. I also cannot delete a Shadow Table, it says “unknown error”. So I try to delete every field first and then try to delete the Shadow table but still no luck.

    I also noticed the website slows when the Shadowing service is running.

    This is a good idea you got going but unfortunately filled with bugs which sometimes make it impossible to query.

    Regards,

    Yoshi

    Like

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