Problem with multiple prompt in IDT for HANA view

[Former Member]

Hello All,

I am facing problem in passing multiple prompt values to HANA Calcuation View in IDT (information design tool) when create a Derived Table.

The SQL works perfectly in HANA studio, and also works when the prompt is a static string value, but when i changed to two prompts, the error messge shows "Substitution failed: No value supplied for Parameter "PDATE". (QTJ 00520")

IDT version 14.0.6

[Former Member]

This message was moderated.

[Former Member]

Did you find a solution Martin, others?

I'm getting the exact same error.

"Substitution failed: No value supplied for Parameter "2014 FYE Begin". (QTJ 00520)"

I have a query that produces two measures:

1. 2013 sales between a date range (7/1/2012) to Key Date (today-365).
2. 2014 sales between a date range (7/1/2013) to Key Date (today).

These key figures query a BW relational cube and pull sales amounts based on date ranges.

The query works when I only use one key figure (2013 or 2014).

I have this working in webi by creating two separate queries for 2013 and 2014, then merging the dimensions.  But now users want to see it in explorer and it will not work.

Thanks!

Matt

[Former Member]

This message was moderated.

[Former Member]

The same problem i faced and the solution is that you need to provide different name for "prompt text"

By default it is "enter parameter" for all of your parameter . so that needs to b different for all your parameter.

[juan_escalante]

Hi Martin,

                The LOV is Static?

Regards,

Juan

[Former Member]

Hi Juan

LOV is a list of distinct values from one column of a HANA table. The problem exists even if the PBD is not from any LOV. just by user input.

thanks

Martin

[juan_escalante]

Martin,

           Why don´t you try making it simpler first with a WHERE clause and check if that works. Im trying to replicate your problem but i have no problems here.

Which version of HANA and BO are you working?

Best Regards,

Juan

[Former Member]

Hi Juan,

HANA is rev56, and BO is 14 SP06, IDT version is 14.0.6 v20130408-136

I need the input parameter to pass down to the script based calc view to filter data at early processing stage for better performance. WHERE CLAUSE can't get what Iexpected.

I have also updated the BOE client jdbc driver to use the on in rev56 HANA client but still have the same problem. I highly suspect that its a bug in this version of IDT.

I now work this problem around by passing the PDATE and PBD in one string parameter and delimit it by "#", i.e. 20130823#company01 then use  string function LEFT() and STRING_AFTER('#') to get what I need.

Thanks

Martin

[patflinn]

I am having the same issue with IDT ( Version: 14.0.5.v20130430-1058) attempting to use two Parameter Prompts for a Derived Table on an Oracle database (11g). 

Both Parameter Prompts will successfully validate and work to return data if they are used in the Derived Table individually.  Also, the error message, "Substitution failed:  No value supplied for Parameter "xxx"" is returned for whichever Parameter Prompt is last in the Dervied Table SQL statement.

This statement...

select distinct a.kunag, a.vkorg, a.vtweg, b.werks, b.matnr, b.prsdt, c.kschl, d.vtext, c.kbetr

from sapwhse.sap_vbrk_tab a, sapwhse.sap_vbrp_tab b, sapwhse.sap_konv_tab c, sapwhse.sap_t685t_tab d

where a.vbeln = b.vbeln

  and a.knumv = c.knumv

  and b.posnr = c.kposn

  and c.kschl = d.kschl

  and a.fkdat = @Prompt('Please select a pricing date.','D')

  and c.kschl in @Prompt(Condition Type)

  and a.kunag in @Prompt(Sold-to Customers)

Produces this error...

This reordered statement...

select distinct a.kunag, a.vkorg, a.vtweg, b.werks, b.matnr, b.prsdt, c.kschl, d.vtext, c.kbetr

from sapwhse.sap_vbrk_tab a, sapwhse.sap_vbrp_tab b, sapwhse.sap_konv_tab c, sapwhse.sap_t685t_tab d

where a.vbeln = b.vbeln

  and a.knumv = c.knumv

  and b.posnr = c.kposn

  and c.kschl = d.kschl

  and a.fkdat = @Prompt('Please select a pricing date.','D')

  and a.kunag in @Prompt(Sold-to Customers)

  and c.kschl in @Prompt(Condition Type)

Produces this error...

If I remove either Parameter Prompt...

select distinct a.kunag, a.vkorg, a.vtweg, b.werks, b.matnr, b.prsdt, c.kschl, d.vtext, c.kbetr

from sapwhse.sap_vbrk_tab a, sapwhse.sap_vbrp_tab b, sapwhse.sap_konv_tab c, sapwhse.sap_t685t_tab d

where a.vbeln = b.vbeln

  and a.knumv = c.knumv

  and b.posnr = c.kposn

  and c.kschl = d.kschl

  and a.fkdat = @Prompt('Please select a pricing date.','D')

  and a.kunag in @Prompt(Sold-to Customers)

No error is produced...

Any help is greatly appreciated!

-Pat

[Former Member]

Hi Martin,

How do you define PDATE and PBD parameters in IDT? could you posted screenshot or script?

Regards,

Jerry

[Former Member]

Hi Jerry

the both parameters are simple string parameters uncheck Allow Multiple Value, unchecked keep last value.

[Former Member]

Hi Martin,

I think that you need to check "Keep last Values", could you check it and try again?

Besides, whether you can fetch related data? when you create "LOV_BB" parameters list.

Regards,

Jerry