Prompts in tabular sql report are not working with multiple values

[mvchaudhari]

I have one report in reportnet which uses following tabular SQL.


Select COUNT("S1"."Member_Number_ID")"Number_of_Members",
char("S1"."c2") "Number_of_Visits"
FROM
(select "T1"."MBRNO" "Member_Number_ID" , sum("T1"."PCDQT") "c2"
from "DWCLAIMS"."CLMCURWK" "T1" LEFT OUTER JOIN "DWMHN"."CLMLVLCARE" "T2" on "T1"."BECAT" = "T2"."BENCATCD"
where "T1"."GRNAME" in (#prompt('GRNAME')#) and "T1"."SPECSVCDT" between char(#prompt('SPECSVCDT1')#) and char(#prompt('SPECSVCDT2')#) and "T1"."GRPID" in ((#prompt('GRPID')#) ) and "T1"."BECAT" = "T2"."BENCATCD" and "T2"."PRMLVLCRCD" = (#prompt('PRMLVLCRCD')#) and "T2"."SCDLVLCRCD" = (#prompt('SCDLVLCRCD')#) and "T2"."TERLVLCRDS" = (#prompt('TERLVLCRDS')#)
group by "T1"."MBRNO" )"S1"
group by "c2"

It works fine for single client( GRNAME). However, it does not work for multiple clients( i.e. GRNAME). when I created the prompts, in all the prompts properties, I selected the multiselect value as "Yes".

I will really appreciate help.

Thanks,

 

[Draoued]

have you tried with a simpled sql without the out join ??

something like
select "T1"."MBRNO" "Member_Number_ID" , sum("T1"."PCDQT") "c2"
from "DWCLAIMS"."CLMCURWK" "T1"
where "T1"."GRNAME" in #prompt('GRNAME')#