0

HTML CrossTab

Hi Mirko. There any way to implement a better look and scroll the weeks in this scrips.

Crosstab by Staff by Weeksbhours spent

let a := this;
let Rows := unique(('History Hours' order by 'Staff:').'Staff:');
let Cols := unique(('History Hours' order by 'Week:_').'Week:_');
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.'History Hours'['Staff:' = r and 'Week:_' = c].Hours) +
            "</td>"
    end;
    h := h + "<td style='text-align:right'>" + sum(a.'History Hours'['Staff:' = r].Hours) +
        "</td></tr>"
end;
h := h + "<tr><td>Total</td>";
for c in Cols do
    h := h + "<td style='text-align:right'>" + sum(a.'History Hours'['Week:_' = c].Hours) +
        "</td>"
end;
h := h + "<td style='text-align:right'>" + sum(a.'History Hours') + "</td></tr></table>";
html(h)

 

Copy

12 Antworten

null
    • mirko3
    • vor 1 JahrThu, June 1, 2023 at 8:08 PM UTC
    • Gemeldet - anzeigen

    Hi Rafael. This is too abstract for me, I need a dummy database. Otherwise, I can only give you a sample database with a similar structure. It is possible to create a cross-scrolling table. Mirko

      • Rafael_Sanchis
      • vor 1 JahrThu, June 1, 2023 at 8:17 PM UTC
      • Gemeldet - anzeigen

      Mirko

      The Crosstab is on Table Print Hours, Tab Crosstab.

      This DB is a dummy.

    • mirko3
    • vor 1 JahrSat, June 3, 2023 at 5:20 AM UTC
    • Gemeldet - anzeigen

    Hi Rafael. Here my example. It is scrollable if the table is large enough. You can adjust colors yourself in the CSS part. Mirko

    let me := this;
    let Rows := unique(('History Hours' order by 'Staff:').'Staff:');
    let Cols := unique(('History Hours' order by 'Week:_').'Week:_');
    "--------------CSS---------------";
    let css := "<style>
    table,th, td {
        border:thin solid #a0a0a0;
        border-collapse:collapse;
    }
    th{
        background-color:#f1f3f4;
        text-align:center;
        padding:0.1em  0.6em;
    }
    tr:nth-child(odd) {background-color:#e4ebf2;}
    td{
        text-align:right;
        padding:0.1em  0.8em;
        font-family:Courier;
    }  th:nth-of-type(1) {
        position:sticky;
        overflow-x:auto;
        white-space:nowrap;
        left:-1em;
        text-align:left;
    }
    .grey {background-color:silver;}
    </style>";
    "------------Content------------";
    let content := "
    <table>
    <tr>
        <th class ='stop'>Week</th>
    " +
        for c in Cols do
            "<td>W" + c + "</td>"
        end +
        "<td>Total</td>
    </tr>
    " +
        for r in range(0, length(Rows)) do
            "<tr><th>" + item(Rows, r) + "</th>" +
            for c in range(0, length(Cols)) do
                "<td>" +
                sum(me.'History Hours'['Staff:' = item(Rows, r) and 'Week:_' = item(Cols, c)].'Hours:') +
                "</td>"
            end +
            "<td class = 'grey'>" +
            sum(me.'History Hours'['Staff:' = item(Rows, r)].'Hours:') +
            "</td>
            </tr>"
        end +
        "
    <tr><th>Total</th>" +
        for c in range(0, length(Cols)) do
            "<td class = 'grey'>" + sum(me.'History Hours'['Week:_' = item(Cols, c)].'Hours:') +
            "</td>"
        end +
        "<td class = 'grey'>" +
        sum(me.'History Hours') +
        "</td></tr>
    </table>";
    html(css + content)
      • Rafael_Sanchis
      • vor 1 JahrSat, June 3, 2023 at 7:06 AM UTC
      • Gemeldet - anzeigen

       Wow man look too good. Appreciate a lot your help 👍

      • Rafael_Sanchis
      • vor 1 JahrMon, June 5, 2023 at 9:53 AM UTC
      • Gemeldet - anzeigen

       Hi Mirko.

      Now I have another great problem here that I don't know is possible resolve.

      I can spend hours on different year, and can't view 2022.

      • Rafael_Sanchis
      • vor 1 JahrMon, June 5, 2023 at 11:04 AM UTC
      • Gemeldet - anzeigen

      The Sum is not correct no 1147 is 806, and don't know were is the error

      • mirko3
      • vor 1 JahrMon, June 5, 2023 at 4:49 PM UTC
      • Gemeldet - anzeigen

       I'm looking at it. Mirko

      • mirko3
      • vor 1 JahrMon, June 5, 2023 at 7:09 PM UTC
      • Gemeldet - anzeigen

       Change line 59 to:

      sum(me.'History Hours'.'Hours:') +
      
      • mirko3
      • vor 1 JahrMon, June 5, 2023 at 7:18 PM UTC
      • Gemeldet - anzeigen

       This is the Problem with week(). It is better to use yearweek(), if you need statistics for more then one year in your db. You should add your database to this, or an other option to use the year and the week. Mirko

      • Rafael_Sanchis
      • vor 1 JahrMon, June 5, 2023 at 7:27 PM UTC
      • Gemeldet - anzeigen

      Thanks  Mirko Appreciate again

      • mirko3
      • vor 1 JahrWed, June 7, 2023 at 11:47 AM UTC
      • Gemeldet - anzeigen

      an example. Mirko

      • Rafael_Sanchis
      • vor 1 JahrWed, June 7, 2023 at 1:09 PM UTC
      • Gemeldet - anzeigen

      Great Man again and again appreciate your contribution