Hi:
I have a Cuba 7.2 application with the reports-addon configured. I have an unusual Postgres SQL report that generates a variable number of columns. I use the STRING_AGG function to generate the data, similar to this (leaving out a bunch of CTEs):
select c.id || ',' ||
'"' || coalesce(c.name,'') || '",' ||
'"' || coalesce(c.address1,'') || '",' ||
'"' || coalesce(c.address2,'') || '",' ||
'"' || coalesce(c.city,'') || '",' ||
'"' || coalesce(c.province,'') || '",' ||
'"' || coalesce(c.postalcode,'') || '",' ||
n.mlgcount || ',' ||
n.totcost || ',' ||
c.numpurch || ',' ||
n.totsales || ',' ||
n.netpl || ',' ||
STRING_AGG(
'"' || mlg.mlgcode || '",' ||
'"' || s.sourcecode || '",' ||
'"' || l.listname || '",' ||
'"' || to_char(mlg.maildate,'YYYY-MM-DD') || '",' ||
'"' || p.category || '",' ||
'"' || p.prodcode || '",' ||
coalesce(v.totpurch,0) || ',' ||
coalesce(v.totcost,0) + coalesce(mc.mlgcost,0) || ',' ||
coalesce(v.netpl,0) - coalesce(mc.mlgcost,0),
','
order by mlg.maildate
)
from customers c
join cusnetpl n on n.pasid = c.id
join mailfile m on m.cus_id = c.id
join sourcecodes s on s.sourcecode = m.source
join lists l on l.id = s.lst_id
join mailings mlg on mlg.id = s.mlg_id
join offers o on o.id = mlg.offer_id
join products p on p.id = o.product_id
join mc on mc.mlgid = mlg.id
left outer join ordervalues v on v.pasid = c.id and v.source = m.source
group by c.id, c.name, c.address1, c.address2, c.city, c.province, c.postalcode, n.mlgcount, n.mlgcost, c.numpurch, n.totsales, n.totcost, n.netpl
order by c.id;
Is there any way to integrate something like this with report addon so that my users can generate it on demand? I currently generate this with a shell script. I then have shell commands that generate the appropriate header row.
Is this a use case for a Class-Defined Template of some kind? Any examples on how to implement a Class-Defined Template that executes an SQL query and outputs the bytes as an XSLX, or at least CSV?
