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 9 Monaten
    • 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 9 Monaten
      • Gemeldet - anzeigen

      Mirko

      The Crosstab is on Table Print Hours, Tab Crosstab.

      This DB is a dummy.

    • mirko3
    • vor 9 Monaten
    • 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 9 Monaten
      • Gemeldet - anzeigen

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

      • Rafael_Sanchis
      • vor 9 Monaten
      • 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 9 Monaten
      • Gemeldet - anzeigen

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

      • mirko3
      • vor 9 Monaten
      • Gemeldet - anzeigen

       I'm looking at it. Mirko

      • mirko3
      • vor 9 Monaten
      • Gemeldet - anzeigen

       Change line 59 to:

      sum(me.'History Hours'.'Hours:') +
      
      • mirko3
      • vor 9 Monaten
      • 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 9 Monaten
      • Gemeldet - anzeigen

      Thanks  Mirko Appreciate again

      • mirko3
      • vor 9 Monaten
      • Gemeldet - anzeigen

      an example. Mirko

      • Rafael_Sanchis
      • vor 9 Monaten
      • Gemeldet - anzeigen

      Great Man again and again appreciate your contribution

Content aside

  • Status Answered
  • vor 9 MonatenZuletzt aktiv
  • 12Antworten
  • 138Ansichten
  • 3 Folge bereits