HRPro Report Control Design Specifications

Summary

Step 1. Define the ‘Content View’ (usually vr_[ReportCode])

Step 2. Define the ‘Format View’ (usually vr_rpt[ReportCode])

Step 3. Add a new record in RptControl Table with AUTO_UPDATE='N'

Step 4. Add a new record in HRProMenu Table with AUTO_UPDATE='N'

1. Define the ‘Content View’

This view contain the content of the report with all columns in the report, this view usually contain inner join, outer join, union etc, to other tables.

e.g.

CREATE VIEW vr_PLRXX

SELECT

E.EE_NO,

E.SURNAME,

E.OTHERNAME,

D.DEPT_NAME,

E.BASIC_SAL

FROM PLEMPLOYEE E

INNER JOIN PLDEPT D ON D.DEPT_CODE=E.DEPT_CODE

2. Define the ‘Format View’

This view is based on the ‘Content View’ with specially design field name that is used by the reportcontrol.frm to format the report.

CREATE VIEW vr_rptPLRXX

SELECT

[Employee No.vr_PLRXX.EE_NO]=EE_NO,

[Surname.vr_PLRXX.SURNAME]=SURNAME,

[Othername.vr_PLRXX.OTHERNAME]=OTHERNAME,

[Department.vr_PLRXX.DEPT_NAME]=DEPT_NAME,

[Basic Salary.vr_PLRXX.BASIC_SAL]=BASIC_SAL

FROM vr_PLRXX

where the field name of the view consists of 4 parts [Report Column Heading.Lookup Table.Lookup Field.Format]. (format is optional)

1. Report Column Heading

Report Column Heading (e.g. Employee No) is the field name display in Field and Order By in the Report Control Screen

2. Lookup Table and Lookup Field

Lookup Table and Lookup Field (e.g. vr_PLRXX.EE_NO) is used by ReportControl to know how to find the popup when the user cick the ".." button in the value text box

3. Format

Format is optional and is supported

NP	No print (do not print this column in the excel)
RS	Required Field (always require the user to input condition for this field)
N0	Numeric field with 0 decimal place (=integer)
C2	Currency field with 2 decimal place
DateTime	Print as yyyy/mm/dd hh:mm:ss
_SUM_	Added sum at the End of Report
NS	Not allow to select at the Report Control Screen

3. Add a new record in ‘RptControl’ Table in SQL Server

These table defined the Report Code, Format View, Excel Printing Properties, Excel Grouping and Total List, etc.

Field Name	Value	Description
RptCode	PLRXX	Report Code unique
RptName	Receipt Report	Report Name in Excel Header
RptTableView	vr_rptPLRXX	Report view frmReportControl.frm use this view
RptExcel	Y	Y = Excel Report defined
RptWordLabel	N	Is it a word label
RptFontName	Arial	Excel Font Name
RptFontSize	10	Excel Font Size
RptOrientation	Landscape	Excel Orientation (Landscape or Portrait)
RptFitWidth	Y	Is the Excel Report fit Page Width
RptZoomRatio	100	If not Fit Page Width, what is the Zoom Ratio
RptPaperSize	9	9 = A4
RptCrystalRpt	NULL	Is the Crystal Report file defined?
RptGroupby		0 The columns that is grouped by (currently support 3 levels) e.g. 1,2,3
RptTotalList	0	The columns that show the total if RptGroupby > 0
RptPageBreaks	N	Is required Page Break for each gp when Rptgroupby > 0 Currently support 3 levels e.g. YNN

e.g.

INSERT INTO [dbo].[RptControl]
([RptCode],[RptName],[RptName_zh],[RptTableView],[RptExcel],[RptFontName],[RptFontSize],[RptOrientation],[RptFitWidth],[RptZoomRatio],[RptPaperSize],[RptCrystalRpt]
,[RptSystemDateFormat],[RptCopies],[RptGroupby],[RptTotalList],[RptPageBreaks],[RptDescription],[RptCRParaName1],[RptCRParaType1],[RptCRParaName2],[RptCRParaType2]
,[URL],[LAST_UPDATE_DATE],[AUTO_UPDATE],[RptStoredProc],[RptModule],[LAST_EXECUTION_DATE])

VALUES
('PLRXX','Employee Basic Salary Report',N'職工基本工資報告','vr_rptPLRXX',1,'Arial Unicode MS',10,'Portrait','Y',100,9,'','Y',1,'','','N','','','','','','',GETDATE(),'N','','',NULL)

4. Add a new record in ‘HRProMenu’ Table in SQL Server

INSERT INTO [dbo].[HRProMenu]
([LINE_NO],[MENU_LEVEL],[FUNCTION_NAME],[FUNCTION_ID],[FUNCTION_TYPE],[MODULE],[IS_WEBCLIENT_VISIBLE],[AUTO_UPDATE])
VALUES
(1615,4,'Employee Salary Report','PLRXX','R','MS',1,'N')

P.S. Supporting Report Control is not covered by General HRPro Support and Maintenance Agreement (unless otherwise specified)

	Search this site