How to create a parametrized Information Link, conditional on its input parameters.

How to create a parametrized Information Link, conditional on its input parameters.

When creating Information Links, you may need to have optional input parameters, or have the Information Link execute different queries depending on the input values. For example:

• If the input parameter is empty, return all rows.
• If the input parameter has a value, only return rows that match my input value.

By editing the SQL of an Information Link, you can create these types of conditional Information Links. The SQL modifications performed MUST be proper syntax for your particular database. This example inserts an IF THEN ELSE clause into an Information Link executing against Microsoft SQL Server, and therefore the syntax may differ from what will work in your environment. This particular example could also be accomplished by just editing the WHERE clause (WHERE ?inputParameterName=B1."NAME"OR ?inputParameterName=''), but this is just an example query. It demonstrates the method for running different queries based on input values (as long as the column structure returned is identical).

1). Create your Information Link.

2). Click on the "SQL.." link, to edit your SQL and add in the 'optional' input parameter.
    The default SQL will be something like this:

SELECT
           B1."NAME" AS "NAME",
           B1."VALUE" AS "VALUE"
        FROM
           "myDatabase"."dbo"."myTable" B1
        WHERE
           <conditions>

    Edit the SQL by using an IF THEN ELSE function in combination with a "?parameter" value, to check its value and perform one of two different SQL queries depending on its value.  For example, (using ?inputParameterName as an input parameter to choose which 'NAME' to return):

IF ?inputParameterName=''    --COMMENT: If my input parameter is empty, perform query 1 and return all rows
            SELECT
               B1."NAME" AS "NAME",
               B1."VALUE" AS "VALUE"
            FROM
               "myDatabase"."dbo"."myTable" B1

        ELSE    --COMMENT: If my input parameter is not empty, perform query 2 and return only rows where my NAME matches my input parameter
            SELECT
               B1."NAME" AS "NAME",
               B1."VALUE" AS "VALUE"
            FROM
               "myDatabase"."dbo"."myTable" B1
            WHERE
            ?inputParameterName=B1."NAME"
            and
               <conditions>

3). Define your parameter.
    In the "Parameters" section of your Information Link, click "Refresh". Then once the 'inputParameterName' parameter is present, click 'Edit' to define it (For example: Data type=string, Value type=single value).

4). Save your parametrized Information Link.

5). Add your on-demand data table using the parameter as the input, not the column.
    When adding your on-demand data table (File > Add Data Tables), choose your parametrized Information Link. Select 'Load on demand' checkbox in the 'Add Data Tales' dialog and click the 'Settings' button to define the input as your 'inputParameterName' parameter (linked to your property value), instead of using a column value as the input. See the documentation reference for more details around the on-demand data table input parameter configuration. Note: your standard non-optional/non-conditional input property values can be defined directly on the column and not on a separate "parameter" like with 'inputParameterName'..

Now your Information Link and on-demand data table can handle both the case when the input parameter is empty/not provided and the case in which it has a value, each using its own unique logic. 

COMMENTS:

• If you have any issues creating your SQL, you should execute your query and then check the sql.log on the TIBCO Spotfire Server that is created in the <Installation Dir>\tibco\tss\X.X.X\tomcat\logs folder. This will contain the final SQL that is sent to your database, and you can check here to see whether you need to adjust the SQL to make the final syntax correct for your database.
• This can also be accomplished by creating a stored procedure that contains the conditional logic, and then the Information link only uses this Stored Procedure element.