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
    • 1 yr ago
    • 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
    • 1 yr ago
    • Reported - view
      • Rafael Sanchis
      • Rafael_Sanchis
      • 1 yr ago
      • Reported - view

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

      Thanks 👍

    • John_Halls
    • 1 yr ago
    • 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
      • 1 yr ago
      • Reported - view

      John Halls Thinkings in Hours by Activitie.

    • John_Halls
    • 1 yr ago
    • 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
      • 1 yr ago
      • Reported - view

      John Halls where you place the formula ?

    • John_Halls
    • 1 yr ago
    • Reported - view

    In a formula field. 

    • Rafael Sanchis
    • Rafael_Sanchis
    • 1 yr ago
    • Reported - view

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

    • John_Halls
    • 1 yr ago
    • 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
      • 1 yr ago
      • 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
      • 1 yr ago
      • 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
    • 1 yr ago
    • 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
    • 1 yr ago
    • Reported - view

    John Halls

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

    • Mel_Charles
    • 1 yr ago
    • 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
      • 1 yr ago
      • Reported - view

      Mel Charles Thanks Mel, glad you like it!

    • Rafael Sanchis
    • Rafael_Sanchis
    • 1 yr ago
    • 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
      • 1 yr ago
      • 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
      • 1 yr ago
      • Reported - view

      John Halls 

      Yes to difficult to me,  but I will try 

Content aside

  • 1 Likes
  • 1 yr agoLast active
  • 19Replies
  • 199Views
  • 3 Following