nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Need a specific, CrossTab-like report

BirtWidi

I need a specific report which looks similar to a cross tab report - but it isnÂ´t a cross tab.I need a group of rows for each customer and a group of columns for each month - thatÂ´s like each other cross tab.The summary field does not contain a aggregation of the matching values. Instead of them it contains a list of all matching values. (Example: see attachment) How can I create such a report? Regards,Widi

Find more posts tagged with

Comments

micajblock

The easiest way I can think of is to have a table for the customers with 12 columns. In each cell you have another table (or list with bindings so that it filters based on the customer and month.

kclark

I'm not a sql expert but this might help too.<pre class="_prettyXprint _lang-">select if(month(payment_date) = 1, amount, null) as Jan, if(month(payment_date) = 2, amount, null) as Feb, if(month(payment_date) = 3, amount, null) as Mar, if(month(payment_date) = 4, amount, null) as Apr, if(month(payment_date) = 5, amount, null) as May, if(month(payment_date) = 6, amount, null) as Jun, if(month(payment_date) = 7, amount, null) as July, if(month(payment_date) = 8, amount, null) as Aug, if(month(payment_date) = 9, amount, null) as Sept, if(month(payment_date) = 10, amount, null) as Oct, if(month(payment_date) = 11, amount, null) as Nov, if(month(payment_date) = 12, amount, null) as Decm, customer_idfrom paymentgroup by payment_date</pre>The only problem with that is the empty rows it creates. But if you can get rid of those empty rows and group the cable on the customer number you should have the output your looking for.

GLO_FR

kclark's answer is good but the sql syntax is not the right one.Try this select 
customer_id, 
myRow, 
sum(case when payMonth = 1 then amount end) as Jan, 
sum(case when payMonth = 2 then amount end) as Feb, 
sum(case when payMonth = 3 then amount end) as Mar, 
sum(case when payMonth = 4 then amount end) as Apr, 
sum(case when payMonth = 5 then amount end) as May, 
sum(case when payMonth = 6 then amount end) as Jun, 
sum(case when payMonth = 7 then amount end) as July, 
sum(case when payMonth = 8 then amount end) as Aug, 
sum(case when payMonth = 9 then amount end) as Sept, 
sum(case when payMonth = 10 then amount end) as Oct, 
sum(case when payMonth = 11 then amount end) as Nov, 
sum(case when payMonth = 12 then amount end) as Dec 
 
FROM (Select customer_id, 
month(payment_date) as payMonth, 
ROW_NUMBER() OVER (ORDER BY customer_id, month(payment_date)) AS myRow, 
amount 
from payment) AS A 
 
GROUP BY customer_id, myRow **So with your data[font="'courier new', courier, monospace;"]Cust01,02/01/13,Value1 
Cust01,02/01/13,Value2 
Cust01,10/01/13,Value12 
Cust02,03/01/13,Value3 
Cust02,05/01/13,Value4 
Cust02,05/01/13,Value5 
Cust03,07/01/13,Value6 
Cust04,03/01/13,Value11 
Cust04,09/01/13,Value8 
Cust04,09/01/13,Value9 
Cust04,09/01/13,Value10[/font] Sub query 1 should give you[font="'courier new', courier, monospace;"]Cust_id | Month | Num of value by custid and month | amount[/font][font="'courier new', courier, monospace;"]

Cust01 | 2 | 1 | Value1 
Cust01 | 2 | 2 | Value2 
Cust01 | 10 | 1 | Value12 
Cust02 | 3 | 1 | Value3 
Cust02 | 5 | 1 | Value4 
Cust02 | 5 | 2 | Value5 
Cust03 | 7 | 1 | Value6 
Cust04 | 3 | 1 | Value11 
Cust04 | 9 | 1 | Value8 
Cust04 | 9 | 2 | Value9 
Cust04 | 9 | 3 | Value10[/font] Than the principal query should give you this[font="'courier new', courier, monospace;"]Cust_id | Num of value by custid and month | Jan | Feb | Mar | Apr | May | Jun | Jul | Sep | Oct | Nov | Dec[/font][font="'courier new', courier, monospace;"]