Jasper Reports Crosstab Query

Question

Hi,

I'm using Jasper Reports/iReports crosstabs to create a matrix of student and results.

So for example Jim is doing subjects A, B, C and Sally is doing A, C

What I want is something like:

    Subj-A   Subj-B   Subj-C

Jim P M D Sally D D

But as my SQL orders by name then subject I get:

    Subj-A   Subj-B   Subj-C   Subj-A   Subj-C

Jim P M D
Sally D D

As you can see in the above the results are correct but the formatting is woeful. Is there anyway I can generate the reports to use names and subject only once and filling in the values from here?

This probably isn't clear.

Answer 1

To follow-up on this.

JasperReports is primarily used to represent the data so the work here needs to be done here via the SQL.

In a case where there is a crosstab of X vs Y we may need to use a cross-join which bacically compute all the combination of X subject and Y students.

After the cross-join we can use an outer-join on the candidate details to find there results. After this it is a basic matter of representation in Jasper.