T-SQL reporting engine

Levente Kupás

5.00/5 (6 votes)

20 Jun 2013CPOL6 min read

26.3K

If you have big reports which run slowly, you may use this T-SQL based engine to speed-up them.

Introduction

In my formal company we try to find different innovative or not standard solutions for warehousing and reporting. Most of our reports are built by an XML definition from which an ad-hoc query are built; however some of the reports are near static and the logic of these are really complex and therefore implemented in stored procedure. Despite all this the report generation time is reaching 30 sec and the expected is 1 sec.

The characteristics of the report are to return around 50.000 rows and 50 columns. Basically all columns can be filtered and attended in a sorting condition; six columns are a multi-value which means an eventually 10 rows are merged into a single comma separated column, moreover any column can be hidden from the result.

If you think about the above requirements these should lead to a slow reporting, however some environmental conditions ease the situation (the database used like a warehouse in which the data changed only once a day).

By taking in account the solutions one stands out from the many, namely to store the result of the procedure into a table and apply some kind of filtering on it by a given metadata and return just the requested page with 10-100 rows.

Let's see what kind of disadvantages of the big report can overcome by storing the result into a table (the name is a unique hash calculated from various parameters):

Fetching out 2.5 million of cells what take some seconds
High network usage (20 concurrent report = 800 MB of data transfer)
High memory usage of the web services since 1 report engrosses 200-1000 MB.

Background

What kind of logic we can use to boost the report performance?

The report is generated by a stored procedure

Reuse of execution plan
Complex logic can be encapsulated
By parameters can be included some dynamics

The result of the procedure stored into a unique hash named table

By obsolescence check the report does not created just once daily (depends on the threshold) so the slowest part which is the generation can be neglected
Any kind of filtering, ordering can be easily applied

Procedure which generates the report is invoked by a manager procedure which is capable to interpret the meta-XML and validate its content

The SQL statement generated is injection free- Generic, can be used for any kind of report built by a procedure
Meta tables available for validation

Report generation steps

Webpage has to build up 6 XML which contains Meta information related to the report and eventually for subset of that.

SQL-Report-Engine procedure has to be executed with these XML’s
XML’s are saved into temp tables (SQL can handle this natively)
Steps and sub steps defined in the XML are interpreted
During the SQL statement generation the injection is not allowed and by executing this we can get back the result set of the report
Concurrent generation handled in transaction

The two main steps:

Build the report and store result into a table
Apply any kind of FILTERING/ORDERING/CHUNKING/DISPLAYING on the previously built table

Many sub steps:

The first step have just one single sub step which is the report building
The second step can contains the following sub step commands which have to build the appropriate SQL statements:

FILT
POSTFILT
CNT
SELECT
DISTCOL
DISTMVCOL

Each sub step has his own proper command in the specified XML
A where clause can have multiple where value

In the following diagram the green lines represents the validation chains of the report.

Report validation and meta info

During the report execution we should able to validate the content of the XML.

There are three tables which contain meta information about the report:

Report name (stored procedure to execute)
Stored procedure parameters and types
Report result-set description, column names and their types

Enlargement policy

Extending the report or creating a new one is very simple just follow the next rules:

Create/Modify the stored procedure which return the report and include/exclude columns, eventually add/remove parameters
Add procedure name into dw_report_type table
Add/Remove parameters into/from dw_report_params table
Add/Remove columns into/from dw_report_result table

Report functions

The report on the website possess with the below functionalities:

Paging; return rows requested from the result-set (ex: skip 500, take 50)
Distinct-field filtering; get distinct values of a column and apply IN-kind of filter with selected values
Distinct-multi-value filtering; get distinct values of the multi-value column, and apply IN-kind of filtering with selected values
LIKE-filter for any column, return a list or rows which contains the filter value
Any other kind of filtering for a given column:
=, >, >=, <, <=, <>
IN, CSV
LIKE
IS NULL, IS NOT NULL
BETWEEN, RANGE BETWEEN
Apply ORDER BY on any or all column
Report can be forced to refresh its content

How to use this engine?

First of all I would like to mention that the web-page which can build XML's displayed below, does not take part of this article. If you have already built the XML that can be interpreted by the engine presented here.

You can download a sample database from the below link which contains the engine-procedures and the samples as well with a demo report.

http://www.2shared.com/file/LE86w69K/sample.html

The data in the sample report was generated by a tool from Red Gate (SQL Data Generator 2), so the report do not have any meanings, however the schema is a real one. So you can check the basic idea before you start to implement the website part.

