This post is the second installment in the Build a Reporting Engine Using K2 SmartForms series.

 

The concept behind the reporting engine is simple: put the querying power of Transact-SQL (T-SQL) into the hands of business users through a simplified and intuitive interface.

That’s really all there is to it.

All the terminology I gave you in a previous post refers to the building blocks of a T-SQL WHERE clause.

T-SQL is WHERE it’s at

Consider the report query we built in the previous post. Two conditions, one specified that the ID had to be greater than zero, and the other specified that the name had to equal “Frank”:

Using the engine, we actually built a WHERE clause that read:

(ID > 0) AND (Name = 'Frank')

Had we executed the query, the engine would have sent that string to the data layer, which would have been appended to a T-SQL SELECT statement to form a completed query against a specified data source. The result set would have been returned to the presentation layer.

K2 SmartForms

From the K2 SmartForms perspective, each of those “rows” pictured is an Item View. Actually, each of the rows containing the conditions are the same item view added multiple times to the SmartForm (Yup! You can do that!)

You can get pretty fancy using rules. For example, I have some rules in place that trigger when the Remove button is clicked that will transfer the data from the row below to the current row, then hide the now empty row.

Not pictured above is the big shiny button that triggers the report query, nor the list view that displays the returned results.

 

Conclusion

So, in summary, the reporting engine is really a pretty way to assemble and send a T-SQL WHERE clause into a stored procedure that will append the clause to a SELECT statement and execute the whole as a query against a the datasource. Keeping this concept in mind may help you navigate future posts in this series.