Automatic Report Parameters

Configuration > Reports > Automatic Report Parameters

Prompt-Free Report Filtering with AutoParams

With FR3 (Fast Reports 3) reports, which are user-configurable reports that are read from SQP files, the user can create reports with customized output and layout. While a full report building tutorial is beyond the scope of this help system (for report writing help, see the Fast Reports User Manual included with the CISPro Global installation), there is one option that warrants mention here. It is the AutoParams feature (short for Automatic Parameters).

Like the user parameters (UserParams) that are used to filter report output by user, site, or some other setting when the report is run, AutoParams are also included in reports for the purpose of filtering results, but with a difference: AutoParams do the filtering without requiring input from the user. AutoParams make it possible to have reports that automatically provide results that apply to the user who runs them.

For example, the Basic Hazard Information report that is shown below uses the auto_siteid AutoParam to limit output to the site of the user who runs the report, without asking the user to specify the site.

Note: The AutoParams only apply to the FR3 reports (print reports), which are configured from the System Configuration Reports link (i.e., not Dynamic Reports). See Static Reports for more information.

An example: “Basic Hazard Information” with and without an AutoParam

Without an AutoParam, the .SQP file for the Basic Hazard Information report looks like this:

Basic Hazard Information Report without an AutoParam

<?xml version="1.0"?> 

<CAFReport>

 <Query name="master">

         <SQLText>

select m.materialid,m.materialname,m.casno,m.firecode,m.healthcode,m.reactivecode,

  m.storage_conditions,m.target_organs

from materials m,materials_subclass sc,materials_class c

where c.materialclassid=sc.materialclassid

 and sc.materialsubclassid=m.materialsubclassid

 and c.classname='CHEMICAL'

 and m.deleted='0'

 and lower(m.MaterialName) like lower ( :MaterialName_Begins + '%')

order by lower(m.materialname)

         </SQLText>

         <Query name="detail">

                 <SQLText>

                         <!-- Using CDATA tag beacuse query contains illegal chars -->

                         <![CDATA[  

select code,phraseenglish 

from jct_rsphrases_materials j,rs_phrases r

where r.rsphraseid=j.rsphraseid

  and j.materialid=:materialid

order by code

                         ]]>

                 </SQLText>

         </Query>

                 <UserParam name="MaterialName_Begins" required="true" type="string" desc="Use percent sign for all." />

 </Query>

</CAFReport>

Note: There is a parameter (known as a vclparam) in the query body (red text preceded by a colon) that exactly matches the name of a UserParam entry following the query (blue text). This causes the report to prompt the user for text with which material names must begin. The report will then filter the query results and show only materials with names that begin with that text.

Adding an AutoParam to the Report

In order to use an AutoParam, you must first assure that an appropriate field exists for its use. For example, you can not use auto_siteid if there is no siteid in the joined scope of the query.

In our Basic Hazard Information example, we will adjust the report’s SQP so that it uses the auto_siteid AutoParam. The modified SQP is shown below, with the changes shown in red.

Basic Hazard Information Report with an AutoParam

<?xml version="1.0"?> 

<CAFReport>

 <Query name="master">

         <SQLText>

select m.materialid,m.materialname,m.casno,m.firecode,m.healthcode,m.reactivecode,

  m.storage_conditions,m.target_organs

from materials m,materials_subclass sc,materials_class c

where c.materialclassid=sc.materialclassid

 and sc.materialsubclassid=m.materialsubclassid

 and c.classname='CHEMICAL'

 and m.deleted='0'

 and lower(m.MaterialName) like lower ( :MaterialName_Begins + '%')

 and m.CreationSiteId = :auto_siteid 

order by lower(m.materialname)

         </SQLText>

         <Query name="detail">

                 <SQLText>

                         <!-- Using CDATA tag because query contains illegal chars -->

                         <![CDATA[  

select code,phraseenglish 

from jct_rsphrases_materials j,rs_phrases r

where r.rsphraseid=j.rsphraseid

  and j.materialid=:materialid

order by code

                         ]]>

                 </SQLText>

         </Query>

                 <UserParam name="MaterialName_Begins" required="true" type="string" desc="Use percent sign for all." />

                 <UserParam name="auto_siteid" required="true" type="string" desc="auto siteid of logged in user" />

 </Query>

</CAFReport>

Note: We did not have to alter the join scope of this query (that is, add tables) because the Materials table has an appropriate attribute already (creationsiteid). Thus, the addition of the auto_siteid AutoParam will force this report to display only materials rows for materials which were created within the logged in user’s site.

Supported AutoParams

The table shown below lists the AutoParams that are supported in FR3 reports. Keep in mind that for an AutoParam to work in a report, it must represent a field that is included in the scope of the report.