0

Please help me to pass a input parameter in my report so that i can use a multi select query with the use of parameter.

In my report i am using $x{IN,ADMIN1,admins} but getting

error: wrong type java.lang.string for parameter admins, admins should be an array or collection.

Mysql query: 

Select ADMIN1,WEEK1,WEEK2,WEEK3,(((WEEK3-WEEK2)/WEEK2)*100) as percentage_change, (WEEK3-

WEEK2) as MSU_Difference,SUMWEEK2,SUMWEEK3,SUMWEEK,WEEK4,WEEK5,(((WEEK5-WEEK4)/WEEK4)*100)

 as percentage_change_PRE,admin,ORGNo,OrgAbbr,msus from ((select admin as ADMIN1, sum(msu) 

as WEEK1 from sccp_raw where dt >= date_sub(date($P{start_date}), INTERVAL 14 DAY) and dt 

< date_sub(date($P{end_date}), INTERVAL 13 DAY) group by admin order by WEEK1) as 

q1,(select admin as ADMIN2, sum(msu) as WEEK2 from sccp_raw where dt >= 

date_sub(date($P{start_date}), INTERVAL 7 DAY) and dt < date_sub(date($P{end_date}), 

INTERVAL 6 DAY) group by admin order by WEEK2) as q2,(select admin as ADMIN3, sum(msu) as 

WEEK3 from sccp_raw where dt between $P{start_date} and $P{end_date} group by admin order 

by WEEK3) as q3,(select sum(msu) as SUMWEEK2 from sccp_raw where dt >= 

date_sub(date($P{start_date}), INTERVAL 7 DAY) and dt < date_sub(date($P{end_date}), 

INTERVAL 6 DAY))as q4,(select sum(msu) as SUMWEEK3 from sccp_raw where dt >= 

date_sub(date($P{start_date}), INTERVAL 14 DAY) and dt < date_sub(date($P{end_date}), 

INTERVAL 13 DAY))as q5,(select sum(msu) as SUMWEEK from sccp_raw where dt BETWEEN 

$P{start_date} and $P{end_date})as q6,(select sum(msu) as WEEK4 from sccp_raw where dt >= 

date_sub(date($P{start_date}), INTERVAL 372 DAY) and dt < date_sub(date($P{end_date}), 

INTERVAL 371 DAY)) as q7,(select sum(msu) as WEEK5 from sccp_raw where dt >= 

date_sub(date($P{start_date}), INTERVAL 365 DAY) and dt < date_sub(date($P{end_date}), 

INTERVAL 364 DAY)) as q8,(select sum(msu) as MSU from sccp_raw where dt between 

$P{start_date} and $P{end_date}) as q9,(select a.dt,a.admin,b.ORGNo, b.OrgAbbr, sum(a.msu) 

as msus from sccp_raw a left join (select ORGNo, ADMINS, OrgAbbr from orig) b on 

a.admin=b.ADMINS where a.dt between $P{start_date} and $P{end_date} group by a.admin)as 

q10)  where $X{IN,ADMIN1,admins} and ADMIN1=ADMIN3 and ADMIN2=ADMIN3 and admin=ADMIN3 

group by ADMIN1 order by WEEK3 desc;

The parameter's configuration: 

parameter "admins":  

class: `java.util.Collection`  

Default Value Expression: "select admin from sccp_raw group by admin"

Also help me to know if i want to create this dropdown to be selected as "all admins" followed by admins list.

Alex K
  • 22,315
  • 19
  • 108
  • 236
user3664402
  • 3
  • 1
  • 3

2 Answers2

0

In the case of multiselect parameter of Collection type and you do not need to give "All" to select all the values in iReport as default value for that parameter by default it means all the values are selected.

But if the parameter is single select then the query for jasperreport server input control will be to get "All" in input control values : -

 SELECT * FROM (
 SELECT 'All Admins' admin from sccp_raw 
 UNION
 SELECT DISTINCT admin from sccp_raw) a
 ORDER BY admin

and iReport Query Will be : - 

  SELECT *
  FROM table
  WHERE ($P{admins}='All Admins' OR Admin=$P{admins})

