Skip to Content

SUM Exception Aggregation and delegated measures

Hi Colleagues,

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Oct 25, 2012 at 05:03 PM

    I think BICS connection for now only supports Standard Aggregation type and only Key Figures with SUM. SAP needs to improve this definitely.

    If you want to use the key figure you defined, instead of using BICS connection, you can try creating an OLAP universe through Universe Design Tool and set the aggregation to SUM. I think this is the only solution you got for now.

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Amon,

      sorry for the late reply.

      With Exception Aggregation things are a bit different, because the Exception Aggregation is a calculation made at the level of the OLAP Engine.

      Once it is passed to WebI the WebI calculation Engine will compute the values with the functions it knows (Sum, Average) and will do it locally with its own engine.

      Therefore you may have either #UNAVAILABLE or in the worst scenario, incorrect results.



  • avatar image
    Former Member
    Sep 27, 2013 at 09:33 AM

    Chris ,

    #UNAVAILABLE ,#TOREFRESH Error occurs due to Database Delegate nature of the Key Figure hence if you are using OLAP universes then change the Database Delegate to Sum or any other aggregate function in Properties and if in case you are using BICS Connection then you have to create variables Iin webi will contain Sum as aggregate function and also if the report contains certain objects as Input controls then that object has to be specified explicitly as a part of " IN " Function .

    For Eg : 

    If webi report contains KF as 'Sales Qty' and Input control as 'Plant' , 'Branch' then the new variable will be =Sum([Sales Qty] IN( [plant];[Branch])).

    Hope this will resolve your issue.


    Jeetan Jagtap

    Add comment
    10|10000 characters needed characters exceeded