Jump to content
Changes to the Jaspersoft community edition download ×

CSV datasource for subreport


arbitary

Recommended Posts

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!!

Link to comment
Share on other sites

  • Replies 23
  • Created
  • Last Reply

Top Posters In This Topic

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?

Link to comment
Share on other sites

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}"

Link to comment
Share on other sites

  • 1 year later...

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)?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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Â

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 9 months later...

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
Link to comment
Share on other sites

  • 2 years later...

 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

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

  • 2 years later...
  • 2 years later...
  • 2 years later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...