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

      Mirko

      The Crosstab is on Table Print Hours, Tab Crosstab.

      This DB is a dummy.

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

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

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

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

      • mirko3
      • vor 10 Monaten
      • Gemeldet - anzeigen

       I'm looking at it. Mirko

      • mirko3
      • vor 10 Monaten
      • Gemeldet - anzeigen

       Change line 59 to:

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

      Thanks  Mirko Appreciate again

      • mirko3
      • vor 10 Monaten
      • Gemeldet - anzeigen

      an example. Mirko

      • Rafael_Sanchis
      • vor 10 Monaten
      • Gemeldet - anzeigen

      Great Man again and again appreciate your contribution

Content aside

  • Status Answered
  • vor 10 MonatenZuletzt aktiv
  • 12Antworten
  • 139Ansichten
  • 3 Folge bereits