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.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 NOSTATUS, 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.
Using a LIMIT Statement With a STATUS Keyword Rather than VALUES
When possible, when you want Oracle OLAP to use the dimension values that are presently in status, use a LIMIT (using values) command with the STATUS keyword (or a LIMIT function with a similar construction) rather than using a VALUES statement. A LIMIT with the STATUS keyword is more efficient than a VALUES (dimname) statement.
The VALUES function is very similar to the CHARLIST function. VALUES(MONTH) returns the same list as CHARLIST(MONTH).
The main differences are:
For dimensions, the NOSTATUS keyword of VALUES lets you use the default status without first limiting the dimension values to ALL.
The VALUES function lets you use a text expression to specify the dimension or valueset name. See Example 8-162, "VALUES with Text Variables".
Special Considerations for an Ampersand (&)
Under certain circumstances, an ampersand (&) that is intended to be a character in a dimension value name is 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 New York instead of NewYork. When you have dimension values that include ampersands in their names, refer to Example 8-163, "Workaround for Dimension Value Names Including an Ampersand".
Example 8-160 Listing the Values of a Valueset
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 Jan95, May95, and Dec95. The following statement displays the values.
SHOW VALUES(monthset) Jan95 May95 Dec95
Example 8-161 Listing Position Numbers of a Dimension
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 month dimension, May95 as the 65th value, and Dec95 as the 72nd value, although they are the first, second, and third values in monthset.
Example 8-162 VALUES with Text Variables
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 district, VALUES(textvar) returns a list of district values.
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.
SHOW CHARLIST(&textvar)
Because ampersands in a program can degrade performance, use VALUES rather than CHARLIST in such cases.
Example 8-163 Workaround for Dimension Value Names Including an Ampersand
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 prod and geog dimensions.
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 generates 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) >