We have been working with a client recently and as their reporting requirements unfolded it became apparent that the OOB and custom reporting made available through blackpearl wasn’t going to cut the mustard in certain instances.

Why? Well these guys needed reporting that was going to provide them with visibility of information that resides in their own custom data store (SQL Server tables) and also process specific information that blackpearl holds. This information was to be presented via SQL Reporting Services reports and for each instance of a process (submission of a Investment Plan) they needed process information (Folio Name, Start Date, End Date, Duration, Number of times a particular activity has occurred – in this case how many times the Rework activity has occurred) combined with information from their own SQL tables (Customer name, custom Status info and other details). All of this information was available through SmartObjects. The blackpearl OOB reports use their own SmartObjects and we made use of the Dynamic SQL Service to provide the connectivity to the custom SQL database which in turn provides data to our SmartObjects.

Initially our thoughts were to provide the reporting utilizing SmartObjects. SmartObjects are indeed a fantastic component within the blackpearl platform but from a reporting perspective are relatively lightweight. They provide limited “TSQL” functionality, we were to discover that they do not allow for things like aggregation (Select * from SmartObjectA) or Sub Selects which in this case were critical in order for us to produce the information our client needed.

I have raised support tickets for these as I believe they should support this common querying capability.

So we turned our attentions to what was contained within the K2 Databases. Within the K2ServerLog database we looked at the following tables:

_Act

_ActInst

_ProcInstData

Time to take a look at the SQL: We use this piece of SQL to essentially count the number of times a particular activity has occurred in this case the Rework Activity.

SELECT

TEO.Name AS 'TEO Name'

, TEO.Edumis

, TEO.Email AS 'TEO Email'

, TEO.AdUserName AS 'TEO Username'

, TEO.TEOType AS 'TEO Type'

, TEO.XYZArea AS 'TEO Area'

, Activity.Name AS 'Activity Name'

, Instance.StartDate

, Instance.FinishDate

, DataFields2.value AS 'Number of reworks'

, Advisor.FirstName + ' ' + Advisor.LastName AS Advisor

FROM

K2ServerLog.dbo._ActInst AS Instance – K2 Activity Instance table

INNER JOIN K2ServerLog.dbo._Act AS Activity ON Instance.ActID = Activity.ID -- K2 Activity table

INNER JOIN K2ServerLog.dbo._ProcInstData AS DataFields1 ON (Instance.ProcInstID = DataFields1.ProcInstID AND (DataFields1.Name = 'TEOID'))-- Process Instance Data Fields (TEOID). So we have the

INNER JOIN K2ServerLog.dbo._ProcInstData AS DataFields2 ON (Instance.ProcInstID = DataFields2.ProcInstID) -- Process Instance Data Fields (ReworkCounter)

K2ServerLog.dbo._ProcInstData table is essentially a key value pair table per instance for its data fields, with data field name as the key value and data field value as the value data.

INNER JOIN XYZ_IIP_POC.dbo.tblTEO AS TEO ON (CAST(CAST(DataFields1.value AS nvarchar(50)) AS INT) = TEO.TEOID)

INNER JOIN XYZ_IIP_POC.dbo.tblAdvisor AS Advisor ON TEO.AdvisorID = Advisor.AdvisorID

WHERE

Activity.Name = 'Plan Rework'

The Output

This article written by Robin Doddridge and Jian Sun of www.intergen.co.nz