Crosstab with dynamic columns

Ask for Help
,
1

I am surprised that Grist is so productive. I would love to make use of this, but I have stucked on one issue.

My client is requesting monthly cross-tabulated data, which Grist does not appear to be able to easily achieve. The Stacked series chart easily achieves this, but I could not do this in a table. In particular, I could not dynamically generate a column for each month for a given period.

The following sheet is a simplified Google Sheets representation of what I want.

https://docs.google.com/spreadsheets/d/1Nm1XcGCFdmgJnyUk2pupPBZj8nQ_AFh6F2aa6f145B4/edit?usp=sharing

Do you have any suggestions or alternatives? I would be very happy if you could tell me how to do.
Thank you.

2

Two options. One creating a custom widget. In fact, @jperon has made one that you can use: PivotTable custom widget

I should mention also that in Grist, it’s often sufficient to create a page with linked widgets instead, since it allows getting at the same data conveniently, in a row-oriented format. But there are reasons to prefer a cross-tab / matrix format, of course.

For a native way in Grist, there isn’t a great way yet – but you can do it if you are willing to create a separate column for each month separately and manually (rather than dynamically).

That could work fine as long as the number of columns is small. If it gets large, in addition to the headache to create them, it would also cause bad performance. For a small number of columns, here is an example how to achieve this:

https://public.getgrist.com/c36Qs7mnP2jz/Credit-Card-Activity-Cross-tab/p/6

Here I have a summary table Activity [by Category], and a hidden summary table Activity [by Category, Month]. The first one looks up values from the second in each of the month columns, using formulas like this:

month = "2019-01"
Activity_summary_Category_Month.lookupOne(Category=$Category, Month=month).Amount

Unfortunately, the month columns have to be created manually, and the formula in each needs to be updated manually to have the month match the name of the column.

There is a similar example here: How to do traditional pivot table in grist - #2 by natalie-grist

We’ve spent some time at one point trying to come up with a good feature to do this nicely. One thing that makes it harder is that there are similar-looking situations where people want to enter data in this layout too (e.g. creating a budget, for an example similar to this data). We don’t have any great proposals that address these wishes yet.

1 Like
3

if the stuff is somewhat fixed (you don´t need to play with columsn and filters etc in the pivot table) I guess you can generate your cross tab with HTML?

I have made some quite interesting reports with HTML

This report looks pretty simple, but it gets data from lots of different filters based on conditions and the user looking at it, etc

image
image651×867 73.2 KB

the code

# Fetch all sessions
sessoes = list(Sessoes.all)  # Convert the RecordSet to a list

# Fetch PDCA records for the given company
pdca_records = PDCA.lookupRecords(EmpresaRef=$id)

# Define session colors and styles
session_colors = ["#C00000", "#1F3864"]  # Red and blue
session_text_color = "#FFFFFF"  # White
column_title_background = "#D8D8D8"  # Gray
summary_background = "#A9A9A9"  # Dark gray for summary
font_family = "Arial"

def get_practice_score(practice_id):
    scores = [record.Nota_da_Pratica for record in pdca_records if record.PraticaRef == practice_id]
    if scores:
        return sum(scores) / len(scores)
    return 0

def calculate_average_percentual(practices):
    total_percentual = 0
    count = 0
    for practice_id in practices:
        score = get_practice_score(practice_id)
        total_percentual += score
        count += 1
    return total_percentual / count if count > 0 else 0

# Generate HTML details for each session and its subsessions
session_details_html = []
total_pontuacao_maxima = 0
total_atingida = 0

