Sunday, August 7, 2011

Part 4 – Using Business Connectivity Services (BCS) with SharePoint 2010 Workflow

· Part 1 - Pattern for Building Stateful Workflows with SharePoint Designer 2010
· Part 2 - Custom Action for Workflow Initiation
· Part 3 - Delays for Workflow Initiation Action
· Part 4 - Using Business Connectivity Services (BCS) with SharePoint 2010 Workflow
· Part 5 - Custom Visio Services Reports with SharePoint 2010 Workflow

Introduction

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

clip_image001

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.

clip_image002

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.

clip_image003

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.

clip_image004

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.

clip_image006

I then created another report that shows the duration by business process instance.

clip_image008

I even created another report that shows how employees perform.

clip_image009

I just embedded these reports into SharePoint 2010 and I was done. Now I can provide some real business insight into process execution.

9 comments:

Shyam Yadav said...

Hi JASON APERGIS you are a great men, I am really impressed by you, This is shyam Yadav From India working as a Software Engineer Sharepoint. I am just a beginner, I wanted to be like you, please keep on posting sharepoint great articles.
Would you be my teacher!
Great!

Shyam Yadav said...

Great Work done by you!

Jason Apergis said...

Shyam,

Thank you for the feedback. I run this blog on my personal time for past four years to help out the community and help myself be a better IT professional.

I do not tutoring or training. I have a lot of work and family life. Please keep reading the blog and go through old ones to see if there is other stuff here that will help you as part of your learning process.

Thanks,
Jason

Max DeRungs said...

Jason,

Your post raises my hopes in getting the BDC Identity value into a SQL table, but I must be missing something. I have one of those cases where the BDC Identity value is needed for a SharePoint reporting services web part that opens the display form of an item in an External List item through the ID= value in a URL. Without Visio Premium 2010, I am limited to working with SPD 2010 and a Site Workflow. Is that even possible?

Jason Apergis said...

Max - I am not sure I completely understand your question and what you want to achieve exactly.

Jason

Max DeRungs said...

Jason - Quite simply, is there a way to get the BDC Identity value from an external list into the origin SQL table of that list? Doing so with a workflow seems the most natural way, by copying the BDC Identity value to an adjacent text column in the same external list that then populates the SQL table of origin with the BDC Identity value.

Jason Apergis said...

Max,

That helps. I am not on the product team, I am field sales so I cannot give an exact reason why the way it unfortunately. However your question does get to the spirit of why I even made this post. Reason why I had to go down the entire path was because I could not get access to the table generated ID in the workflow when I performed both an insert and update in the same workflow instance. But when I performed the insert in one workflow instances, and then chained it to another workflow instance, I could get the update to work on the generated database key id.

Now I am rambling because it is late at night :-)

I did not think you have access to the BDC Identity in that way. Now as I am typing, and it months after I have written this blog, I would have investigated generating a GUID (or some other unique value) that can be pushed into the table on insert and then later used to perform subsequent updates.

Sorry I do not have a good answer on the BDC Identity.

Jason

Max DeRungs said...

Being the good Microsoft chap you are, is there someone on the product team you can point me in the right direction?

Jason Apergis said...

Max - Sorry I am probably not going to be able to get you an answer in a timely fashion. This is my blog and not an official Microsoft support mechanism.

Jason