Prophix Functions allow you to read specific member values, member names, and cell comments from your models into Excel.

Analyzer provides the following functions:

Additionally, you can use Analyzer's Writeback mode to use Prophix Functions to update values stored in the model.

 Note:  Each time that Analyzer is updated with new functions, to access them Microsoft requires that you perform a couple of simple steps.

PROPHIX.GETCOMMENT()

  • Returns the latest cell comment stored against the member combination.
  • Takes the model and member combination as arguments.

Example

  • =PROPHIX.GETCOMMENT("Revenue Planning Model","A400","C0000","All","All","T0000","2019M01","BASE","ACT")

  • Example syntax: PROPHIX.GETCOMMENT("Model name" , "Account member key", Customer member key, "Organization member key", "Product member key", "Territory member key", "Time member key", "Time Perspective member key", "Version member key")
  • For this member combination the function returns the cell comment: This value has been adjusted by Martin.N.

Syntax

PROPHIX.GETCOMMENT("Model name" , "Dimension1 member key", "Dimension2 member key", ..., "Dimension_n member key")

PROPHIX.GETCOMMENT() has the following arguments:

  • Model name: The name of the model that you want to use.
  • Dimension1 member key, Dimension2 member key, and so on; the member key for each dimension listed in the Models tab.

Remarks

  • If the Models tab does not show one or more models, select the Settings tab and confirm that the correct connection is selected (this option only appears if you have more than one enabled connection).
  • In the function, type the member key for each dimension in the model, in the order that appears in the Models tab. (In the Analyzer panel, select the Functions tab and then the Models tab and select the model.)
  • The arguments assume the default hierarchy for members.
  • GETCOMMENT() can also read from alternate hierarchies.

Error codes

  • #BUSY!: Excel is processing the function.
  • #NOMODEL!: The model is offline and cannot be accessed.
  • #VALUE!: The member combination cannot be found for the specified model. This error can be caused by the following:
    • The dimensions were not ordered as in the Models tab.
    • One or more member keys were missing.
    • One or more member keys was incorrect.
  • #NOACCESS!:This error can be caused by the following:
    • You are not signed in to Analyzer.
    • You do not have Read access to the member combination.

Back to top

PROPHIX.GETMEMBER()

  • Returns the member name.
  • Takes the model, dimension, and member key as arguments.

Example

  • =PROPHIX.GETMEMBER("Revenue Planning Model","Account","400")
  • Returns the value for this member: Product Revenue.

Syntax

PROPHIX.GETMEMBER("Model name", "Dimension name", "Dimension member key")

PROPHIX.GETMEMBER() has the following arguments:

  • Model name: The name of the model that you want to use.
  • Dimension name: The name of the dimension that contains the member.
  • Dimension member key: The key for the member.

Remarks

  • If the Models tab does not show one or more models, select the Settings tab and confirm that the correct connection is selected (this option only appears if you have more than one enabled connection).

Error codes

  • #BUSY!: Excel is processing the function.
  • #NOMODEL!: The model is offline and cannot be accessed.
  • #VALUE!: The member cannot be found for the specified model and dimension.
  • #NOACCESS!:This error can be caused by the following:
    • You are not signed in to Analyzer.
    • You do not have Read access to the member combination.

Back to top

PROPHIX.GETVALUE()

  • Returns the value stored against that member combination.
  • Takes a model and a member combination as arguments.

Example

  • =PROPHIX.GETVALUE("Revenue Planning Model","A400","C0000","All","All","T0000","2019M01","BASE","ACT")
  • In this example, the syntax is: PROPHIX.GETVALUE("Model name" , "Account member key", Customer member key, "Organization member key", "Product member key", "Territory member key", "Time member key", "Time Perspective member key", "Version member key")
  • For this member combination, the function returns the value: 475394.847684.

Syntax

PROPHIX.GETVALUE("Model name" , "Dimension1 member key", "Dimension2 member key", ..., "Dimension_n member key")

PROPHIX.GETVALUE() has the following arguments:

  • Model name: The name of the model that you want to use.
  • Dimension1 member key, Dimension2 member key, and so on (the member key for each dimension that appears in the Models tab).

Remarks

  • In the function, type the member key for each dimension in the model, in the order that appears in the Models tab. (In the Analyzer panel, select the Functions tab and then the Models tab and select the model.)
  • The arguments assume the default hierarchy for members.
  • GETVALUE() can also read from alternate hierarchies.

