Additional Result Sets for a Grid Report

• Additional Result Sets for a Grid Report
  • Overview
  • Details
    • Structure of a DSC File
    • Parameter Use in the DSC's SQL Query Statement
      • Language Context
      • Search Criteria
      • Grid Content
      • Custom Attributes
      • Object Attributes
    • Using Double Curly Quotes to Remove Statements where the Object Variable is not Populated
    • Using Single Quotes to Inspect Parameters
    • Export To Report Limitation

Overview

A direct source connection (DSC) file is used to create result sets for the report, including database fields, logo images and text notes.

A DSC file can be embedded with the following report engines: Crystal Reports, Chunk Text, and Other—Groovy. If a DSC file is used, it needs to be zipped with the report template and the ZIP file uploaded to CCLAS using the CCTPLR—Report Template application. Where the ZIP file includes more than one DSC file, the first randomly found one is used—therefore it is best to have only one DSC file in the ZIP file.

When a report template is accessed, the first line of the report template indicates to the report generator whether to look for a DSC file and the specified result sets within that DSC file.

For example, if the first line of the template file contains:

{!-- Ventyx-Datasource-Aliases: VBOXES,VJOB_CL --}

then the DSC must contain result set construction for VBOXES and VJOB_CL result sets.

Details

Structure of a DSC File

DSC is a pseudo SQL statement written in the following XML format.

<?xml version="1.0" ?>

<SQL>

  <SQLQUERY>

    <SQLTABLE>ResultSet1</SQLTABLE>

ResultSet1 is the name of the result set established by the code within the <SQLCOMMAND> section. The name of the result set must match the result set referenced in the first line of the TPL file:

    <SQLCOMMAND>

      select T1Field1, T1Field2, ... T1Fieldn

      from SourceTable1 T1

      where WhereClauses

      order by OrderByClauses

    </SQLCOMMAND>

  </SQLQUERY>

The <SQLCOMMAND> section builds the result set.

Depending on the type of grid report, various parameters are populated contextually and passed into the DSC for use in the SQL query statement to either be included in the SELECT clause or quality the WHERE clause. See below for comments on parameter use.

  <SQLQUERY>

    <SQLTABLE>ResultSet2</SQLTABLE>

    <SQLCOMMAND>

      select T2Field1, t2Field2, ... T2Fieldn

      from SourceTable2 T2

      where WhereClauses

      order by OrderByClauses

    </SQLCOMMAND>

  </SQLQUERY>

  ...

  <SQLQUERY>

    <SQLTABLE>ResultSetn</SQLTABLE>

    <SQLCOMMAND>

      select TnField1, TnField2, ... TnFieldn

      from SourceTablen Tn

      where WhereClauses

      order by OrderByClauses

    </SQLCOMMAND>

  </SQLQUERY>

</SQL>

Parameter Use in the DSC's SQL Query Statement

Depending on the source application (that is, a search results grid in a search application or a details grid in an update application), various parameters are passed into the report generator to provide context for the report. These variables can be used in a SELECT or WHERE clause to provide context to a query statement.

Note: Exact case must be used for any parameter name, record set property name or search criteria field name.
Parameter names are listed by bullet point below. Record set property names are listed in the RS Columns for each record set type in Reporting Result Sets and Views. Search criteria field names can be found by checking Show attribute tooltips in CCLAS settings.

Limitation: Report generation returns an error if an attempt is made to pass > 1000 parameters into the DSC. A parameter is passed in to the DSC where there is a reference to the parameter in the DSC. For example, where there are > 1000 JOBCODES or GRIDCODES passed into the DSC. This is an Oracle limitation, as the query built is WHERE x is IN (list), and the list cannot contain more than 1000 comma-separated items. If there is a need to report more than 1000 rows, then the DSC could construct the dataset itself, instead of using the default RS data set passed into the DSC.

The following parameters are passed into a DSC for a grid report:

• :ORGID—The ID of the current organisation.

• :ORGCODE—The code of the current organisation.

• :LABID—The ID of the current laboratory.

• :LABCODE—The code of the current laboratory.

• :LABNAME—The name of the current laboratory.

Language Context

• :LANGUAGE—The language in which to extract the additional report result sets, set to the language set by the EXPORT_REPORT_LANGUAGE application preference. If the preference does not exists or contains an invalid language, then an error is raised: Invalid report language.

Search Criteria

Search criteria can be referenced in the DSC for use in SQL clauses.

Accessing Search Criteria in a DSC for a Grid Report

Grid Content

Accessing Grid Content in a DSC for a Grid Report

Custom Attributes

Custom attributes of type Boolean, Decimal, Date, Integer, Timestamp and String type custom attributes are supported.

Custom attribute uses the same syntax as standard attribute.

For example, where CCJOB has the custom attribute MY_COLOUR, then the custom attribute can be used in a query through a inner join to the MSF0P5 table. To return all the jobs where their colour (Column:MY_COLOUR) is set to BLACK:

SELECT * FROM ccjob myJob INNER JOIN MSF0P5 msf0p5 ON myjob.id= msf0p5.entity_key

WHERE msf0p5.entity_type LIKE '%.%.MY_COLOUR%' AND msf0p5.property_value = 'BLACK';

Note: Different types of custom attribute values can be stored in different tables. See link for current 'understandings' on where/how the CA are stored. Refer to Configuring Custom Attributes.

Object Attributes

Attributes of an object (including custom attributes) are accessed using syntax defined within {{ }}. For example:

{{ AND (job.LABORATORY_CODE = (^laboratoryCode) AND job.ORGANISATION_CODE = (^organisationCode) }}

{{ AND B.PROPERTY_VALUE = CASE (^CA_BOOLEAN)

WHEN 'Y' THEN 'true'

WHEN 'N' THEN 'false'

ELSE 'unknown' END }}

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

Using Double Curly Quotes to Remove Statements where the Object Variable is not Populated

The generated content within {{}} is only be included if ALL attribute have non-null values.

Any DSC lines that are enclosed in double curly quotes are completely removed from the formulated query statement if the object variables are not populated. For example:

And  CODE in (:GRIDCODES)

is always be included, whereas:

{{  And  CODE in (:GRIDCODES)  }}

is only included in the output where the user has select one or more rows in the search results grid. Where the user has not selected any items, then the DSC really has to include ALL the search criteria again in the DSC query.

Therefore, if the criteria is optional for the search, enclose the whole line in double curly brackets. For example:

{{  AND job.ORGANISATION_CODE = (^organisationCode) }}

{{ AND  LAB1.CODE =:LABCODE   }}

{{ AND  (^laboratoryCode ,  job.LABORATORY_CODE =) }}

Using Single Quotes to Inspect Parameters

Where using a search parameter inside single quotes in a DSC:

{{, ('^clientCode') as clientCode}}

then, the number of the referenced parameter is returned, with respect to the sequence of other parameters referenced in the DSC:

clientCode #: :p_10

Directly Accessing the Search Criteria Value

Where using a search parameter without single quotes in a DSC:

{{, (^clientCode) as clientCode}}

• and a value is not entered into the search criteria field, then the value is not instanced:

  clientCode #: <Attribute "CLIENTCODE" does not exist>

• and a value is entered into the search criteria field, then the value is returned:

  clientCode #:CLIENT1

Export To Report Limitation

The fields / parameters in the Export to Report dialog, including customised or personalised fields, are not available to a DSC at the moment.