I need to have the parent key on all child records when using rollup. Is there any easy way to solve this using alasql and rollup?
It will be used to show/hide child rows in reports.
There are multiple levels of subtotals in the report. One child can have new children.
Is it possible to have more than 3 parameters in the aggregator function?
Updated 18.05.2016:
I want to create a account report with drilldown. I have managed to get it working by using custom functions to get level and leveldescription. leveldescription and level is custom function to get correct text on each aggregation. filterDimension is a custom function to filter on an array of values.
SELECTGROUPS, ROLLUPGROUPS and FIELDSGROUPS is replaced by custom grouping done by users.
This solves the main problem of showing and hiding children. Now I want to have all details for the last aggregation in one field. I have now used custom function "CHILDREN(a._) as children" based on this solution https://github.com/agershun/alasql/issues/571
But I only want details to show on the highest level of aggregations. Now I get children for each level of aggregation.
Rollup levels will be based on which fields (R1 .. R12) the user want to drilldown on before showing the details for the last level.
alasql.aggr.CHILDREN = function(val, acc, stage) {
console.log(stage);
if(stage == 1 || stage == 2) {
if(typeof acc == 'undefined') {
if(val && Object.keys(val).length > 0) {
return [val];
} else {
return;
}
} else {
acc.push(val);
return acc;
}
} else {
return acc;
}
};
SET @data = ?;
SET @year = ?;
SET @r1s = ?;
with reportdefinition as
(
select * from @data
), data as
(
SELECT * FROM (
SELECT c1.yr,
c1.acno,
c1.acnm,
c1.webpg,
c1.txt,
c1.r1, c1.r1nm,
c1.r2, c1.r2nm,
c1.r3, c1.r3nm,
c1.r4, c1.r4nm,
c1.r5, c1.r5nm,
c1.r6, c1.r6nm,
c1.r7, c1.r7nm,
c1.r8, c1.r8nm,
c1.r9, c1.r9nm,
c1.r10, c1.r10nm,
c1.r11, c1.r11nm,
c1.r12, c1.r12nm,
CASE WHEN pr = 1 AND c1.bgno = 0 THEN c1.acam ELSE 0 END as jan,
CASE WHEN pr = 2 AND c1.bgno = 0 THEN c1.acam ELSE 0 END as feb,
CASE WHEN pr = 3 AND c1.bgno = 0 THEN c1.acam ELSE 0 END as mar,
CASE WHEN pr = 4 AND c1.bgno = 0 THEN c1.acam ELSE 0 END as apr,
CASE WHEN pr = 5 AND c1.bgno = 0 THEN c1.acam ELSE 0 END as may,
CASE WHEN pr = 6 AND c1.bgno = 0 THEN c1.acam ELSE 0 END as jun,
CASE WHEN pr = 7 AND c1.bgno = 0 THEN c1.acam ELSE 0 END as jul,
CASE WHEN pr = 8 AND c1.bgno = 0 THEN c1.acam ELSE 0 END as aug,
CASE WHEN pr = 9 AND c1.bgno = 0 THEN c1.acam ELSE 0 END as sep,
CASE WHEN pr = 10 AND c1.bgno = 0 THEN c1.acam ELSE 0 END as oct,
CASE WHEN pr = 11 AND c1.bgno = 0 THEN c1.acam ELSE 0 END as nov,
CASE WHEN pr = 12 AND c1.bgno = 0 THEN c1.acam ELSE 0 END as dec,
CASE WHEN c1.bgno = 0 THEN c1.acam ELSE 0 END tot,
CASE WHEN pr = 1 AND c1.bgno = 2015 THEN c1.acam ELSE 0 END as jan_bg,
CASE WHEN pr = 2 AND c1.bgno = 2015 THEN c1.acam ELSE 0 END as feb_bg,
CASE WHEN pr = 3 AND c1.bgno = 2015 THEN c1.acam ELSE 0 END as mar_bg,
CASE WHEN pr = 4 AND c1.bgno = 2015 THEN c1.acam ELSE 0 END as apr_bg,
CASE WHEN pr = 5 AND c1.bgno = 2015 THEN c1.acam ELSE 0 END as may_bg,
CASE WHEN pr = 6 AND c1.bgno = 2015 THEN c1.acam ELSE 0 END as jun_bg,
CASE WHEN pr = 7 AND c1.bgno = 2015 THEN c1.acam ELSE 0 END as jul_bg,
CASE WHEN pr = 8 AND c1.bgno = 2015 THEN c1.acam ELSE 0 END as aug_bg,
CASE WHEN pr = 9 AND c1.bgno = 2015 THEN c1.acam ELSE 0 END as sep_bg,
CASE WHEN pr = 10 AND c1.bgno = 2015 THEN c1.acam ELSE 0 END as oct_bg,
CASE WHEN pr = 11 AND c1.bgno = 2015 THEN c1.acam ELSE 0 END as nov_bg,
CASE WHEN pr = 12 AND c1.bgno = 2015 THEN c1.acam ELSE 0 END as dec_bg,
CASE WHEN c1.bgno = 2015 THEN c1.acam ELSE 0 END tot_bg
FROM table1 AS c1
WHERE c1.yr = @year.id
AND c1.pr <= 5
AND (c1.bgno = 0 or c1.bgno = 2015)
AND filterDimension("id", @r1s, r1)
)
), result as
(
select *
from data as d1
join reportdefinition as d2 on d1.acno >= d2.fromvalue and d1.acno <= d2.tovalue
)
SELECT *, leveldescription(group1, group2, group3[FIELDSGROUPS]) as description, level(group1, group2, group3[FIELDSGROUPS]) as level
FROM (
SELECT rownum() as id, FIRST(group1) as group1, group2, group3, [SELECTGROUPS]
SUM(jan) as jan,
SUM(feb) as feb,
SUM(mar) as mar,
SUM(apr) as apr,
SUM(may) as may,
SUM(jun) as jun,
SUM(jul) as jul,
SUM(aug) as aug,
SUM(sep) as sep,
SUM(oct) as oct,
SUM(nov) as nov,
SUM(dec) as dec,
SUM(tot) as tot,
SUM(jan_bg) as jan_bg,
SUM(feb_bg) as feb_bg,
SUM(mar_bg) as mar_bg,
SUM(apr_bg) as apr_bg,
SUM(may_bg) as may_bg,
SUM(jun_bg) as jun_bg,
SUM(jul_bg) as jul_bg,
SUM(aug_bg) as aug_bg,
SUM(sep_bg) as sep_bg,
SUM(oct_bg) as oct_bg,
SUM(nov_bg) as nov_bg,
SUM(dec_bg) as dec_bg,
SUM(tot_bg) as tot_bg,
CHILDREN(a._) as children
FROM result AS a
GROUP BY ROLLUP(group2, group3[ROLLUPGROUPS])
ORDER BY group2, group3[FIELDSGROUPS]
)
ORDER BY CASE WHEN id = 1 THEN 2 ELSE 1 END, id;
