Clarity

  • Private Community

  • 1.  Wildcards in jaspersoft studio reports

    Posted Sep 29, 2016 12:59 PM
    Basically i want to pass a string into parameter with wildcard. 
     
    the stored data is like:
    Inline image 1
    I am using the "code" on the right. 
    if i use in TOAD:
    WHERE port.port_code LIKE '%prd_mob_app%'
    results in jaspersoft studio
    Works perfectly. 
    Then i add in the parameter
    WHERE port.port_code LIKE '%'||'$P!{portfolioKey_1}'||'%'
    This doesn't give me any errors, and i am using a collection as class type:
    Inline image 2
    Unfortunately, nothing is generated within the report, the report is blank.
    I also tried using an Array
    <parameter name="portfolioKey_1" class="java.sql.Array">
    <parameterDescription><![CDATA[Portfolio]]>     </parameterDescription>
    </parameter>
    Does anyone have any ideas please?
    Thanks, 
    Keri


  • 2.  Re: Wildcards in jaspersoft studio reports

    Posted Sep 29, 2016 01:21 PM

    Couple of assumtions here.

    1. You are using JasperSoft Studio to develop a custom report.

    2. You are using custom sql to connect to the database thru the report server, as you post sql that works from Toad.

     

    Make your parameter a java.util.collection class.

    In your query, try this: and $X{IN, port.port_code, portfolioKey_1}

    The syntax above is used by JasperSoft for collections, assuming you want users to actually use the parameter as a filter in the final report.

     

    I am not sure why your JasperSoft parameter is called portfolioKey_1, as this may be confusing. Many OOB reports use this type parameter when connecting to CA_PPM_DWH_BEAN. I do not recommend connecting to bean unless you have no other method of developing the report, as performance may be an issue. Many times this parameter is an integer, so exercise caution.



  • 3.  Re: Wildcards in jaspersoft studio reports
    Best Answer

    Posted Sep 29, 2016 07:12 PM

    Thank you, however, i just adjusted my thinking a bit and now have the exact results i was looking for: 

    I used this in my query.

       AND ($X{IN, 'all', portfolioKey_1} OR $X{IN, clv.lookup_code, portfolioKey_1})

    I created a new query based input control. I basically just mimic'ed the OOTB "Investment Type" way of doing strings.

     

    This now works perfectly :-)

     

    Thanks, 

    Keri