javapentahopentaho-report-designer

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!


Solution

  • 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.