Accessing Search Criteria in a DSC for a Grid Report

Referencing Search Criteria using a Caret ^ Symbol

These parameters are referenced by specifying a caret ^ symbol in front of the property, encasing that in round brackets, and having the exact same name as the related search criteria field on the search form.

  1. To declare the attribute value:

    '(^ATTRIBUTE_NAME)'

    For example

    job.WORKFLOW_STATUS IN (^workflowStatus)

    If the workflowStatus attribute has values 7 (STARTED) and 3 (RECEIVED), then the generated SQL is:

    job.WORKFLOW_STATUS IN ('7','3')

  2. To declare an expression:

    '(^ATTRIBUTE_NAME, CLAUSE)'

    For example:

    (^workflowStatus, job.WORKFLOW_STATUS=)

    If workflow status has values 7 (STARTED) and 3 (RECEIVED), then the generated SQL is:

    job.WORKFLOW_STATUS = '7' AND job.WORKFLOW_STATUS='3'

Accessible Criteria in a Search Screen

Search criteria is ONLY available where the grid report is for a search results grid.

In grid reports from an Export to Report flow in a search application, the search criteria used to return the search results can be accessed directly in queries and sub-queries, useful for where the user does not select specific records prior to selecting the grid flow.

Turn on Show attribute tooltips in CCLAS settings, then hover over a search criteria on any CCLAS search screen to see the name of the field. For example, in the CCREGN—Job Search application if the Description search criteria is populated, and hovering over the field indicates that it is called description, then the (^description) parameter is available to the DSC.

Note: Not all search criteria are passed in as parameters. Refer to Example CCSAMP search grid text template to display all search parameters passed into the DSC.

  • :ORGCODES—The collection of searched organisation codes, populated when one or more organisation codes are selected as search criteria.

  • :JOBCODES— The collection of searched job codes, populated when one or more job codes are selected in the search criteria.

  • :JOBNAMES—The collection of searched job names, populated when one or more job names are selected in the search criteria.

Notes on Search Criteria that Contain Multiple Selections:

Strings can come from a list.

The match is treated as an OR syntax where the IN qualifier is used. For example:

{{  AND job.WORKFLOW_STATUS IN (^workflowStatus)   }}

expands into a statement like:

