This post is the fifth installment in the Build a Reporting Engine Using K2 SmartForms series.
In the previous post I shared some thoughts about database functions that would make life easier when building your reporting engine. In this post, I thought I would wrap up the data layer discussion with a word or two about stored procedures.
Creating the Report Query
Let’s talk a bit about the elephant in the room — the whole reason we built a reporting engine to begin with: creating the report query.
As I’ve mentioned in previous installments, you should understand that the secret sauce to this whole engine is the generation of a
WHERE clause that gets matched to a
SELECT statement to form a T-SQL query. This procedure is where that happens. Here are a few thoughts related to this important procedure.
Columns List Function
If my previous post left you wondering about the usefulness of a function for listing the column names of your datasource, consider this: This is the code I used for building that
SELECT @strColumnsList = [dbo].[fn_Columns_List] (@strTableName)
IF @strColumnsList IS NOT NULL AND LEN(@strColumnsList) > 0
SELECT @strSELECT = CAST('SELECT DISTINCT ' + @strColumnsList + ' FROM ' + @strTableName AS NVARCHAR(1000))
. . .
I stuffed the columns list into a variable, then (assuming a list was returned), I popped that list inside of another variable. BOOM.
SELECT statement complete.
Why did I use an
NVARCHAR? Read on!
The key to this procedure is a system stored procedure called
sp_ExecuteSQL. Normally, stored procedures are compiled code. But this command allows one to execute a T-SQL command in a string format (say, stored in a variable of a
NVARCHAR datatype). So when you unify your
SELECT statement and that
WHERE clause into a query, you’ll use the
sp_ExecuteSQL command to run it.
Multiple Procedures for Multiple DataSources
If you’re planning on allowing the user to select from multiple datasources, you’re going to need one of these procedures per datasource, I’m afraid. The limiting factor here is the K2 SmartObject. A SmartObject depends on the creation of properties that match the inputs and outputs of the stored procedure to which it’s coupled both by name and by data type. This is great for us 99% of the time, but in this case, not so much: I was hoping to return whatever columns I wanted from any datasource without having to map them to SmartObject properties. I thought about creating generic properties (“Column1”, “Column2”, “Column3”, etc.), but then I’d also have to convert the data types to something common, like
VARCHAR. Rather than mess with all of that, I opted for separate reporting procedures, to be called according to datasource by actions within the SmartForms rules.
Saving a Report Query
If you’re going to allow your users to save the queries they build, you’re going to need somewhere to put them. I’d mentioned previously that I have two tables set aside for this purpose: one to save the entire
WHERE clause and an arbitrary name, and another to save the component parts of the clause to ease loading the data back into the engine. The first table issues a Report ID that is a foreign key in the second table.
Here’s how I structured my
INSERT INTO [dbo].[Report] ([Data Source], [Query], [Name], [Originator], [Datetime], [Active])
VALUES (@strTableName, @strQuery, @strReportName, @strOriginator, GETDATE(), 1)
SET @intQueryID = @@IDENTITY;
-- call the report condition insert proc to parse the query into conditions and
-- further down into their components (attribute, operator, value). When a saved
-- report query is loaded, this data will be returned to the query engine and
-- loaded into the various condition controls.
EXEC [dbo].[usp_ReportCondition_Insert] @intQueryID, @strQuery
SET @intReportCount += 1;
Report Condition Insert
This procedure makes heavy use of that parsing function I talked about in previous posts. You’ll first split the
WHERE clause into conditions by splitting the clause on the conjunctions:
INSERT INTO @tblConditions (strCondition, blnActive)
SELECT item, 1
FROM [dbo].[fn_Split](@strQuery, ' AND ');
and then, inside of a loop, split each condition at it’s operator:
INSERT INTO @tblText (strString)
FROM [dbo].[fn_Split](@strCurrentCondition, @strCurrentOperator);
With the attribute and the value now both contained in the
@tblText TABLE variable, you’ll have to determine which is which. This is part of the reason why I added the brackets to each column name inside of my column list function — the value with the brackets is my attribute!
In this installment I shared some information you might consider when designing some of the stored procedures that will directly support the reporting engine. Hopefully the reasons for including the functions I talked about in earlier posts have become clear — they’ll take care of some of the “heavy lifting” for you!
I think this wraps up the reporting engine data layer discussion. We’ll talk more about K2 in the next posts.