Analysis Office 2.7 - Data Source for Defining Formulas Part 2

I received an email from Jean-Pierre, who tried the new function Data Source for Defining Formulas of Analysis Office 2.7 - but it didn't work as he mentioned in his email. The option Select Data Soure for Defining Formulas was gray out for BEx Queries.  It worked when he insert the Multiprovider or the Composite Provider. So I was very confused and tried to find a solution.

The Problem only exists when he insert a Bex Query and he received the error message:

"The selected data source cannot be used formula-optimized. The data source is inserted without crosstab to be used for analysis (ID-113017)

So I checked my test query on our system and it worked. It was very confusing.

I tried another query in our system and now I received the same error. 🤔 Now I thought, ok my query is just a dummy query where I test things, so maybe I switched a switch while I have done something else and this may the solution. So I looked into the two queries and compared them. I didn't found a single deviation. The only thing I noticed was, my query was on a Composite Provider with an ADSO and the other query on an old InfoCube.

Based on this solution Jean-Pierre look a little deeper into the function Use formula-optimized and found it only works on

• BEx Query based on ADSO
• Advanced DSO
• MultiProvider
• Composite Provider

So here we are, it doesn't work on Queries based on Composite Providers? Let me think. In my case it do so maybe the conclusions from Jean-Pierre are wrong?

Then I got an update from him. The User Guide of Analysis Office 2.7 SP2 says:

"As data source, you can use InfoCube and Queries. The queries must have a key figure strcture and they can contain restrictions, restricted key figures and calculated key figures.

The queries must not have two key figure structes, any conditions, exceptions or formulas. The report RSO_RES_QD_FMLMODE (via transaction SE38) can be used to check queries for these criteria."

His tests failed, because his queries had formulas and my test query does not. If you create a new query, the parameter FormulaMode will be set to X automaticly for old query you have to use the program RSO_RES_QD_FMLMODE to set it. Now it works if you don't have any conditions, exceptions or formulas in it.

So the summary is no new functions from SAP aren't intuitive and we have to read the manual. 😉

Thanks to Jean-Pierre for testing and the solution.

These posts might also be interesting:

• Analysis Office Restrictions
• Analysis Office Launching Workbooks from BIP with variables
• Analysis Office Filter by Member Attribute
• Analysis Office Hide Customize User Interface

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.