3

Display extensive datasets via a custom HTML table.

Pivot_Table_3.ninox

Hello everyone,
Following up on my previous post, I am happy to share the lessons I have learned working with an extended dataset.

Although Ninox offers many ways to present data, a custom HTML table lets you show it exactly how you want.

In my experience so far:

  1. One form of pagination is possible, i.e., loading only chunks of the data, dramatically reducing waiting times.
  2. The 'Select' statement in combination with 'where' is quite efficient when used on the server side.
  3. Create a new JSON object based on initial data to reduce further the amount of information fetched. Build it on the server side.
  4. 'Nested loops' are heavy computational tasks. They cannot be avoided during pivot to ensure data consistency, especially if missing values are present. If enough memory and computing power are available, running the code on the user side is better.
  5. The combination of cached() and invalidate() functions works quite well.

The code of the Pivot Table is following:

let startDate := 'Select start date';
let endDate := 'End date';
let values := number('Select data to present');
let data := cached(do as server
            let x := ((select Covid19_Data where date >= startDate and date <= endDate) order by date);
            switch values do
            case 1:
                x.{
                    ID: Id,
                    Date: date,
                    Country: country,
                    Values: daily_new_cases
                }
            case 2:
                x.{
                    ID: Id,
                    Date: date,
                    Country: country,
                    Values: daily_new_deaths
                }
            case 3:
                x.{
                    ID: Id,
                    Date: date,
                    Country: country,
                    Values: active_cases
                }
            case 4:
                x.{
                    ID: Id,
                    Date: date,
                    Country: country,
                    Values: cumulative_total_cases
                }
            case 5:
                x.{
                    ID: Id,
                    Date: date,
                    Country: country,
                    Values: cumulative_total_deaths
                }
            end
        end);
let tdata := "<tr><th></th>" + unique(data.("<th>" + date(Date) + "</th>")) + "</tr>" +
    for loop1 in unique(sort(data.Country)) do
        "<tr>
            <td style='background-color: #ffffff'>" +
        loop1 +
        "</td>" +
        for loop2 in unique(data.Date) do
            let y := last(data[Country = loop1 and Date = loop2]);
            "<td><span onclick=""ui.popupRecord('" + raw(y.ID) +
            "')""; style=""cursor: pointer;"">" +
            y.Values +
            "</span></td>"
        end +
        "</tr>"
    end;
html("<head>
<style>
    .pivot-table {
        border-collapse: collapse;
        width: auto;
        table-layout: auto; /* Column width based on content */
        white-space: nowrap; /* Prevent text wrapping */
    }
    .pivot-table th, .pivot-table td {
        border: 1px solid #dddddd;
        text-align: center;
        padding: 8px;
    }
    .pivot-table th {
        background-color: #ffffff;
        position: sticky;
        top: 0;
        z-index: 2; /* Higher z-index to ensure it's above other elements */
    }
    .pivot-table th:first-child {
        background-color: #ffffff;
        font-weight: bold;
        position: sticky;
        left: 0;
        z-index: 3; /* Higher z-index to ensure it's always on top */
        width: 200px; /* Custom width for the first column */
    }
    .pivot-table td:first-child {
        background-color: #ffffff;
        font-weight: bold;
        position: sticky;
        left: 0;
        z-index: 1; /* Lower z-index to ensure it's below the first column header */
        max-width: 200px; /* Set maximum width for the first column */
        overflow: hidden; /* Hide overflow content */
    }
</style>
</head>
<body>
<div>
<table id='pivotTable' class='pivot-table'>
" +
tdata +
"
</table>
</div>
</body>")

I have included a working example in the attached database, incorporating all of the above.

I hope this is useful to you.  🙂

4 replies

null
    • Rafael Sanchis
    • Rafael_Sanchis
    • 10 mths agoSun, May 12, 2024 at 7:12 AM UTC
    • Reported - view

    Hi Sotirios , you can post the DB with only 100 or 150 records ? I can Upload the DB.

    Appreciate

      • szormpas
      • 10 mths agoSun, May 12, 2024 at 1:13 PM UTC
      • Reported - view

        

      Sure, you will find a tiny portion of the dataset attached, including only the first 150 records.

      Let me know whether you can upload the DB now.

      small_Pivot_Table_3.ninox
      • Rafael Sanchis
      • Rafael_Sanchis
      • 10 mths agoSun, May 12, 2024 at 1:14 PM UTC
      • Reported - view

       

      Thanks Sotirios Appreciate

    • Ninox Widgets & User Interfaces
    • Jakob_Jordan
    • 8 mths agoWed, July 10, 2024 at 12:33 PM UTC
    • Reported - view

    Hey :) Cool post... we also discovered that customers need very individual display options for a table. In order to customize the user interface and save clicks in sub-tables, we have developed Ninox Widgets. Here you can, for example, carry out actions directly in the table: checklists, for example, or dropdowns directly in the table column. Feel free to take a look: https://www.arc-rider.de/documentation/custom-table-mit-mini-widgets

Login to reply

Content aside

Categories

View all topics
  • 3 Likes
  • 8 mths agoWed, July 10, 2024 at 12:33 PM UTCLast active
  • 4Replies
  • 322Views
  • 3 Following