CSV datasource for subreport

[arbitary]

Hi,

 

I can see many examples on jasper sites which explain how to create subreports from different queries, but none of them explain how to do it from 2 or more different csv datasources.

 

I have a mainreport.jrxml which has 2 subreports - sub1 and sub2. sub1 uses the default datasource(a csv file), so its ds expression is:

<dataSourceExpression><![CDATA[$P{REPORT_DATA_SOURCE}]]></dataSourceExpression>

 

sub2 uses a different csv file, so I have created a parameter called 'SUBREPORT_DATA_SOURCE' and set it as a datasource for sub2. It looks something like this:

 

<dataSourceExpression><![CDATA[new <dataSourceExpression><![CDATA[$P{SUBREPORT_DATA_SOURCE}]]></dataSourceExpression>

 

Can someone please tell me what value the 'SUBREPORT_DATA_SOURCE' parameter should have? I know it has be a 'JREmptyDataSource' type, but how can it point to my csv file(say 'sub2data.csv')?

 

Alternately, I also tried to specify the datasourceexpression for sub2 as:

<dataSourceExpression><![CDATA[new net.sf.jasperreports.engine.data.JRCsvDataSource(new File("sub2data.csv"))]]></dataSourceExpression>

 

It compiled but gave a runtime error -

 

Unknown column name - Column_1. Column_1 is a column in sub2.

 

Please help!!

[dheffelfinger]

What you can do is pass the CSVDataSource as a parameter to the parent report, then pass it along to the subreport by using the <dataSourceExpression> subelement of <subreport>.

 

David R. Heffelfinger

Author: JasperReports For Java Developers

[arbitary]

Thanks for the reply, David.

 

I tried to do this:

 

1) Create a parameter in the main report for the csv datasource:

 

<parameter name="SUBREPORT_DATA_SOURCE" isForPrompting="false" class="net.sf.jasperreports.engine.data.JRCsvDataSource">

<defaultValueExpression ><![CDATA[new net.sf.jasperreports.engine.data.JRCsvDataSource(new File("sub2data.csv"))]]></defaultValueExpression>

</parameter>

 

2) Then set this parameter in the subreport datasourceexpression:

 

<dataSourceExpression><![CDATA[$P{SUBREPORT_DATA_SOURCE}]]></dataSourceExpression>

 

Compile - OK

Run - Same problem 'Unknown column COLUMN_1'. Am I supposed to map all fileds from this subreport to my main report too? I mean, create params in main report for all fields in subreport? This does not seem logical somehow.

 

Also, would it be possible to do this programatically through java? How will I fill the report with 2 datasource objects?

[rsilverns.sympatico.ca]

Here is an example of passing the subreport data source programatically...

 

