Webi Query Filter Value From List not available using Free-hand SQL query

[DerekJ]

Hi There

I am trying to get a dropdown list of values on a filter prompt on a Webi report. A direct connection to SAP HANA as a datasource brings back this functionality, however when I use Free-hand SQL query for SAP Datasphere (as SAP HANA does not show HANA Cloud connection) the prompt only allows you to type in values, there is no drop down, so the user would have to know the exact value.

HANA WEBi report:

If a prompt variable is setup to allow it you can make a selection from a list (need to reload values first):

Datasphere free-hand SQL report:

The ‘Reload list of values’ is greyed out:

You don’t get the option to search for an entry only manual entry where you have to know the exact key.

[amitrathi239]

There is no option to display LOV's in Freehand sql as you cannot reference any universe object/direct query  to HANA, or a LOV of a universe object.

other option you can try to add one more query in the webi report and define the same prompt definition from HANA query  to display.

[DerekJ]

Thanks, sorry I'm not sure what you mean by add another query, wouldn't the second free-hand query have the same issue?

[amitrathi239]

I mean add one query from Hana Webi provider and select Material no in select and Filter part of the query. Add another free hand sql provider and add the Material no as a prompt.If prompt text is same in both queries then you will get single prompt pop up and can select the LOV's

[DerekJ]

Ah OK, you mean still use traditional SAP HANA to bring list of Materials through the connect that to the free-hand SQL from Datasphere? If so, that's not our direction of travel, we are trying to remove all need to go directly to ECC HANA for any reporting. If I ahve misunderstood please try and re-explain solution. Thanks

[amitrathi239]

If all you want to use Free hand sql providers in webi then there is no direct solution to display LOV's. Lov’s can be displayed either with universe or direct Webi connections e.g BICS or HANA One workaround is to add dummy webi queries on top of HANA as you showed in the first screenshot. e.g One webi query on HANA to select Material no and create prompt on Material no.with this query you will get the lovs of material no. Second query on free hand sql provider and prompt condition on material no.If prompt text in both queries is same then you will be asked to enter material no once and lov’s you can select from first query.