0

I'm working on jasper reports. I've created a parameter called Employment Type, for which my code would fetch values from DB for populating dropdown values in the page.

The problem is, in db, table has columns Alias and table name, and alias value is "Employment Type" (space b/w strings). In jrxml, the query will fail if space are provided (i.e parameter value given as "Employment Type").

So can any suggest me how to fix it. Can I use a new fx_variable, assign the values of parameter to this variable and use in the query?

The query

SELECT Slno as "Sl. No.", EmpUID , EmployeeID as "Employee ID", EmployeeName as "Employee Name", cast(DateOfJoining as date) as "Date of joining", Department , Division , SalaryEarnedDaily as "Salary-Earned(Daily)", ApprovedOTHours As "O.T Hours", OTHours, OTAmount as "OT Amount", TotalAmount as "Total ( earned salary + OT)", employment_Type 
FROM Temp_EmpSalaryDaily 
WHERE ((EmployeeID = $P{EmployeeID}) or (EmployeeName like $P{Name})) and Division in ($P!{Divisions}) and Department in ($P!{Departments})and employment_Type in ($P!{Employment Type})

Parameters:

<parameter name="Name" class="java.lang.String"/> <parameter name="HR" class="java.lang.String"/>
<parameter name="Divisions" class="java.lang.String"/>
<parameter name="Departments" class="java.lang.String"/>
<parameter name="OTReportDate" class="java.util.Date"/>
<parameter name="Employment Type" class="java.lang.String"/>
Petter Friberg
  • 21,252
  • 9
  • 60
  • 109
Amar
  • 17
  • 4
  • Select Slno as "Sl. No.", EmpUID , EmployeeID as "Employee ID", EmployeeName as "Employee Name", cast(DateOfJoining as date) as "Date of joining", Department , Division , SalaryEarnedDaily as "Salary-Earned(Daily)", ApprovedOTHours As "O.T Hours", OTHours, OTAmount as "OT Amount", TotalAmount as "Total ( earned salary + OT)", employment_Type from Temp_EmpSalaryDaily where ((EmployeeID = $P{EmployeeID}) or (EmployeeName like $P{Name})) and Division in ($P!{Divisions}) and Department in ($P!{Departments})and employment_Type in ($P!{Employment Type}) – Amar Feb 03 '16 at 14:30
  • Now if I replace employment_Type in ($P!{Employment Type}) with employment_Type in Replace($P!{Employment Type},' ',''), then it would still throw an error – Amar Feb 03 '16 at 14:31
  • Ok now tell me the definition of Employment Type show jrxml definition. – Petter Friberg Feb 03 '16 at 15:05
  • these are my parameters, – Amar Feb 03 '16 at 15:15
  • the last one, may get multiple values. Now because of space b/w Employment Type, the query is failing – Amar Feb 03 '16 at 15:16
  • I imagine you have a string like this "'ET1','ET2','ET3' in it.. then you need to use the !, even if this is not the best way to do this (sql injection risk) – Petter Friberg Feb 03 '16 at 15:17
  • The correct way is to pass a List (Collection) and then use the $X{IN, command as in link above... – Petter Friberg Feb 03 '16 at 15:19
  • yes, I've put ($P!{Employment Type}), but if I remove space(i.e EmploymentType, instead of Employment Type), its not executing – Amar Feb 03 '16 at 15:22
  • I have tested I don't think it depends on space in your parameter, it probably depends on whats in it!!, its not in correct format... – Petter Friberg Feb 03 '16 at 15:22
  • If you need to change name you need to change both query and jrxml – Petter Friberg Feb 03 '16 at 15:23
  • my code fetches values from table based on parameters, & its column value is Employment Type(space exists). – Amar Feb 03 '16 at 15:24
  • yes this worked. that example in the link helped me. $X{IN,employment_Type,Employment Type} Thanks a lot – Amar Feb 03 '16 at 15:47
  • Flagged as duplicate it would be useful for community if you acknowledge that. – Petter Friberg Feb 03 '16 at 15:49
  • Hi, I faced one more problem in exporting data to excel. If no records found then I want onlr the hearder(fields) to appear. But when report is generated it shows "data is corrupt, do you want to recover". – Amar Feb 04 '16 at 09:45
  • Since you showed good community behavior I will tell you the secret... – Petter Friberg Feb 04 '16 at 09:51
  • set whenNoDataType="AllSectionsNoDetail" on the jasperReport tag (top tag) ; ) – Petter Friberg Feb 04 '16 at 09:52

0 Answers0