(AND job.WORKFLOW_STATUS in ('first argument', 'second argument')

The match is treated as an AND syntax where an equals qualifier is used. For example:

{{ (^workflowStatus, job.WORKFLOW_STATUS =) }}

expands into a statement like:

   (AND job.WORKFLOW_STATUS ='first argument'  AND  job.WORKFLOW_STATUS = 'second argument' )

Strings can be compared where the user might have supplied wildcards. For example:

{{ and (^description, S.DESCRIPTION like) }}

Enum values are not trimmed when passed into a DSC. For example:

  • Where the following statement is used in DSC and a list of workflow statuses are supplied in the Workflow Status criteria then:

    {{ (^workflowStatus, job.WORKFLOW_STATUS =) }}

    expands into a statement like:

    (AND job.WORKFLOW_STATUS ='first argument'  AND  job.WORKFLOW_STATUS = 'second argument' )

    which is not the desired outcome, in that, the AND is useless; it really needs to be applied as OR.

    Since the workflowStatus criteria is padded to 20 characters, then the following condition is not evaluated to true: 

    {{job.WORKFLOW_STATUS in (^workflowStatus)}}

    However, right-padding the other side of the match works, for example, the following can be used to overcome this:

    {{AND RPAD(job.WORKFLOW_STATUS,20) in (^workflowStatus)}}

    Note: Where free text is entered into the workflowStatus criteria, it works as expected, that is, the criteria is not padded to 20 characters.

    Example:

    CCREGN-Job-Sample-Grid-using-WFSSearchCriteria.tpl

    {!-- Ventyx-Datasource-Aliases: RSJobSearch, JOB --}
    From the default data source:
    {.loop in $RSJobSearch as $RS_J}
    {$RS_J.Name}
    {.onEmpty}
    No jobs in grid.
    {/loop}

    From the DSC:
    {.loop in $JOB as $DSC_J}
    :ORGCODE={$DSC_J.J_ORGCODE}x
    :LABCODE={$DSC_J.J_LABCODE}x
    :GRIDCODE={$DSC_J.J_GRIDCODE}x
    J.NAME={$DSC_J.J_NAME}x
    J.DESCRIPTION={$DSC_J.J_DESC}x
    J.PRIORITY={$DSC_J.J_PRIORITY}x
    J.WORKFLOWSTATUS={$DSC_J.J_WORKFLOW_STATUS}x


    {.onEmpty}
    No job data found.
    {/loop}

    CCREGN-Job-Sample-Grid-using-WFSSearchCriteria.dsc

    <?xml version="1.0" ?> 
    <SQL>
        <SQLQUERY>
            <SQLTABLE>JOB</SQLTABLE>
            <SQLCOMMAND>
            select
                :ORGCODE as J_ORGCODE, :LABCODE as J_LABCODE, :GRIDCODE as J_GRIDCODE
                ,J.NAME as J_NAME, J.DESCRIPTION as J_DESC, J.PRIORITY as J_PRIORITY, J.WORKFLOW_STATUS as J_WORKFLOW_STATUS
                ,J.REGISTRATION_DATE as J_REGDATE

            from
                CCJOB J
            where
                (ORGANISATION_CODE = :ORGCODE and LABORATORY_CODE = ' ' or LABORATORY_CODE =:LABCODE)
                {{and RPAD(J.WORKFLOW_STATUS,20) in (^workflowStatus)}}

            order by
                J_REGDATE DESC
            </SQLCOMMAND>
        </SQLQUERY>
    </SQL>

  • The following statement works in DSC without round brackets around the ^workflowStatus search parameter, that is:

    SELECT
    J.CODE,
    J.NAME
    FROM CCJOB J
    WHERE
    (LABORATORY_CODE =:LABCODE)
    {{ and (^workflowStatus, j.WORKFLOW_STATUS like)}}
    {{ and (^contractSubmissionNumber, j.CONTRACT_SUBMISSION_NUM like) }}

    expands into a statement like:

    SELECT
    J.CODE,
    J.NAME,
    FROM CCJOB J
    WHERE
    (LABORATORY_CODE =:LABCODE)
    and (j.CONTRACT_SUBMISSION_NUM like :p_2)
    and ((j.WORKFLOW_STATUS like:p_0) or (j.WORKFLOW_STATUS like:p_1))

Notes on Search Criteria that are Date-based

The resulting query depends upon the value of the dateAttributenameOption.

Date Option Minimum parameters Resulting SQL if the DSC code is:
{{job.STARTED_DATE (^startedFrom)}} or {{(^startedFrom,job.STARTED_DATE)}}
Null dateAttributeName Null 
job.STARTED_DATE is null
Any dateAttributeName Any 
job.STARTED_DATE is not null
Between

dateAttributeNameFrom

or

dateAttributeNameTo

job.STARTED_DATE > TO_DATE('2015/01/01 07:30:00', 'yyyy/mm/dd HH24:MI:SS') job.STARTED_DATE < TO_DATE('2015/01/01 07:30:00', 'yyyy/mm/dd HH24:MI:SS') job.STARTED_DATE BETWEEN TO_DATE('2015/01/01 07:30:00', 'yyyy/mm/dd HH24:MI:SS') AND TO_DATE('2015/02/01 07:30:00', 'yyyy/mm/dd HH24:MI:SS')
Delta dateAttributeNameDelta

Given that today is 28/08/2015 and the delta value is:

  • 0:

    J.STARTED_DATE BETWEEN TO_DATE('2015/08/28 00:00:00', 'yyyy/mm/dd HH24:MI:SS') AND TO_DATE('2015/08/28 23:59:59', 'yyyy/mm/dd HH24:MI:SS') 0,0: J.STARTED_DATE BETWEEN TO_DATE('2015/08/28 00:00:00', 'yyyy/mm/dd HH24:MI:SS') AND TO_DATE('2015/08/28 23:59:59', 'yyyy/mm/dd HH24:MI:SS')

  • 1

     J.STARTED_DATE BETWEEN TO_DATE('2015/08/29 00:00:00', 'yyyy/mm/dd HH24:MI:SS') AND TO_DATE('2015/08/29 23:59:59', 'yyyy/mm/dd HH24:MI:SS')

  • -1:

    J.STARTED_DATE BETWEEN TO_DATE('2015/08/27 00:00:00', 'yyyy/mm/dd HH24:MI:SS') AND TO_DATE('2015/08/27 23:59:59', 'yyyy/mm/dd HH24:MI:SS'

  • 1,2:

    J.STARTED_DATE BETWEEN TO_DATE('2015/08/29 00:00:00', 'yyyy/mm/dd HH24:MI:SS') AND TO_DATE('2015/08/30 23:59:59', 'yyyy/mm/dd HH24:MI:SS')

  • -2,1:

    J.STARTED_DATE BETWEEN TO_DATE('2015/08/26 00:00:00', 'yyyy/mm/dd HH24:MI:SS') AND TO_DATE('2015/08/29 23:59:59', 'yyyy/mm/dd HH24:MI:SS')

Where a specific FromDate and ToDate variable is used, then it is supplied as:

{{ and (^approvedFrom, SV.APPROVED_DATE) }}
{{ and (^approvedTo, SV.APPROVED_DATE) }}

Delta date variables can be used:

{{ and (^approvedDelta, SV.APPROVED_DATE) }}

This constructs a BETWEEN qualifier using the current DateTime as the source of the Delta offset.

{{ and (^approvedAny, SV.APPROVED_DATE) }}
{{ and (^approvedNull, SV.APPROVED_DATE) }}

Example of use in both SELECT and WHERE clauses:

  • From the CCREGN—Job Search application:

    {{and exists(...where S.CODE like (^schemeCode) and SV.VERSION = (^schemeversion))}}

    is expanded to:

    {{and exists(... where S.CODE like ('AAA40Q') and SV.VERSION = ('2')}}

  • From the CCPRTN—Update Portion application:

    where
    J.ORGANISATION_CODE = :ORGCODE
    and J.LABORATORY_CODE = :LABCODE
    {{and J.NAME = regexp_replace((^barCode),'\..*','')}}

    is expanded to:

    where J.ORGANISATION-CODE = 'GLOBAL' 
    and J.LABORATORY_CODE = 'MYLAB' 
    {{and J.NAME = 'JOB123')}}

CCREGN-Job-Sample-Grid-using-SearchCriteria.tpl

{!-- Ventyx-Datasource-Aliases: RSJobSearch, JOB --}
From the default data source:
{.loop in $RSJobSearch as $RS_J}
{$RS_J.Name}
{.onEmpty}
No jobs in grid.
{/loop}

From the DSC:
{.loop in $JOB as $DSC_J}
:ORGCODE={$DSC_J.J_ORGCODE}x
:LABCODE={$DSC_J.J_LABCODE}x
:GRIDCODE={$DSC_J.J_GRIDCODE}x
J.NAME={$DSC_J.J_NAME}x
J.DESCRIPTION={$DSC_J.J_DESC}x
^jobCategoryCode=Grid Job category={$DSC_J.GRID_JOB_CAT}x
^priority=Grid Priority={$DSC_J.GRID_PRIORITY}x
^clientCode=Grid Client code={$DSC_J.GRID_CLI_CODE}x
^workflowStatus=Workflow status={$DSC_J.GRID_WFS}x

{.onEmpty}
No job data found.
{/loop}

CCREGN-Job-Sample-Grid-using-SearchCriteria.dsc

<?xml version="1.0" ?> 
<SQL>
    <SQLQUERY>
        <SQLTABLE>JOB</SQLTABLE>
        <SQLCOMMAND>
        select
            :ORGCODE as J_ORGCODE, :LABCODE as J_LABCODE, :GRIDCODE as J_GRIDCODE
            ,J.NAME as J_NAME, J.DESCRIPTION as J_DESC
            {{,(^jobCategoryCode) as GRID_JOB_CAT}}
            {{,(^priority) as GRID_PRIORITY}}
            {{,(^clientCode) as GRID_CLI_CODE}}
            {{,(^workflowStatus) as GRID_WFS}}
        from
            CCJOB J
        where
            (ORGANISATION_CODE = :ORGCODE and LABORATORY_CODE = ' ' or LABORATORY_CODE =:LABCODE)
            {{and PRIORITY=(^priority)}}
            {{and CLIENT_CODE=(^clientCode)}}
            {{and WORKFLOW_STATUS=trim(^workflowStatus))}}
        order by
            J_NAME
        </SQLCOMMAND>
    </SQLQUERY>
</SQL>
The following output results where rows three, four and six are selected and no search criteria is entered
From the default data source:
LABJOB3_NAME
LABJOB4_NAME
LABJOB6_NAME

From the DSC:
:ORGCODE=MYLOGINORGx
:LABCODE=MYLOGINLABx
:GRIDCODE=LABJOB1_CODEx
J.NAME=LABJOB1_NAMEx
J.DESCRIPTION=LABJOB1_DESCRIPTIONx
^jobCategoryCode=Grid Job category=<Attribute "GRID_JOB_CAT" does not exist>x
^priority=Grid Priority=<Attribute "GRID_PRIORITY" does not exist>x
^clientCode=Grid Client code=AUTSV_M_INTERNALx
^workflowStatus=Workflow status=<Attribute "GRID_WFS" does not exist>x

From the DSC:
:ORGCODE=MYLOGINORGx
:LABCODE=MYLOGINLABx
:GRIDCODE=LABJOB2_CODEx
J.NAME=LABJOB2_NAMEx
J.DESCRIPTION=LABJOB2_DESCRIPTIONx
^jobCategoryCode=Grid Job category=<Attribute "GRID_JOB_CAT" does not exist>x
^priority=Grid Priority=<Attribute "GRID_PRIORITY" does not exist>x
^clientCode=Grid Client code=AUTSV_M_INTERNALx
^workflowStatus=Workflow status=<Attribute "GRID_WFS" does not exist>x

...
<All jobs are returned because the search criteria are not available to quality the WHERE clause>

From the DSC:
:ORGCODE=MYLOGINORGx
:LABCODE=MYLOGINLABx
:GRIDCODE=LABJOBn_CODEx
J.NAME=LABJOBn_NAMEx
J.DESCRIPTION=LABJOBn_DESCRIPTIONx
^jobCategoryCode=Grid Job category=<Attribute "GRID_JOB_CAT" does not exist>x
^priority=Grid Priority=<Attribute "GRID_PRIORITY" does not exist>x
^clientCode=Grid Client code=AUTSV_M_INTERNALx
^workflowStatus=Workflow status=<Attribute "GRID_WFS" does not exist>x

The following output results where rows three, four and six are selected and relevant search criteria is entered
From the default data source:
LABJOB3_NAME
LABJOB4_NAME
LABJOB6_NAME

From the DSC:
:ORGCODE=MYLOGINORGx
:LABCODE=MYLOGINLABx
:GRIDCODE=LABJOB1_CODEx
J.NAME=LABJOB1_NAMEx
J.DESCRIPTION=LABJOB1_DESCRIPTIONx
^jobCategoryCode=Grid Job category=<Attribute "GRID_JOB_CAT" does not exist>x
^priority=Grid Priority=<Attribute "GRID_PRIORITY" does not exist>x
^clientCode=Grid Client code=AUTSV_M_INTERNALx
^workflowStatus=Workflow status=<Attribute "GRID_WFS" does not exist>x

From the DSC:
:ORGCODE=MYLOGINORGx
:LABCODE=MYLOGINLABx
:GRIDCODE=LABJOB2_CODEx
J.NAME=LABJOB2_NAMEx
J.DESCRIPTION=LABJOB2_DESCRIPTIONx
^jobCategoryCode=Grid Job category=<Attribute "GRID_JOB_CAT" does not exist>x
^priority=Grid Priority=<Attribute "GRID_PRIORITY" does not exist>x
^clientCode=Grid Client code=AUTSV_M_INTERNALx
^workflowStatus=Workflow status=<Attribute "GRID_WFS" does not exist>x

...
<All jobs are returned because the search criteria are not available to quality the WHERE clause>

From the DSC:
:ORGCODE=MYLOGINORGx
:LABCODE=MYLOGINLABx
:GRIDCODE=LABJOBn_CODEx
J.NAME=LABJOBn_NAMEx
J.DESCRIPTION=LABJOBn_DESCRIPTIONx
^jobCategoryCode=Grid Job category=<Attribute "GRID_JOB_CAT" does not exist>x
^priority=Grid Priority=<Attribute "GRID_PRIORITY" does not exist>x
^clientCode=Grid Client code=AUTSV_M_INTERNALx
^workflowStatus=Workflow status=<Attribute "GRID_WFS" does not exist>x