Creating an on-demand template app

An on-demand template app is a regular Qlik Sense app with one important difference: its load script contains data binding expressions used to formulate the queries made on the data sources. These data binding expressions are used at on-demand app generation time to read values from the selection state of the selection app and bind them to the template app script so that the generated app is loaded with a user-controlled subset of the data.

The template app typically connects to the same data source as the selection app. The load script of a selection app typically loads aggregated data to reduce data volumes while still offering interactive visualizations of important dimensions and measures. The load script of a template app uses queries that load a controlled subset of more granular data.

Structure of a template app

A template app is linked to a selection app using an on-demand app navigation link. The app navigation link includes properties that control the number of records queried when the on-demand app is loaded with data. The load script of the template app contains data binding expressions that specify which field data from the selection app is used to formulate the queries issued when loading data into the on-demand app.

The data binding expressions have the form:

$(od_FIELDNAME)

The od_ prefix is used to bind the selection state of the selection app to the load script of the on-demand app, which is created by copying the template app. The part of the data binding expression that follows the od_ prefix must be a name that matches a field in the selection app. When the on-demand app is generated, the current selection state of the selection app is used to obtain the desired values to bind for each field. Each occurrence of a $(od_FIELDNAME) expression in the load script of the newly created on-demand app is replaced with the list of values selected for the corresponding field in the selection state of the selection app.

To understand how to bind values using INLINE load statements:

See: Binding expressions in on-demand template apps

To be valid SQL syntax, the template app's SELECT statement for filtering on multiple values must use an IN clause. The recommended practice is to write a subroutine to create the correct WHERE clause:

SUB ExtendWhere(Name, ValVarName)
  LET T = Name & '_COLNAME';
  LET ColName = $(T);
  LET Values = $(ValVarName);
  IF len(Values) > 0 THEN
  	IF len(WHERE_PART) > 0 THEN
    	LET WHERE_PART = '$(WHERE_PART) AND $(ColName) IN ( $(Values) )';
    ELSE
    	LET WHERE_PART = ' WHERE $(ColName) IN ( $(Values) )';
    ENDIF
  ENDIF
END SUB;

Prior to calling the ExtendWhere subroutine, you must set the WHERE_PART variable.

If you want the SQL statement to have an unchanging WHERE clause in addition to the clauses that will be inserted by the selection app, insert a 'WHERE <condition>' in the SET statement. Otherwise, use the simple SET statement.

The following is an example of how the ExtendedWhere subroutine is used:

FOR EACH fldname IN 'ORIGIN', 'DEST', 'YEAR', 'QUARTER', 'ORIGIN_STATE_ABR', 'DEST_STATE_ABR'
  LET vallist = $(fldname);
  WHEN (IsNull(vallist)) LET vallist = '';
  IF len(vallist) > 0 THEN
    CALL ExtendWhere('$(fldname)','vallist');
  ENDIF
NEXT fldname

The list of field names should match the names of the fields used in each field binding you created.

Once the value list for each field has been built, a SELECT statement can be written. For example:

SQL SELECT  "MKT_ID",
	     "YEAR",
	     "QUARTER",
            "ORIGIN",
            "ORIGIN_STATE_ABR",
            "DEST",
            "DEST_STATE_ABR",
            "TICKET_CARRIER",
            "FARE_CLASS",
            "PASSENGERS",
            "DISTANCE",
            1 AS "FLIGHT_COUNT"
FROM "SAPH7T"."/QT/AIRPORT_FACT"
$(WHERE_PART);

The $(WHERE_PART) portion of the SELECT statement will be expanded to include the WHERE clause generated by the execution of the FOR-NEXT loop illustrated above. The list of column expressions that follow the SELECT keyword should be modified to match your specific database table's columns.

Single Sign-On (SSO)

On-demand apps can use single sign-on (SSO) with data sources that support SSO. The engine and the data source must be configured to allow SSO.

See: Configuring SAP HANA for SAML Single Sign-On with Qlik Sense

See: Configuring Cloudera Impala for Single Sign-On

Once the engine and data source have been configured for SSO, the template app must enable SSO by adding the following syntax to the template app script:

///!ODAG_SSO

The On-Demand App Service parses the script when an on-demand app is generated and each time it is reloaded.

When an on-demand app is loaded with SSO, the identity of the end user is sent to the data source. The end user must have access to the sources used in the template app's data connections. Only data that user has access to in those sources is loaded, even if a larger set of data is selected.