Using constant selection, you can resolve problems with MultiProviders.
Example 1: The MultiProvider has an InfoCube with the actual values and an InfoCube with the plan values. If the calendar month characteristic is contained in the plan InfoCube but not in the actual InfoCube, then if you drilldown by calendar month, all the data under the initial value # (not assigned) is displayed. You can define a constant selection for calendar month= # (not assigned). This means you select the year plan value as a constant - regardless of the filter. The value is displayed in every row (in other words, for every month). You can now divide the year plan value by 12. This means you can perform a plan-actual comparison for individual calendar months, even though the plan InfoCube only contains year values.
Example 2: The MultiProvider has two InfoCubes with data on price and quantity for various products and the corresponding sites. InfoCube 2 also contains the characteristic Customer. Therefore, if you drilldown by customer, all the data from InfoCube 1 under the initial value # (not assigned) is displayed. This example is explained in more detail in the next section.
Plant |
Product |
Price |
---|---|---|
101 |
Candy Tin |
0,12 |
101 |
Coffee Mug |
0,14 |
102 |
Candy Tin |
0,11 |
103 |
Mouse Pad |
0,23 |
104 |
Post-It Set |
0,15 |
Plant |
Product |
Customer |
Quantity |
---|---|---|---|
101 |
Candy Tin |
Dawson Agency Inc |
100 |
101 |
Candy Tin |
Acadia Transfer Inc |
110 |
101 |
Coffee Mug |
Acadia Transfer Inc |
110 |
101 |
Coffee Mug |
Evans Hotel Inc |
110 |
102 |
Candy Tin |
Evans Hotel Inc |
105 |
103 |
Mouse Pad |
Thompson Inc |
115 |
104 |
Post-It Set |
Thompson Inc |
110 |
104 |
Post-It Set |
Bear Express Co Inc |
110 |
The characteristic Customer is contained in InfoCube 2 but not in InfoCube 1. Therefore, when you drilldown by Customer, all the data from InfoCube 1 under the initial value # (not assigned) is displayed. The query on the MultiProvider would look like this:
Plant |
Product |
Customer |
Price |
Quantity |
---|---|---|---|---|
101 |
Candy Tin |
# (not assigned) |
0,12 |
0 |
101 |
Candy Tin |
Dawson Agency Inc |
0 |
100 |
101 |
Candy Tin |
Acadia Transfer Inc |
0 |
110 |
101 |
Coffee Mug |
# (not assigned) |
0,14 |
0 |
101 |
Coffee Mug |
Acadia Transfer Inc |
0 |
110 |
101 |
Coffee Mug |
Evans Hotel Inc |
0 |
110 |
102 |
Candy Tin |
# (not assigned) |
0,11 |
0 |
102 |
Candy Tin |
Evans Hotel Inc |
0 |
105 |
103 |
Mouse Pad |
# (not assigned) |
0,23 |
0 |
103 |
Mouse Pad |
Thompson Inc |
0 |
115 |
104 |
Post-It Set |
# (not assigned) |
0,15 |
0 |
104 |
Post-It Set |
Thompson Inc |
0,15 |
110 |
104 |
Post-It Set |
Bear Express Co Inc |
0 |
110 |
In Query Designer, define a selection that contains the Price key figure and the Customer characteristic.
We recommend that you set the 0INFOPROV characteristic on the Price key figure to Constant Selection. This means the rows with initial value # (not assigned) are filtered out. However, the data for the Price key figure is retained.
For more information, see Constant Selection in the section Constant Selection on Characteristic 0INFOPROV.
Alternatively, set the Customer characteristic on the Price key figure to Constant Selection. However, this process can have a disadvantage, in the case of multiple characteristics that are not contained in the same InfoProvider. Unlike with the recommended procedure described above (set 0INFOPROV to Constant Selection), here you need to set the Constant Selection property for every one of these characteristics.
You can also solve this problem for MultiProviders by using InfoSets (instead of using constant selection). However, InfoSets are predefined in the data model and are very static. In contrast, constant selection is configured in Query Designer and is very flexible: you can configure the data record links to any InfoObject, which is part of a selection, for a query based on a MultiProvider.
In the drilldown, the rows with initial value # (not assigned) on the Customer characteristic are hidden. However, the data for the Price key figure is retained. The query looks like this:
Plant |
Product |
Customer |
Price with constant selection using customer |
Quantity |
---|---|---|---|---|
101 |
Candy Tin |
Dawson Agency Inc |
0,12 |
100 |
101 |
Candy Tin |
Acadia Transfer Inc |
0,12 |
110 |
101 |
Coffee Mug |
Acadia Transfer Inc |
0,14 |
110 |
101 |
Coffee Mug |
Evans Hotel Inc |
0,14 |
110 |
102 |
Candy Tin |
Evans Hotel Inc |
0,11 |
105 |
103 |
Mouse Pad |
Thompson Inc |
0,23 |
115 |
104 |
Post-It Set |
Thompson Inc |
0,15 |
110 |
104 |
Post-It Set |
Bear Express Co Inc |
0,15 |
110 |