In some discussions, I realized that there is a need to define condition according to the value of a characteristic. It is very easy to deal with key figure values to create a condition, but when it comes to the characteristics, we need some work around. In this blog I will discuss a few alternatives and I will try to explain the procedure for one of them with a small scenario.
Scenario:
Suppose we have a scenario where we have material type, materials and their related prices. We want to apply a condition on material type. When material type is “A” we don’t want to show the list of materials with 0 price. But when the material type is something else, we need to show all materials in the BEx query result.
Alternative Ways:
1. Change in design of the model
We can create a new characteristic which will point out the records to be showed in the report. The data type may be char1 and in the transformation rule, we can assign 1 to the records that will be showed and 0 for the records which we don’t want to be in the query result. Then in the query designer we can simply apply a filter on this characteristic in the characteristic restrictions tab.
This is a solution where we are flexible with changes in the modelling side. In most of the situations, change to the model may not be feasible due to huge amount of data which cannot be reloaded. There are also cases where the customers don’t prefer to change the model even without any reason! Then BI experts are restricted to find solution on BEx side.
2. Replacement Path Variables in BEx Query Design
We can use this approach only if we have the characteristic data type defined as numeric. With this approach, we can create a formula variable with replacement path where we get the key value of the characteristic. When we have this variable, we can use this variable in a formula to write an if-else statement.
This solution is also restricted with the data type of characteristic. If it is a char defined characteristic, this solution also becomes useless.
3. Creating calculated key figures and using conditions in BEx Query Design
With this approach we define a calculated key figure (CFK) to count the records we want to show in the query result. According to the value of this CKF, we define a condition to show the results.
When other approaches I mentioned above are inefficient, this solution is what we are left with. Now, I will go into details of this approach.
Suppose we have data:
Material | Material Type | Unit | Price |
ABC129 | A | USD | 912 |
ABC128 | A | USD | 178 |
ABC127 | A | USD | 0 |
ABC126 | A | USD | 167 |
ABC125 | A | USD | 154 |
ABC124 | A | USD | 0 |
ABC123 | A | USD | 0 |
ABC135 | B | USD | 0 |
ABC134 | B | USD | 25 |
ABC133 | B | USD | 0 |
ABC132 | B | USD | 266 |
ABC131 | B | USD | 187 |
ABC130 | B | USD | 644 |
We want to show the all records of material type B, but we also want to hide 0 prices for material type A. The final report should look like this way:
Material | Material Type | Unit | Price |
ABC129 | A | USD | 912 |
ABC128 | A | USD | 178 |
ABC126 | A | USD | 167 |
ABC125 | A | USD | 154 |
ABC135 | B | USD | 0 |
ABC134 | B | USD | 25 |
ABC133 | B | USD | 0 |
ABC132 | B | USD | 266 |
ABC131 | B | USD | 187 |
ABC130 | B | USD | 644 |
We create query on the infoprovider adding material to rows and price to columns.
Then we create two calculated key figures to count the number of materials we want to include for each material type. We want to show the materials where price is greater than 0 for material type A and all of the materials type B. The first CKF will count the number of all materials where the price has a value.
In the aggregation tab we select counter for all detailed values that are not zero, null or error:
We create another CKF to calculate the number of all materials whatever the price is. The same way we add price to the general tab and select counter for all detailed values in the aggregation tab:
At the next step we use these CKFs. We can either create a selection or a Restricted Key Figure (RKF) for this purpose. We add the CKF where all materials are counted and restrict the material type to type “A”:
The same we create another selection (or RKF)
Now when we run this query we will see that in each of the row we want to show, either type A >0 column is 1 or type B all prices is 1:
At this point we are successfull to eliminate 0 values in type A. The remaining procedure will include adding a formula to sum up the two columns type –A>0 and Type-B all prices. And then we can add a condition on this new formula:
Now we have the report shown as:
When we create the condition:
And we make sure that we don’t suppress zeros in the query properties:
We hide the formula and selections:
And when we run the report we get:
We can use this procedure in all cases where we can count the number of detailed records.
Hope it helps :smile:
Regards
Yasemin Uluturk
- SAP Managed Tags:
10 Comments
