Accessing Grid Content in a DSC for a Grid Report

Search Results Grid

Important: The RS<object>Search must be included in the Crystal Reports or Chunk Text report template for the GRIDCODE and GRIDCODES parameters to be populated for use in the DSC.

The following parameters are ONLY VALID where the grid pertains to search results.

:GRIDCODE—

Where no rows are selected, then provides the code of the entity on the first returned row.
Where one or more rows are selected, then provides the code of the entity on the first selected row.
Where no rows are returned from the search, then is empty.

Example of use in both SELECT and WHERE clauses:

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

{!-- Ventyx-Datasource-Aliases: RSJobSearch, JOB --}
Output 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

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

CCREGN-Job-Sample-Grid-using-GRIDCODE.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.REGISTRATION_DATE
        from
            CCJOB J
        where
            (ORGANISATION_CODE = :ORGCODE and LABORATORY_CODE = ' ' or LABORATORY_CODE =:LABCODE)
            and CODE = :GRIDCODE
        order by
            J.REGISTRATION_DATE
        </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:
MYLABJOB3_NAME
MYLABJOB4_NAME
MYLABJOB6_NAME

From the DSC:
:ORGCODE=MYLOGINORGx
:LABCODE=MYLOGINLABx
:GRIDCODE=MYLABJOB3_CODEx
J.NAME=MYLABJOB3_NAMEx
J.DESCRIPTION=MYLABJOB3_DESCRIPTIONx

:GRIDCODES—
- Where no rows are selected, then provides ALL the codes that fit the search criteria. For example, say the search criteria returns a total of 5 rows, then contains A, B, C, D, E.
- Where one or more rows are selected, then provides the code of the entity on the first selected row.
- Where no rows are returned from the search, then is empty.
Where supplied as:
```
And CODE in (:GRIDCODES)
```
then the clause is always included in the full query, whereas when supplied as:
```
{{  And  CODE in (:GRIDCODES)  }}
```
then the clause is only included in the full query where one or more rows are selected in the search grid. This allows for the case in which the parameter's value is empty, that is, the query will not fail when executed. If no rows are selected, then the DSC has to include ALL the search criteria again in the DSC query.

Important: The order of the code(s) assigned to these variables are by grid row selection order (if rows are selected). For example, where a grid contains 6 rows with codes of (A, B, C, D, E, F) top-to-bottom order and the user selects 4 rows in the order of (D,B,A,C), then GRIDCODE = (D) and GRIDCODES = (D,B,A,C).

Note: If there are no codes, GRIDCODE has the value of "null" and GRIDCODES has the value of "--CCLAS_NONE–".

Important: If the IN SQL statement is used, for example, "AND CODE IN (:GRIDCODES)" and GRIDCODES > 1000 items, then the query fails in Oracle: ORA-01795: maximum number of expressions in a list is 1000.

Important: If the intention is to populate the GRIDCODE and GRIDCODES parameters in the DST query, then the RS<object>Search MUST be included as a result set in the Chuck and Crystal Templates.

Job Update Grids

:JOBCODE—The code of the opened job.
:JOBNAME—The name of the opened job.

On the Schemes tab:

:SCHEMECODE—The code of the selected scheme.
:SCHEMECODES—The codes of the selected schemes.

On the Samples tab:

:SAMPLEID—The ID of the selected sample.
:SAMPLECODE—The code of the selected sample.
:SAMPLENAME—The name of the selected sample.
:SAMPLEIDS—The IDS of the selected samples.
:SAMPLECODES—The codes of the selected samples.
:SAMPLENAMES—The names of the selected samples.

Report Request Update Grids

:RRCODE—The code of the opened report request.
:RRNAME—The name of the opened report request.