plaagje

After some googling I found a solutions that works: I've created a user defined function dbo.Split: CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1)) returns @temptable TABLE (items varchar(8000)) as begin declare @idx int declare @slice varchar(8000) select @idx = 1 if len(@String)<1 or @String is null return while @idx!= 0 begin set @idx = charindex(@Delimiter,@String) if @idx!=0 set @slice = left(@String,@idx - 1) else set @slice = @String if(len(@slice)>0) insert into @temptable(Items) values(@slice) set @String = right(@String,len(@String) - @idx) if len(@String) = 0 break end return end DECLARE @a VARCHAR(5000) SET @a = '$P!{VERRICHTINGSTYPE}' SELECT * FROM verrichting WHERE typeID = (CASE WHEN @a <> 'ALL' THEN CASE WHEN typeID IN (select items from dbo.split(@a,',')) THEN typeID ELSE NULL END ELSE typeID END) This works fine, hope this helps others with the same problem. Post Edited by plaagje at 10/14/2010 13:38

Hello, The most reports we use are made to be multi-language. (made with iReport) Every field will be a text field with the $R{} syntax. From our Java program we call jasper and pass on the language data. Everything works fine when using a single report (when calling a report from our program the $R{} fields are filled with the correct data). But when using a subreport it displays 'null' on all $R{} fields from the subreport. Might it be a bug that not all required language data is passed from the main to the subreport? (Both main and sub report have no data filled in at the resource bundle, so calling the report from iReport always gives nulls, but when calling it from our java app everything works fine (for a single report)) I hope someone has a solution for this problem. Rick

Kick :) Anyone got an bright idea of how to make this work ? /tools/fckeditor/editor/images/smiley/msn/embaressed_smile.gif

Hi Teodord, Thanks for your reaction. Indeed what I wrote up there isn't just a single command, but thats just so you can test it without having my database. I used multiple queries in iReport, some of them quite complex and all worked. This is the first one I found that worked in my query builder but not in iReport. Normaly I have my filled database, and if the query below here would work it would be great :). SELECT * FROM verrichting WHERE typeID = (CASE WHEN $P!{TYPE} <> 'ALL' THEN CASE WHEN typeID IN ($P!{TYPE}) THEN typeID ELSE NULL END ELSE typeID END) (As you don't got these tables I added a little bit of testing script in the openings post)

HI there, Using iReports I've created a report. I'll first explain why I need the query: A user often has multiple selection criteria. For instance a user likes to see all his open documents (easy), now he wants to only see the documents that are addressed to 5 specific persons. The database often has tens of thousands persons. The query now will be (simplified): SELECT * FROM documents WHERE addressedto IN (1,2,3) AND user = 2 But when the user likes to see all documents our program can supply a long string containing ALL 'addressedto' ID's. as you'll understand this probibly ain't the best way. It also can supply a string like "ALL" So with some help the following query was created: DECLARE @a VARCHAR(4) SET @a = 'ALL' SELECT * FROM verrichting WHERE typeID = (CASE WHEN @a <> 'ALL' THEN CASE WHEN typeID IN (@a) THEN typeID ELSE NULL END ELSE typeID END) AND user != 2 This query works, if I replace ALL with 8,11, I'll only find the types '8,11' Now i'm using the folling query in ireport: CREATE TABLE #documents (addressedto INT) INSERT INTO #documents VALUES(1) INSERT INTO #documents VALUES(2) INSERT INTO #documents VALUES(3) INSERT INTO #documents VALUES(4) DECLARE @a VARCHAR(4) SET @a = $P!{TYPE} SELECT * FROM #documents WHERE addressedto = (CASE WHEN @a <> 'ALL' THEN CASE WHEN addressedto IN (@a) THEN addressedto ELSE NULL END ELSE addressedto END) DROP TABLE #documents $P!{TYPE} is a string with default value "'ALL'" ("ALL" doesn't work) case 1: If I enter "ALL" everything works case 2: if I enter 1, only 1 is displayed (also correct) case 3: if I enter 1,2 an error occurs case 3 should work (as it does with query browsers) It would even be better if I could replace the @a with the iReport variable, but this also gives errors... I've attached a ireport and Jasper file for testing purposes. (And I assume this is a jasper report error, not A iReport error) Code:Error filling print... Error executing SQL statement for : report1 net.sf.jasperreports.engine.JRException: Error executing SQL statement for : report1     at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:143)     at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:686)     at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:606)     at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1277)     at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:892)     at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:841)     at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:58)     at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:417)     at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:247)     at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:877)     at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572)     at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997) Caused by: java.sql.SQLException: Incorrect syntax near ','.     at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:364)     at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2754)     at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2195)     at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:620)     at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:372)     at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:672)     at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:137)     ... 11 more Print not filled. Try to use an EmptyDataSource...

We'll the title says it all :). I've got a report containing about 10 subreports. I've put every subreport on a separate details band so I can choose to hide the entire band. By doing this there will be no empty space in between 2 subreports. Is it posible to hide the band on the main report if the subreport contains no data? Returning a variable doesn't help because it will only be set once the subreport is complete. Any Ideas?