1

Cross Tab in a Form

I have come up with a way to cross tabulate a table of values in a form

This is the table of values

 

And this is the table I use to create the Cross Tab

This code 

do as server
    delete XTab;
    let a := this;
    let b := unique((List order by Row).Row);
    (create XTab).(
        X := "0";
        '0' := a.Name;
        '1' := item(b, 0);
        '2' := item(b, 1);
        '3' := item(b, 2);
        '4' := item(b, 4);
        T := "Total";
        Parent := a
    );
    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;
        (create XTab).(
            X := n;
            '0' := c;
            '1' := s1;
            '2' := s2;
            '3' := s3;
            '4' := s4;
            T := st;
            Parent := a
        );
        t1 := t1 + s1;
        t2 := t2 + s2;
        t3 := t3 + s3;
        t4 := t4 + s4;
        tt := tt + st;
        n := n + 1
    end;
    (create XTab).(
        X := "T";
        '0' := "Total";
        '1' := t1;
        '2' := t2;
        '3' := t3;
        '4' := t4;
        T := tt;
        Parent := a
    )
end

Turns this

Into this

Because you can't hide the headings I have added a row 'heading' and made them look a bit like a spreadsheet.

Regards John

19 replies

null
    • Rafael Sanchis
    • Rafael_Sanchis
    • 2 yrs agoSat, June 11, 2022 at 9:59 AM UTC
    • Reported - view

    Very interesting.

    Can you Share the DB, I try to recreate it, but the code give me error, I'm doing something wrong.

    • John_Halls
    • 2 yrs agoSat, June 11, 2022 at 10:48 AM UTC
    • Reported - view

    XTab.ninox 

      • Rafael Sanchis
      • Rafael_Sanchis
      • 2 yrs agoSat, June 11, 2022 at 10:57 AM UTC
      • Reported - view

      John Halls Thank 😔obviosly don't create the parent table

      Thanks 👍

    • John_Halls
    • 2 yrs agoSat, June 11, 2022 at 11:35 AM UTC
    • Reported - view

    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.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 2 yrs agoSat, June 11, 2022 at 11:48 AM UTC
      • Reported - view

      John Halls Thinkings in Hours by Activitie.

    • John_Halls
    • 2 yrs agoSat, June 11, 2022 at 1:41 PM UTC
    • Reported - view

    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

      • Rafael Sanchis
      • Rafael_Sanchis
      • 2 yrs agoSat, June 11, 2022 at 6:53 PM UTC
      • Reported - view

      John Halls where you place the formula ?

    • John_Halls
    • 2 yrs agoSat, June 11, 2022 at 8:22 PM UTC
    • Reported - view

    In a formula field. 

    • Rafael Sanchis
    • Rafael_Sanchis
    • 2 yrs agoSun, June 12, 2022 at 5:49 AM UTC
    • Reported - view

    😂 obviosly, Work perfect. There any way to change the rows and colum by the original options. ? 

    • John_Halls
    • 2 yrs agoSun, June 12, 2022 at 7:13 AM UTC
    • Reported - view

    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

      • Rafael Sanchis
      • Rafael_Sanchis
      • 2 yrs agoSun, June 12, 2022 at 7:41 AM UTC
      • Reported - view

      John Halls 

      Look Great, and with only change the Name Col, Row for Row and Col is ok no need change code.

      I can have few Week on Column but many Activities on Row. 

      Thanks

      • Rafael Sanchis
      • Rafael_Sanchis
      • 2 yrs agoSun, June 12, 2022 at 4:34 PM UTC
      • Reported - view

      John Halls 

      Hi John.

      One question I change the Col for Week and Row for Activities and Value for Cost.

      In the example is ok work perfect.

      But in my DB the Activities is on other Table and have Relation 1:N Activities from Cost

      There are any way to implent this.

      Thanks

    • Rafael Sanchis
    • Rafael_Sanchis
    • 2 yrs agoSun, June 12, 2022 at 8:23 AM UTC
    • Reported - view

    John Halls

    The idea look good, I think is very difficult no value 0 and the graphics can't grouped by Wxx

    • Rafael Sanchis
    • Rafael_Sanchis
    • 2 yrs agoMon, June 13, 2022 at 12:18 PM UTC
    • Reported - view

    John Halls

    This is good example.  The Problem is the Formula Field don't have scroll bar or fix colum.

    • Mel_Charles
    • 2 yrs agoMon, June 13, 2022 at 6:49 PM UTC
    • Reported - view

    Hi John

    What a useful script. I particularly like the way you attacked it from all sides. 🙂 

    I can use this ....  I love it when clever people come up with cracking ideas !!

      • John_Halls
      • 2 yrs agoMon, June 13, 2022 at 9:23 PM UTC
      • Reported - view

      Mel Charles Thanks Mel, glad you like it!

    • Rafael Sanchis
    • Rafael_Sanchis
    • 2 yrs agoTue, June 14, 2022 at 10:01 AM UTC
    • Reported - view

    John Halls

    Hi John 👍

    I'm do some Changes for my DB, 

    Now I have in the first Row my Activity with the Weeks,  and in the second line the Expenses by weeks with the total. I will try in another tab place all Activities with the same idea, I hope.

      • John_Halls
      • 2 yrs agoTue, June 14, 2022 at 11:59 AM UTC
      • Reported - view

      Rafael Good luck. It might be tricky having different entities in each row. Let me know how you get on. 

      • Rafael Sanchis
      • Rafael_Sanchis
      • 2 yrs agoTue, June 14, 2022 at 12:47 PM UTC
      • Reported - view

      John Halls 

      Yes to difficult to me,  but I will try 

Login to reply

Content aside

Categories

View all topics
  • 1 Likes
  • 2 yrs agoTue, June 14, 2022 at 12:47 PM UTCLast active
  • 19Replies
  • 208Views
  • 3 Following