ADHOC Report
ADHOC Report - 临时的report，随时可以去系统中按照你选择的条件打出你想看的report
Add ADHOC Report
--AddReport
use [QEO_Insurance] go
if not exists (
select *
from [QEO_Insurance].[dbo].[System_Reports]
where sREP_Name = 'BUSINESS_AUTO_DECLARATIONS_1'
)
insert into System_Reports
(
sLOB_ID
,sFRM_ID
,sREP_State
,sREP_Name
,sREP_Description
,sREP_TemplatePath
,sREP_ReportPath
,sREP_AdHoc
,sREP_Activity
,sREP_UWLetter
,sREP_ClaimLetter
,sREP_EOD
,sREP_PolicyForm
,sREP_NoDailyPrint
,sREP_IsSystemNotice
,sREP_ConditionQuery
,sREP_Print_Order1
,sREP_Print_Order2
,sREP_DefaultSelect
,sREP_Trigger
,sREP_TriggerDesc
,sRT_ID
,sREP_DefaultType
,sREP_EmailList
,sREP_CopyToFile
,sREP_ScheduleTypeID
,sREP_LastRunDate
,sREP_NextRunDate
,sREP_LastRunStatus
,sREP_LastRunMessage
,sREP_Disabled
,NB_EffectiveDate
,NB_ExpirationDate
,RN_EffectiveDate
,RN_ExpirationDate
,sREP_FTP_Setting
,sREP_Hidden
,sREP_adhocExternal
,sREP_Form_Version
,sREP_EmailAttachMaxSizeMB
,sREP_ZipReport
,sREP_ManOrOpt
,sREP_QuoteQuery
,sREP_NeedDataCompare
,sREP_EN_Bind
,sREP_EN_ConditionQuery
,sREP_FormNumber
,sREP_PrintInQuote
,sREp_TrigValue
)
values
(
1 /* sLOB_ID */
,0 /* sFRM_ID */
,'ALL' /* sREP_State */
,'BUSINESS_AUTO_DECLARATIONS_1' /* sREP_Name */
,'BUSINESS_AUTO_DECLARATIONS_1' /* sREP_Description */
,'C:\mtstingray_QEO\qeo\documents\qeo\BUSINESS AUTO DECLARATIONS-1.pdf' /* sREP_TemplatePath */
,'C:\mtstingray_QEO\qeo\reports\qeo\BUSINESS AUTO DECLARATIONS_1.rep' /* sREP_ReportPath */
,0 /* sREP_AdHoc */
,0 /* sREP_Activity */
,0 /* sREP_UWLetter */
,0 /* sREP_ClaimLetter */
,1 /* sREP_EOD */
,1 /* sREP_PolicyForm */
,0 /* sREP_NoDailyPrint */
,0 /* sREP_IsSystemNotice */
,'select 1 as ApplyForm FROM PolicyData as A where 1=1' /* sREP_ConditionQuery */
,300 /* sREP_Print_Order1 */
,300 /* sREP_Print_Order2 */
,0 /* sREP_DefaultSelect */
,null /* sREP_Trigger */
,null /* sREP_TriggerDesc */
,null /* sRT_ID */
,null /* sREP_DefaultType */
,null /* sREP_EmailList */
,null /* sREP_CopyToFile */
,null /* sREP_ScheduleTypeID */
,null /* sREP_LastRunDate */
,null /* sREP_NextRunDate */
,null /* sREP_LastRunStatus */
,null /* sREP_LastRunMessage */
,0 /* sREP_Disabled */
,'1900-01-01 00:00:00.000' /* NB_EffectiveDate */
,'2100-01-01 00:00:00.000' /* NB_ExpirationDate */
,'1900-01-01 00:00:00.000' /* RN_EffectiveDate */
,'2100-01-01 00:00:00.000' /* RN_ExpirationDate */
,null /* sREP_FTP_Setting */
,0 /* sREP_Hidden */
,0 /* sREP_adhocExternal */
,null /* sREP_Form_Version */
,null /* sREP_EmailAttachMaxSizeMB */
,null /* sREP_ZipReport */
,'M' /* sREP_ManOrOpt */
,null /* sREP_QuoteQuery */
,0 /* sREP_NeedDataCompare */
,1 /* sREP_EN_Bind */
,'select 1 as ApplyForm FROM PolicyData as A where 1=1' /* sREP_EN_ConditionQuery */
,'' /* sREP_FormNumber */
,0 /* sREP_PrintInQuote */
,null /* sREp_TrigValue */
) go
ADHOC Report参数配置
ADHOC Report需要向System_Reports_Params表中添加参数
use QEO_Insurance go
declare @repID int
set @repID = 0
select @repID = sREP_ID
from System_Reports
where sREP_Name = 'Policy and Claims Checks'
delete
from System_Reports_Params
where sREP_ID = @repID
insert into System_Reports_Params
(
sREP_ID
,sRPAR_Name
,sRPAR_Type
,sRPAR_Description
,sRPAR_Selection
,sRPAR_SelQuery
)
values
(
@repID
,'DateID'
,'INTEGER'
,'Entered date type'
,1
,
'select '''' as OptName, ''
<option value="-1">Please select the date type.</option>'' as OPT union select ''Check Date'' as OptName, ''
<option value="6">Check Date</option>'' as OPT union select ''Cleared Date'' as OptName, ''
<option value="7">Cleared Date</option>'' as OPT union select ''Printed Date'' as OptName, ''
<option value="8">Printed Date</option>'' as OPT union select ''Status Date'' as OptName, ''
<option value="9">Status Date</option>'' as OPT union select ''Stop Date'' as OptName, ''
<option value="10">Stop Date</option>'' as OPT union select ''Void Date'' as OptName, ''
<option value="11">Void Date</option>'' as OPT'
),
(@repID ,'StartDate' ,'DATE' ,'Start date for report' ,'0' ,''),
(@repID ,'EndDate' ,'DATE' ,'End date for report' ,'0' ,''),
(
@repID
,'StatusId'
,'INTEGER'
,'Check status'
,1
,
'select '''' as OptName, ''
<option value="-1">Please select the status type.</option>'' as OPT union select ''Not Cleared'' as OptName, ''
<option value="1">Not Cleared</option>'' as OPT union select ''Cleared'' as OptName, ''
<option value="2">Cleared</option>'' as OPT union select ''Full List/All'' as OptName, ''
<option value="3">Full List/All</option>'' as OPT union select ''Void'' as OptName, ''
<option value="4">Void</option>'' as OPT'
)
select *
from System_Reports_Params
where sREP_ID = @repID
posted @ 2017-02-28 11:46 by Mark