【Question】

I have these rows from the SQL:

'201401';'t';2000;'F'

 

'201401';'t';9000;'M'

 

'201401';'f';12000;'F'

 

'201401';'f';7000;'M'

 

'201402';'t';2200;'F'

 

And I want to show them in on single jasper row:

Periodo	Titulares	(sex   M/F)	Familiares	(sex   M/F)	Total
201401	11000	(9000/2000)	19000	(7000/12000)	31000

 

【Answer】

Here’s the solution in SQL:

select periodo,

 

 tM+tF Titulares,

 

 CONCAT('(',tM,'/',tF,')') tsex,

 

 fM+fF Familiaresa,

 

 CONCAT('(',fM,'/',fF,')') fsex,

 

 tM+tF+fM+fF total

 

from(

 

 select periodo,

 

  sum(case when channel='t' and sex='M' then visit else 0 end)tM ,

 

  sum(case when channel='t' and sex='F' then visit else 0 end)tF ,

 

  sum(case when channel='f' and sex='M' then visit else 0 end)fM ,

 

  sum(case when channel='f' and sex='F' then visit else 0 end)fF

 

 from siteData1 group by periodo

 

) t

 

	A
1	=myDB1.query("select * from   siteData1").record([201403,"t",4000,"F"])
2	=A1.group(periodo; (a=~.align(["tM","tF","fM","fF"],channel+sex).(visit),a(1)+a(2)):Titulares, "("+string(a(1))+"/"+string(a(2))+")":tsex, a(3)+a(4):Familiares, "("+string(a(3))+"/"+string(a(4))+")":fsex, sum(a):total   )

The result:

SPL can help prepare complex data source in Jasper. Here are the SPL functions: Function Reference.