As discussed in SAP note 2002395, there might be requirements to create an InfoSet in order to be able to use 'unposted values' in Exception Aggregation. The following simple example with screenshots could be helpful to better understand this point.
Query on InfoCube
- Characteristic Product in the drilldown, Access Type 'Master Data' active
- 1st column: basis Key Figure 'Net Sales'
- 2nd column: Key Figure 'Net Sales' with Exception Aggregation AVG regarding characteristic 'Product'
Please note that
- The products FC003,FC015 and FC016 aren'T booked in the cube. The 3 corresponding rows appear in the result set since Access Type 'Master Data' is active
- The calculation of the average does not take into account these 'unposted' materials. So, AVG = 320/3=106,66 and not 320/6=53,33.
Query on InfoSet
AS discussed in SAP note 2002395, there are basically two possible workarounds. Either you load the corresponding 'zero records' into the cube or you create an InfoSet (or CompositeProvider, MultiCube is aslo possible but has some disadvantages). In our example the InfoSet is defined as follows
The LEFT OUTER JOIN guarantees that also products are displayed in the query which are not booked in the cube. A 'copy' of query from above defined on the Infoset delivers the following result:
The calculation of the Average is now as expected(desired): 320/6=53,33.
Please note that the usage of an InfoSet has the downside that you need to be careful when setting filters: all characteristics which which are only part of the cube are displayed as 'Not assigned'(initial value') in the query for unposted products. E.g.
Hence when you want to restrict this query to some customers you need to include 'Not assigned' if the unposted products should be displayed as well: