If you have been reading this blog series closely I mentioned that I will be discussing how we can do reporting for our workflow solutions in SharePoint 2010.
As you may know when you configure a workflow in SharePoint there is a Workflow History list that must be associated which will stored information about the execution of a workflow instance. I have typically viewed this as more of a log. It is not something to be used for reporting. Here is a quick reference about it and I suggest you read it - http://technet.microsoft.com/en-us/library/ee662522.aspx
The reasons I do not use it for reporting is because:
· This list can potentially store a lot of data from a lot of different workflows.
· It will be purged after a period time. Even though you can disable a job to ensure logs are not deleted, doing this would disable the job for every workflow across an entire web application which is not good practice.
· Personally I like it for its logging capability but I would not use it for a reporting capability.
Solution
My approach will be to store data in a SQL Server table using Business Connectivity Services (BCS). With SharePoint 2010 it is so easy to support this from a workflow without doing a ton of code. Now I can do inserts and updates operations to reporting tables and then build reports off of them using the either Excel Services, SSRS, etc.
I am not going to go over the steps on how to create a SQL Table or how to create an external content type using BCS. What I will cover is how to use the external content type from a workflow and discuss one tricky configuration you may not know about.
For this solution you need to:
· Create any sort of SQL tables you need.
· Create external content types to connect to those tables.
· Create external lists on the site to use the external content types.
· Finally I will cover how the workflow will call the external list as a proxy to make transactions against the SQL tables that you will use for reporting.
Insert a Record from a Workflow
There is really nothing special to this at all. Add a Create List Item action into my workflow that references the external list. In my case, the external list I created was called “Review Actions Table”.
Note that I created a variable called ReviewActionID, where I store the SQL generated ID of the new item that was created. If you need to update this SQL record as part of a different workflow process, I recommend updating this value into a column on the item where the workflow is running against.
Updating a Record
Now this is the tricky part. Let’s say later on in the same workflow process you want to update the same SQL record you just created. To accomplish this just add an update action like the following.
The tricky part is the configuration of the update – look at the screen shot below. Notice how I have to select the BDC Identity Field to get the primary key value to perform the update. If you do not do this the update will fail (sorry do not have the error log handy).
Initially I had set it to use the ID Field on the external content type thinking it would have the generated key from the SQL table as part of the original insert I did early. However that was not the case.
Note you do not need to create this BDC Identity column; it is auto-generated for you..
One more note, I had to do this for a SQL Server table with an auto-generated primary key. I have not tested this out for other scenarios like a web service or another DB platform.
Finally let’s say I need to update an external list item on a record that was created prior to the workflow being started. Add an Update List Item action to the external list like the following. Notice here for the Field I use the ID column from the external content type. We do not have to do what we did earlier.
Conclusions
Why do I get so excited about this? Now I am able to start dumping data out into SQL server to do reporting with SharePoint 2010 Business Intelligence solutions. I can create reports with Excel Services or SSRS. In this example I used SSRS using Report Builder 3.0 to create reports about the execution of my business process.
For instance I created an SSRS report that shows the average duration of steps in the business process. I embedded the reporting into SharePoint and I was off and running.
