【Question】

I am currently creating a report (jrxml) using Jaspersoft Studio and using Amazon Redshift as data source. I have a SQL script that computes base on a series of data. Below is an example:

 

select date_month_yr, sum(total_count), sum(total_nerwork), (sum(total_count)/lag(sum(total_count)) - 1)*100 as per_total_count_change from ( select date_month_yr, total_count, total_count from tb where …. ….. union all select date_month_yr, total_count, total_count from tb where ….)

 

The result would be:

date\_month\_yr|sum(total\_count)|sum(total\_nerwork)| per\_total\_count_change

 

2015-05_ _ __|1234__ _ _ _ _ _ _ |4321_ _ _ _ _ _ _ _ _|

 

2015-06_ _ __|2345__ _ _ _ _ _ _ |8642_ _ _ _ _ _ _ _ _|90.03

 

2015-07_ _ __|3456__ _ _ _ _ _ _ |9876_ _ _ _ _ _ _ _ _|47.37

 

......

 

It’s not possible to use the above result as data for the Jasper crosstab of that should generate similar to:

 

                       |2015-05|2015-06|2015-07

 

sum(total\_count)\_ _ _ |1234_ _|2345_ _ |3456

 

sum(total\_nerwork)\_ _ |4321_ _ |8642_ _ |9876

 

per\_total\_count\_change|\_ _ _ _ |90.03_ _|47.37

 

What I initially think was to use UNNEST:

 

select date\_month\_yr, unnest(array\['sum(total\_count)', 'sum(total\_nerwork)', 'per\_total\_count\_change'\])AS "parameters", unnest(array\[sum(total\_count), sum(total\_nerwork), (sum(total\_count)/lag(sum(total_count)) - 1)*100\]) AS "Values" from (.......)

 

However Amazon Redshift doesn’t support UNNEST. Is there anyone who encountered this problem and has a solution? Or any idea? Any help would be highly appreciated. Thanks!

 

【Answer】

This is a simple transposition. Oracle supports PIVOT. But Amazon Redshift doesn’t. In this case, you can prepare report data source in SPL (Structured Process Language). Below is the standard SPL code for performing transposition over a database table:

	A
1	$select * from tb
2	=create(subtotal,${A1.(date_month_yr).concat@c()})
3	>A1.fno().to(2,).run(A2.record(A1.fname(~)|A1.field(~)))

A1: Retrieve data in SQL;

A2: Create an empty resulting table sequence by date;

A3: Write A1’s data to the table sequence in loop.

The result:

You can connects to esProc in JasperReport via JDBC. The method of calling an SPL script is the same as that of calling a stored procedure. For more details, see How to Call an SPL Script in JasperReport.