for i, sessao in enumerate(sessoes):
    subsessoes_html = []
    subsessoes = Subsessoes.lookupRecords(RefSessao=sessao.id)
    sessao_pontuacao_maxima = 0
    sessao_atingida = 0

    for subsessao in subsessoes:
        praticas = Questoes.lookupRecords(RefSub=subsessao.id)
        pratica_html = []
        subsessao_pontuacao_maxima = subsessao.PontuacaoMaxima
        subsessao_atingida = 0
        all_practices = [p for pratica in praticas for p in pratica.Praticas]
        unique_practices = list(set(all_practices))
        average_subsessao_percentual = calculate_average_percentual(unique_practices)
        pontuacao = round(subsessao.PontuacaoMaxima / len(unique_practices), 2)
        alcancada = round((average_subsessao_percentual * subsessao.PontuacaoMaxima) / 100, 2)

        for pratica in praticas:
            pratica_ids = list(pratica.Praticas)  # Convert RecordSet to list
            pratica_percentuals = [get_practice_score(pratica_id) for pratica_id in pratica_ids]
            average_pratica_percentual = sum(pratica_percentuals) / len(pratica_percentuals) if pratica_percentuals else 0
            pratica_html.append("<tr><td style='border: 1px solid black; padding: 5px; width: 679px;'>{0}</td><td style='border: 1px solid black; padding: 5px; width: 100px;'>{1}</td><td style='border: 1px solid black; padding: 5px;'>{2:.2f}</td><td style='border: 1px solid black; padding: 5px;'>{3:.2f}</td><td style='border: 1px solid black; padding: 5px;'>{4:.2f}</td></tr>".format(
                pratica.Questoes,
                '<br>'.join([Practices.lookupOne(id=p).PractNum for p in pratica_ids]),
                average_pratica_percentual,
                pontuacao,
                round((average_pratica_percentual * pontuacao) / 100, 2)
            ))

        sessao_pontuacao_maxima += subsessao.PontuacaoMaxima
        sessao_atingida += alcancada
        subsessoes_html.append("""
            <div style="background-color: {0}; color: {1}; padding: 5px; font-family: {2}; font-size: 10px; font-weight: bold;">
                <h3 style="margin: 0;">{3}</h3>
            </div>
            <table style="width: 100%; border-collapse: collapse; margin-bottom: 0; font-family: {2}; font-size: 10px;">
              <thead>
                <tr style="background-color: {4};">
                  <th style="border: 1px solid black; padding: 5px; width: 679px; font-weight: bold;">Questão</th>
                  <th style="border: 1px solid black; padding: 5px; width: 100px; font-weight: bold;">Práticas</th>
                  <th style="border: 1px solid black; padding: 5px; width: 100px; font-weight: bold;">Percentual</th>
                  <th style="border: 1px solid black; padding: 5px; width: 100px; font-weight: bold;">Pontuação</th>
                  <th style="border: 1px solid black; padding: 5px; width: 100px; font-weight: bold;">Alcançada</th>
                </tr>
              </thead>
              <tbody>
                {5}
                <tr style="background-color: {6};">
                  <td colspan="2" style="border: 1px solid black; padding: 5px; font-weight: bold;">Totais</td>
                  <td style='border: 1px solid black; padding: 5px;'>{7:.2f}</td>
                  <td style='border: 1px solid black; padding: 5px;'>{8:.2f}</td>
                  <td style='border: 1px solid black; padding: 5px;'>{9:.2f}</td>
                </tr>
              </tbody>
            </table>
        """.format(
            session_colors[i % len(session_colors)],
            session_text_color,
            font_family,
            subsessao.Subsessao,
            column_title_background,
            '\n'.join(pratica_html),
            column_title_background,
            average_subsessao_percentual,
            subsessao.PontuacaoMaxima,
            alcancada
        ))

    sessao_percentage = round((sessao_atingida / sessao_pontuacao_maxima) * 100, 2)
    total_pontuacao_maxima += sessao_pontuacao_maxima
    total_atingida += sessao_atingida

    # Add the complete session details HTML
    session_details_html.append("""
        <div style="background-color: {0}; color: {1}; padding: 10px; font-family: {2}; font-size: 11px; text-transform: uppercase; font-weight: bold;">
            <h2 style="text-align: center; margin: 0;">{3}</h2>
        </div>
        {4}
        <div style="background-color: {0}; height: 20px;">
          <table style="width: 100%; border-collapse: collapse; margin-bottom: 0; font-family: {2}; font-size: 11px; color: {1};">
            <tbody>
              <tr>
                <td style="font-weight: bold;">Pontuação Sessão:</td>
                <td style="text-align: center;">{5}%</td>
                <td style="text-align: center;">{6}</td>
                <td style="text-align: center;">{7}</td>
              </tr>
            </tbody>
          </table>
        </div>
    """.format(
        session_colors[i % len(session_colors)],
        session_text_color,
        font_family,
        sessao.Sessao_NomeSessao,
        '\n'.join(subsessoes_html),
        sessao_percentage,
        round(sessao_pontuacao_maxima, 2),
        round(sessao_atingida, 2)
    ))

# Calculate overall totals and percentages
total_percentage = round((total_atingida / total_pontuacao_maxima) * 100, 2)

