Need help: Pre-defined filters + SAP BW universe

[Former Member]

Hello Experts,

I am looking to implement a following logic. I am using a BEx query to create my universe.

Prompt1 will receive a user input as u2018Input Typeu2019 say Check number or Invoice Number. Based on the user input to this prompt, the user input to Prompt2 should be evaluated to corresponding info object ie. Check number or Invoice Number fields.

Having known that IIF expression doesnu2019t allow to return u2018Stringsu201D, we created following two pre-defined filters in the universe. On a report the two filters will be used with a u2018ORu2019 clause.

1)

 <OPTIONAL><FILTER EXPRESSION="IIF(@Prompt('Select Input Type 1 for Check or 2 for Invoice','A',,mono,free)=1,1,2)"><CONDITION OPERATORCONDITION="Equal"><CONSTANT CAPTION="1"/></CONDITION></FILTER><OPERATOR VALUE = "AND"/> 
<FILTER KEY="[ZFIAP_IS1___F20].[LEVEL01]"><CONDITION OPERATORCONDITION="Equal">
<CONSTANT CAPTION="@Prompt('Enter Check Number or Invoice Number','A',,mono,free)"/></CONDITION></FILTER></OPTIONAL> 

2)

 <OPTIONAL><FILTER EXPRESSION="IIF(@Prompt('Select Input Type 1 for Check or 2 for Invoice','A',,mono,free)=2,2,1)"><CONDITION OPERATORCONDITION="Equal"><CONSTANT CAPTION="2"/></CONDITION></FILTER><OPERATOR VALUE = "AND"/> 
<FILTER KEY="[ZFIAP_IS1___F46].[LEVEL01]"><CONDITION OPERATORCONDITION="Equal">
<CONSTANT CAPTION="@Prompt('Enter Check Number or Invoice Number','A',,mono,free)"/></CONDITION></FILTER></OPTIONAL> 

Is it a right approach ?

When I run the report by entering values to the above Prompts, the webi report engine fails and I get following error.

u201CThe Web Intelligence server cannot be reached.u201D

The underlying BEx query has other optional filters too. If I enter No parameters to Prompt1 & Prompt2 above & enter a value to BEx variable instead, the report runs successfully.

Can someone let me know what could be going wrong here?

[Former Member · Accepted solution]

Hi,

The approach is too complex, you need only one static prompt in the object definition and not in the filter definition.

You just have to write the prompt syntax in the object definition without <EXPRESSION> tags.

Here is the correct syntax:

@Prompt('Select Input Type?','A:N',{'Check':'[ZFIAP_IS1___F20].[LEVEL01]','Invoice':'[ZFIAP_IS1___F20].[LEVEL46]'},mono,primary_key)

Regards,

Didier

[Former Member · Accepted solution]

Thanks Didier for your inputs.

I tried what you suggested. However the object couldnu2019t parse in the universe. It gave an error u201CParse failed: Exception DBD, The supplied XML is not valid.u2019[ZFIAP_IS1__F20].[LEVEL01]u2019 State: N/Au201D

Any thoughts?

Regards,

Vaibhav

[Former Member · Accepted solution]

Hi,

The Designer parser tool is not free of bugs. It will be fixed in SP3.

So I recommend to test the new object directly in WebIntelligence.

Regards,

Didier

[Former Member · Accepted solution]

Thanks Again Didier. It worked like a charm.

It brings two more questions in my mind.--

1) Could you please shed some light on this prompt syntax? I donu2019t recall seeing it in documentation of standard prompt syntax.

2) If I enable List of values for my SAP fields, I get warning as u201CPartial Resultsu201D just above the search box. The bad part is if I search for a value which I know is there in the system but is out side the u201CPartial Resultsu201D, it doesnu2019t show up when I search for it.

I checked my universe parameters & ensured that I donu2019t have any limits set. Is there any way to get rid of this?

Regards

Vaibhav

[Former Member · Accepted solution]

Hi,

There is a parameter in the universe that restrict the number of rows and that also apply to LOVs.

This value is set to 5000 rows by default.

Select "File" menu, then "Parameters" menu option. Go to the "Controls" tab and change the value for "Limit size result to:".

Regards

Didier

[Former Member · Accepted solution]

Yes, I checked the Universe Parameter you mentioned, There is no limit set on the same. (The box is unchecked). I will probably open a ticket with BO Support for this.

Could you please explain the Prompt Syntax or refer me to any document if you have handy ?

Thanks a lot for all your time & help !!

[Former Member · Accepted solution]

Hi,

The Prompt syntax is described in [Universe Designer guide|http://help.sap.com/businessobject/product_guides/boexir31SP2/en/xi31_sp2_designer_en.pdf].

This enhanced syntax is available since XI 3.1 SP2 and there is now also a prompt editor.

Didier

[Former Member · Accepted solution]

Thanks Didier. This is it !!

Regards

Vaibhav