Parameters with different data types.

0

 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.

jburczyk's picture
Joined: Sep 20 2019 - 1:13pm
Last seen: 1 month 1 day ago

1 Answer:

0

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.

 

 

danoldenkamp@gmail.com's picture
Joined: Aug 29 2018 - 11:12am
Last seen: 9 hours 12 min ago
Feedback
randomness