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