It feels like not too long ago that we needed custom service brokers with coding and quite a bit of work to surface any SmartObject data into Excel. And it just so happens that this is a very often requested feature that I have seen in so many customers. So while we have some nice custom brokers to do this these days, I have never been a big fan of writing code in Office to update documents.
Well, the good news is that there is a much easier alternative available now. So let’s have a look at what that looks like.
The concept is that you will expose SmartObject data through an oData web service. Excel is then able to connect to oData services as an external data source.
First you need to enable oData for SmartObjects in the K2 management console. Open the K2 Management site and then navigate to Integration > APIs > SmartObject OData.
Toggle the SmartObject OData API to on. Now you can select which SmartObjects you want to make available. You can either select only certain SmartObjects, all but exclude certain SmartObjects, or make all of them available (which is obviously not recommended because it would be possible to retrieve interesting information from your server with that.
So in the example here I just selected a few of the built-in K2 reporting SmartObjects to pull some data about our process instances.
Note that the record limit is set to 1000 by default. In my environment this will be more than enough, because I do not have a lot of data on here, but you may need to evaluate this setting in your environment to determine what will work best.
Right – now, let’s hit “Apply Changes” and then go check this out. On the configuration screen you can see that it conveniently provides you the oData end point URL. So let’s load the URL and see what’s going on.
You should see an XML document loaded that describes the available Smos that has been published for your environment. This means we are good to go. If you see some error message here, then you will need some troubleshooting to fix it.
So with this we know we have SmartObject data exposed as an oData service, so let’s see what we can do with this in Excel. Open up Excel and go to the data tab.
On the data tab you can click on Get External Data from Other Sources. Then click on From OData Data Feed. Copy and paste the URL that we used before, and select your authentication options. I had to select “Use this name and password” on my VM to work. (PS: If you need AAD then come back soon for more details on how to do that).
Next you can select the SmartObjects tables you want to load. You can load multiple or just one. Up to you.
Lastly you can save the connection and finish this wizard. I did not change anything here and just clicked on finish.
Now you can select where to place the imported data in your Excel. I would recommend to put it in a blank or new worksheet. I think this will be easier to manage growing data sets.
Viola!! There you have Smo data in your Excel.
What you do with this next is of course up to you, but you should be able to use this data in any other sheets in the Excel document with formulas, graphs and what not.
If you need to refresh the data from the server then just hit the Refresh button on the Data toolbar and you have the latest data directly from your K2 server. Powerful stuff isn’t it! 🙂