Product Review: Reflex Reporting – Easily Create Reports from SharePoint Lists

There have been lots of good utilities for Sharepoint have been made that helps make life for the users easier. For example, a permission management tool like http://www.qipoint.com/sharepoint-permissions-tool can be used to improve security. Is this new utility up to the same useful standards?

Reporting in SharePoint using out-of-the-box methods is not a great experience. You’re only able to access a single list, and you can’t aggregate your reports across multiple sites very easily. Your reports or views can break if a field is changed or removed. And while you have some basic sorting and filtering capabilities, you can’t do advanced filtering or joins across multiple lists.

While you could query data across lists by making web service calls, you’d need a developer to write and maintain that code, which is time consuming and costly. The content query web part is one OOTB alternative to rolling up data, but it’s very limited on functionality and difficult to configure the look and feel to display the data you actually want to see (again you’d need a developer for that). However, there are other alternatives to look at such as this sharepoint dashboard variation that’s been built by SaaS developers to allow for easy data visualization without any coding or additional tools.

Multi-site, real-time SharePoint reporting with Reflex Reporting

Reflex Reporting from ThorApps gives you the capability to easily produce multi-dimensional reports from content in your SharePoint lists. By instantly mirroring SharePoint list items into SQL tables, your users can easily create real-time reports in the reporting tool of their choosing – Excel, Reporting Services, Tableau, even Crystal Reports – without disrupting the SharePoint list data or placing any load on the SharePoint server.

Some of the features included in Reflex Reporting, from the ThorApps website, are:

• Cross site collection reporting (or across an entire farm) by processing multiple list instances of the same type into a single SQL table for that list type.
• Real-time data access through item level event receivers the SQL data is kept up to date as the user saves the item in SharePoint.
• Rapid report development using industry standard tools (e.g. Reporting Services, Crystal Reports or more)
• Performance through querying SQL server directly and using indexes rather than complex SharePoint list connectors or custom web services.
• Flexibility by storing the content in simply structured SQL table you can do all the common functions you need (i.e. grouping, sorting, summing etc).
• Custom Input Validation by using SQL server you can define almost any validation rule you need.
• The right way by extracting content into a separate database for reporting you don’t compromise your support from Microsoft for your SharePoint environment.

There are two versions of Reflex Reporting, one for SharePoint on premises, and a newly-released version for SharePoint Online. I am reviewing the on premises version for SharePoint 2013.

Note: You will need to have access to the SharePoint server and Central Administration in order to install and configure the tool.

Installing the Solution

Installing the solution is very simple. You can download a free trial from the ThorApps website and test it out before purchasing. The cool part is that you can download the trial without giving any user information if you choose (see figure 1). That way you don’t have to give any user information until you decide to purchase.

After you’ve downloaded the zip file, simply unzip it and run the install batch file for the SharePoint version that you have. This will run a PowerShell script that installs and deploys the reporting and licensing solutions to your farm. You can see that the solutions have been deployed successfully by going to the Solution Management screen in Central Administration (see Figure 2).

And navigating to the Application Management page, you will see two new sections at the bottom of the screen (Figure 3). These links will be used to configure the reporting databases in the next steps.

Configuring the Reporting Databases

After installation, next you need to set up and configure the reporting databases. There are two databases that need to be created. One is to store the schema or configuration information, and the other is to store the actual data from the SharePoint lists.

On the Application Management page in Central Admin, click on Reflex Databases. This will take you to a screen where you will set up the reporting schema database (Figure 4).

The screen defaults to the SQL database server that your SharePoint farm is utilizing, however you can point this to any SQL database server that you wish. It’s also suggested that you name the database Reflex_Schemas, but again, you can name it whatever you want.

After saving, you’re redirected to a page that will allow you to add a new reporting database (Figure 5).

After clicking Add, you’ll provide a display name, database server, and database name for your new reporting database (Figure 6).

After saving, you can verify that your two new databases got created by opening SQL Server Management Studio (Figure 7).

The next step is to create one or more new reporting schemas. A reporting schema will map fields from a SharePoint list or lists into appropriate fields in a SQL table.

NOTE: Before creating a reporting schema, you must make sure that your SharePoint list exists.

I’ve created an Issues list that I’m going to use for my reporting schema (Figure 8).

Click Add New from the Reporting Schemas page (Figure 9).

Give your reporting schema a name and list data table name. The name is what will be used to distinguish this schema from others, and the list data table name is what the SQL table that gets created will be named (Figure 10).

Do NOT click Save yet. You must first configure the fields by clicking Add Fields. Next you will select a site collection if you’re not currently in the correct one, and then navigate to the list you want and select it (Figure 11).

Now you can unselect any fields that you don’t wish to create in the database, as well as display and select hidden fields if you wish (Figure 12).

After saving, you’ll see all the fields have been configured with their field type, and the SharePoint field name and type (Figure 13).

Make sure that the Enabled check box is checked, then click Save and the Issues table will get created (if the field is not checked, the schema information will get saved, but the table will not get created in the database yet).

There is still not any data in the table however, even though there is data in my SharePoint list. A nightly job runs that copies the data into the database, but if you don’t want to wait that long, you can manually populate the data immediately.

From the Application Management page, click Lists to Manage, then click on the SharePoint list name that you want to manage (Figure 14).

Click Process Now (Figure 15). This will run the job that populates the Issues database table with all the SharePoint list data.

We can go into Management Studio to verify that the records exist in the table now (Figure 16):

You may be wondering how often new records get added to the table after a new entry is made in the SharePoint list. For new entries, there is an event receiver that runs on the SharePoint list that copies the entry to the SQL table immediately. The nightly job (or manually processing) is for copying all records from a new list that has just been configured.

Now that your SharePoint list data is in a SQL table, users can start creating custom reports using the tool of their choice. You can even join this table to other SQL tables so that the business users can analyze and visualize related data in meaningful ways.

My Thoughts

While obviously the setup and configuration of the database and field mappings need to be done by a technical person such as a SharePoint Administrator, I view this tool as a way to help the end users consume SharePoint data in new ways, including viewing richer reports that can provide better interaction and intelligence.

The installation and configuration was fairly straightforward and not difficult to set up, although it can seem a little daunting when glancing at the configuration manual the first time. As long as you are patient and follow the steps exactly in the manual, you won’t have any issues.

Another nice feature is that you can configure a reporting schema to “roll up” several lists into one. In the example I went through above, I mapped a single list to a reporting schema. However, if you have several lists on different sites with the same type and name, you can map them all into one schema and aggregate all the data together (think of how the content query web part works). A great example is if you have many Tasks lists across multiple sites. You could aggregate all those together and then create reports where users could sort and filter on their own tasks, for example.

One thing to note, if you create a schema that includes lists from across a site collection, you’ll need to manually add the event receivers to the schema. This can be easily done through Central Administration. In the example I showed above, the event receiver was automatically applied when the table was created since I was working with only one list.

I also think that the pricing model is very fair. There is only a one-time fee for the product and you are not paying annual support costs year after year. And the price is a fixed fee per farm. It doesn’t matter how many servers you have in your farm, or how many users; the price is the same.

To purchase is very simple as well, simply contact ThorApps and they’ll provide purchasing details and send you a license key. Or if you need an official printed quote you can request a quote and it will auto-generate one for you.

To learn more about Reflex Reporting for SharePoint, please go to the ThorApps website.

Product review is sponsored by ThorApps, but opinions are my own