Pentaho SQL Trick

Om man i Pentaho vill jämföra t.ex. årets försäljning med tidigare år så går det utmärkt i Analyzer, men inte i Report Designer eller Interactive Reports eftersom de inte tillåter att man lägger in fält horizontellt. Ett sätt att gå runt detta är att trolla lite med SQL:en istället så att försäljning per år kommer i olika kolumner i resultatsettet från databasen. Följande är ett exempel med Pentahos Steel Wheels där vi hämtar alla kunders försäljning på ett sådant sätt att det går att jämföra med tidigare år samt att rapporten blir parameterstyrd på år.

SELECT
  t.customernumber,
  SUM(t.totalprice),
  SUM(t.totalprice_ly1),
  SUM(t.totalprice_ly2)
FROM (
  SELECT
    customernumber AS customernumber,
    totalprice AS totalprice,
    0 AS totalprice_ly1,
    0 AS totalprice_ly2
  FROM ORDERFACT
  WHERE year_id = ${year_id}
  UNION ALL
  SELECT
    customernumber AS customernumber,
    0 AS totalprice,
    totalprice AS totalprice_ly1,
    0 AS totalprice_ly2
  FROM ORDERFACT
  WHERE year_id = ${year_id} - 1
  UNION ALL
  SELECT
     customernumber AS customernumber,
    0 AS totalprice,
    0 AS totalprice_ly1,
    totalprice AS totalprice_ly2
  FROM ORDERFACT
  WHERE year_id = ${year_id} - 2
) AS t
GROUP BY
  t.customernumber
ORDER BY
  t.customernumber

Tricket är alltså att göra en select för varje år som man vill titta på. Varje select innehåller i sin tur en kolumn för varje år, men där vi nollar de övriga åren. Sedan summeras det upp per kund och alla noll värden vi satt kommer inte att påverka slutsumman. Om man istället skulle vilja jämföra månader och formatet skulle vara yyyyMM skulle where villkoret kunna se ut någonting i stil med

WHERE month_no = CASE WHEN MOD(${month_no},100) <= X THEN (${month_no} - 100) + (12-X) ELSE ${month_no} - X END

Där X är antalet månader tillbaka i tiden, dvs X=1 förra månaden, X=2 för två månader sedan, osv.