Forum Discussion

Sergey's avatar
Sergey
Contributor III
3 years ago

XFCell Calculation

Dear community,

 

I am trying to calculate 2 values directly in my XF Document.

There is a part in the online documentation that is saying :

Include Member Scripts in XFCells in order to retrieve data for specific Dimension Members.  Culture User is based on the computer’s Windows settings and the User settings.
XFCell(Memberscript, Culture=User, NumberFormat=N3,DisplayNoDataAsZero=True, Scale=3, FlipSign=True, ShowPercentageSign=False)

 Unfortunalety it does not give much details as to what the "memberscript" should be ...

So far, I have 2 parameters build up with XFCell, that are giving me the right values. But Now that I simply need to calculate the ratio between those value, and get this ratio back in the word document, I'm a little bit stuck ...

 

Regards,

Excel Add In
  • ChristianW's avatar
    ChristianW
    3 years ago

    Hi Sergey

    Memberscript is just another word for POV.

    If you want to run a calculation, you have to use a business rule within the XFCell call.

    BR#[YourBrName, FunctionName=Sum, PoV1=Value1, PoV2=Value2]

    Cheers

    Christian

  • ChristianW's avatar
    ChristianW
    Valued Contributor
    3 years ago

    Hi Sergey

    Memberscript is just another word for POV.

    If you want to run a calculation, you have to use a business rule within the XFCell call.

    BR#[YourBrName, FunctionName=Sum, PoV1=Value1, PoV2=Value2]

    Cheers

    Christian

  • Sergey's avatar
    Sergey
    Contributor III
    3 years ago

    Thanks Christian,

    Indeed only the Business Rule was able to serve this need 🙂 But it really works like a charm in the XF Word document !

    Regards,

  • PFowler's avatar
    PFowler
    New Contributor II
    3 years ago

    Returning back to this topic can I get an example of using this expression to retrieve some type of data not really using calculations on an extendable doc? 

  • Vincent's avatar
    Vincent
    New Contributor III
    2 years ago

    Hello,

    I have just discovered the power in a quickview of the GetDataCell, as an example I Use : 

    GetDataCell((T#2024:S#BUDGET_V3 - T#2023:S#Planning_2023M10) / T#2023:S#Planning_2023M10*1000):Name(Variance)

    To obtain a variance.

    But in my Quickview I need to have both data with the Comma [0] (my default param) and with percent.

    I use the formula for example :

    GetDataCell(VariancePercent(T#2024:S#BUDGET_V3,T#2023:S#Planning_2023M10)):Name(Var%)

    Is it possible to have the % sign? I found this from the cubeview but don't know the correct syntax :

    ReportNoDataNumberFormat = [#,###,0.00;(#,###,0.00);0.00], ShowPercentSign = True

     

    Thank you.

  • Vincent's avatar
    Vincent
    New Contributor III
    2 years ago

    Also how to add blank lines or columns? I sax the |Space| variable but don't know if it works.