Re: Multi select field in Report (370 Views)
Reply
Advisor
Posts: 25
Registered: ‎09-18-2012
Message 1 of 5 (393 Views)

Multi select field in Report

Hi All,

 

I have requirement wherein there is field in the report filter which are multiselect.

And this is run against Requests which also have multiple values in a field. Currently the report give records for multiple select values, but for those request which contain mutiple values in a fields are excluded in the records list.

Eg if Text is a field haviing A, B for request 111. and text2 = A request 222, Text3 = B request 333

when A, B are entered in the report fiter, the report only list out 222, 333, leaving out 111.

Is there any possible way to display all of them.

 

Regards

Honored Contributor
Posts: 410
Registered: ‎05-06-2008
Message 2 of 5 (378 Views)

Re: Multi select field in Report

The problem with Multi-Selecti fields as filters for Multi-Select fields on Requests is one of how the data is stored for each and how you implement the WHERE clause.  A typical use of a MS-ACL file against a single value Request field would be

 

AND rd1.PARAMETERx in ([P.MS_FILTER.TO_STRING])

 

so that you can compare the value against all of the values in the filter.  With a MS field on the Request form, the data is stored in the DB as 'Val1#@#Val2#@#Val3', so that when the WHERE clause looks at the column in the database against the values in the field, it will not match.   From a pure SQL standpoint, I don't know that there is a simple answer.  One possible solution would be to create a function that you could pass the DB column and the filter values into and have it return a 1 for a match and a 0 for no match (or any other return values that want).  I don't have such a function, myself, as I have tried to heed the warning that PPM displays when you create a MS Filter on a Report that you need to take special precautions for a MS Filter on a MS Field.

Highlighted
Advisor
Posts: 38
Registered: ‎06-19-2012
Message 3 of 5 (370 Views)

Re: Multi select field in Report

We also experienced an issue with multi select fields in Reports (jsp).

 

This is what we have done on the jsp reports to translate the Report filter:

**************************************************************************************************

<%-- Business Area Filter --%>
<c:if test="${not empty BUS_AREA}">
<c:forEach items="${BUS_AREA}" var="a" varStatus="busa">
    <c:choose>
        <c:when test="${busa.first == true}">
            <c:set var="busAreaResult" value="'${busa.current}'"/>
        </c:when>
        <c:otherwise>
            <c:set var="busAreaResult" value="${busAreaResult},'${busa.current}'"/>
        </c:otherwise>
    </c:choose>
</c:forEach>
and reqhd.parameter2 in (<c:out escapeXml="false" value="${busAreaResult}"/>)
</c:if>


****************************************************************************************

BUS_AREA is the token received from the Report Type filter. Then we use expresion language to put the string received in the correct SQL format. The red text is part of the where clause in the SQL statement.

 

You can read more on how to use the EL to get the correctly formattes SQL string: http://www.tutorialspoint.com/jsp/jsp_standard_tag_library.htm

 

Hope this helps.

Alfred Puth
PPM Consultant
EOH Application Management
Advisor
Posts: 25
Registered: ‎09-18-2012
Message 4 of 5 (351 Views)

Re: Multi select field in Report

Thanks for the help, currently we are not using JSP report , we are writing a procedure containing html tag to populate the records.

Any idea how to go abt in this situation.

 

Thanks in advance.

Honored Contributor
Posts: 743
Registered: ‎07-13-2010
Message 5 of 5 (83 Views)

Re: Multi select field in Report

In Report, if you want to use string in where clause (example - IN ('xyz','abc','iop')

 

then use this

 

(<rpt:replaceAll value="'${<token_parameter>}'" from="," to="', '"/>) 

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.