PAFE Spreadsheet Calculation Issue for one Consolidated member, but working for all others

[michaelc99]

Good Morning,

I have a user accessing TM1 through PAfE (Build 2.0.64.3). She received a built report that accesses a single cube with twelve dimensions. Most of the dimensions are set to Control Total (consolidated member) with a few small exceptions for version, account, period, cost center, and currency. Most of the formulas return values with the exception of a single column of data that uses a consolidated member called Budget Variance within the VERSION dimension, which returns an #Value error. The Budget Variance member sits within the VERSION dimension as a consolidated member that contain Actual and Budget elements as children with Actual receiving a -1 weight. The Actual and Budget members, separately, return results in the report.

Since all formulas are firing correctly for my Admin account, I initially leaned toward security settings. However, the user can access the cube and all twelve dimensions for the rest of the formulas. So, I am not sure if I continue to explore this as a security setting, PAfE option, or other.

Code: Select all

=DBRW($D$2,W$2,$D$5,$D$8,$D$6,$D$7,$C25,$D$9,$D$10,$D$11,$D25,W$3,$D$12)

Thank you in advance,
Michael

[MarenC]

Hi,

I think we will need a bit more information than this.

You might start by matching up every cell reference in the DBRW formula to the actual dimension element name, and then show us that each element name does exist in the dimension. Just to make sure all the dimension names are correct. (You could substitute the actual element names for the cell references as a test while you are at it).

You might also want to open a cube view in architect with all the same elements as in your formula and confirm a value is returned.

Maren

[Wim Gielis]

You could substitute the actual element names for the cell references as a test while you are at it).

Correct. One quick way to do that is double click the cell reference in the Formula bar and hit F9.

[michaelc99]

You could substitute the actual element names for the cell references as a test while you are at it).

Correct. One quick way to do that is double click the cell reference in the Formula bar and hit F9.

I am confirming the positions of the formula align with the exact order of the cube and I am able to see dollars at the intersection point. So, I can confirm the formulas are working for me, but they not for the selected user.

Is there a way to show the sheet here but protect company confidentiality?

Thank you,
Michael

[Wim Gielis]

Is there a way to show the sheet here but protect company confidentiality?

Well, you could take a screengrab and make sure every confidential bit is blurred.

[michaelc99]

Is there a way to show the sheet here but protect company confidentiality?

Well, you could take a screengrab and make sure every confidential bit is blurred.

Added a screenshot and blurred out the Cost Center which contained sensitive information.

[Wim Gielis]

Cell D2 is supposed to contain the TM1 server name as well, for example:

CXMD:GL Main

Usually, that cell D2 actually houses a VIEW function, which gives you the server name and cube name, but in the background could also lead to improvements in the report performance.

[michaelc99]

Cell D2 is supposed to contain the TM1 server name as well, for example:

CXMD:GL Main

Usually, that cell D2 actually houses a VIEW function, which gives you the server name and cube name, but in the background could also lead to improvements in the report performance.

There was element level security on VERSION MAIN that I initially over-looked that caused the behavior for the user. Since I have Admin-level permissions, it worked for me but not for a non-admin user.

Thank you!
Michael