Example 1: filter with input parameter on numeric value
This example is referenced in my other post about input parameters. Please have a look at the other post to get a better understanding of the context for this example
We will use input parameter IP_1 to filter for records that have a productRating larger than 2.
As we will use the input parameter to filter on a numeric value the documentation tells us that the calling syntax should be (Note: you might sometimes find other syntax for passing input parameters. Please use this syntax with “=>”. This is especially relevant when passing input parameters inside procedures – not discussed in these posts – as otherwise the activation might fail):
i.e., without enclosing the value 2 in apostrophes. This makes intuitively sense as we treat IP_1 as integer and not as string.
In addition, the documentation tells us that the expression during processing should also not include IP_1 in apostrophes:
“productRating” > $$IP_1$$
Again, it makes sense as we are not treating the value 2 as a string (and might want to evaluate, e.g., 1+1):
Here are the steps to model this requirement:
a) Create a Calculation View
b) Add table IPExample as a datasource
c) Map all fields to the output
d) Create direct input parameter IP_1 of type INTEGER (see screenshot below)
e) In the Aggregation Node enter the following filter (a graphical expression editor is also available):
f) Save, build and do a data preview on the view
g) When prompted for a value enter 2
You will now see only records that have a productRating higher than 2:
If you look at the respective SQL you will see that the expected placeholder syntax is used in the data preview query:
SELECT TOP 1000
SUM(“productRating”) AS “productRating”,
SUM(“amount”) AS “amount”
Click here to navigate back to the context in which this example is discussed. You will also find further examples there.