The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.

Forum Discussion

Sergey's avatar
Sergey
Icon for OneStream Employee rankOneStream Employee
4 years ago
Solved

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
    4 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

5 Replies

  • ChristianW's avatar
    ChristianW
    Icon for OneStream Employee rankOneStream Employee
    4 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
    Icon for OneStream Employee rankOneStream Employee
    4 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.