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

 

In the previous post I shared some information you might consider when designing your data source and some of the database objects that will directly support the reporting engine — specifically, tables and views. In this installment I thought I’d offer some insight into functions I used when building my reporting engine.

Parsing Function

I’d mentioned previously that the reporting engine is really nothing more than a nice interface for building a T-SQL WHERE clause, and that the engine works by sending the entire clause into the data layer, where it is combined with a SELECT statement to form a complete query.

I’d also underscored the importance of being able to parse that clause back into
its component parts so it may be loaded back into the engine so the report can be re-run.

This ability to parse the clause back into each condition and further into each attribute, operator, and value is critical to loading saved query information back into the engine. And this is best achieved through the use of a database function.

If you’re running at a high enough compatibility level, you may have access to a split function already built into Microsoft SQL Server. Unfortunately, I’m not that lucky.

Happily, there are plenty of examples of parsing functions available on the Web. If you don’t have one (and don’t want to build one yourself), consider copying one and trying it out.

 

Column List Function

This one might be optional, depending on how important a capability for selecting multiple datasources is to you. It’s a capability I wanted, so I wrote a function to look inside the information schema to get the column names and return them as a list.

If you care to opt for such a function, here are a few things to consider:

• The ability to sort the column names by your preference. Sometimes you might want the list in alphabetic order (hint: SmartForms will arrange them this way by default!) and at other times you might have them in a some other specific order. So you might want to build this capability into your function, so the list can be returned in your preference.
• The ability to exclude columns from datasources could be a desirable thing.
  Sometimes it doesn’t make sense to expose all of the columns for reporting —
  say, for example, columns that were included in the design but never actually used in the application. If this appeals to you, consider adding a NOT IN statement into your WHERE clause.

The heart of your column list function might resemble this:


INSERT INTO @tblColumn (strColumn, blnActive)
SELECT '[' + COLUMN_NAME + ']', 1
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE   (
        TABLE_NAME = @strTableName
        )
        AND
        (
        COLUMN_NAME NOT IN (SELECT item FROM [dbo].[Split(@strExcludedList,','))
        )
ORDER BY COLUMN_NAME ASC


You can see that inserted the column name records that I wanted into a table variable, and that I extracted those column names from INFORMATION_SCHEMA.COLUMNS. You can also see that I chose to surround the column names with literal brackets, which does two things: (1) it makes sure the column names are interpreted as literal values, so any column names containing spaces or using reserved words are still interpreted as column names; and (2) it makes these attributes easier to differentiate from values when parsing later. Finally, you can also see that I’ve pressed a string function into service here — that’s because I chose to create comma-delimited lists of the column names I wanted to exclude.

 

Conclusion

In this installment I shared some database functions you might consider when designing your report engine data layer. A split function could be essential for parsing the WHERE clauses you store into their component parts for loading back into the engine later. And a column list function could be a very nice addition, particularly if you plan on providing multiple datasources for users to build reports against.

In the next installment, I’ll touch on some things I learned while writing stored procedures to support the reporting engine.