Cross Tab in a Form

XTab.ninox 

No, the parent table is just a place holder.

On a similar, probably better way, this works well in a formula field

let a := this;
let h := "<table style='width:100%'>";
let b := unique((List order by Row).Row);
h := h + "<tr>";
h := h + "<th>" + a.Name + "</th>";
h := h + "<th>" + item(b, 0) + "</th>";
h := h + "<th>" + item(b, 1) + "</th>";
h := h + "<th>" + item(b, 2) + "</th>";
h := h + "<th>" + item(b, 3) + "</th>";
h := h + "<th>Total</th>";
h := h + "</tr>";
let t1 := 0;
let t2 := 0;
let t3 := 0;
let t4 := 0;
let tt := 0;
let n := 1;
for c in unique(List.Col) do
    let s1 := number(first(a.List[Col = c and Row = item(b, 0)]).Value);
    let s2 := number(first(a.List[Col = c and Row = item(b, 1)]).Value);
    let s3 := number(first(a.List[Col = c and Row = item(b, 2)]).Value);
    let s4 := number(first(a.List[Col = c and Row = item(b, 3)]).Value);
    let st := s1 + s2 + s3 + s4;
    h := h + "<tr>";
    h := h + "<td>" + c + "</td>";
    h := h + "<td>" + s1 + "</td>";
    h := h + "<td>" + s2 + "</td>";
    h := h + "<td>" + s3 + "</td>";
    h := h + "<td>" + s4 + "</td>";
    h := h + "<td>" + st + "</td>";
    h := h + "</tr>";
    t1 := t1 + s1;
    t2 := t2 + s2;
    t3 := t3 + s3;
    t4 := t4 + s4;
    tt := tt + st;
    n := n + 1
end;
h := h + "<tr>";
h := h + "<td>Total</td>";
h := h + "<td>" + t1 + "</td>";
h := h + "<td>" + t2 + "</td>";
h := h + "<td>" + t3 + "</td>";
h := h + "<td>" + t4 + "</td>";
h := h + "<td>" + tt + "</td>";
h := h + "</tr>";
h := h + "</table>";
html(h)

No need for a second table, works on the fly, and could be simplified with an array of columns.

Aside from having my rows and cols the wrong way round(!), I now have the formula reduced down to this

let a := this;
let Rows := unique((List order by Row).Row);
let Cols := unique((List order by Col).Col);
let h := "<table style='width:100%'><tr><th>" + a.Name + "</th>";
for c in Cols do
    h := h + "<td>" + c + "</td>"
end;
h := h + "<th>Total</th></tr>";
for r in Rows do
    h := h + "<tr><td>" + r + "</td>";
    for c in Cols do
        h := h + "<td>" + sum(a.List[Row = r and Col = c].Value) + "</td>"
    end;
    h := h + "<td>" + sum(a.List[Row = r].Value) + "</td></tr>"
end;
h := h + "<tr><td>Total</td>";
for c in Cols do
    h := h + "<td>" + sum(a.List[Col = c].Value) + "</td>"
end;
h := h + "<td>" + sum(a.List.Value) + "</td></tr></table>";
html(h)

Regards John

H Rafael

Yes, the rows on line 2, column on line 3, and the values on lines 12, 14, 18, and 20 can all be changed. I have improved to look of the table created by right aligning the values.

let a := this;
let Rows := unique((List order by Row).Row);
let Cols := unique((List order by Col).Col);
let h := "<table style='width:100%'><tr><th>" + a.Name + "</th>";
for c in Cols do
    h := h + "<th style='text-align:right'>" + c + "</th>"
end;
h := h + "<th style='text-align:right'>Total</th></tr>";
for r in Rows do
    h := h + "<tr><td>" + r + "</td>";
    for c in Cols do
        h := h + "<td style='text-align:right'>" + sum(a.List[Row = r and Col = c].Value) + "</td>"
    end;
    h := h + "<td style='text-align:right'>" + sum(a.List[Row = r].Value) + "</td></tr>"
end;
h := h + "<tr><td>Total</td>";
for c in Cols do
    h := h + "<td style='text-align:right'>" + sum(a.List[Col = c].Value) + "</td>"
end;
h := h + "<td style='text-align:right'>" + sum(a.List.Value) + "</td></tr></table>";
html(h)

Regards John