Code:

    Map customParameters = new HashMap(); 	      customParameters.put("SubDataSource", new CustomDataSource(new File("file.csv"«»)); //could be CSV datasource
	            
jasperPrint = JasperFillManager.fillReport( 		jasperReport, customParameters, ds);
		
JasperExportManager.exportReportToPdfFile(
	          jasperPrint, "c:\output.pdf"«»);

 

The main report then has a parameter declared for "SubDataSource", and the sub-report configured to have a datasource expression of "$P{SubDataSource}"

[freedd]

I've got the same problem under iReport with subreports, and for each one a different datasource (csv files):

unknown column name.

 

Is there a way to do it in iReport ?

or can I do it by programming (but I have no knowledge yet about jasperreport programming)?

[lshannon]

I don't understand. Are you getting an error that says 'unknown column name'?

 

In iReport you can right click on an subreport element and go to Properties -> Subreport (tab) -> Data Source Expression.

 

Put a data source expression something like this:

 

Code:

new JRCsvDataSource(new File("path to my file/file.csv"«»)

 

Make sure that you have 'Use Data source expression' for this to work.

 

You should be able to use this same expression for each of the subreports with the file path and name changing as necessary.

 

HTH

[freedd]

Ishannon,

 

I've already did what you said, but it doesnt work when I want to preview the report under iReport 3.0.

 

To summarize, this is what I have :

 

A master report with a csv datasource

(columns names are: CodeDossier#LibDossier# )

 

My subreport has another csv datasource, columns are:

CodeDossier#nom#prenom#

 

The key between csv files are "CodeDossier".

 

So when the master report displays the datas (libDossier) for one CodeDossier (it s like an ID), the subreport must show the corresponding datas (nom, prenom).

 

When I run the subreport alone in iReport, it works.

When I run the masterReport alone, it works too.

But when I run the masterReport including the subreport, it doesnt work:

 

ErreurÂlorsÂduÂremplissageÂdeÂl’impression…Ânet.sf.jasperreports.engine.JRException:ÂUnknownÂcolumnÂnameÂ:ÂCodeDossier

net.sf.jasperreports.engine.JRRuntimeException:Ânet.sf.jasperreports.engine.JRException:ÂUnknownÂcolumnÂnameÂ:ÂCodeDossier ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRFillSubreport.prepare(JRFillSubreport.java:635) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRFillElementContainer.prepareElements(JRFillElementContainer.java:344) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:346) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:305) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRVerticalFiller.fillColumnBand(JRVerticalFiller.java:1382) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRVerticalFiller.fillDetail(JRVerticalFiller.java:692) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:255) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:113) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:879) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:801) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:89) ÂÂÂÂatÂnet.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:601) ÂÂÂÂatÂnet.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:517) ÂÂÂÂatÂit.businesslogic.ireport.IReportCompiler.run(IReportCompiler.java:973) ÂÂÂÂatÂjava.lang.Thread.run(UnknownÂSource) CausedÂby:Ânet.sf.jasperreports.engine.JRException:ÂUnknownÂcolumnÂnameÂ:ÂCodeDossier ÂÂÂÂatÂnet.sf.jasperreports.engine.data.JRCsvDataSource.getFieldValue(JRCsvDataSource.java:172) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRFillDataset.setOldValues(JRFillDataset.java:787) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRFillDataset.next(JRFillDataset.java:751) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRBaseFiller.next(JRBaseFiller.java:1422) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:111) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:879) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:801) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:536) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:63) ÂÂÂÂatÂnet.sf.jasperreports.engine.fill.JRThreadSubreportRunner.run(JRThreadSubreportRunner.java:209) ÂÂÂÂ...Â1ÂmoreÂ

[freedd]

no idea ?

is it a limitation from iReport ?

 

http://www.jasperforge.org/sf/go/artf1526?nav=1

 

 

Can I do it by programming ?

 

Post edited by: freedd, at: 2008/06/30 12:42

Post edited by: freedd, at: 2008/06/30 12:42

[lshannon]

I am not sure where the problem is. Can you upload the JRXML for the master and sub? After looking it over I may have a better idea.

[freedd]

Here it is.

The archive includes:

 

- masterReport.jrxml : the master report

- masterReport.csv : Datasource for the master

- subReport1.jrxml : subreport

- csvsousrapp1.csv : datasource for subreport

 

The master receive CodeDossier as prompt parameter in order to apply a filter.

 

I've tested it by programming and it works : the report print.

But under iReport, I have the error message as above. (unknown column name).

 

Thank you for your help.

Post edited by: freedd, at: 2008/07/01 07:12

[freedd]

sorry here is the attached file [file name=iReportTest.zip size=18173]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/iReportTest.zip[/file]

Post edited by: freedd, at: 2008/07/01 07:24

[freedd]

nobody knows ?

[lshannon]

Sorry for the delay, let me look over your report and see if I can figure something out. Stand by...

[lshannon]

Well the first thing I noticed is the delimiter is '#'.

 

In your subreport datasource expression you create a new instance of the CSV Datasource. The default delimiter for the datasource (unless I misread the docs) is a ','.

 

That might account for some of the behavior you are seeing. Once I get a chance I am going to try converting your subreport datasource to use commas and try running the report like that (also you might be able to set the delimiter in the datasource expression, although it doesn't look like any of the constructors do this).

 

Unfortunately I have own report issue to resolve today so I need to get back to those :-)

 

Sorry I have not been of much help, I will try and look at this at some point later today.

[freedd]

Thanx,

 

