SBM 11.6 Report Improvements

Using Query at Runtime in Advanced SQL

In SBM 11.6,  you can use a Query At Runtime condition in an advanced SQL statement using the following format: 

%QAR{{Table.Column, User text}}%

The Query At Runtime definition starts with the string %QAR{{ and ends with }}%. 

The portion in between consists of two parts:

  • Table.Column – This defines the database table and the column to use.  The table name is optional.  If you leave it out, the table you select in the Report Item Type field is used instead.  Your SQL statement will just be: %QAR{{Column, User text}}%.
  • User text – This is text that you add to the field name on the Query At Runtime report page.  

For example, if your statement is:

@WHERE

TS_DEVELOPER in (%QAR{{UBG_ISSUES.TS_DEVELOPER, is this person}}%) and

TS_OWNER in (%QAR{{UBG_ISSUES.TS_OWNER, is this person}}%)

The Query at Runtime report page will look like this:

Note that the following field types are not supported with advanced SQL Query At Runtime:

  • System fields
  • Multi-Relational, Multi-Selection, Multi-User, and Multi-Group fields

Here are some Query at Runtime examples with supported field types:

  • Last Modified Date field:

@WHERE

UIM_INCIDENTS.TS_LASTMODIFIEDDATE >

%QAR{{ UIM_INCIDENTS.TS_LASTMODIFIEDDATE, is after }}%

  • Owner field:

@WHERE

TS_OWNER in (%{{Current User}}%)

  • Text field:

@WHERE

TS_ISSUEID like '%%QAR{{TS_ISSUEID, contains}}%%'

@WHERE

TS_ISSUEID like '%%QAR{{TS_ISSUEID, contains}}%%' OR

TS_ISSUEID like '%%QAR{{TS_ISSUEID, contains}}%%' OR

TS_ISSUEID like '%%QAR{{TS_ISSUEID, contains}}%%'

  • Date/Time field:

@WHERE

TS_EST_DATE_TO_FIX >= %QAR{{UBG_ISSUES.EST_DATE_TO_FIX, >=}}%

@WHERE TS_EST_DATE_TO_FIX > %QAR{{UBG_ISSUES.TS_EST_DATE_TO_FIX, >}}% and

TS_EST_DATE_TO_FIX < %QAR{{UBG_ISSUES.TS_EST_DATE_TO_FIX, <}}%

  • Binary field:

@WHERE

TS_P4STATUS = %QAR{{UBG_ISSUES.P4STATUS, =}}%

  • User field:

@WHERE

TS_DEVELOPER in (%QAR{{UBG_ISSUES.TS_DEVELOPER, in}}%) and

TS_OWNER in (%QAR{{UBG_ISSUES.TS_OWNER, in}}%)

  • Numeric floating point field:

@WHERE

TS_FLD_NUM_2_FP > %QAR{{UBG_ISSUES.TS_FLD_NUM_2_FP, >}}%

  • Numeric integer field:

@WHERE

TS_FLD_NUM_1_INT > %QAR{{UBG_ISSUES.TS_FLD_NUM_1_INT, >}}%

  • Single-Relational field:

@WHERE

TS_FLD_SINGLE_RELATIONAL in (%QAR{{TS_FLD_SINGLE_RELATIONAL, in}}%)

Using Query at Runtime in Advanced SQL

In SBM 11.6, you can combine basic and advanced SQL conditions in your report definition.  This takes some of the hassle out of writing the advanced SQL statement.  You can also add basic conditions that use Query at Runtime parameters and use them with advanced SQL conditions.

 

For example, to find active items owned by Joe that have a file attachment, define part of the search using basic conditions, and then use pass-through SQL for the remainder:

(In this example, 16 represents a file attachment.)

Other Report Improvements

Make sure to check out the following additional improvements in this release:

  • You can now sort rows in Distribution reports by count. You can order rows by name, total value, or maximum value, and then sort in ascending or descending order.
  • You can now choose to display percentages instead of the number of items in Distribution reports.
  • A new grid-based HTML template (gridlist.htm) is available for Listing reports. This template includes a static header section, which keeps the report title, column titles, and Actions drop-down list always visible. It also provides automatic pagination.

 

 

Custom Work Center Themes in SBM 11.6
SBM 11.6 Work Center Search Improvements

Comments

 
No comments yet

Recent Tweets