Download and restore the database
Execute the below samples or test_* procedures.
Modify the XML manually to get back the result-set what you want

change skip/take rows
include order by columns in the @p4 part following the syntax applied there
set is_display="0" for some of the columns

PAGING

The below request have to return rows between 61 and 120 and the total row numbers in separate result-set
You may check the messages in the Management Studio after executing the below samples.

XML generated by the WEBSITE (test_paging procedure)

SQL

declare @p2 xml
set @p2=convert(xml,N'
<row step_id="1" step_type="PROC" step_base="usp_REP_sample" 
      step_hash="EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE"/>
<row step_id="2" step_type="RES" dependency_step="1"/>
')
declare @p3 xml
set @p3=convert(xml,N'
<row step_id="1" sub_step_id="1" sub_step_type="EXEC" output_type="HASH"/>
<row step_id="2" sub_step_id="1" sub_step_type="SELECT" skip_rows="60" take_rows="60"/>
<row step_id="2" sub_step_id="2" sub_step_type="CNT"/>
')
declare @p4 xml
set @p4=convert(xml,N'
<row step_id="2" sub_step_id="1" order_id="1" field="ClientName" direction="ASC"/>
<row step_id="2" sub_step_id="1" order_id="2" field="Sector" direction="ASC"/>
')
declare @p5 xml
set @p5=convert(xml,N'
<row step_id="1" sub_step_id="1" condition_id="1" field="param1"/>
<row step_id="1" sub_step_id="1" condition_id="2" field="param2"/>
<row step_id="1" sub_step_id="1" condition_id="3" field="param3"/>
<row step_id="1" sub_step_id="1" condition_id="4" field="param4"/>
')
declare @p6 xml
set @p6=convert(xml,N'
<row id="1" step_id="1" sub_step_id="1" condition_id="1" value="REP_TYPE"/>
<row id="2" step_id="1" sub_step_id="1" condition_id="2" value="CLIEN_NAME"/>
<row id="3" step_id="1" sub_step_id="1" condition_id="3" value="1/1/2011 12:00:00 AM"/>
<row id="4" step_id="1" sub_step_id="1" condition_id="4" value="5/29/2013 12:00:00 AM"/>
')
declare @p7 xml
set @p7=convert(xml,N'
<row id="1" step_id="1" sub_step_id="1"/>
')
declare @p8 xml
set @p8=convert(xml,N'
<row step_id="2" sub_step_id="1" field_id="1" field="ParentEid" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="2" field="ClientName" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="3" field="ClientKey" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="4" field="ClientTicker" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="5" field="Sector" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="6" field="Region" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="7" field="Country" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="8" field="IndexCode" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="9" field="ParentCoverageCode" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="10" field="Client52WeekLowSharePrice" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="11" field="Client52WeekHighSharePrice" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="12" field="ClientMarketCapUSD" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="13" field="PortfolioYN" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="14" field="ECMSinceFullVolumeUSD" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="15" field="ECMSinceDealCount" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="16" field="DealDate" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="17" field="DealSubType" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="18" field="DealSubTypeDecode" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="19" field="DealValueTotal" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="20" field="BankRole" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="21" field="BankRoleDecode" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="22" field="TranchOfferChangedPercentage" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="23" field="LastDealDealNumber" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="24" field="DueDateTotal" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="25" field="DueDateECM" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="26" field="DueDateDCM" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="27" field="DueDateLoan" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="28" field="DealsInBacklog" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="29" field="LastShelfFiledDate" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="30" field="LastShelfDealNumber" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="31" field="CONVMaturityDate" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="32" field="CONVMaturityDealNumber" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="33" field="PutDate" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="34" field="PutDateDealNumber" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="35" field="LockupExpiryDate" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="36" field="LockupExpiryDealNumber" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="37" field="ECMFeesPaid" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="38" field="ECMWalletPercent" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="39" field="ECMWalletRank" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="40" field="ECMWalletLeadBank" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="41" field="ECMWalletLeadBankDecode" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="42" field="LOANFeesPaid" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="43" field="LOANWalletPercent" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="44" field="LOANWalletRank" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="45" field="LOANWalletLeadBank" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="46" field="LOANWalletLeadBankDecode" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="47" field="MNAFeesPaid" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="48" field="MNAWalletPercent" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="49" field="MNAWalletRank" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="50" field="MNAWalletLeadBank" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="51" field="MNAWalletLeadBankDecode" friendly_name="" is_display="1"/>
')
exec usp_DW_generate_report_PROC @is_force_refresh=0,@steps=@p2,@sub_steps=@p3,
  @order_by=@p4,@where_clause=@p5,@where_value=@p6,@dependency=@p7,@display_group=@p8

SQL statement generated based on the above XML

SQL

if exists (select * from tempdb.sys.tables where name = 'EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE')
	drop table tempdb.dbo.EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE

create table tempdb.dbo.EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE ( rowid int not null identity(1,1) 
  primary key clustered, ParentEid int null,Sector varchar(128) null,Region varchar(128) null,
  Country varchar(128) null,IndexCode varchar(128) null,ParentCoverageCode varchar(128) null,
  ClientName nvarchar(512) null,ClientKey nvarchar(64) null,ClientTicker varchar(32) null,
  Client52WeekLowSharePrice float null,Client52WeekHighSharePrice float null,ClientMarketCapUSD float null,
  PortfolioYN varchar(1) null,ECMSinceFullVolumeUSD float null,ECMSinceDealCount int null,
  DealDate date null,DealSubType varchar(10) null,DealSubTypeDecode varchar(100) null,
  DealValueTotal float null,BankRole nvarchar(5) null,BankRoleDecode nvarchar(150) null,
  TranchOfferChangedPercentage float null,LastDealDealNumber bigint null,DueDateTotal float null,
  DueDateECM float null,DueDateDCM float null,DueDateLoan float null,DealsInBacklog int null,
  LastShelfFiledDate date null,LastShelfDealNumber bigint null,CONVMaturityDate date null,
  CONVMaturityDealNumber bigint null,PutDate date null,PutDateDealNumber bigint null,
  LockupExpiryDate date null,LockupExpiryDealNumber bigint null,ECMFeesPaid float null,
  ECMWalletPercent float null,ECMWalletRank int null,ECMWalletLeadBank varchar(1000) null,
  ECMWalletLeadBankDecode nvarchar(4000) null,MNAFeesPaid float null,MNAWalletPercent float null,
  MNAWalletRank int null,MNAWalletLeadBank varchar(1000) null,MNAWalletLeadBankDecode nvarchar(4000) null,
  LOANFeesPaid float null,LOANWalletPercent float null,LOANWalletRank int null,
  LOANWalletLeadBank varchar(1000) null,LOANWalletLeadBankDecode nvarchar(4000) null, )


declare @param1 varchar(10) = ( select top 1 value from 
  #where_value where step_id = 1 and sub_step_id = 1 and condition_id = 1 )
declare @param2 varchar(10) = ( select top 1 value from 
  #where_value where step_id = 1 and sub_step_id = 1 and condition_id = 2 )
declare @param3 date = ( select top 1 value from #where_value 
  where step_id = 1 and sub_step_id = 1 and condition_id = 3 )
declare @param4 date = ( select top 1 value from #where_value 
  where step_id = 1 and sub_step_id = 1 and condition_id = 4 )


insert into tempdb.dbo.EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE
exec usp_REP_sample @param1 = @param1,@param2 = @param2,@param3 = @param3,@param4 = @param4


select cast(RowId as int) as RowId, [ParentEid], [Sector], [Region], [Country], [IndexCode], 
  [ParentCoverageCode], [ClientName], [ClientKey], [ClientTicker], [Client52WeekLowSharePrice],
   [Client52WeekHighSharePrice], [ClientMarketCapUSD], [PortfolioYN], [ECMSinceFullVolumeUSD], 
   [ECMSinceDealCount], [DealDate], [DealSubType], [DealSubTypeDecode], [DealValueTotal], [BankRole], 
   [BankRoleDecode], [TranchOfferChangedPercentage], [LastDealDealNumber], [DueDateTotal], [DueDateECM], 
   [DueDateDCM], [DueDateLoan], [DealsInBacklog], [LastShelfFiledDate], [LastShelfDealNumber], 
   [CONVMaturityDate], [CONVMaturityDealNumber], [PutDate], [PutDateDealNumber], [LockupExpiryDate], 
   [LockupExpiryDealNumber], [ECMFeesPaid], [ECMWalletPercent], [ECMWalletRank], [ECMWalletLeadBank], 
   [ECMWalletLeadBankDecode], [MNAFeesPaid], [MNAWalletPercent], [MNAWalletRank], [MNAWalletLeadBank], 
   [MNAWalletLeadBankDecode], [LOANFeesPaid], [LOANWalletPercent], 
   [LOANWalletRank], [LOANWalletLeadBank], [LOANWalletLeadBankDecode]
from ( select rowid = row_number() OVER(order by [ClientName] ASC, [Sector] ASC), [ParentEid], 
  [Sector], [Region], [Country], [IndexCode], [ParentCoverageCode], [ClientName], [ClientKey], 
  [ClientTicker], [Client52WeekLowSharePrice], [Client52WeekHighSharePrice], [ClientMarketCapUSD], 
  [PortfolioYN], [ECMSinceFullVolumeUSD], [ECMSinceDealCount], [DealDate], [DealSubType], 
  [DealSubTypeDecode], [DealValueTotal], [BankRole], [BankRoleDecode], [TranchOfferChangedPercentage], 
  [LastDealDealNumber], [DueDateTotal], [DueDateECM], [DueDateDCM], [DueDateLoan], [DealsInBacklog], 
  [LastShelfFiledDate], [LastShelfDealNumber], [CONVMaturityDate], [CONVMaturityDealNumber], [PutDate], 
  [PutDateDealNumber], [LockupExpiryDate], [LockupExpiryDealNumber], [ECMFeesPaid], [ECMWalletPercent], 
  [ECMWalletRank], [ECMWalletLeadBank], [ECMWalletLeadBankDecode], [MNAFeesPaid], [MNAWalletPercent], 
  [MNAWalletRank], [MNAWalletLeadBank], [MNAWalletLeadBankDecode], [LOANFeesPaid], [LOANWalletPercent], 
  [LOANWalletRank], [LOANWalletLeadBank], [LOANWalletLeadBankDecode]
	   from tempdb.dbo.EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE
	  ) res 

where RowId between 61 and 120
select count(*) as ROW_COUNT
from tempdb.dbo.EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE

Distinct fields

You can get easily the distinct values of any column by the following command, there are some filtering conditions already set

XML generated by the Website (test_distinct_values procedure)

SQL

declare @p2 xml
set @p2=convert(xml,N'
<row step_id="1" step_type="PROC" step_base="usp_REP_sample" 
        step_hash="EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE"/>
<row step_id="2" step_type="RES" dependency_step="1"/>
')
declare @p3 xml
set @p3=convert(xml,N'
<row step_id="1" sub_step_id="1" sub_step_type="EXEC" output_type="HASH"/>
<row step_id="2" sub_step_id="1" sub_step_type="FILT"/>
<row step_id="2" sub_step_id="3" sub_step_type="DISTCOL"/>
')
declare @p4 xml
set @p4=convert(xml,N'
<row step_id="2" sub_step_id="1" order_id="0" field="Region" direction="Asc"/>
<row step_id="2" sub_step_id="3" order_id="1" field="Country" direction="Asc"/>
')
declare @p5 xml
set @p5=convert(xml,N'
<row step_id="1" sub_step_id="1" condition_id="1" field="param1"/>
<row step_id="1" sub_step_id="1" condition_id="2" field="param2"/>
<row step_id="1" sub_step_id="1" condition_id="3" field="param3"/>
<row step_id="1" sub_step_id="1" condition_id="4" field="param4"/>
<row step_id="2" sub_step_id="1" condition_id="1" 
  field="TranchOfferChangedPercentage" operator="GREQUAL" condition="AND" bracket="0"/>
<row step_id="2" sub_step_id="1" condition_id="2" 
  field="LOANWalletLeadBank" operator="CSV" condition="AND" bracket="0"/>
<row step_id="2" sub_step_id="1" condition_id="3" 
  field="Region" operator="IN" condition="AND" bracket="0"/>
')
declare @p6 xml
set @p6=convert(xml,N'
<row id="1" step_id="1" sub_step_id="1" condition_id="1" value="REP_TYPE"/>
<row id="2" step_id="1" sub_step_id="1" condition_id="2" value="CLIEN_NAME"/>
<row id="3" step_id="1" sub_step_id="1" condition_id="3" value="1/1/2011 12:00:00 AM"/>
<row id="4" step_id="1" sub_step_id="1" condition_id="4" value="5/29/2013 12:00:00 AM"/>
<row step_id="2" sub_step_id="1" condition_id="1" value="60"/>
<row step_id="2" sub_step_id="1" condition_id="2" value="GS,CS"/>
<row step_id="2" sub_step_id="1" condition_id="3" value="Confections"/>
<row step_id="2" sub_step_id="1" condition_id="3" value="Seafood"/>')
declare @p7 xml
set @p7=convert(xml,N'<row id="1" step_id="1" sub_step_id="1"/>')
declare @p8 xml
set @p8=convert(xml,N'
<row step_id="2" sub_step_id="2" field_id="1" 
  field="ParentEid" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="3" field_id="1" 
  field="Country" friendly_name="" is_display="1"/>
')
exec usp_DW_generate_report_PROC @is_force_refresh=0,@steps=@p2,@sub_steps=@p3,
   @order_by=@p4,@where_clause=@p5,@where_value=@p6,@dependency=@p7,@display_group=@p8

SQL statement generated based on the above XML

SQL

declare @param_2_1_1 float = ( select top 1 value from 
  #where_value where step_id = 2 and sub_step_id = 1 and condition_id = 1 )


create table #t_REP_filtered ( RowId int not null primary key clustered, 
  ParentEid int null,Country varchar(128) null, )
create table #t_FILT_params_2_1_2 (LOANWalletLeadBank varchar(1000))


insert into #t_FILT_params_2_1_2 (LOANWalletLeadBank)
select distinct lst.Item from #where_value wv  cross apply [dbo].[clr_split2string] 
  (wv.value, ',') lst where wv.step_id = 2 and sub_step_id = 1 and wv.condition_id = 2


insert into #t_REP_filtered
select cast(RowId as int) as RowId, [ParentEid], [Country]
from ( select rowid = row_number() OVER(order by [Region] ASC), [ParentEid], [Country]
	   from tempdb.dbo.EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE
       where [TranchOfferChangedPercentage] >= @param_2_1_1
AND [LOANWalletLeadBank] is not null and exists (select * from [dbo].[clr_split2string] 
  (LOANWalletLeadBank, ',') split where exists ( select * from #t_FILT_params_2_1_2 
  as tmp_2_1_2 where split.item = tmp_2_1_2.LOANWalletLeadBank))
AND [Region] IN (select value from #where_value where step_id = 2 and sub_step_id = 1 and condition_id = 3)
	  ) res 

select distinct [Country]
from #t_REP_filtered
order by [Country] ASC

Distinct multi-value fields

Some of the columns contains consolidated rows, however we would like to filter on the dismantled values

XML generated by the WEBSITE (test_distinct_multi_values procedure)

SQL

declare @p2 xml
set @p2=convert(xml,N'
<row step_id="1" step_type="PROC" step_base="usp_REP_sample" 
  step_hash="EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE"/>
<row step_id="2" step_type="RES" dependency_step="1"/>
')
declare @p3 xml
set @p3=convert(xml,N'
<row step_id="1" sub_step_id="1" sub_step_type="EXEC" output_type="HASH"/>
<row step_id="2" sub_step_id="2" sub_step_type="DISTMVCOL"/>
')
declare @p4 xml
set @p4=convert(xml,N'
<row step_id="2" sub_step_id="1" order_id="1" field="ClientName" direction="ASC"/>
<row step_id="2" sub_step_id="1" order_id="2" field="Sector" direction="ASC"/>
<row step_id="2" sub_step_id="2" order_id="1" field="ECMWalletLeadBank" direction="Asc"/>
')
declare @p5 xml
set @p5=convert(xml,N'
<row step_id="1" sub_step_id="1" condition_id="1" field="param1"/>
<row step_id="1" sub_step_id="1" condition_id="2" field="param2"/>
<row step_id="1" sub_step_id="1" condition_id="3" field="param3"/>
<row step_id="1" sub_step_id="1" condition_id="4" field="param4"/>
')
declare @p6 xml
set @p6=convert(xml,N'
<row id="1" step_id="1" sub_step_id="1" condition_id="1" value="REP_TYPE"/>
<row id="2" step_id="1" sub_step_id="1" condition_id="2" value="CLIEN_NAME"/>
<row id="3" step_id="1" sub_step_id="1" condition_id="3" value="1/1/2011 12:00:00 AM"/>
<row id="4" step_id="1" sub_step_id="1" condition_id="4" value="5/29/2013 12:00:00 AM"/>
')
declare @p7 xml
set @p7=convert(xml,N'<row id="1" step_id="1" sub_step_id="1"/>')
declare @p8 xml
set @p8=convert(xml,N'
<row step_id="2" sub_step_id="1" field_id="1" 
  field="ParentEid" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="2" field_id="1" 
  field="ECMWalletLeadBank" friendly_name="" is_display="1"/>
')
exec usp_DW_generate_report_PROC @is_force_refresh=0,@steps=@p2,@sub_steps=@p3,
  @order_by=@p4,@where_clause=@p5,@where_value=@p6,@dependency=@p7,@display_group=@p8

SQL statement generated based on the above XML

SQL

select distinct Item as [ECMWalletLeadBank]
from tempdb.dbo.EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE
cross apply [dbo].[clr_split2string] (isnull([ECMWalletLeadBank],''),',') 
order by [ECMWalletLeadBank] ASC

LIKE filter on a column

We should able to retrieve different rows of a given filtering condition, that means by typing 3 characters we go to database to get all rowids and the matching values of that column, by having the rowid we easily can navigate to that page.

XML generated by the WEBSITE (test_like_search procedure)

SQL

declare @p2 xml
set @p2=convert(xml,N'
<row step_id="1" step_type="PROC" step_base="usp_REP_sample" 
  step_hash="EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE"/>
<row step_id="2" step_type="RES" dependency_step="1"/>
')
declare @p3 xml
set @p3=convert(xml,N'
<row step_id="1" sub_step_id="1" sub_step_type="EXEC" output_type="HASH"/>
<row step_id="2" sub_step_id="1" sub_step_type="POSTFILT"/>
')
declare @p4 xml
set @p4=convert(xml,N'
<row step_id="2" sub_step_id="1" order_id="1" field="ClientName" direction="ASC"/>
<row step_id="2" sub_step_id="1" order_id="2" field="Sector" direction="ASC"/>
')
declare @p5 xml
set @p5=convert(xml,N'
<row step_id="1" sub_step_id="1" condition_id="1" field="param1"/>
<row step_id="1" sub_step_id="1" condition_id="2" field="param2"/>
<row step_id="1" sub_step_id="1" condition_id="3" field="param3"/>
<row step_id="1" sub_step_id="1" condition_id="4" field="param4"/>
<row step_id="2" sub_step_id="1" condition_id="1" field="ClientName" 
  operator="LIKE" condition="AND" bracket="0"/>
')
declare @p6 xml
set @p6=convert(xml,N'
<row id="1" step_id="1" sub_step_id="1" condition_id="1" value="REP_TYPE"/>
<row id="2" step_id="1" sub_step_id="1" condition_id="2" value="CLIEN_NAME"/>
<row id="3" step_id="1" sub_step_id="1" condition_id="3" value="1/1/2011 12:00:00 AM"/>
<row id="4" step_id="1" sub_step_id="1" condition_id="4" value="5/29/2013 12:00:00 AM"/>
<row step_id="2" sub_step_id="1" condition_id="1" value="tru"/>
')
declare @p7 xml
set @p7=convert(xml,N'<row id="1" step_id="1" sub_step_id="1"/>')
declare @p8 xml
set @p8=convert(xml,N'
<row step_id="2" sub_step_id="1" field_id="1" 
  field="ParentEid" friendly_name="" is_display="1"/>
<row step_id="2" sub_step_id="1" field_id="2" 
  field="ClientName" friendly_name="" is_display="1"/>
')
exec usp_DW_generate_report_PROC @is_force_refresh=0,@steps=@p2,@sub_steps=@p3,@order_by=@p4,
  @where_clause=@p5,@where_value=@p6,@dependency=@p7,@display_group=@p8

SQL statement generated based on the above XML

SQL

declare @param_2_1_1 nvarchar(512) = ( select top 1 value from 
  #where_value where step_id = 2 and sub_step_id = 1 and condition_id = 1 )


select RowId = min(RowId), [ParentEid], [ClientName]
from tempdb.dbo.EOD_CS_3DC60DAE76A11935241AA3BCD2FBD2FE
where [ClientName] LIKE '%' + @param_2_1_1 + '%'
group by [ParentEid], [ClientName]
order by [ClientName] ASC

Points of Interest

In my environment the first generation time took 5 sec. and any other request is between 0.2-0.7 sec.

It was challenging to create such kind of method which allow some dynamic usage within the report, also to be SQL-Injection free and most importantly to be really fast.

I am very curious to see who else can apply this method and engine in their live environment. If you need any additional documentation, help or you have any ideas about how to extend this engine please drop me an email at kladna@hotmail.com.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)