Example 7: allow to display all values without filtering when a certain character (*) is entered

[jan_zwickel]

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

 

Sometimes you would like to allow users to enter a value on which they want to filter a column but at the same time also allow to show all values if the user enters a certain character. How you implement this behavior depends very much on your filter definition.

In the example below it is assumed that

a) the filter is defined as in Example 2:

"product"= '$$IP_1$$'

b) the character * is used to indicate that all values should be displayed (no filter should be applied).

 

to reproduce the example:

- take Example 2 and add or '$$IP_1$$' = '*' to the filter:



This means that a record is displayed if either product matches the input parameter value or the input parameter value equals *

 

If you fill * into the input parameter:

 

SELECT 

"date",

"product",
SUM("productRating") AS "productRating",

SUM("amount") AS "amount"

FROM "inputParameters.db::example7"

    (placeholder."$$IP_1$$"=>'*')

GROUP BY "date", "product";

 

all values will be displayed:



 

In essence the user can enter a value for which column product should be filtered, or request that all values should be displayed by entering *

 

Click here to navigate back to the context in which this example is discussed. You will also find further examples there.

[DirkO]

Dear Mr. Zwickel,

many thanks for providing such detailed insights for input parameters when using graphical calculation views.

As per my understanding the columns we are using to set up input parameters are defined upon CHAR based fields. May I kindly ask you for advice how to deal with when a column is set up as a numerical type?

To demonstarte what I´m looking for I set up a calculation view based on data from the Titanic data model, in a table named ''Complete''.

CREATE COLUMN TABLE "<schema>"."Complete" ("pclass" INTEGER CS_INT,

"survived" INTEGER CS_INT,

"name" VARCHAR(5000),

"gender" VARCHAR(5000),

"age" DOUBLE CS_DOUBLE,

"sibsp" INTEGER CS_INT,

"parch" INTEGER CS_INT,

"ticket" VARCHAR(5000),

"fare" DOUBLE CS_DOUBLE,

"cabin" VARCHAR(5000),

"embarked" VARCHAR(5000)) UNLOAD PRIORITY 5 AUTO MERGE;

And four entries:

INSERT INTO "<schema>"."Complete" ("pclass", "survived", "name", "gender",

"age", "sibsp", "parch", "ticket", "fare", "cabin", "embarked")

VALUES(1,1,'Allen, Miss. Elisabeth Walton','female',29.0,0,0,'24160',211.3375,'B5','S');

INSERT INTO "<schema>"."Complete" ("pclass", "survived", "name", "gender",

"age", "sibsp", "parch", "ticket", "fare", "cabin", "embarked")

VALUES(1,1,'Allison, Master. Hudson Trevor','male',0.92,1,2,'113781',151.55,'C22 C26','S');

INSERT INTO "<schema>"."Complete" ("pclass", "survived", "name", "gender",

"age", "sibsp", "parch", "ticket", "fare", "cabin", "embarked")

VALUES(1,0,'Allison, Miss. Helen Loraine','female',2.0,1,2,'113781',151.55,'C22 C26','S');

INSERT INTO "<schema>"."Complete" ("pclass", "survived", "name", "gender",

"age", "sibsp", "parch", "ticket", "fare", "cabin", "embarked")

VALUES(1,0,'Allison, Miss. Hedda Lori','female',null,1,2,'113781',151.55,'C22 C26','S');

(Needless to say filter on "age" is missing due to my bad in the trailing picture.)

I´m using two CHAR based columns ''gender'' and ''name'' and the column ''age'' which is defined as DOUBLE. And I to set up input parameter for this columens in question.

The input parameters are defined as type 'column' using the underlying column of the calculation view, e.g. ''p_age'' from column ''age'' of ''CV_TITANIC_COMPLETE'' .

At least what I understood the key is the default value maintained for each input parameter.

I want to achieve an output by with an input parameter for ''age'':

1. Entering a given value, e.g. 29.

2. Entering no value at all to receive all entries (with or without NULL).

So what is the default value of a column based on a numerical data type or what approach has to be followed to succeed?

The filter expression has been defined like this:

(in("gender", $$p_gender$$) or match("gender", $$p_gender$$))

AND

(in("name", $$p_name$$) or match("name",$$p_name$$) )

AND

(in("age",$$p_age$$) or match("age",$$p_age$$))

This is the input and result for #1, entering a given value, e.g. 29 (which is fine).

This is input and the result for #2: Entering no value at all to receive all entries (with or without NULL)

Unfortunately neighter * nor % are not a valid default entries for a numerical value.

So, how to overcome this? Would you be so kind to have a look to this and give me a brief advice, pls?

Many thanks for your response in advance.

Br.,

DirkO

[jan_zwickel]

Hi DirkO,

thanks for the concrete example! In your example, you are using numeric values inside your age column. However, your "in" operator requires a value of type string. Therefore, you need to cast your value to type string first:

(in(string("age"),$$p_age$$) or match("age",$$p_age$$)

As the statement is currently defined null values would not match. If null values should be included you can explicitly check for them:

(in(string("age"),$$p_age$$) or match("age",$$p_age$$) or (isnull("age") and match('*',$$p_age$$) )

Generally speaking it would be good to change to SQL expressions as these are the expressions that are used in HANA Cloud, so that switching to HANA Cloud becomes easier.

You can find examples for the SQL expression syntax in the context of input parameters e.g., here:
https://help.sap.com/docs/hana-cloud-database/sap-hana-cloud-sap-hana-database-modeling-guide-for-sa...

Best,

Jan

[DirkO]

Hello Mr. Zwickel,

many thanks for your immediate response and the provided advise.

Ok. I see.

With the definition you provided

(in("gender", $$p_gender$$) or match("gender", $$p_gender$$))

AND (in("name", $$p_name$$) or match("name",$$p_name$$))

AND ( in(string("age"),$$p_age$$) or match("age",$$p_age$$) or (isnull("age")

AND match('*',$$p_age$$) ))

and after changing the type of input paramter "p_age" to "direct" with data type "NVARCHAR".

Any desired selection was working. 🎉

You helped me a lot!

Thank you very very much.

Br.,

DirkO

P.S. It is also running in SQL 🙂