I will try to change the delimiter to ',' (I've already try ';', same matter).

If it works, I'll change the delimiter by jasperETL before using iReports.

[freedd]

You were right:

 

The CSV files need a comma as delimiter, but also that columns are named COLUMN_0, COLUMN_1, COLUMN_2, ... when the datasource is declared as a new instance in iReport property of the subreport.

 

But I can't change the delimiter in my CSV files (comma is used in string text) and I would like to use my own columns names.

 

Is there a way to use theses methods on the JRCsvDataSource in iReport before the rendering of the subReport ?

- setFieldDelimiter(char fieldDelimiter)

- setColumnNames(java.lang.String[] columnNames)

[lshannon]

I think what you might have to do is construct the datasource in a external java class.

 

The method returning it needs to be public and static.

 

Then for the datasource expression you can just call that method.

 

Perhaps someone else might think of an easier way.

 

Hope this helps.

[peterdp]

The reason why Ireport doesn't know the column names is because you haven't declared that there are columnheaders with that specific name. Like Ishannon says, you'll have to create your own Java class.  Follow these steps:

 

* Define your own class; put the jasperreports-jar to your classpath of your IDE:

 

import java.io.File;

import java.io.FileNotFoundException;

import net.sf.jasperreports.engine.data.JRCsvDatasource

 

public class CsvDataSource extends JRCsvDataSource{

     public CsvDataSource(File file, char fieldDelimiter, String recordDelimiter, boolean useFirstRowAsHeader) throws FileNotFoundException{

     super(file);

     setFieldDelimiter(fieldDelimiter);

     setRecordDelimiter(recordDelimiter);

     setUseFirstRowAsHeader(useFirstRowAsHeader);

}

}

 

* Put your class to the classpath of Ireport

 

* Create a parameter to use as the subreport Data Source Expression:

 

Parameter Class Type: here you put your selfmade classtype: my.package.CsvDataSource

Default Value Expression: new my.package.CsvDataSource(new File($P{SUBREPORT_DIR} + my_csv.csv), ';', "t", true)

 

As you can see, my fielddelimiter is ; and my recorddelimiter is a tab.

 

This should work ...

 

Have fun

Peter

 

Post Edited by peter de per at 04/12/09 20:07

[saran1097]

 Hi Peter,

I follwed your instructions to create the custom CSVDataSource - but when I compile my reports after adding the PARAMETER sepecified by you, I keep running into a compilation error: 

net.sf.jasperreports.engine.JRRuntimeException: java.lang.ClassNotFoundException: com.reports.datasource.CsvDataSource

Can you please provide me the Parameter details specified as "* Create a parameter to use as the subreport Data Source Expression:" in your post.

I have a unique situation: where my main report is a collection of JavaBeans and Sub report has to be filled with a CSV data source already available in file system. 

If only we can specify the "Use First Row as Header Row" somehow in the subreport's data source expression all this problems will be solved.

Thanks for your time and help,

Saran

[saran1097]

Hi,

I have gotten around the Class not found exception...now when I fill the reports from my web application, this is what I run into.

 

Error evaluating expression : 

Source text : new com.reports.datasource.CsvDataSource(new java.io.File("C:\\csvFiles\\Activity.CSV"), ',', true)

 

net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression : 

Source text : new com.reports.datasource.CsvDataSource(new java.io.File("C:\\csvFiles\\Activity.CSV"), ',', true)

at net.sf.jasperreports.engine.fill.JREvaluator.evaluate(JREvaluator.java:203)

at net.sf.jasperreports.engine.fill.JRCalculator.evaluate(JRCalculator.java:589)

at net.sf.jasperreports.engine.fill.JRCalculator.evaluate(JRCalculator.java:557)

at net.sf.jasperreports.engine.fill.JRFillElement.evaluateExpression(JRFillElement.java:867)

at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluateSubreport(JRFillSubreport.java:353)

at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluate(JRFillSubreport.java:272)

at net.sf.jasperreports.engine.fill.JRFillElementContainer.evaluate(JRFillElementContainer.java:257)

at net.sf.jasperreports.engine.fill.JRFillBand.evaluate(JRFillBand.java:468)

at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillColumnBand(JRVerticalFiller.java:2037)

at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillDetail(JRVerticalFiller.java:761)

at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:271)

at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:129)

at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:903)

at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:832)

at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:84)

at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:624)

[joniaraujo]

I try this, but witout success. The subreport open, but with blank data

new net.sf.jasperreports.engine.data.JRCsvDataSource(new File("C:\Users\Admin\Documents\reports\Analisys\Stats.csv")).setFieldDelimiter(";".charAt(0))

[joniaraujo]

I try this, but witout success. The subreport open, but with blank data
new net.sf.jasperreports.engine.data.JRCsvDataSource(new File("C:\Users\Admin\Documents\reports\Analisys\Stats.csv")).setFieldDelimiter(";".charAt(0))

[garreth.golding]

I've a post on how to do this here -> http://stackoverflow.com/questions/16741597/jasper-report-csv-datasource-in-table-component/39107145#39107145

 

[vanhoai.lampart]

i solved this problem. i pushed this solution to git.

You can see this here: https://github.com/hoai/jasper-CSV-datasource-for-subreport