The following is the error i got while compiling the jrxml file.
Error filling print...
Error executing SQL statement for : Activity_Summary_Report
Setting up the file resolver...
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : Activity_Summa ry_Report
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:240)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1086)
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:667 )
at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1258)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:877)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:822)
at net.sf.jasperreports.engine.fill.JRFiller.fill(JRFiller.java:61)
at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:446)
at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:276)
at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:745)
at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:89 1)
at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572)
at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997)
Caused by: commicrosoft.sqlserver.jdbc.SQLServerException: Operand type clash: date is incompatible with int
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:233)
... 12 more
Print not filled. Try to use an EmptyDataSource...
The parameter I am taking in jrxml file and query strings are
<parameter name="EndDate" isForPrompting="true" class="java.util.Date">
<defaultValueExpression ><![CDATA[new Date(System.currentTimeMillis())]]></defaultValueExpression>
</parameter>
<queryString><![CDATA[declare @startdate as datetime;
declare @enddate as datetime;
declare @sitegroup as nvarchar(50);
set @startdate = $P{EndDate}-1;
set @enddate = $P{EndDate};
set @sitegroup = 'BBXNCR';
set NOCOUNT ON; -- Added by JC230090: Fixes bug when running query with Jasper
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [#temp_invlines]') AND type in (N'U'))
DROP TABLE [dbo].[#temp_invlines]
create table #temp_invlines (site_iid uniqueidentifier, invoice_line_type_iid int, sub_total float, tax_total float,line_total float, product_type_iid int);
insert into #temp_invlines (site_iid,invoice_line_type_iid, sub_total, tax_total, line_total, product_type_iid)
select h.site_iid, invoice_line_type_iid, l.sub_total, l.tax_total, l.line_total, l.product_type_iid
from invoice_lines l
inner join invoices h on l.invoice_iid = h.invoice_iid
where h.invoice_dt >= @startdate and h.invoice_dt < @enddate
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#temp_payment_invoices]') AND type in (N'U'))
DROP TABLE [dbo].[#temp_payment_invoices]
create table #temp_payment_invoices (site_iid uniqueidentifier, inv_amount float);
insert into #temp_payment_invoices (site_iid, inv_amount)
select i.site_iid, i.total
from invoice_payments p
inner join Invoice_Payment_Invoice_Xref pXref on p.invoice_payment_iid = pXref.invoice_payment_iid
inner join invoices i on pXref.invoice_iid = i.invoice_iid
where p.payment_dt >= @startdate and p.payment_dt < @enddate
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#temp_promocodes]') AND type in (N'U'))
DROP TABLE [dbo].[#temp_promocodes]
create table #temp_promocodes (site_iid uniqueidentifier, invoice_promo_line_type_iid int, promo_amount float);
insert into #temp_promocodes (site_iid, invoice_promo_line_type_iid, promo_amount)
select h.site_iid, pl.invoice_promo_line_type_iid, pl.amount
from invoice_promo_lines pl
inner join invoices h on pl.invoice_iid = h.invoice_iid
where h.invoice_dt >= @startdate and h.invoice_dt < @enddate
select
rtrim(s.site_id) as [Site ID]
, rtrim(ss.Description) as [Status]
, @startdate as [Date]
, datename(dw,@startdate) as [DoW]
, rtrim(s.name) as [Store Code]
, rtrim(isnull(sg.Description,'')) as [Site Group]
, rtrim(s.address1) + ', ' + rtrim(s.city) + ', ' + rtrim(s.state) + ' ' + rtrim(s.Zip) as [Address]
, rtrim(pg.description) as [RentPriceGroup]
, (select count(*) from invoices h where h.invoice_dt >= @startdate and h.invoice_dt < @enddate and h.site_iid = s.site_iid) as [Trans]
, (select isnull(sum(sub_total),0) from #temp_invlines l where l.site_iid = s.site_iid and l.invoice_line_type_iid = 1) as [Rent Rev]
, (select isnull(sum(sub_total),0) from #temp_invlines l where l.site_iid = s.site_iid and l.invoice_line_type_iid = 2) as [Ext Day Rev]
, (select isnull(sum(sub_total),0) from #temp_invlines l where l.site_iid = s.site_iid and l.invoice_line_type_iid = 0 and l.product_type_iid = 2) as [Used Sell Rev]
, (select isnull(sum(sub_total),0) from #temp_invlines l where l.site_iid = s.site_iid and l.invoice_line_type_iid = 0 and l.product_type_iid <> 2) as [New Sell Rev]
, (select isnull(sum(sub_total),0) from #temp_invlines l where l.site_iid = s.site_iid) as [Rev Total]
, (select isnull(sum(tax_total),0) from #temp_invlines l where l.site_iid = s.site_iid) as [Tax Total]
, (select isnull(sum(line_total),0) from #temp_invlines l where l.site_iid = s.site_iid) as [Grand Total]
, (select isnull(sum(sub_total),0) from #temp_invlines l where l.site_iid = s.site_iid) /
(select case when count(*) = 0 then 1 else count(*) end from invoices h where h.invoice_dt >= @startdate and h.invoice_dt < @enddate and h.site_iid = s.site_iid)
as [Rev/Trans]
, (select isnull(count(sub_total),0) from #temp_invlines l where l.site_iid = s.site_iid and l.invoice_line_type_iid = 1) as [Rent Cnt]
, (select isnull(count(sub_total),0) from #temp_invlines l where l.site_iid = s.site_iid and l.invoice_line_type_iid = 2) as [Ext Day Cnt]
, (select isnull(count(sub_total),0) from #temp_invlines l where l.site_iid = s.site_iid and l.invoice_line_type_iid = 0 and l.product_type_iid = 2) as [Used Sell Cnt]
, (select isnull(count(sub_total),0) from #temp_invlines l where l.site_iid = s.site_iid and l.invoice_line_type_iid = 0 and l.product_type_iid <> 2) as [New Sell Cnt]
, (select isnull(count(promo_amount),0) from #temp_promocodes p where p.site_iid = s.site_iid and p.invoice_promo_line_type_iid = 0) as [CustSrv Promo Cnt]
, (select isnull(sum(promo_amount),0) from #temp_promocodes p where p.site_iid = s.site_iid and p.invoice_promo_line_type_iid = 0) as [CustSrv Promo Amt]
, (select isnull(count(promo_amount),0) from #temp_promocodes p where p.site_iid = s.site_iid and p.invoice_promo_line_type_iid = 1) as [Marketing Promo Cnt]
, (select isnull(sum(promo_amount),0) from #temp_promocodes p where p.site_iid = s.site_iid and p.invoice_promo_line_type_iid = 1) as [Marketing Promo Amt]
, (SELECT
isnull(count(*),0)
FROM Invoice_Lines ilrtn
INNER JOIN Invoices ihrtn on ilrtn.invoice_iid = ihrtn.invoice_iid
inner join customer_ids c on ihrtn.customer_id_iid = c.customer_id_iid
INNER JOIN Invoices ihrent on ihrtn.customer_id_iid = ihrent.customer_id_iid
INNER JOIN Invoice_lines ilrent on ihrent.invoice_iid = ilrent.invoice_iid
WHERE ihrtn.invoice_dt >= @startdate
and ihrtn.invoice_dt < @enddate
and ilrtn.invoice_line_type_iid in (0,2)
AND ihrent.invoice_dt <= ihrtn.invoice_dt
AND ilrent.invoice_line_type_iid in (1)
AND ilrent.piece_id = ilrtn.piece_id
and ihrtn.site_iid = s.site_iid
) as [Invoices]
, (SELECT
isnull(sum(ilrent.line_total + ilrtn.line_total),0)
FROM Invoice_Lines ilrtn
INNER JOIN Invoices ihrtn on ilrtn.invoice_iid = ihrtn.invoice_iid
inner join customer_ids c on ihrtn.customer_id_iid = c.customer_id_iid
INNER JOIN Invoices ihrent on ihrtn.customer_id_iid = ihrent.customer_id_iid
INNER JOIN Invoice_lines ilrent on ihrent.invoice_iid = ilrent.invoice_iid
WHERE ihrtn.invoice_dt >= @startdate
and ihrtn.invoice_dt < @enddate
and ilrtn.invoice_line_type_iid in (0,2)
AND ihrent.invoice_dt <= ihrtn.invoice_dt
AND ilrent.invoice_line_type_iid in (1)
AND ilrent.piece_id = ilrtn.piece_id
and ihrtn.site_iid = s.site_iid
) as [Collectable]
, ( select isnull(sum(p.amount),0)
from invoice_payments p
where p.payment_dt >= @startdate and p.payment_dt < @enddate
and p.invoice_payment_iid in (
select distinct p.invoice_payment_iid
from invoice_payments p
inner join Invoice_Payment_Invoice_Xref pXref on p.invoice_payment_iid = pXref.invoice_payment_iid
inner join invoices i on pXref.invoice_iid = i.invoice_iid
where p.payment_dt >= @startdate and p.payment_dt < @enddate
and i.site_iid = s.site_iid
group by p.invoice_payment_iid, pXref.invoice_iid)
) as [Payments Collected]
from sites s
inner join Site_Statuses_Enum ss on s.site_status_iid = ss.site_status_iid
inner join Site_Rent_Price_Groups as pg on s.site_rent_price_group_iid = pg.site_rent_price_group_iid
left outer join Site_SiteGroup_XRef sgx on s.site_iid = sgx.site_iid
left outer join Site_Groups sg on sgx.site_group_iid = sg.site_group_iid
where sg.Description in (@sitegroup) or sg.Description is null
order by s.site_id
drop table #temp_promocodes
drop table #temp_payment_invoices
drop table #temp_invlines;]]></queryString>
Please help me how to resolve it. When I am executing the same query directly in Sql server management studio, this is executing fine. But through jasper reports, it is throwing exception. Please Help me..
The issue got resolved. It was because of the input parameters ($P{StartDate},$P{EndDate}) are of data type date not datetime. Because the date data type's precision only runs to day level, not anything smaller.
If you ensure that the parameters supplied by your application (below) are of type datetime and not date, you should get rid of the error.
$P{StartDate}
$P{EndDate}