Cross Tab in a Form

John_Halls
2 yrs ago
19replies

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

- Rafael Sanchis
- Rafael_Sanchis
- 2 yrs 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
- 2 yrs ago
- Reported - view
XTab.ninox
- Rafael Sanchis
  
  Rafael_Sanchis
  
  2 yrs ago
  
  Reported - view
  John Halls Thank obviosly don't create the parent table
  
  Thanks

John_Halls
2 yrs 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
- 2 yrs ago
- Reported - view
John Halls Thinkings in Hours by Activitie.

John_Halls
2 yrs 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
- 2 yrs ago
- Reported - view
John Halls where you place the formula ?

- John_Halls
- 2 yrs ago
- Reported - view
In a formula field.
- Rafael Sanchis
- Rafael_Sanchis
- 2 yrs ago
- Reported - view
obviosly, Work perfect. There any way to change the rows and colum by the original options. ?

John_Halls
2 yrs 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
- 2 yrs 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
- 2 yrs 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
- 2 yrs 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
- 2 yrs 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
- 2 yrs 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
  
  2 yrs ago
  
  Reported - view
  Mel Charles Thanks Mel, glad you like it!
- Rafael Sanchis
- Rafael_Sanchis
- 2 yrs 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
  
  2 yrs 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
  
  2 yrs ago
  
  Reported - view
  John Halls
  
  Yes to difficult to me, but I will try

Cross Tab in a Form

19 replies

Content aside

Categories

Overview