In Analysis for Office 2.2 a new feature was published. You can now use your own Excel formulas in a Crosstab. First you have to add a row or column in your Crosstab. The table design is a feature since Analysis for Office 2.0.
You can add your formula.
Then you can drag your formula down to the end of the Crosstab.
When you now add a new characteristic to the rows, you will see the Excel formula will be automatically adjusts.
These posts might also be intersting:
author.
I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.
SAP Analysis for Office - The Comprehensive Guide
The book SAP Analysis for Office - The Comprehensive Guide by Tobias Meyer is a pdf book about SAP Analysis for Office. It is based on Analysis for Office 2.8 and contains 346 Pages.
45,00 €
SAP Analysis for Office - The Comprehensive Guide
SAP Analysis for Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis for Office. It is based on Analysis for Office 2.7 and contains
299 Pages.
37,00 €
Write a comment
Iwan Santoso (Friday, 15 March 2019 21:52)
Hi Tobias,
Have you come across situation where you move your AO workbook to Test environment via transport and get the formula to be available in the target system? my situation is that in target system, the formula is left blank and it works fine in development environment.
Regards,
Iwan
Tobias (Saturday, 16 March 2019 10:52)
Hi Iwan,
is your workbook stored in BW or on a BIP? I have workbooks on a BIP and no problem with any formulas. The created columns with Table Design are also visible in your target system?
Best regards,
Tobias
DAmian (Wednesday, 27 March 2019 22:03)
Hello tobias:
we are trying to write a formula in SAP IP but for some reason the formula dissappears and the value only remains. Any suggestions woulr be welcome.
Thanks!
Tobias (Thursday, 28 March 2019 10:16)
Hi Damian,
can you send me your example via mail? On which version are you? It seems like a bug.
Best regards,
Tobias
Othmar (Thursday, 20 August 2020 19:09)
From experience:
1. Workbook must be connected with the Datasource with activated updating (should be obvious, but can be a source, why things don't work as expected.)
2. If there are too many values selected, the copying down the formulas does not work correctly; therefore select first less data, write your formulas and then reupdate the Workbook with all the data needed..
3. If Excel-Formula is not supported by AO, the Formula disappears - or at least just gives N/A back. (Easy formulas like Sum, Medium; Add / Minus; Multiplication / Division work fine. Even referencing to cells out of the AO-Workbook. Not possible: vlookup, if - then, etc.).
@Tobias: A list of supported formulas/functions would be appreciated if available. (Could not yet find something)
Tobias (Monday, 31 August 2020 09:55)
Hi Othmar,
in the ebook you find a more information about formulas. What exactly are you looking for? Normally you can use all Excel formulas. If you like we can exchange via mail.
Best regards,
Tobias
Rupad (Thursday, 06 May 2021 05:00)
Hi Tobias,
In my AFO report I have hidden some rows using VBA Macro depends on requirements.
after display, when I click anywhere in the report the hidden rows coming back again. looks like Excel macros/formulas not persist on the crosstab.
Can you please let me know how to control this issue? Is it possible to handle with CallBack events?
Regards,
Rupid
Tobias (Thursday, 06 May 2021 15:34)
Hi,
when your hidden rows inside the Crosstab, then it will be shown after refresh. You can use the Callback Events (I have another example in my book).
Best regards,
Tobias