dohertm2 Posted March 18, 2009 Share Posted March 18, 2009 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? Link to comment Share on other sites More sharing options...
kchaudhry Posted March 18, 2009 Share Posted March 18, 2009 When you go to crosstab properties > row and column groups, what groups are listed there? Link to comment Share on other sites More sharing options...
dohertm2 Posted March 18, 2009 Author Share Posted March 18, 2009 For the row-group it "student_fullname" and for the column group it is "module". Is this correct? Link to comment Share on other sites More sharing options...
kchaudhry Posted March 18, 2009 Share Posted March 18, 2009 In your previous post you mentioned that you were grouping by "class_name". Do you mind posting your jrmxl? Link to comment Share on other sites More sharing options...
dohertm2 Posted March 18, 2009 Author Share Posted March 18, 2009 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. Link to comment Share on other sites More sharing options...
kchaudhry Posted March 18, 2009 Share Posted March 18, 2009 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. Link to comment Share on other sites More sharing options...
dohertm2 Posted March 18, 2009 Author Share Posted March 18, 2009 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now