# Add summary to the report
session_details_html.append("""
    <div style="height: 20px;"></div> <!-- Empty line -->
    <div style="background-color: {0}; color: white; padding: 10px; font-family: {1}; font-size: 11px; text-transform: uppercase; font-weight: bold;">
        <table style="width: 100%; border-collapse: collapse; margin-bottom: 0;">
          <thead>
            <tr style="background-color: {0}; color: white; font-size: 11px;">
              <th style="border: 1px solid black; padding: 10px;">Pontuação Máxima</th>
              <th style="border: 1px solid black; padding: 10px;">Total Atingida</th>
              <th style="border: 1px solid black; padding: 10px;">Percentual Total</th>
            </tr>
          </thead>
          <tbody>
            <tr style="background-color: {0}; color: white; font-size: 11px;">
              <td style="border: 1px solid black; padding: 10px;">{2}</td>
              <td style="border: 1px solid black; padding: 10px;">{3}</td>
              <td style="border: 1px solid black; padding: 10px;">{4}%</td>
            </tr>
          </tbody>
        </table>
    </div>
""".format(
    summary_background,
    font_family,
    round(total_pontuacao_maxima, 2),
    round(total_atingida, 2),
    total_percentage
))

# Valores de referência para cálculo
valores_referencia = {
    "8.1 Econômico-financeiros": 420,
    "8.3 Relativos a clientes e mercados": 180,
    "8.2 Sociais e ambientais": 100,
    "8.4 Relativos às pessoas": 210,
    "8.5 Relativos aos processos": 490
}

# Indicadores e suas pontuações fixas
indicadores = [
    {"nome": "RESULTADOS ECONÔMICOS-FINANCEIROS", "pontuacao": 80, "referencia": valores_referencia["8.1 Econômico-financeiros"], "tipo_indicador": "8.1 Econômico-financeiros"},
    {"nome": "RESULTADOS SÓCIO-AMBIENTAIS", "pontuacao": 30, "referencia": valores_referencia["8.2 Sociais e ambientais"], "tipo_indicador": "8.2 Sociais e ambientais"},
    {"nome": "RESULTADOS RELATIVOS A CLIENTES E MERCADO", "pontuacao": 80, "referencia": valores_referencia["8.3 Relativos a clientes e mercados"], "tipo_indicador": "8.3 Relativos a clientes e mercados"},
    {"nome": "RESULTADOS RELATIVOS ÀS PESSOAS", "pontuacao": 80, "referencia": valores_referencia["8.4 Relativos às pessoas"], "tipo_indicador": "8.4 Relativos às pessoas"},
    {"nome": "RESULTADOS RELATIVOS AOS PROCESSOS", "pontuacao": 80, "referencia": valores_referencia["8.5 Relativos aos processos"], "tipo_indicador": "8.5 Relativos aos processos"}
]

# Add the additional table
additional_table_html = """
    <div style="height: 20px;"></div> <!-- Empty white row -->
    <div style="background-color: #1F3864; color: white; padding: 10px; font-family: {0}; font-size: 11px; text-transform: uppercase; font-weight: bold; text-align: center;">
        RESULTADOS
    </div>
    <table style="width: 100%; border-collapse: collapse; margin-bottom: 20px; font-family: {0}; font-size: 11px;">
      <thead>
        <tr style="background-color: #D8D8D8;">
          <th style="border: 1px solid black; padding: 5px;">Indicador</th>
          <th style="border: 1px solid black; padding: 5px;">Pontuação</th>
          <th style="border: 1px solid black; padding: 5px;">Atendido</th>
          <th style="border: 1px solid black; padding: 5px;">Percentual</th>
        </tr>
      </thead>
      <tbody>
"""

total_pontuacao = 0
total_atendido = 0

for indicador in indicadores:
    # Filtrar os resultados para o tipo de indicador atual
    resultados_filtrados = Resultados.lookupRecords(RefEmpresa=$id, Num_TipoIndicRef=indicador["tipo_indicador"])
    
    # Somar as pontuações dos resultados filtrados
    soma_pontuacao = sum([resultado.PONTUACAO for resultado in resultados_filtrados])
    
    # Calcular o percentual e o valor atendido
    percentual = (soma_pontuacao / indicador["referencia"]) * 100 if indicador["referencia"] != 0 else 0
    atendido = (percentual / 100) * indicador["pontuacao"]
    
    total_pontuacao += indicador['pontuacao']
    total_atendido += atendido
    
    additional_table_html += f"""
        <tr>
          <td style="border: 1px solid black; padding: 5px;">{indicador['nome']}</td>
          <td style="border: 1px solid black; padding: 5px;">{indicador['pontuacao']}</td>
          <td style="border: 1px solid black; padding: 5px;">{atendido:.2f}</td>
          <td style="border: 1px solid black; padding: 5px;">{percentual:.2f}%</td>
        </tr>
    """

