Hi,

Let's say I have a four-level dimension, and I want to see all the measures for a particular value in the fourth-level of the dimension. I do not know what Levels 1-3 contain, just Level 4.

Example:

  &DIMENSION_NAME = '<UNKNOWN>~<UNKNOWN>~<UNKNOWN>~087-50-6562';

If I was building a gadget, I could do a -SET on the Dimension name, as long as I knew the values for levels 1-4. Is it possible to use wildcards for levels 1-3 and a specific value for level 4? I tried % and *, but it didn't return any results.

I also experimented with querying the DIMENSIONS table directly, like this:

TABLE FILE DIMENSIONS
BY LEVEL_01_VALUE
BY LEVEL_02_VALUE
BY LEVEL_03_VALUE
BY LEVEL_04_VALUE
WHERE LEVEL_04_VALUE EQ '087-50-6562';
END

I was going to do a READ and then concatenate the values to build a filter, but I immediately ran into a problem. There are actually two records returned for this dimension value, which throws my plan out the window. I need the results to include both records, not just the highest/lowest/first/last record.

Any ideas?

Cheers,

Joey

This message has been edited. Last edited by: Moogle,

Hey Joey,

Is this value ('087-50-6562') in different branches of the same Dimension, like you could have the city Springfield in many states, or is it two different Dimensionsv- perhaps there is a customer who just happens to be named 'Springfield'?

EricH

Hi Eric,

Same dimension, different branches, just like your Springfield example.

Cheers,

Joey

I wonder if anyone has any ideas about this? It's becoming more of a requirement each day, although I have said I'm still researching if it is even possible.

Cheers,

Joey

Hi Joey,

Try using 30 characters of '^' for each level that you want to use a wildcard for.

So for your example of:
&DIMENSION_NAME = '~~~087-50-6562';

You would use:

&DIMENSION_NAME =
'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^~087-50-6562';

For those using pre-514 of PMF you would not use the delim character of ~. So it would instead be:

&DIMENSION_NAME =
'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^087-50-6562';

Let us know if this works.

Cheers,
Mike

Hi Mike,

This works very well, actually. I am impressed, thanks so much.

Cheers,

Joey