Error codes

  • #BUSY!: Excel is processing the function.
  • #NOMODEL!: The model is offline and cannot be accessed.
  • #VALUE!: The member combination cannot be found for the specified model . This error can be caused by the following:
    • The dimensions were not ordered as in the Models tab.
    • One or more member keys were missing.
    • One or more member keys was incorrect.
  • #NOACCESS!:This error can be caused by the following:
    • You are not signed in to Analyzer.
    • You do not have Read access to the member combination.

Back to top

PROPHIX.SETVALUE()

  • Updates a model value for a member combination with the output of the formula you provide.
  • Takes a formula, a model, and a member combination as arguments.

 Note:  For PROPHIX.SETVALUE() to write data to the model, you must turn on Writeback mode.

 Caution:  PROPHIX.SETVALUE() formulas should not reside in the same worksheet as an Analyzer data view. If they are on the same page, there is a risk that when the data view is refreshed, the PROPHIX.SETVALUE() function could write a blank value to the model .

Example

  • =PROPHIX.SETVALUE(C3*1.05,"Revenue Planning Model","A400","C0000","10","P014","T0000","2019M01","BASE","BUD01")

  • In this example, the syntax is: PROPHIX.SETVALUE(formula, "Model name" , "Account member key", Customer member key, "Organization member key", "Product member key", "Territory member key", "Time member key", "Time Perspective member key", "Version member key")
  • For this member combination, assuming the value in C3 is $1,000,000.00, the function returns the value: $1,050,000.00.

Syntax

PROPHIX.SETVALUE(formula, "Model name" , "Dimension1 member key", "Dimension2 member key", ..., "Dimension_n member key")

PROPHIX.GETVALUE() has the following arguments:

  • formula: The formula that provides a numeric value that updates the model value for the member combination.
  • Model name: The name of the model that you want to use.
  • Dimension1 member key, Dimension2 member key, and so on (the member key for each dimension that appears in the Models tab).

Error codes

  • #NOMODEL!: The model is offline and cannot be accessed.
  • #VALUE!: This error can be caused by the following:
    • The member cannot be found for the specified model and dimension.
    • The result of the first argument (formula) is a non-numeric value.
  • #NOACCESS!:This error can be caused by the following:
    • You are not signed in to Analyzer.
    • You do not have Write access to the member combination.

Back to top

Read from alternate hierarchies

To use GETVALUE() and GETCOMMENTS() to read from unique member combinations that use alternate hierarchies, insert the following syntax for that dimension:

  • [AlternateHierarchyName][Level1MemberPropertyValue][Level2MemberPropertyValue] ... [TargetLevelMemberPropertyValue]

 Note:   The GETVALUE() and GETCOMMENTS() functions can accept a key value from either a dimension’s default hierarchy or an alternate hierarchy, but not both.

 Example:  

An alternate hierarchy named Location for the Department dimension has the following structure:

  • North America
    • Canada
      • Ontario
      • Quebec
      • Alberta
    • USA
      • New York
      • Ohio
      • Pennsylvania
      • California
  • South America
    • Argentina
    • Brazil

To use an alternate hierarchy for the Department dimension to specify a unique member combination that includes Ontario, in the function you would include:

  • [Location][North America][Canada][Ontario]

To specify a unique member combination for Canada, you would include:

  • [Location][North America][Canada]

Writeback mode

The Writeback feature allows the functions PROPHIX.GetValue() and PROPHIX.SetValue() to update values stored in the model from within Analyzer.

The process is as follows:

  1. In Analyzer, add PROPHIX.GETVALUE() to one or more cells to read model values into your worksheet.
  2. Turn on Writeback:
    • In the Analyzer panel, select the Functions tab.
    • Click Settings.

    • Click Writeback mode.

  3. In any cell containing the PROPHIX.GETVALUE() function (from step 1) type the new value and press Enter.

    In all cells containing functions the #BUSY code appears, indicating the data is refreshing; when the refresh finishes, in the model the new values have replaced the old ones.

Writeback limits

You can only use Writeback with objects that are editable; therefore you can't use Writeback for the following:

  • Read-only members
  • Non-leaf members (aside from non-leaf input members)
  • Calculated Account members
  • Account members used for data entry
  • Account members that require a line item
  • Calculated Version members
  • Calculated Time Perspective members
  • A Version that is complete
  • A Version that is a folder
  • A Version outside the editable time range

Back to top