Transferring Values Between Queries Using Characteristic Value Variables

Objectives

After completing this lesson, you will be able to:

  • Create characteristic value variables with replacement path and precalculated value set

Replacement Path from Query

Characteristic value variables that are defined with the replacement path processing type can be replaced from the CURRENT MEMBER variable, from other variables, and from a query result. You can transfer values between queries with this type of variable by inserting the query result as the variable in a different query. The variable will run as a prequery when you execute the query.

For example, insert the result of the Top 5 Products query into another query, Net order amount - Calendar Year/Month, variably. Execute the Net order amount - Calendar Year/Month query to determine how the net order amount of the top five products has developed on a month-by-month basis.

Note that the prequery is executed each time the query is executed. Running the prequery may contribute to slow performance. If you do not need to execute the prequery every time you run the query, use a variable that represents a precalculated value set, so that the variable values are stored over time.

Characteristic Value Variable with Replacement Path from a Query

  • The variable accesses the values from an additional query, the prequery.

  • The prequery is executed each time the result query is executed.

  • Example: How do the Top 5 Products dynamically determined by the prequery perform in an analysis by month?

Replacement Path from Query

In this video, you explore how a pre-query can filter a result query.

Precalculated Value Set (Bucket)

If you want to transfer results from one query to another query, but the runtime of the prequery is very long, you can use an alternative scenario: Precalculated Value Set (Bucket). Query runtime can be improved considerably when you use this feature. The query that fills the precalculated value set is executed independently of the result query. The precalculated value set is represented by a characteristic value variable, which is used as filter in the result query.

Characteristic Value Variable Representing Precalculated Value Set

  • Define a first query for filling the value set.
  • Instead of executing this query, for example, in Analysis, choose Update Value Set by Query.
  • Create a Characteristic Value Variable with processing type Manual Input / Default Value (User Entry) and the setting Var. Represents: Precalculated value set.

  • Define a second Query for your reporting where you use the previously defined variable as a filter; you can thus switch between various precalculated value sets at query runtime.

Other Ways of supplying a Precalculated Value Set (Bucket) with Values

There are three different ways to supply precalculated value sets with values:

  • Using a query (see above)

  • Entering them manually

  • Referencing a table

Precalculated Value Set

In this video, you learn how to fill a characteristic value variable with precalculated values.

Log in to track your progress & complete quizzes