Webi 4.1 on relational universe on HANA - old prompt values not cleared

[Former Member]

Hi,

We are having a problem with a Webi report built on a relational universe (.unx). IDT is vers 4.1 sp 2, Webi is 4.1 sp3.

The HANA View has optional date prompts. It works with a year/month range, or defaults if the prompts are empty

When the business layer was created in IDT the HANA View was run and certain prompts were entered

Now when previewing the data in IDT or opening the report in Webi, the prompts are always initially set to the original values when it was run in IDT (which are not the current defaults in HANA).

We have tried from the Webi perspective Data Access -> Purge -> Purge All (with the clear last selected prompts option selected), but the prompts come back. We have also tried rerunning in IDT with blank parameters, but the keep coming back there (that is the original values)

Any ideas on how to remove the prompts? We don't want the users to have to keep adding in prompts when they run the report (as there are a few HANA views included, each with their own prompts). It also prevents scheduling.

Thanks,
Ken

[Former Member]

Hi

Reading that document about variables on HANA, my suggestion would be to acquire control on those variables and parameters at the universe level, by building a derived table in the universe using this syntax

SELECT [Columns]

FROM <view> ('PLACEHOLDER' = ('$$<parameter_name>$$', <parameter value>))

WHERE <attribute name> = <filter value>

Input parameters defined in HANA are the ones in the FROM part of the statement and the variables comes in the WHERE part.

By using directly the table in IDT you don't see these prompts and can't modify them either.

So, if you build a derived table in the universe (foundation layer) with the syntax above and use that derived table for the universe you will get control over the parameters. Replace <parameter value> with a @prompt(prompt_name) statement where prompt_name is a prompt created in the universe with properties you can modify like 'keep last values' (this is the parameter that would do the trick of clearing those values out of the prompt). Make sure that option is not checked and it should work.

Also, by getting control on the prompts you should also be able to match the names of the prompts so they can be merged in webi when you use more than one universe, otherwise you'll get several prompts that mean the same but with different names.

Hope this helps.

Regards,

Christian

[Former Member]

Christian,

What if my HANA view have the Optional Input Parameter?

If i use the derived table i cannot use the parameter created in the universe because there is no optional option available.

I useed the traditional @prompt syntax for optional, the derived table was validated successfully but the universe query builder throwing "Failed to execute runnable error" error when i run wide open optional prompt.

Couple of SAP note mentioned Optional prompt work only as a Business Filter. Is that mean the HANA view should not have Optional Input parameter and the optional parameter should only declare in the universe?

[amitrathi239]

Hi,

In the IDT under the filters-> edit filter and check if default value is set there or Keep last values option is checked for the filters.

Amit

[Former Member]

Hi Amit,

Can you confirm where the filter would be (e.g. which layer). I can confirm there is no filter in the query layer of the universe created.

FYI - I picked up the following script by building a simple query in the universe. In HANA there are Input Parameters and Variables. The input parameters are seen as PLACEHOLDER in the script. I'm not sure if input parameters are causing the problem.

SELECT

  Table__1."MaterialGroup",

  SUM(Table__1."ServicePOItemCount"),

  SUM(Table__1."PercentServiceLineCount")

FROM

  "_SYS_BIC"."npm.rep.mm.pur/CLV_ECC_MM_PUR_Q001" 

('PLACEHOLDER' = ('$$P_YearMonthFrom$$', @Prompt(Hana Variable npm.rep.mm.pur/CLV_ECC_MM_PUR_Q001/P_YearMonthFrom)),

'PLACEHOLDER' = ('$$P_YearMonthTo$$', @Prompt(Hana Variable npm.rep.mm.pur/CLV_ECC_MM_PUR_Q001/P_YearMonthTo)))

Table__1

WHERE

( Table__1."SAPClient" = @Prompt(Hana Variable npm.rep.mm.pur/CLV_ECC_MM_PUR_Q001/V_SAPClient) )

  AND  ( Table__1."Vendor" @Prompt(Hana Variable npm.rep.mm.pur/CLV_ECC_MM_PUR_Q001/V_Vendor) )

  AND  ( Table__1."Material" @Prompt(Hana Variable npm.rep.mm.pur/CLV_ECC_MM_PUR_Q001/V_Material) )

  AND  ( Table__1."MaterialGroup" @Prompt(Hana Variable npm.rep.mm.pur/CLV_ECC_MM_PUR_Q001/V_MaterialGroup) )

GROUP BY

  Table__1."MaterialGroup"

Thanks,
Ken

[amitrathi239]

Hi,

I am not very sure about what is the cause.

But Placeholder is "In SQL, parameters values are passed via the PLACEHOLDER reserved word."

Check attached link.

Amit

[Former Member]

Hi Amit,

Christian seems to have a solution, which we will check out, but FYI BI 4.1 works differently with HANA in that it will accept the prompts from HANA including Input Parameters (placeholders). It seems that it has some side effects, so the suggestion from Christian is to try the old BO 4.0 method.

Regards,
Ken