I have a couple of parameters with the definitions below:
<parameter name="mgmtGroup" class="java.util.Collection">
<defaultValueExpression><![CDATA[new ArrayList()]]></defaultValueExpression>
</parameter>
<parameter name="TypeGroup" class="java.lang.String"/>
<parameter name="ChooseGroup" class="java.lang.String" isForPrompting="false">
<defaultValueExpression><![CDATA["Primary Management Group".equals($P{TypeGroup}) ? "pcl_info.primary_group" : "pcl_info.management_groups"]]></defaultValueExpression>
</parameter>
The issue is that the datafields that the parameters need to refer to are set up as such:
Management Groups
select mga.* from
(select distinct mg.protocol_id
,LISTAGG((case when mg.primary='Y' then mg.mgmt_group_description || ' (P)' else mg.mgmt_group_description end), '; ') within group (order by primary DESC, mgmt_group_description) management_groups
from SV_PCL_MGMT_MGMTGROUP mg
GROUP BY mg.protocol_id)mga
inner join
(select distinct protocol_id
from SV_PCL_MGMT_MGMTGROUP mg
where $X{IN,mg.mgmt_group_description,mgmtGroup}
on mga.protocol_id = mgb.protocol_id
Primary Management Group
select mg.protocol_id
,case when mg.primary='Y' then mg.mgmt_group_description end as primary_group
from SV_PCL_MGMT_MGMTGROUP mg
where $X{IN,mg.mgmt_group_description,mgmtGroup}
So, management_groups appears as ManagementGroup1; ManagementGroup2;...ManagementGroupn
Primary_group appears as PrimaryManagementGroup
I've tried a number of different arrangements, and if I only want to find any row where the selected management groups appear, the report works fine. It's when I added the join to the Primary Management Group where things began to fail. When I tried to compare the mgmtGroup parameter to the ChooseGroup parameter, I got an error about column index - I'm thinking it was because the two parameters aren't the same type. I can't refer to ChooseGroup instead of mgmtGroup in the $X statement, since I have that set up as a String parameter. I just can't seem to figure out what I need to do.
1 Answer:
You are correct that you can't have a string datatype in array IN clase.
You can't replace:
where $X{IN,mg.mgmt_group_description,mgmtGroup}
with
$X{IN,mg.mgmt_group_description,ChooseGroup}
beacuse ChooseGroup is a string and only holds one string value.
Use
where mg.mgmt_group_description like $PChooseGroup
or if you want to go crazy:
add another parameter as an arrany (collection) and set it as a multiple select from list with $PChooseGroup populated as one of the list items.
