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

 

Through reading the previous related posts, 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. So I thought I’d offer some thoughts on how to structure your database objects to support the reporting engine.

Data Source

Naturally, you’re not going to have much of a reporting engine if you don’t have data to make reports from. I’d recommend you create at least one database view for this purpose. Each view should be comprised of related data that could normally be obtained in a query through the use of joins.

Another advantage to using a database view is that you can rename the columns to terms that might be more friendly to the people who will use your engine. If, for example, your naming convention prepends a trigraph that describes the data type (say, “intID” to denote an ID column of data type integer), naming the column in your view as “ID” might clear up some confusion.

Finally, try to flatten everything out as well as you can. If you’re including data that is relationally held, like this (let’s say the table is called tblPhaseDate):

ID	Phase	Start Date	End Date
7	1	2018-11-03	2018-11-06
7	2	2018-11-06	2018-11-07
7	3	2018-11-07	2018-11-12

You’ll need to flatten it out in your view using multiple queries, like this:


(SELECT TOP (1) [Start Date]
FROM tblPhaseDate
WHERE (ID = MAIN.ID) AND (Phase = 1)
ORDER BY ID DESC) AS [Phase 1 Start],
(SELECT TOP (1) [End Date]
FROM tblPhaseDate
WHERE (ID = MAIN.ID) AND (Phase = 1)
ORDER BY ID DESC) AS [Phase 1 End],
(SELECT TOP (1) [Start Date]
FROM tblPhaseDate
WHERE (ID = MAIN.ID) AND (Phase = 2)
ORDER BY ID DESC) AS [Phase 2 Start],
(SELECT TOP (1) [End Date]
FROM tblPhaseDate
WHERE (ID = MAIN.ID) AND (Phase = 2)
ORDER BY ID DESC) AS [Phase 2 End],
(SELECT TOP (1) [Start Date]
FROM tblPhaseDate
WHERE (ID = MAIN.ID) AND (Phase = 3)
ORDER BY ID DESC) AS [Phase 3 Start],
(SELECT TOP (1) [End Date]
FROM tblPhaseDate
WHERE (ID = MAIN.ID) AND (Phase = 3)
ORDER BY ID DESC) AS [Phase 3 End],


and so on (where MAIN is a table joined on elsewhere in the query), to produce:

ID	Phase 1 Start	Phase 1 End	Phase 2 Start	Phase 2 End	…
7	2018-11-03	2018-11-06	2018-11-06	2018-11-07	…

The more data you include in the source, the more useful your reporting engine will be.

Finally, don’t forget that you don’t have to display all of the data you include. Flattening tables out like this can create a view with many more columns than will fit on the screen, so try to separate the data to be shown from the data available to mine.

 

Tables

To support your reporting engine, I’d recommend three tables — one to hold the queries that people submit and two for holding data related to report queries that people are saving.

Of the report query tables, one is to hold the name and the WHERE clause the engine sent to be executed, and the other breaks the clause back down into component parts so it may be loaded back into the report engine.

I want to show you the design I chose for that last table I talked about:


CREATE TABLE [ReportEngine].[Comparison](
[ComparisonID] [bigint] IDENTITY(1,1) NOT NULL,
[ReportID] [bigint] NOT NULL,
[Comparison] [varchar](500) NOT NULL,
[Attribute] [varchar](100) NOT NULL,
[Operator] [varchar](10) NOT NULL,
[Value] [varchar](200) NULL,
[Ordinal] [smallint] NOT NULL,
[Active] [bit] NULL,
CONSTRAINT [PK_Comparison] PRIMARY KEY CLUSTERED
(
[ComparisonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


I think it’s important to talk about these columns.

• [ComparisonID] is simply the primary key for the table. Everything below the word CONSTRAINT is associated with creating that key.
• [ReportID] is a foreign key that points to the other report query table.
• [Comparison]: Remember I defined a comparison as a complete equation, containing an attribute, an operator, and a value.
• [Attribute]: The attribute is what you’re comparing to the value
• [Operator]: The operator is how you’re comparing the attribute to the value
• [Value]: The value is what you’re comparing to the attribute.
• [Ordinal]: Recall how the reporting engine had three rows available for creating conditions. The ordinal value keeps track of what condition went on which row.
• [Active]: A bit used for “soft deletion” of data.

Here’s another look at the reporting engine interface:

Hopefully you can see by the structure how the data matches up with the interface design. The idea of this table is to return saved data back to the reporting engine.

 

Conclusion

In this installment 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. Hopefully you found value in some strategy for defining a database view to serve as your engine’s datasource, and for the careful attention which should be paid to persisting the clause in a way that will allow you to easily reassemble it in the engine at runtime.

In the next installment, I’ll touch on some key functions which could be vital to the engine.