Webi report Cascading Prompts in LOV’s with multiple repeated data with semicolon

[former_member448449]

In Webi report Cascading Prompts in LOV’s with multiple repeated data with semicolon
City is column name, having multiple repeated LOV’s with semicolon for different Mem ID’s
In webi report prompt i need to show the distinct values of city alone also if i select one city repeated for same city with different Member ID data has to come in report

Ex:

MemID City
~~~~~~~~~~~~~
Mem56 Texas
Mem57 SanAntonio; Texas
Mem58 Dallas;Texas;SanAntonio
Mem59 Dallas;SanAntonio
Mem60 Austin;Texas
Mem61 Austin;Colorado
Mem62 Florida;Austin;Texas

Expected Output:
~~~~~~~~~~~~~~~~~~~~~
When i run the report user have to get the city prompt inlist as below

Texas
SanAntonio
Dallas
Austin
Colorado
Florida

If user selects the Texas it is repeated in 5-times and Also user gets Mem56, Mem57, Mem58, Mem60 and Mem62 ID’s data into the report.

Please give me any solutions.
Thanks,
Gopinath Thangaraj

[mhmohammed · Accepted solution]

Hi Gopi,

Getting unique LOVs is going to be tough!! I've an idea for the second part of your question, as you said if Texas is selected, all the 5 rows should be included in report.

Create a filter in the Universe as below, the idea is a use Like operator, and Texas would be concatenated with the wildcard character % (as prefix & suffix). Makes sense?

TableName.City LIKE ('%'+@Prompt('Enter City','A','Class/City','Mono',persistent)+'%')

Thanks,
Mahboob Mohammed

[former_member448449 · Accepted solution]

Really thanks!!!.,. Its a one way of proceeding with this.

Thanks a lot for suggestion!!!