Optional Input Parameter with 'PLACEHOLDER' syntax

[Former Member]

Hi

I've created a universe in IDT connecting to a HANA view.  The HANA view contains Input Parameters, as well as Variables.  In my universe I've created a derived table and I use the 'PLACEHOLDER' syntax in the From part of the SQL to cater for the HANA Input Parameters.  I've catered for the
HANA Variables by creating prompts (IDT parameters) in the universe, as well as creating Prompt filters in the Business layer using these parameter prompts.  In doing this, we could choose if we wanted the prompts to be optional or mandatory.

My question is this:  Is there a way to make the Input Parameter which is part of the 'PLACEHOLDER' syntax optional?

Thanks

Alta

[harsha_ganapathy]

Hi,

On creating a derived table, you'll have PLACEHOLDER in from clause.

You will add it as 'PLACEHOLDER' = ($$COUNTRY.COUNTRY_ID= @Prompt('Select Country','A','Class\Object',multi,constrained,not_persistent,,optional)

Use the extended prompt syntax and make it optional.

Not sure if it works, but just give a try.

[Former Member]

Hi Harsha,

I have made a parameter with a SQl edited List of values.

The Placeholder is then given that parameter.

The above syntax will not work in that case.

[Former Member]

I am in IDT 4.1

Version 4.1 SP3 patch 6.

In SQL editor I am selecting 2 columns from a different table and then making it as a LOV.

And then making a parameter on top  of this LOV .

[Former Member]

Hi,

I developed a solution 2 years ago to mimic optional prompts used in placeholder in a derived table.

Here is an example that you can can reuse.

First of all you have to create a list of values that will support the prompt data.

The SQL in the List of values is:

SELECT ' ' AS COUNTRY,  ' ' AS PLACEHOLDER FROM SYS.DUMMY

UNION

SELECT COUNTRY, '(''PLACEHOLDER'' = (''$$COUNTRY$$'', ''' ||  COUNTRY || ''') )' as PLACEHOLDER

FROM "_SYS_BIC"."foodmart.variables/INP_ATTRIBUTE_MANDATORY_DEFAULT"

You can notice that I set a default value equal to null.

Now the Derived Table SQL is:

SELECT "UNIT_SALES", "STORE_SALES", "COUNTRY", "STATE", "CITY"

from "_SYS_BIC"."foodmart.variables/INP_ATTRIBUTE_MANDATORY_DEFAULT" @Prompt(Placeholder)

So the user is always priompted and (s)he select the default value (null) then no placeholder statement is generated otherwise a value is selected by the user and the placeholder statement is generated.

There is no way to have true optional prompts in a derived table.

Didier

[Former Member]

Hi Didier,

If u see in the image there is no option to give the Default value as constant.

I have given the parameter a List of values(Through SQL)

[Former Member]

Hi,

Which version of IDT are you using?

If you don't have the ability to use a constant then you can change the LOV SQL by replacing the null value by a space.

And then select the space as the default value in the prompt.

Didier

[Former Member]

Hi,

Please have a look at the following:

http://scn.sap.com/community/semantic-layer/blog/2013/05/24/optional-prompt-in-bo40

on how to define optional prompts in the IDT.

Kind regards,

Leandi

[Former Member]

Hi Leandi

Thanks for your reply.  Unfortunately this does not solve my problem.  I have already incorporated these into my universe.  Creating optional prompts like this only caters for the Variables created in the HANA view.  But not for the HANA view Input Parameters.

Regards

Alta

[Former Member]

Alta,

We are having same issue as you. We have optional input parameter created in the HANA view and we tried to pass the input parameter in the derived table using @prompt and we getting all sort of error.

We have no issue in using the Mandatory input parameter.

Do you happen to find the solution for this issue?

Thanks

VJ

[Former Member]

Did anyone found the Workaround for this?

[TammyPowlas]

Hi - I moved this to the Semantic Layer space for faster response