- Requirement
The final report is a multiple pages PDF with many different Key Business Questions. Customers are global based, with different languages and different KBQs. Obviously, we can develop custom report for each customer. This approach is very expensive and will take very long time to deliver report. The idea is to find a new approach that is cost effective and quick time to market.
- Conceptual model
The solution is to create a report platform that can generate report (multiple pages PDF) for an individual client by selecting needed KBQs. All pre-built report templates for all KBQs are saved into question library. Consultant can select questions from KBQ library and arrange them with a requested sequence. Report engine can then generate report as defined by consultant.
- Architectural model
Below is solution architectural model.
The idea is to generate report template based on the input from Baxter consultant. The input includes following items:
1) What question (building block) is selected?
2) What sequence number will be this selected question assigned?
3) Is there page break after this question?
4) What identifier should be used for the selected question, such as 1,2,1a,1b
Based on this input, report template generator will generate report template and saved it as report template file. This process should be run at the beginning, but not every time when generating report. From report template file, and report parameters and selected language, report generate will generate PDF file on monthly basis, or as needed.
- Technical implementation model
The major design approach is to leverage SSRS functionality as much as possible, also with minimal effort, and all initial decisions are listed as follows:
1) XML technology is used to generate report template.
2) SQL 2012 functionality for XML DML is fully leveraged to modify and assembly XML, instead of programming.
3) The sequence of all sub reports (or questions) is fully defined by user.
4) Each sub report (or question) will have option to specify line before, page break after and question title ID.
5) Each core sub report (or question) will be standardized with 4 parameters to be connected with overall report.
6) The master report will be standardized with 3 parameters
- Technical tips and tricks
Load report RDL (SSRS Report Definition Language) file into database
/*
load master xml file to table
*/
INSERT INTO ReportTemplate(ID, Report,Template)
SELECT 'Master', 'Prismaflex', CAST(x AS XML) AS Template
FROM OPENROWSET(BULK 'C:\BAR\Report\Prismaflex\Template\Master.xml', SINGLE_BLOB) AS T(x);
-- remove scheme to normalize xml, which will be added at the end of process
update ReportTemplate SET Template = CAST(REPLACE(CAST(Template AS VARCHAR(MAX)),
' xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition"','') AS XML)
where ID = 'Master'
/*
load line xml file to table
*/
INSERT INTO ReportTemplate(ID, Report,Template)
SELECT 'Line', 'Prismaflex', CAST(x AS XML) AS Template
FROM OPENROWSET(BULK 'C:\BAR\Report\Prismaflex\Template\Line.xml', SINGLE_BLOB) AS T(x);
INSERT INTO ReportTemplate(ID, Report,Template)
SELECT 'Line', 'Physician', CAST(x AS XML) AS Template
FROM OPENROWSET(BULK 'C:\BAR\Report\Physician\Template\Line.xml', SINGLE_BLOB) AS T(x);
/*
load SubReport xml file to table
*/
INSERT INTO ReportTemplate(ID, Report,Template)
SELECT 'SubReport', 'Prismaflex', CAST(x AS XML) AS Template
FROM OPENROWSET(BULK 'C:\BAR\Report\Prismaflex\Template\SubReport.xml', SINGLE_BLOB) AS T(x);
/*
load Pagebreak xml file to table
*/
INSERT INTO ReportTemplate(ID, Report,Template)
SELECT 'PageBreak', 'Prismaflex', CAST(x AS XML) AS Template
FROM OPENROWSET(BULK 'C:\BAR\Report\Prismaflex\Template\PageBreak.xml', SINGLE_BLOB) AS T(x);
Change contents of individual RDL via XML DML, including line, sub report, page break
--2.1 Add line
if ( @LineBefore = 1)
begin
set @xmlLine = @xmlLine0
-- change line name
set @LineName = 'T_Line' + convert(varchar(10), @QuestionNo)
Set @xmlLine.modify('
replace value of
(/Line[1]/@Name)
with
(sql:variable("@LineName"))
')
-- change hight
set @LineHeight = convert(varchar(10), @Height) + 'cm'
Set @xmlLine.modify('
replace value of
(/Line[1]/Top[1]/text()[1])
with
(sql:variable("@LineHeight"))
')
-- add it to master
Set @xmlMaster.modify('
insert
(
sql:variable("@xmlLine")
)
as last into
(/Report[1]/ReportSections[1]/ReportSection[1]/Body[1]/ReportItems[1])
')
-- aadjust hight for next item
set @Height = @Height + @DistanceBetweenElement
end
--2.2 Add subreport
set @xmlSubReport = @xmlSubReport0
-- change subreport name
set @subreportName = 'T_SubReport' + convert(varchar(10), @DisplayOrder) + '_' + @RDLFileName
Set @xmlSubReport.modify('
replace value of
(/Subreport[1]/@Name)
with
(sql:variable("@subreportName"))
')
-- change report name
Set @xmlSubReport.modify('
replace value of
(/Subreport[1]/ReportName[1]/text()[1])
with
(sql:variable("@RDLFileName"))
')
-- change hight
set @SubReportHeight = convert(varchar(10), @Height) + 'cm'
Set @xmlSubReport.modify('
replace value of
(/Subreport[1]/Top[1]/text()[1])
with
(sql:variable("@SubReportHeight"))
')
-- pass titleID
Set @xmlSubReport.modify('
replace value of
(/Subreport[1]/Parameters[1]/Parameter[4]/Value[1]/text()[1])
with
(sql:variable("@TitleID"))
')
-- add it to master
Set @xmlMaster.modify('
insert
(
sql:variable("@xmlSubReport")
)
as last into
(/Report[1]/ReportSections[1]/ReportSection[1]/Body[1]/ReportItems[1])
')
-- aadjust hight for next item
set @Height = @Height + @HeightOfSubreport
--2.3 Add page break
if ( @PageBreakAfter = 1)
begin
set @xmlPageBreak = @xmlPageBreak0
-- change PageBreak name
set @PageBreakName = 'T_PageBreak' + convert(varchar(10), @QuestionNo)
Set @xmlPageBreak.modify('
replace value of
(/Rectangle[1]/@Name)
with
(sql:variable("@PageBreakName"))
')
-- change hight
set @Height = @Height + @DistanceBetweenElement
set @PageBreakHeight = convert(varchar(10), @Height) + 'cm'
Set @xmlPageBreak.modify('
replace value of
(/Rectangle[1]/Top[1]/text()[1])
with
(sql:variable("@PageBreakHeight"))
')
-- add it to master
Set @xmlMaster.modify('
insert
(
sql:variable("@xmlPageBreak")
)
as last into
(/Report[1]/ReportSections[1]/ReportSection[1]/Body[1]/ReportItems[1])
')
end
Append report RDL into master report RDL XML and Export composed RDL into report template file
--3. Normalize Master RDL
--3.1 setup footer posistion
set @Height = @Height + @DistanceToFooter
set @PageFooterHeight = convert(varchar(10), @Height) + 'cm'
Set @xmlMaster.modify('
replace value of
(/Report[1]/ReportSections[1]/ReportSection[1]/Body[1]/Height[1]/text()[1])
with
(sql:variable("@PageFooterHeight"))
')
--3.2 add schemes to make recongnized RDL template
SET @xmlMaster = CAST(REPLACE(CAST(@xmlMaster AS VARCHAR(MAX)),
' xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"',
' xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"') AS XML)
update ReportTemplate set GeneratedTemplate = @xmlMaster where ID = 'Master' and report = @Report
--4. export composed RDL to report template file
declare @Command varchar(1000)
set @Command = 'bcp "SELECT GeneratedTemplate FROM bar.dbo.ReportTemplate where ID = ''Master'' and report = ''' + @Report + ''' " queryout "'+@TemplateFile+'" -c -T'
execute xp_cmdshell @Command
- Configuration file
No comments:
Post a Comment