Configuring drill backs

  • Drill backs can be done for both single and multiple object columns in the widget.
  • For non-versioned objects, provide the <<ObjectColumn>> as the value for the drill back column in the query.
  • For versioned objects, provide <<ObjectColumn>> + '\'+ <<VersionColumn>> as the value for the drill back column in the query.
  • Syntax for ‘DR_’ column in Lookup Language label:

    DR_<<Objectsymbol>>_<<ObjectColumnLabel>>

  • Single Object Drill back example queries:
    Query Lookup Labels and Query Parameters
    SELECT TOP 20 FORMULA_CODE, VERSION, DESCRIPTION, STATUS_IND, MODIFY_DATE, FORMULATOR_CODE,FORMULA_CODE + '\'+ VERSION AS DR_FORMULA_FORMULA_CODE,CASE STATUS_IND WHEN '0' THEN 'PALEGREEN' WHEN '100' THEN 'BURLYWOOD' WHEN '400' THEN '#F4D03F' WHEN '600' THEN '#48C9B0' WHEN '999' THEN '#7FB3D5'END AS F_BG_STATUS_IND,CASE WHEN GETDATE() <= MODIFY_DATE -100 THEN '#00FF00'WHEN GETDATE() > MODIFY_DATE -100 THEN '#FF0000' END AS F_FG_MODIFY_DATE FROM FSFORMULA WHERE FORMULATOR_CODE = [%1] Lookup Label: Formula Code;Version;Description;Status;Modify Date;Formulator Code;DR_FORMULA_Formula Code;F_BG_Status;F_FG_Modify Date

    Parameters:

    Parameter Type: String

    Parameter Value: ADMIN
    drill_back_1
    Query Lookup Labels and Query Parameters
    SELECT PROJECTMGR, STATUS_IND, PROJECT_CODE, DESCRIPTION, PROJ_CUSTOMER, PROJ_TYPE, PORTFOLIO, PROJECT_CODE AS DR_PROJECT_PROJECT_CODE FROM FSPROJECT WHERE PROJECTMGR =[%1] AND STATUS_IND =[%2] AND LOGICAL_DELETE=0 ORDER BY PROJECTMGR Lookup Label: Project Manager;Status;Project code;Description;Project Customer;Project Type; Portfolio; DR_PROJECT_Project code

    Parameters:

    Parameter Type: String

    Parameter Value: JOHNNY

    Parameter Type: Number

    Parameter Value: 200

    drill_back_2

  • Multiple Objects Drill back example queries:
    Query Lookup Labels and Query Parameters

    SELECT PROJECTMGR, STATUS_IND, PROJECT_CODE, DESCRIPTION, PROJECT_CODE AS DR_PROJECT_PROJECT_CODE, PROJECTMGR AS DR_USER_PROJECTMGR FROM FSPROJECT WHERE LOGICAL_DELETE=0 AND PROJECTMGR LIKE [%1] AND STATUS_IND LIKE [%2] ORDER BY PROJECTMGR

    Lookup Label: project manager;statusind;project code;description;DR_PROJECT_project code;DR_USER_project manager

    Parameters:

    Parameter Type: String

    Parameter Value: JOHNNY

    Parameter Type: Number

    Parameter Value: 200
    drill_back_3
    Query Lookup Labels and Query Parameters
    SELECT LBL.LABEL_CODE, LBL.VERSION, FORM.FORMULA_CODE, FORM.VERSION, FORM.FORMULA_CODE + '\'+ FORM.VERSION AS FORMULA_DRILLBACK, LBL.LABEL_CODE + '\'+ LBL.VERSION AS LABEL_DRILLBACK FROM FSLABELFORMULA LBLFORM LEFT JOIN FSLABEL LBL ON LBLFORM.LABEL_ID=LBL.LABEL_ID LEFT JOIN FSFORMULA FORM ON LBLFORM.FORMULA_ID=FORM.FORMULA_ID Lookup Label: Label Code;Label Version;Formula Code;Formula Version;DR_FORMULA_Formula Code;DR_LABEL_Label Code

    drill_back_4