【问题】
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 |
【回答】
以下 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
除了用 SQL 之外,还可以用 SPL 来准备报表数据源,且 SPL 可用于数据来源不是数据库的场景:
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 ) |
结果如下:
对于 Jasper 中复杂的数据准备,很多都可以用 SPL 来实现,关于 SPL 函数的用法介绍可参考在线文档《函数参考》。