How to embed parameters in Information link using Stored Procedure to work with Scheduled Updates in TIBCO Spotfire Web Player

How to embed parameters in Information link using Stored Procedure to work with Scheduled Updates in TIBCO Spotfire Web Player

When a parameterized Information Link is used in a Spotfire analysis, it may be assumed that the parameter values provided while importing the Information link shall be saved in the analysis and the same parameter values are used by Scheduled Updates while caching the analysis or just opening the analysis on the TIBCO Spotfire Web Player. But this is not the case.

When parameter values are passed to a Stored Procedure through an Information Link, if you only follow the below steps, your cache refresh for the analysis using Scheduled Updates will fail.

Scenario 1: Stored Procedure with no default value

1. Create a Stored Procedure element with Prompt value as "None"
2. Import the Stored Procedure element in an Information Link. If you click the "SQL..." button the SQL will be like: { call "SYSTEM"."F_GET_COUNTRIES"(?) }
3. Refresh the Parameter section in the Information Link. As no default value for the parameter is specified, it will show nothing. So you may edit the SQL by providing a parameter name like { call "SYSTEM"."F_GET_COUNTRIES"(?inputparameter) }. Now refresh the parameter section in the Information Link and you will find "inputparameter" with the option to set its data type and value type 
4. Save the Information Link
5. In the analysis, select File > Add Data Tables > Add > Information Link and select the Information Link saved in Step 4. It will prompt for inputparameter value and load the data based on the provided value.
6. Save the analysis in the Library

Scenario 2: Stored Procedure with a default value

1. Create a Stored Procedure element with Prompt value as "None" and default value as ?inputparameter
2. Import the Stored Procedure element in an Information Link. If you click the "SQL..." button the SQL will be like: { call "SYSTEM"."F_GET_COUNTRIES"(?) }
3. Refresh the Parameter section in the Information Link and you will find "inputparameter" with the option to set its data type and value type 
4. Save the Information Link
5. In the analysis, select File > Add Data Tables > Add > Information Link and select the Information Link saved in Step 4. It will prompt for inputparameter value and load the data based on the provided value.
6. Save the analysis in the Library

Now the input parameter value provided at time of loading data is not saved in dashboard, it is just for initial testing of data. Parameter values need to be passed programmatically through an on-demand data tables in TIBCO Spotfire Analyst otherwise it will fail on TIBCO Spotfire Web Player and every time you reload the analysis in the TIBCO Spotfire Analyst client, you will get a dialog box to enter the parameter value.

There may be other scenarios where you need to use same Information Link in multiple analysis files but with different parameter values, so you would want to embed the parameter value in each analysis individually. Option 1: Not using an on-demand data table

1. Create a Stored Procedure element without a default value and a single prompt value
2. Import the Stored Procedure element in an Information Link. If you click the "SQL..." button the SQL will be like: { call "SYSTEM"."F_GET_COUNTRIES"(?) }
3. Save the Information Link
4. In the analysis, select File > Add Data Tables > Add > Information Link and select the Information Link saved in Step 3
5. A Prompt dialog box will appear. Provide prompt values while adding the Information Link in analysis
6. Save the analysis in the Library

 

Option 2: Using an on-demand data table

1. Create a Stored Procedure element with Prompt value as "None" and default value as ?inputparameter
2. Import the Stored Procedure element in an Information Link. If you click the "SQL..." button the SQL will be like: { call "SYSTEM"."F_GET_COUNTRIES"(?) }
3. Save the Information Link
4. In the analysis, select File > Add Data Tables > Add > Information Link and select the Information Link saved in Step 3.
5. In the prompt dialog boxes, provide the prompt values
6. While adding the Information Link, select "Load on demand" option in the "Load method" section
7. Save the analysis in the Library
8. Save dashboard and open on TIBCO Spotfire Webplayer. It will open up successfully
9. Then cache the dashboard and it will be cached as well. 

Now if you open this report in the TIBCO Spotfire Web Player it will open up successfully, and if the report is added to Scheduled Updates it will cache successfully, using the stored input parameter values.

Note: On-demand data tables always cache the first set of data with the provided input parameter value. So both the first and second options will return the same results.
  Doc: Parameterized Information Link

• https://docs.tibco.com/pub/sfire-analyst/7.11.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/id/id_parameterized_information_links.htm

Doc: On Demand Data Tables

• https://docs.tibco.com/pub/sfire-analyst/7.11.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/add/add_on_demand_overview.htm

Doc: Scheduled Updates

• https://docs.tibco.com/pub/spotfire_server/7.11.0/doc/html/TIB_sfire_server_tsas_admin_help/GUID-77D00E34-7A16-4984-B4E0-A7F404A24B70.html