As explained in note 1903468(and Query as InfoProvider), not all formulas are 'supported' in the context of Query as InfoProvider. It is recommended to only use formulas which are commutative (which can be calculated before or after aggregation with the same result) since then the result(e.g. the data in the target of the DTP) is independent of the level of aggregation and further aggregation is possible(yields consistent results). Therefore, if you run the so called Adhoc Query of the QueryProvider, such formulas are not offered/available (since, in general, the results of these formulas could change during navigation). However, you can use such formulas in a DTP/Transformation and write the result into a target provider. In such cases you need to assure that the values in the target provider are interpreted correctly, e.g. when aggregations takes place. Check out the following example in order to get a better idea of this topic.
We use the same test query as in Example I. It contains three basic key figures and one formula with an boolean operator.
When run the query in RSRT we get the following result:
E.g. the value of formula 1 is zero since the value of key figure Transaction Counter is zero(and hence does not fulfill the condition Transaction Counter > 10).
When e.g. only the Customer is in the drilldown, the result is always 1 for the formula since the key figure Transaction Counter is always greater than 10(by default,formulas are always calculated after aggregation, see Formulas and Calculated Key Figures):
QPROV Adhoc Query
We now execute the Adhoc Query of the QueryProvider and take a look at the key figures. The formula is not offered(as explained above):
Transformation and DTP
In the DTP the formula can be used and mapped to a key figure of the target provider
The basic key figure has aggregation=SUM.
We once simulate the DTP and take a look at the data:
When we compare this data set with the query result from above, we can see that there is no difference. So, the extraction works as expected.
However, when we check a query which is based on the target provider, the query result will deviate from the original query result during navigation:
Ahhoc Query of Target Provider
This is exactly the same result as from the query above. However, since now the key figure Transaction Counter isn't a formula any longer, values are aggregated according to the key figure definition when characteristics are removed from the drilldown:
So, the value of customer CT1 is now 4 in contrast to the original query where is it 1: