Chaning report format.

[ktrinad]

By: Navaneetha Krishnan J - navaneethanj

Chaning report format.

2004-01-22 03:57

Hi,

 

I have a report in below format.

 

Student

Name Subject Marks

A VB 89

A C++ 59

A Java 98

B VB 78

B C++ 89

C VB 56

C Java 89

 

Can i display it in the below format.

 

Student

Name VB C++ Java

A 89 59 98

B 78 89 -

C 56 - 89

 

Thanks & Regards

Navaneethan.

 

 

By: Navaneetha Krishnan J - navaneethanj

Changing report format.

2004-01-22 04:02

Dear Teodor,

 

Format was not proper in the previous thread.

 

I have a report in below format.

 

Student

Name Subject Marks

A VB 89

A C++ 59

A Java 98

B VB 78

B C++ 89

C VB 56

C Java 89

 

Can i display it in the below format.

 

Student

Name VB C ++ Java

A 89 59 98

B 78 89 -

C 56 - 89

 

Thanks & Regards,

Navaneethan.

 

 

By: Vincent Hikida - vhikida

RE: Changing report format.

2004-01-22 11:35

Navaneethan,

 

What you are describing is a cross tab report. I believe that the documentation specifically says that cross tab reports are not supported although I think that I read that there is a workaround.

 

If you know that VB, C++, and Java or some known set are all the known columns you can use SQL to the data in this format for Jasper to process as in the usual type of report. In Oracle SQL you would do something like the following (I don't know your exact table structure)

 

SELECT student_name

, SUM(DECODE

(language,'VB',grade,0)

) vb

, SUM(DECODE

(language,'C++',grade,0)

) cplus

ETC ETC

FROM grades

 

If you are using ANSI SQL, then you would use the CASE statement instead of DECODE. This assumes of course that you are using SQL.

 

If you are in a situation where you don't know before hand what the languages are in the tables, this will not work.

 

 

By: Navaneetha Krishnan J - navaneethanj

RE: Chaning report format.

2004-01-22 12:20

Hi,

 

Thank you very much for your answers.

 

But if i execute the query it is throughing the below error. I am using MySQL 4.0.X.

 

ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ' grade, 0)), SUM(DECODE(language,'C++',grade,0)

 

Thanks,

Navaneethan.

 

 

By: Vincent Hikida - vhikida

RE: Chaning report format.

2004-01-22 13:56

Navaneethan,

 

DECODE is only supported in Oracle. I don't know much about MySQL but most databases including Oracle support the CASE statement. I've never used it but I've been planning to learn the syntax which I just looked up. You can try

 

SUM

(

CASE language

WHEN 'VB' THEN 1

ELSE 0

END

) vb,

SUM

(

CASE language

WHEN 'C++' THEN 1

ELSE 0

END

) cplus,

ETC ETC .............

 

 

Please let me know how this works out in MySQL because I am curious. If this doesn't work using SQL then you would have to create an analogous JRDataSource object (I think) in Java.

 

 

 

By: Navaneetha Krishnan J - navaneethanj

RE: Changing report format.

2004-01-22 20:40

Thank you Vincent.

 

CASE works perfectly in MySQL.

 

Thanks for your help again.

Navaneethan.