Skip to Content

     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.

1.JPG

     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.

2.JPG

     In the aggregation tab we select counter for all detailed values that are not zero, null or error:

3.JPG

     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:



4.JPG


5.JPG


     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”:


6.JPG


     The same we create another selection (or RKF)


7.JPG


     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:


8.JPG


     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:


9.JPG


     Now we have the report shown as:


10.JPG

     When we create the condition:


11.JPG


     And we make sure that we don’t suppress zeros in the query properties:


12.JPG


     We hide the formula and selections:


13.JPG


And when we run the report we get:

14.JPG


We can use this procedure in all cases where we can count the number of detailed records.

            Hope it helps πŸ™‚

Regards

Yasemin Uluturk




To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

    1. Yasemin ULUTURK Post author

      That’s great news Manna πŸ™‚ ,

      You are the 3rd member I directly heard that used this procedure. It is really motivating.Thanks.

      Regards

      Yasemin…

      (0) 

Leave a Reply