Create and use CSV data in a subreport

Question

Background

The Jaspersoft community forum is riddled with unanswered questions about how to use a CSV data source with a report.

Problem

When using Jaspersoft Studio, it is convenient to use a local file as a data source. The instructions for accomplishing this task are piecemeal at best and incomplete at worst.

Question

What are the steps required to use a CSV file (data source) with a main report and have that data be used by a subreport?

Answer 1

Note: This solution drops the first result from the list in the subreport, which requires a workaround to resolve, which is not described in this answer.

Overview

The major steps include:

1. Create CSV file
2. Create data adapter
3. Create main report and subreport
4. Connect data set
5. Create report fields
6. Connect subreport

Create CSV file

Create the CSV file as follows:

1. Open a text editor.
2. Paste the following:

   city,age,name,address,status
   "Dallas",47,"Janet Fuller","445 Upland Pl.","Trial"
   "Lyon",38,"Andrew Heiniger","347 College Av.","Active"
   "Dallas",43,"Susanne Smith","2 Upland Pl.","Active"
   "Berne",22,"Bill Ott","250 - 20th Ave.","Active"
   "Boston",32,"Michael Ott","339 College Av.","Trial"
   "Dallas",4,"Sylvia Ringer","365 College Av.","Active"
   "Boston",23,"Julia Heiniger","358 College Av.","Active"
   "Chicago",39,"Mary Karsen","202 College Av.","Active"
   "Dallas",40,"Susanne Miller","440 - 20th Ave.","Trial"
   "Berne",9,"James Schneider","277 Seventh Av.","Active"
   "Dallas",36,"John Steel","276 Upland Pl.","Suspended"
   "Chicago",35,"George Karsen","412 College Av.","Suspended"
   "Dallas",37,"Michael Clancy","19 Seventh Av.","Deleted"
   "Lyon",2,"Anne Miller","20 Upland Pl.","Active"
   "Dallas",0,"Laura Steel","429 Seventh Av.","Active"
   "Lyon",28,"Susanne White","74 - 20th Ave.","Deleted"
   "Paris",5,"Laura Miller","294 Seventh Av.","Active"
   "Lyon",17,"Laura Ott","443 Seventh Av.","Active"
   "New York",46,"Andrew May","172 Seventh Av.","Active"
   "New York",44,"Sylvia Ott","361 College Av.","Active"
   "Dallas",19,"Susanne Heiniger","86 - 20th Ave.","Active"
   "Chicago",11,"Julia White","412 Upland Pl.","Active"
   "Dallas",10,"Anne Fuller","135 Upland Pl.","Active"
   "New York",41,"Bill King","546 College Av.","Deleted"
   "Oslo",45,"Janet May","396 Seventh Av.","Active"
   "Paris",18,"Sylvia Fuller","158 - 20th Ave.","Trial"
   "San Francisco",48,"Robert White","549 Seventh Av.","Active"
   "Paris",25,"Sylvia Steel","269 College Av.","Suspended"
   "San Francisco",7,"James Peterson","231 Upland Pl.","Active"
   "Oslo",42,"Robert Ott","503 Seventh Av.","Trial
   

3. Save the file as data.csv to a known location.

The CSV file is created.

Create Data Adapter

Create the data adapter as follows:

1. Start Jaspersoft Studio.
2. Open the Project Explorer.
3. Right-click the project.
4. Click New → Data Adapter.
5. Set File name to: csv.xml.
6. Click Next.
7. Click CSV File.
8. Click Next.
9. Set Name to: CSV
10. Click File.
11. Browse to and select data.csv created in the previous section.
12. Click Get column names from the first row of the file.
13. Click Finish.

The data adapter is created.

Create main report and subreport

This section describes how to create the reports.

Create main report

Create the main report as follows:

1. Click File → New → Jasper Report.
2. Select Blank A4.
3. Click Next.
4. Set File name to: main.jrxml
5. Click Next.
6. Set Data Adapter to: csv.xml.
7. Click Finish.

The main report is created.

Note that the data source is not yet connected to the main report. This is where people run into trouble. Assigning the CSV data source does not mean that the report can "query" the data. An additional step is required, described later in this answer.

Create subreport

Create the subreport as follows:

1. Click File → New → Jasper Report.
2. Select Blank A4.
3. Click Next.
4. Set File name to: subreport.jrxml
5. Click Finish.

The subreport is created.

Create data set

Create a data set from the CSV file as follows:

1. Switch to the main report tab (main.jrxml) in Jaspersoft Studio.
2. Click the Design tab.
3. In the Outline tab, right-click main.
4. Click Create Dataset.
5. Set Dataset name to: csv
6. Click Next.
7. Set Data Adapter to: csv.xml.
8. Click Next.
9. Click >> to shuttle the fields to the right.
10. Click Finish.

The CSV data set is created.

Connect data set

1. In the Outline, expand main.
2. Right-click csv.
3. Click Connect to Main Dataset.
4. Set Data Adapter Language to: csv
5. Click Finish.

The data set is connected.

Create report fields

Create fields in the main report as follows:

1. In the Outline, right-click main.
2. Click Dataset and Query.
3. Set Language to: csv.
4. Click Read Fields.
5. Click OK.

The report fields are created.

Connect subreport

Connect the subreport to the main report as follows:

1. Drag a Subreport basic element onto the Title band of the main report.
2. Click Select a report file.
3. Choose Workspace resource.
4. Click Browse
5. Select subreport.jrxml.
6. Click OK.
7. Click OK again.
8. Click Finish.
9. Right-click the subreport element.
10. Click Size to Container → Fit Both.
11. In the Properties tab, click Subreport.
12. Switch to the main.jrxml report.
13. In the Outline tab, expand main → Fields
14. Select the fields (do not use the CSV fields)
15. Right-click the selected fields.
16. Click Copy.
17. Switch to the subreport.jrxml report.
18. Right-click Fields.
19. Click Paste.
20. Select the fields.
21. Drag and drop the fields onto the Detail band.
22. Return to the main.jrxml report.
23. Set Data Source Expression on the subreport to: $P{REPORT_DATA_SOURCE}.
24. Click Save All.
25. Click Build.
26. Run the main report.

The output resembles: