CODEWITHSUNDEEP
javamysqlsqlparametersjasper-reports
PeakGen
PeakGen

Reputation: 23035

Issues in passing parameters to Jasper Reports

I am trying to pass 2 parameters into Jasper report and get the result. In SQL query, I am combining them with the query as below.

SELECT 
ROUND(SUM(case when Updated_Date BETWEEN $P{secondYear}+"-03-01" AND $P{secondYear}+"-03-31" then Amount else 0 end),2) AS `March` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{secondYear}+"-02-01" AND $P{secondYear}+"-02-29" then Amount else 0 end),2) AS `February` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{secondYear}+"-01-01" AND $P{secondYear}+"-01-31" then Amount else 0 end),2) AS `January` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}+"-12-01" AND $P{firstYear}+"-12-31" then Amount else 0 end),2) AS `December` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}+"-11-01" AND $P{firstYear}+"-11-31" then Amount else 0 end),2) AS `November` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}+"-10-01" AND $P{firstYear}+"-10-31" then Amount else 0 end),2) AS `October` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}+"-09-01" AND $P{firstYear}+"-09-31" then Amount else 0 end),2) AS `September` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}+"-08-01" AND $P{firstYear}+"-08-31" then Amount else 0 end),2) AS `August` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}+"-07-01" AND $P{firstYear}+"-07-31" then Amount else 0 end),2) AS `July` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}+"-06-01" AND $P{firstYear}+"-06-31" then Amount else 0 end),2) AS `June`, 
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}+"-05-01" AND $P{firstYear}+"-05-31" then Amount else 0 end),2) AS `May`,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}+"-04-01" AND $P{firstYear}+"-04-31" then Amount else 0 end),2) AS `April` 
FROM VAT

However, In my Jasper report, what I get is 0 as the VAT for all the months, all the time. What have I done wrong?

Upvotes: 0

Views: 208

Answers (1)

PeakGen
PeakGen

Reputation: 23035

I found the answer. In jasper report, if you want to combine parameters like String in your SQL Query, you should not use the + sign. So the correct code is below.

SELECT 
ROUND(SUM(case when Updated_Date BETWEEN $P{secondYear}"-03-01" AND $P{secondYear}"-03-31" then Amount else 0 end),2) AS `March` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{secondYear}"-02-01" AND $P{secondYear}"-02-29" then Amount else 0 end),2) AS `February` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{secondYear}"-01-01" AND $P{secondYear}"-01-31" then Amount else 0 end),2) AS `January` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}"-12-01" AND $P{firstYear}"-12-31" then Amount else 0 end),2) AS `December` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}"-11-01" AND $P{firstYear}"-11-31" then Amount else 0 end),2) AS `November` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}"-10-01" AND $P{firstYear}"-10-31" then Amount else 0 end),2) AS `October` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}"-09-01" AND $P{firstYear}"-09-31" then Amount else 0 end),2) AS `September` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}"-08-01" AND $P{firstYear}"-08-31" then Amount else 0 end),2) AS `August` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}"-07-01" AND $P{firstYear}"-07-31" then Amount else 0 end),2) AS `July` ,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}"-06-01" AND $P{firstYear}"-06-31" then Amount else 0 end),2) AS `June`, 
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}"-05-01" AND $P{firstYear}"-05-31" then Amount else 0 end),2) AS `May`,
ROUND(SUM(case when Updated_Date BETWEEN $P{firstYear}"-04-01" AND $P{firstYear}"-04-31" then Amount else 0 end),2) AS `April` 
FROM VAT

If you are using Jaspersoft Studio, it will display this as an error in Query editor. However, it works with no issues.

Upvotes: 1

Related Questions