Crosstab Problem with blank records

[dohertm2]

Code:

Hi,



I am trying to use a crosstab to fill a report of student and grades.





My basic query looks like this:



select    class_name,

          student_name,

             module_name

where     ...

          ...

order by  class_name,

          student_name,

             module_name



I am grouping by class_name, so when the students are doing the same subjects the report is correct and

looks like:

        ModuleA    ModuleB    ModuleC  

Jim          P           M          P

Sally        F           D          F



My problem is when a student is not doing one of the module, for example Jim is not doing ModuleB. The results

look like



        ModuleA    ModuleC     ModuleA    ModuleB    ModuleC  

Jim          P           P

Sally                                F           D         F



This isn't correct, I want only a blank where they are not doing that module.



Is there a way to do this with jasper, or do I have to change the SQL to have an outer join?



Any ideas?

[kchaudhry]

When  you go to crosstab properties > row and column groups, what groups are listed there?

[dohertm2]

For the row-group it "student_fullname" and for the column group it is "module".

 

Is this correct?

[kchaudhry]

In your previous post you mentioned that you were grouping by "class_name". Do you mind posting your jrmxl?

[dohertm2]

Yes, i've attached it to this reply.

The query is a bit more complicated in reality as I need to join from several but the fields and order by are the same.

 

Thanks a lot.

[kchaudhry]

Hmm ... I don't see anything wrong with the report (probably not the answer that you're looking for) ... but you might have to use the outer join in your query.

[dohertm2]

OK, I was hoping there was a handy way through to do it. I try to change the SQL.

 

Thanks a lot for your help,

Martin