The VALUES function returns the default status list or the current status list of a dimension or dimension surrogate, or it returns the values in a valueset. VALUES returns a multiline text value that contains one dimension value on a line.
Note:Because composites do not have status, you cannot use the VALUES function with a composite. When you attempt to do so, Oracle OLAP displays an error message.
VALUES(dimension [keyword] [INTEGER])
A text expression whose value is the name of a dimension, dimension surrogate, or valueset.
One of the following keywords that specify whether you want the current status list or the default status list for a dimension or a surrogate:
NOSTATUS which indicates that VALUES should return the default status list of a dimension or dimension surrogate rather than its current status list.
STATUS which indicates that VALUES should return the current status list of a dimension or dimension surrogate (Default).
These keywords do not affect valuesets. For a valueset, VALUES returns all the values in that valueset whether you specify
STATUS, or nothing.
When you use the INTEGER keyword, the function returns the position numbers of the dimension or dimension surrogate values rather than the values. When you use INTEGER with a valueset, the function returns the position numbers of the values in the existing dimension, not in the valueset.
The VALUES function is very similar to the CHARLIST function.
VALUES(MONTH) returns the same list as
The main differences are:
For dimensions, the NOSTATUS keyword of VALUES lets you use the default status without first limiting the dimension values to
The VALUES function lets you use a text expression to specify the dimension or valueset name. See Example 8-160, "VALUES with Text Variables".
Under certain circumstances, an ampersand (
&) that is intended to be a character in a dimension value name will be interpreted as ampersand substitution. When this happens, Oracle OLAP generates an error message.
This happens because Oracle OLAP recognizes special characters in dimension value names with when they are used in tuples in text expressions. For example, you can include spaces, such as naming a dimension value
York instead of
NewYork. When you have dimension values that include ampersands in their names, refer to Example 8-161, "Workaround for Dimension Value Names Including an Ampersand".
The easiest way to display the values of a valueset is simply by using the name of the valueset in a SHOW or a REPORT statement. You can also use VALUES to list the values in that valueset.
For example, suppose an analytic workspace contains a valueset called
monthset that has the values
Dec95. The following statement displays the values.
SHOW VALUES(monthset) Jan95 May95 Dec95
You can use VALUES to list the position numbers instead of the actual values in a dimension or valueset. In this example, because you are using the INTEGER keyword with a valueset instead of a dimension, the function returns the position numbers of the values in the
month dimension as shown by the output returned by the following statement.
SHOW VALUES(monthset INTEGER) 61 65 72
Therefore, the value
Jan95 is shown as the 61st value in the
May95 as the 65th value, and
Dec95 as the 72nd value, although they are the first, second, and third values in
This example shows how to assign a dimension name to a text variable and use the text variable in the VALUES function instead of the variable name itself. As the following statements illustrate, when the variable
textvar has the value
VALUES(textvar) returns a list of
textvar = 'district' SHOW VALUES(textvar) Boston Atlanta Chicago Dallas Denver Seattle
To list the values of
district using the CHARLIST function rather than VALUES, you must use an ampersand.
Because ampersands in a program can degrade performance, you should use VALUES rather than CHARLIST in such cases.
When a dimension value name contains an ampersand (
&) as one of its characters, and when that dimension is a base dimension of a conjoint dimension, then a text expression that contains the names of dimension values in a tuple can generate an error in certain circumstances. This example shows how to avoid this error.
Suppose you use the following statements to define two dimensions.
DEFINE prod DIMENSION TEXT DEFINE geog DIMENSION TEXT
Next, you use the following statements to define two conjoint dimensions.
DEFINE conj1 DIMENSION <prod geog> DEFINE conj2 DIMENSION <prod geog>
The following statements add dimension values to the
MAINTAIN prod ADD 'prod1' 'prod&val2' MAINTAIN geog ADD 'geog1' 'geog&val2'
The following statements add tuples (combinations of dimension values) to the CONJ1 conjoint dimension.
MAINTAIN conj1 ADD <'prod1' 'geog1'> MAINTAIN conj1 ADD <'prod&val2' 'geog1'>
Now, suppose you want to use the VALUE function with a MAINTAIN statement to add those same tuples to the
conj2 conjoint dimension. When you attempt to use the following statement, it will generate an error message.
MAINTAIN conj2 ADD VALUES(conj1) ERROR: (MXMSERR) val2 does not exist in any attached workspace.
This error occurs because the ampersand in the dimension value name
prod&val2 is interpreted as an attempt at ampersand substitution.
Instead of using the preceding MAINTAIN statement, you can use the following statement to add the tuples to the CONJ2 conjoint dimension.
MAINTAIN conj2 MERGE < KEY(conj1 prod) KEY(conj1 geog) >