# Adicionar a linha de totais
total_percentual = (total_atendido / total_pontuacao) * 100 if total_pontuacao != 0 else 0

additional_table_html += f"""
        <tr style="background-color: #D8D8D8;">
          <td style="border: 1px solid black; padding: 5px; font-weight: bold;">Totais</td>
          <td style="border: 1px solid black; padding: 5px; font-weight: bold;">{total_pontuacao}</td>
          <td style="border: 1px solid black; padding: 5px; font-weight: bold;">{total_atendido:.2f}</td>
          <td style="border: 1px solid black; padding: 5px; font-weight: bold;">{total_percentual:.2f}%</td>
        </tr>
      </tbody>
    </table>
""".format(font_family)

session_details_html.append(additional_table_html)

# Calculate total points and punctuality
total_pontualidade = sum(record.Pontos for record in Pontualidade_IC.lookupRecords(EmpresaRef=$id))
soma_dos_pontos = total_atingida + total_atendido
pontuacao_total = soma_dos_pontos + total_pontualidade
pontuacao_total_percentual = (pontuacao_total / 1000) * 100

# Add the final summary table
final_summary_html = f"""
    <div style="height: 20px;"></div> <!-- Empty white row -->
    <div style="background-color: #C00000; color: white; padding: 10px; font-family: {font_family}; font-size: 11px; text-transform: uppercase; font-weight: bold;">
        <table style="width: 100%; border-collapse: collapse; margin-bottom: 0;">
          <thead>
            <tr style="background-color: #C00000; color: white; font-size: 11px;">
              <th style="border: 1px solid black; padding: 10px;">SOMA DOS PONTOS</th>
              <th style="border: 1px solid black; padding: 10px;">PONTOS POR PONTUALIDADE NA ATUALIZAÇÃO DOS IC'S</th>
              <th style="border: 1px solid black; padding: 10px;">PONTUAÇÃO TOTAL</th>
              <th style="border: 1px solid black; padding: 10px;">PONTUAÇÃO TOTAL (%)</th>
            </tr>
          </thead>
          <tbody>
            <tr style="background-color: #C00000; color: white; font-size: 11px;">
              <td style="border: 1px solid black; padding: 10px;">{soma_dos_pontos:.2f}</td>
              <td style="border: 1px solid black; padding: 10px;">{total_pontualidade:.2f}</td>
              <td style="border: 1px solid black; padding: 10px;">{pontuacao_total:.2f}</td>
              <td style="border: 1px solid black; padding: 10px; font-weight: bold;">{pontuacao_total_percentual:.2f}%</td>
            </tr>
          </tbody>
        </table>
    </div>
"""

session_details_html.append(final_summary_html)

# Generate the final HTML report
html_report = """
<html>
  <head>
    <title>Relatório de Sessões e Subsessões - {0}</title>
  </head>
  <body>
    <h1 style="text-align: center; font-family: {2}; font-size: 12px;">Relatório de Sessões e Subsessões - {0}</h1>
    {1}
  </body>
</html>
""".format(
    $Nome_da_Empresa,
    '\n'.join(session_details_html),
    font_family
)

return html_report
1 Like
4

Thank you for your sincere support. I’m so glad you got the intent of my question.
Options you suggested are very helpful to understand Grist deeply.

For the past few days I have been experimenting with a feature called Community Connectors in Looker Studio to try to connect to the Grist API from Looker Studio. It seems that I can easily change the design of the report if I successfully connect.

Since my clients do not need to edit on crosstab, it might be useful to be able to easily create read-only views using SQL.
I hope Grist continues to grow significantly.
Thanks.

1 Like
5

Thank you for your great codes.
I am a Python newbie, so this is very clear and helpful. This is another great and practical solution. I would like to take the time to learn from this.
While many no-code products do not accept coding, Grist’s major advantage is that it can be freely customized.
I sincerely thank you for your willingness to provide your valuable code.

6

I know nothing of Python. That code was created by Chat GPT :slight_smile:

1 Like