CODEWITHSUNDEEP
javapentahopentaho-report-designer
Fabricio
Fabricio

Reputation: 63

How to query external DataFactory in Pentaho Reporting

I need to implement the following report in Pentaho Reporting using java:

Student 1
 - Calification 1
 - Calification 2
Student 2
 - Calification 1
 - Calification 2
...

The report receives all the information from the code. It has no access to the database. I am implementing this using Subreports. (This is a simplified example. I have other fields in the student, so I cannot use grouping)

I tried the following approach:

Create two Table Models for the DataFactory.

TypedTableModel model = new TypedTableModel();
model.addColumn("studentId", String.class);
model.addColumn("calification", String.class);
model.addRow("1", "10");
model.addRow("1", "10");
model.addRow("2", "5");

TypedTableModel model2 = new TypedTableModel();
model2.addColumn("studentId", String.class);
model2.addColumn("studentName", String.class);
model2.addRow("1", "Name 1");
model2.addRow("2", "Name 2");

TableDataFactory dataFactory = new TableDataFactory();
dataFactory.addTable("master-query", model2);
dataFactory.addTable("subreport-query", model);

Then, in the master report, I set query.name = master-query in the subreport, I set query.name = subreport-query

What I get is:

Name 1
10
10
5

Name 2
10
10
5

In the subreport I managed to import the param "studentId" from the master report. I need to filter the califications by studentId in order to display only the califications from each student. I need this:

Name 1
10
10

Name 2
5

How can I accomplish that? Can I implement a query on "subreport-query"? How?

All the examples on the web query to JDBC connections using ${studentId}, but I need to query an external DataFactory.

Thanks!

Upvotes: 1

Views: 270

Answers (1)

Fabricio
Fabricio

Reputation: 63

I solved the problem in a different way.

Java dataFactory code:

TypedTableModel cal1= new TypedTableModel();
cal1.addColumn("calification", String.class);
cal1.addRow("10");
cal1.addRow("10");

TypedTableModel cal2= new TypedTableModel();
cal2.addColumn("calification", String.class);
cal2.addRow("5");

TypedTableModel model = new TypedTableModel();
model.addColumn("studentId", String.class);
model.addColumn("studentName", String.class);
model.addColumn("califications", TypedTableModel.class);
model.addRow("1", "Name 1", cal1);
model.addRow("2", "Name 2", cal2);

TableDataFactory dataFactory = new TableDataFactory();
dataFactory.addTable("master-query", model);

Pentaho subreport:

1) Imported the parent parameter called "califications". (Parameters - Import parameters)

2) Added a Scriptable Dataset with the following code (beanshell):

return dataRow.get("califications");

3) Right click in Scriptable Query -> Select query

Now the califications TypedTableModel is accesible from the subreport.

Upvotes: 1

Related Questions