0

I want to display all the data when the input parameter is left empty.

I am able to make it work with the following code in the filter expression in the calculation view. However, when I pass a value in the input parameter, it does not work.

if('$$CTUSER$$' = '',match("USER",'*'),in("USER", '$$CTUSER$$'))

Note that there are single quotes around $$IP_DE_CTUSER$$.

When I remove the single quotes around $$ IP_DE_CTUSER $$ as follows, the passed value is filtered correctly, but when the parameter is left blank, it does not work.

if($$CTUSER$$ = '',match("USER",'*'),in("USER", $$CTUSER$$))

What am I doing wrong? How I can make it work?

Below are my test data:

CREATE COLUMN TABLE TEST_IP_FILTER (
RESQTSN VARCHAR(30),COL1 VARCHAR(1),COL2 VARCHAR(1),COL3 VARCHAR(1),COL4 VARCHAR(1),COL5 VARCHAR(1),COL6 VARCHAR(1),COL7 VARCHAR(1),COL8 VARCHAR(10),USER VARCHAR(10));

INSERT INTO TEST_IP_FILTER VALUES ('00000000000000000000000', 'E', 'E', 'R', 'R', 'E', 'R', 'R', 'SD_000456', 'JACK');
INSERT INTO TEST_IP_FILTER VALUES ('00000000000000000000000', 'N', 'N', 'R', 'N', 'N', 'N', 'N', 'SFD_000486', 'HENRY');
INSERT INTO TEST_IP_FILTER VALUES ('00000000000000000000000', 'E', 'E', 'E', 'E', 'E', 'E', 'E', 'SFD_000489', 'PAUL');

Calculation View:

Input Parameter

Filter Expression

Calculation View

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Boy12343
  • 75
  • 3
  • 10

2 Answers2

2

The single-quotes around the input parameter names need to stay in order to make them work correctly.

I tested your scenario with HANA 2 SP04 and found that it worked in all three cases of parameter specification:

  • parameter provided with an actual filter value, e.g. ('PLACEHOLDER' = ('$$ProductID$$', 'DB-1081'))

  • parameter provided with empty-string (to indicate no selection), e.g. ('PLACEHOLDER' = ('$$ProductID$$', ''))

  • no parameter value provided

All of these worked as expected.

In order to make the filter condition a bit more resilient, I changed it to this (note that I filter on ProductID, but you get the idea):

if( '$$ProductID$$'= '' or isNULL('$$ProductID$$')
   , 1=1
   , in("PRODUCTID", '$$ProductID$$')
  )

Instead of the match() condition, I used the constant 1=1 expression to make it clearer, that this should always resolve to TRUE (and to reduce computation effort).

(note, that it is possible to include line-breaks in the expression editor, which I highly recommend in order to keep the expressions readable.
Another comment: the IP_ notation for input parameters or values is not mandatory. Since there is no way to confuse those with column names, I do not see any value in using such prefixes.).

I also included a check for NULL of the parameter, as empty-string and NULL are two different "empty" values.

Thanks for the reproduction information. "Unfortunately", the example works just fine for me.

select current_timestamp, * from m_database;
/*
CURRENT_TIMESTAMP           SYSTEM_ID   DATABASE_NAME   HOST    START_TIME                  VERSION                 USAGE      
29/11/2019 2:51:53.03 PM    HXE         HXE             hxehost 29/11/2019 2:44:08.561 PM   2.00.040.00.1553674765  DEVELOPMENT
*/

SELECT
    *
FROM "_SYS_BIC"."sp/CTUSER" 
      ('PLACEHOLDER' = ('$$CTUSER$$','JACK'));
/*
RESQTSN                 COL1    COL2    COL3    COL4    COL5    COL6    COL7    COL8        USER
00000000000000000000000 E       E       R       R       E       R       R       SD_000456   JACK

*/     

SELECT
  *
FROM "_SYS_BIC"."sp/CTUSER"
    ('PLACEHOLDER' = ('$$CTUSER$$', ''));

/*
RESQTSN                 COL1    COL2    COL3    COL4    COL5    COL6    COL7    COL8        USER 
00000000000000000000000 E       E       R       R       E       R       R       SD_000456   JACK 
00000000000000000000000 N       N       R       N       N       N       N       SFD_000486  HENRY
00000000000000000000000 E       E       E       E       E       E       E       SFD_000489  PAUL 

*/    

SELECT
   *
FROM "_SYS_BIC"."sp/CTUSER";
/*
RESQTSN                 COL1    COL2    COL3    COL4    COL5    COL6    COL7    COL8        USER 
00000000000000000000000 E       E       R       R       E       R       R       SD_000456   JACK 
00000000000000000000000 N       N       R       N       N       N       N       SFD_000486  HENRY
00000000000000000000000 E       E       E       E       E       E       E       SFD_000489  PAUL 
*/

Since you wrote that this example reproduces on your system, it's probably safe to say, that it is not caused by some SAP BW special case.

At this point, I'd recommend opening a support case with SAP for this.

Lars Br.
  • 9,949
  • 2
  • 15
  • 29
  • Br I have tried your code, I still get the same results as my code. That is, it works only when empty-string and no parameter value is provided. I cannot get all scenarios to work with the same code. – Boy12343 Nov 27 '19 at 05:27
  • What version of HANA do you use? – Lars Br. Nov 27 '19 at 05:42
  • 2.00.040.00.1553674765 (fa/hana2sp04) – Boy12343 Nov 27 '19 at 06:26
  • 1
    Looks like your scenario is more complex than my simple reproduction example. Please create a minimal example to reproduce the issue and add it to your question. Make sure to include table definition, test data and the calculation view. – Lars Br. Nov 27 '19 at 07:10
  • For information the calculation view is based on an InfoObject table generated by SAP BW on Hana in the form of a calculation view. I will try to reproduction an example based on a new created table. – Boy12343 Nov 27 '19 at 07:45
  • I've added my test data in my question. – Boy12343 Nov 27 '19 at 08:19
0

I happened to same issue and tried to give a default value to input parameter, and then seems work. I use below code and test cases.

if(in('*',$$IP$$)
  ,1=1,
  in("FIELD",$$IP$$)
)

test cases: one input:

  1. '' --> get all result, here is delete default '*' and leave it as blank
  2. '*' --> get all result
  3. actual value like 'A' --> get expected result based on value 'A'

multiple inputs:

  1. '*' and '' --> get all result
  2. '*' and 'A' --> get all result
  3. '' and 'A' --> get result filtered by 'A', not all result
syub97
  • 1