cancel
Showing results for 
Search instead for 
Did you mean: 

EPMDimensionOverrideRange for Dimension

frederico_lapa
Explorer
0 Kudos

Hi,

I’ve been trying to use the function EPMDimensionOverrideRange for dimension Account with hierarchy nodes in a report without success. I’ve prepared two examples: in the first example I’m getting the expected results, but in the second example some nodes are missing.

Example 1 – works fine:

Formula: =EPMDimensionOverrideRange("000";"ACCOUNT";Parametros!E4:F6)

Where ranges are:

Looking at the editor all the selections are correct because there are no other hierarchy nodes between:

Example 2 – the result is not correct:

With a new range: from H11201110 to H11202200 I was expecting the same result above - H11201110, H11201210, H11202100, H11202200.

Formula: =EPMDimensionOverrideRange("000";"ACCOUNT";Parametros!E4:F4)

The new range is:

Looking at the editor the selections are not correct because it only considers the low and high hierarchy nodes and nodes H11201210 and H11202100 are missing:

Does anyone know how to properly use this range function?

My version is: 10.0 SP 38.

Thanks,

Frederico

Accepted Solutions (0)

Answers (7)

Answers (7)

frederico_lapa
Explorer
0 Kudos

Hi benniejay.guevarra ,

Please check the following real example.

For report 000 I need to provide a single row with the following filters:

With the the following formula:

=EPMDimensionOverrideRange("000";"ACCOUNT";Parametros!G4:G14)

I can get the accounts filtered in row Axis:

After removing empty and zero values the output is:

My expected output is one row with the sum of the selected nodes:

Total -> 283 812

Bellow you'll find an example of the hiearachy:

To sumup, I need to provide a sheet with several reports (a row for each combination of filters). Bellow, in green you can see what I need to deliver:

Thanks for your help,

Frederico

frederico_lapa
Explorer
0 Kudos

Hi benniejay.guevarra ,

Thanks for your feedback. For me is now clear that I can only use members in page Axis.

I've tried to use your first suggestion in row Axis, but the only issue is that I get several rows (one for each node) and the business requirement is to have the result in a single row.

Do you know if there's a way to show the result of a range in Row Axis in a single row?

Thanks,

Frederico

benniejayfguevarra
Participant
0 Kudos

Hi Fredirico,

Can you give an example how your hierarchy looks like? and an example of the range and the expected output? Maybe you can add additional filter like HLEVEL to further trim your result.

Thanks,

Bennie

frederico_lapa
Explorer
0 Kudos

Hi nikhil.anand,

You're right! Your formula works perfectly in rows:

However, the business requirement is to have the result in a single row and this way I get a row for each node and that's why I was trying to use the page Axis.

Is there a way to show the result of a range in Row Axis in a single row?

Thanks a lot,

Frederico

N1kh1l
Active Contributor
0 Kudos

frederico.lapa

This will work only if your account is in Rows or Columns. If its in page axis, then you have to try approach

Hope this helps

Br.

Nikhil

frederico_lapa
Explorer
0 Kudos

Hello nikhil.anand and benniejayfguevarra,

Thanks a lot for throwing some light on this topic.

I've already tested both of your suggestions, but I didn't manage to solve the issue.

In both situations the selected member didn't change in the editor.

Test with formula =EPMDimensionOverride("000";"ACCOUNT";H17):

Test with formula EPMDimensionOverride("000";"ACCOUNT";H18):

My syntax should be missing something... I've already tried many variations, but I coudn't found out the issue.

Any ideas?

Thanks,

Frederico

benniejayfguevarra
Participant
0 Kudos

Hi Fredirico,

The only available member elationship in page axis is Member only. You have no choice but to enumerate it. Please also note there is a limit to the number of members in the page key.

Thanks,

Bennie

benniejayfguevarra
Participant
0 Kudos

Hi Frederico,

EPMDimensionOverrideRange doesn't work like ranges in BW (Low-High). It just gets the specific members on the range you put that's why the first one work.

An alternative is the Between keyword.

Thanks,

Bennie

N1kh1l
Active Contributor
0 Kudos

frederico.lapa

The behavior for EPMDimensionOverrideRange is correct.

You can specify a Range of Excel cells as the third parameter for members. This Range should have all member ID which you want the override to consider. The word "range" should not be interpreted as High to Low as its the excel cell range and not the range between high and low id's

You can achieve what you want by using the normal EPMDimensionOverride. Just replace the time with your account dimension and provide the Account ID for <= and >=

Hope this helps !!

Br.

Nikhil