SUM Exception Aggregation and delegated measures
I have a WebI 4.0 SP4 report connecting via BICS to a BW 7.3 BEx query that suffers from the already widely discussed #UNAVAILABLE behavior. In my scenario, I'm showing undelivered net value for sales orders. Because the net price for a material can fluctuate (in particular over time), the only way to calculate this accurately is to use SUM Exception Aggregation with reference to Sales Document and Item. However, because I'm using Exception Aggregation, the WebI report is treating this measure as delegated and when I attempt to define any variables in the WebI that are based on dimensions and include them in my output results, I face the #UNAVAILABLE behavior.
My thought is that this measure can be safely used as non-delegated because the exception aggregation is done, the final results can be re-aggregated in any way that doesn't go below the document and item level. Is there any way to override the default for this? The other alternatives I've considered:
- Making the calculations in WebI -- IMO, more difficult. Also requires returning much more data.
- Providing a new Infocube with data aggregated at the Document and Item level and the measures already calculated -- ends up being a one-off solution only for this one report, which is not desireable.
- Including / hiding the dimensions that underly any variables I create in my output results -- gives me multiple lines in my output when I instead want aggregated results
I haven't seen this particular situation fully answered anywhere, but if there's already a thread that I missed that has a complete answer, please point it out to me.
Simone Caneparo replied
Zahid is right, at the moment in BICS we have a forced delegation whenever the aggregation is different from SUM.
Please note that even if in your scenario the level of complexity is low this doesn't mean that can be applicable to all situations and this prevents any different behavior right now.
If a hierarchy is involved you may try to use this workaround:
- Create a variable which containing the Hierarchy with a qualification Measure
In your table use this variable and the also directly the same Hierarchy use by the variable
- Make the hierarchy as not visible - In this way the axis of the table will be drive by the Hierarchy and the variable will be not part of the axis and display value
- Use your current expression in your hierarchy and in the body of the table use something like :
[MySmartMeasure] In ([Yourhierarchy])
In this way if your current axis cardinality match the cardinality of the dimension [Yourhierarchy] which drive the smart measure will be evaluate in the context [Yourhierarchy] and display the value.