For Example see this report using Sample HSQL database in ireport:-

  <?xml version="1.0" encoding="UTF-8"?>
  <jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports"  
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
  xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports 
  http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="report1"     
  pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20"  
   topMargin="20" bottomMargin="20" uuid="0d2f9917-101e-4d51-9d78-172909ca097a"> 
<property name="ireport.zoom" value="1.0"/> 
<property name="ireport.x" value="0"/> 
<property name="ireport.y" value="0"/> 
<parameter name="p_SHIPCOUNTRY" class="java.lang.String">
    <defaultValueExpression><![CDATA["Germany"]]></defaultValueExpression>
</parameter>
<queryString>
    <![CDATA[SELECT SHIPCOUNTRY,SHIPCITY

    FROM orders
    WHERE  ($P{p_SHIPCOUNTRY}='All' OR SHIPCOUNTRY=$P{p_SHIPCOUNTRY})]]>
</queryString>
<field name="SHIPCOUNTRY" class="java.lang.String"/>
<field name="SHIPCITY" class="java.lang.String"/>
<background>
    <band splitType="Stretch"/>
</background>
<detail>
    <band height="29">
        <textField>
            <reportElement uuid="f6cbe144-e64a-4537-b3b8-e6a461dd5a72"
                   x="114" y="0" width="100" height="20"/>
            <textElement/>
            <textFieldExpression><![CDATA[$F{SHIPCOUNTRY}]]>
                </textFieldExpression>
        </textField>
        <textField>

            <reportElement uuid="c13e2483-d005-4f56-8ce8-d64f923f0359"
                 x="417" y="3" width="100" height="20"/>
            <textElement/>
            <textFieldExpression><![CDATA[$F{SHIPCITY}]]>             
        </textFieldExpression>
        </textField>
    </band>
        </detail>

            </jasperReport>

For mor detail you can visit my blog

Sharad
  • 3,562
  • 6
  • 37
  • 59
  • 1. As suggested by you, now "All admins" and distinct "admin" works fine in ireport but all i have to do is manually type admin names as **there is no drop down list visible**. 2. my parameter is admins to which i have assigned below query: "SELECT 'All admins' admin from sccp_raw UNION SELECT DISTINCT admin from sccp_raw" however as said in ireport-4.7.0 i need to manually give admin name and while using the same jrxml and input control (as suggested above) in jasperserver 4.7.0, out of thousand admin only one admin name is visible. Please suggest. – user3664402 May 22 '14 at 19:10
  • Check the query of input control or use above query to create single select query based input control in Jaspereport server – Sharad May 23 '14 at 04:37
  • the parameter query given by you is running fine if i test it on mysql command line but same problem when using it on ireport and jasperserver. Atleast in I repotrt i have to manually enter admin name and then it populates correct report but i jasper it is only showing one admin name in drop down nothing else :( – user3664402 May 23 '14 at 06:25
  • i have updated my parameter configuration on jasper server and now all the admins are visible in drop down. but still its not compiling report in jasper server. – user3664402 May 23 '14 at 09:57
  • As i have already told you if the multiselect parameter is of class type: `java.util.Collection` then you do not need to select 'All' to pass all the value of input control, my solution will work with single select of class type: `java.land.String` . – Sharad May 23 '14 at 17:32
0

You have to use java.util.Collection in your jrxm file. 

Here, send your multi ids from your selection to your .jasper file.
      String qGelirGrubu = "1,2,3,4,5";
      String[] convertedGelirArray = qGelirGrubu.split(",");
      List<Integer> convertedGelirList = new ArrayList<Integer>();
      for (String number : convertedGelirArray) {
           convertedGelirList.add(Integer.parseInt(number.trim()));
      }
     parameters.put("qGelirGrubu1", convertedGelirList);

In your .jrxml file ; Define your parameter like this;

<parameter name="qGelirGrubu1" class="java.util.Collection"/>

and your SQL query you can use your parameter like this;

select * from TABLE_NAME A where A.ID IN (1,2,3,4,5); like that in .jrxml file: 

 select * from TABLE_NAME A where  $X{IN,A.ID,qGelirGrubu1}

$X{IN,COLUMN_NAME,YOUR_PARAMETER_NAME}

harun ugur
  • 1,718
  • 18
  • 18