User Guide‎ > ‎

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
AS
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
AS
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)