How to use "Substitution based Parameterization"?

Products	Versions
Spotfire Statistica	12.0 and later versions

Description

How to use "Substitution based Parameterization" in Data configurations in Statistica ?

Issue/Introduction

Environment

Windows/ Statistica Enterprise installation.

Resolution

1. Create a typical data configuration with a query. The click the Options button, and select Substitution_based Parametrization:

2. Click on Commit button to save the data configuration.

3. Add parameters to the query:

Note that you must create the initial standard query, select the Parametrization option, and save the data config. Then you can add parameters to the query.

Let's look more closely at that parametrized query:

SELECT Y, COLOR, WIDTH, ${OPTIONAL_FIELD:WEIGHT} FROM CRABS WHERE COLOR = ${CHOSEN_COLOR:'medium'}

The code ${OPTIONAL_FIELD:WEIGHT} allows you to select an addition field at run-time, and specify a default value for that additional field. In this case, the parameter is named OPTIONAL_FIELD, and the the default value is WEIGHT.

The code ${CHOSEN_COLOR:'medium'} allows you to specify a filter criteria for the WHERE clause at run-time, and specify a default filter criteria. In this case, the parameter is named CHOSEN_COLOR, and the default criteria is â€˜mediumâ€™.

Once these parameters have been defined in the data configuration, parameter values can be specified whenever you run an analysis configuration based on that data configuration. Parameter values can be specified in 2 ways: via SVB macro, or interactively via an option in the analysis configuration. Let's see examples of both:

1) You can reference the query parameter in SVB as follows:

Sub Main

Dim ms As MonitorService

Set ms = MonitorServiceCreator.CurrentServiceEx(Application)

Dim opt As New MonitorRunOption

opt.Add "RunQuiet", False

opt.Add "profile.query.1.param.${OPTIONAL_FIELD}", "CATWIDTH"

opt.Add "profile.query.1.param.${CHOSEN_COLOR}", "'dark'"

Dim st As MonitorRunStatus

Set st = ms.RunMonitor ("\StatSoftTulsa\Germany\Crabs", opt)

If st Is Nothing Then Exit Sub

st.WaitForComplete

st.Delete False

End Sub

See attached QueryParameterize.svb.

2) In the analysis configuration, Run optins, select the Show Substitution-Based Parameters dialog option:

Then when you run the analysis configuration, you will see a dialog that allows you to specify the parameter values:

You can also use this kind of query parametrization to specify the value of an additional field to be added to the query output at run-time. Consider the following example:

SELECT StabilityBig."ID", StabilityBig."Product", StabilityBig."Study", StabilityBig."Batch", StabilityBig."Month", StabilityBig."Potency", StabilityBig."LowerSpec", StabilityBig."UpperSpec", StabilityBig."NumberOfDecimals", ${Time Periods:36} as 'Time Periods' FROM "ProductionData"."dbo"."StabilityBig" StabilityBig

At run-time, you will see the following prompt:

The Time Periods phrase in the braces indicates the name to be displayed in the above prompt dialog. The alias as "Time Periods" indicates that the new field in the spreadsheet with the query results will be named "Time Periods"

When dealing with field names and aliases in Oracle use:

Single quotes for names within the parametrization.

Double quotes for Oracle column name aliasing.

Welcome to "KB Articles"