Dynamic Condition in Bex
Goal of the report was showing all the supplements or all the purchase orders from one year and seeing all the other supplements for the other years. This screamed for the use of a “condition”. But the difficulty was that the year should be variable as well as it should be possible on both keyfigures (supplements and purchase order). To create this report. I created a pre-query to fetch the list of PO’s. That list is filtered by a dynamic condition. The filtered list is finally used in the user report. What I mean with a dynamic condition, will I try to explain in this document.
- Prequery (Supplements = Avenants & purchase orders = Commandes)
On 0CALYEAR we put our “Year variable”, on the key figures we put our conditions. The condition is simple, a Not Equal To Operator and a variable as value. Create for each key figure this condition, with a different variable.
I called my variables: Avenants Condition and Commandes Condition (optional input)
- Creation of main query, using a replacement path variable on Purchase Document.
Now! Let’s take a look at the report.
We Fill in year and put a zero value for Commandes Condition. Meaning that our condition for avenants condition, will do nothing, and for the commandes condition, it will exclude the zero values.
The result shows us all the purchase orders made in 2013 and all the supplements created on these purchase orders (PO’s).
When we change our input variables and now trigger the avenants condition instead of the commandes conditiion
Our output are all the supplements created in 2012 with the supplements in the other years and the initial purchase orders
So, we play dynamically with our conditions!