How to get SmartObject Data into Excel

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.

odata1

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.

odata2

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.

odata3

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

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

odata5

Next you can select the SmartObjects tables you want to load. You can load multiple or just one. Up to you.

odata6

Lastly you can save the connection and finish this wizard. I did not change anything here and just clicked on finish.

odata7

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.

odata8

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! 🙂

Advertisements

K2 SmartForms Debug Table concept

The K2 SmartForms debug table is a concept that has been floating around our team for a while and which brings out some very interesting discussions in our best practices meetings whenever it is being discussed. Some people are very much in favour of it, and some people does not like it at all. Either way – I’d like to share the concept with you today. I agree that it is not applicable to every situation and to every project. If you have larger and more complex forms and views in your projects then you probably will find a lot of value in this.

Continue reading

Configure MSDTC with a firewall

Problem description:

Some of you might have encountered an error like the following when you try to deploy a process – either with normal MSBuild deploy or with P&D:

System.Xml.XmlException: Root element is missing.

If you did a quick search on community.k2.com or other resources you probably would find out fairly quick that this usually is a problem when MSDTC has not been configured correctly. So I won’t go into the details of that configuration again, because this is quite well documented in several places such as: Continue reading

K2 and HTTP 400 errors

400Problem description

We had random and intermittent HTTP 400 errors when visiting various K2 sites on various environments / servers at our customer. This would include any combination of the following or all of these websites:

  • K2 Workspace
  • K2 designer
  • K2 viewflow
  • Smartforms Runtime

It could happen from any browser, but often when we receive HTTP 400 from one browser (say IE) you could open a different browser (say Chrome) and then it would work from there. Also one user would have the error while another user has no problems, and later on it would be the other way around. Continue reading

The one with the “Unable to find assembly” with K2 Connect for SAP.

The Problem:

We have a K2 connect service instance that was recently updated and K2 connect restarted. After this we started getting the following error message when trying to call any of our K2 Connect SmartObjects:

10702 An error occurred in the ConnectService service instance. Unable to find assembly ‘*********SAP*****, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null’.

We restarted the K2 Connect service, refreshed the K2 connect service instance, make sure the username and password was provided correctly, etc, but nothing seemed to work.

The Solution:

Eventually I found that I also had to restart the K2 Host Server. The reason being that the dll in memory by the K2 Host Server and in K2 connect is now slightly different and that is why you are getting the error. So I restarted both K2 Connect and K2 Host Server and everything was back to normal.

Hope that helps someone out somewhere.

K2 and AvePoint Scandanavia Roadshow

While we are on the topic of announcing things…

K2 and AvePoint have teamed up to for a roadshow of the latest insights around Business Applications, Workflows, Cloud Computing and Governance inside of SharePoint. Sounds like fun! This roadshow is aimed at the Scandinavian region and will be held in Oslo, Copenhagen and Stockholm, on March 18, 19 and 20 respectively.

Here’s the link to find out more: http://eu.avepoint.com/k2-avepoint/