3.4 Tabelas dinâmicas

Tabela Dinâmica no CRM One Web

O uso de tabelas dinâmicas é uma excelente maneira de realizar análises gerenciais com rapidez e flexibilidade. Com esse recurso, é possível criar relatórios que podem ser montados diretamente na tela pelo usuário, sem depender de novas consultas para cada tipo de análise.

Cenário de Exemplo

Neste exemplo, será criado um relatório baseado em notas fiscais de saída. Para isso, são utilizadas as seguintes tabelas do SAP Business One:

OINV – Cabeçalho da nota fiscal de saída
INV1 – Linhas da nota fiscal
OSLP – Vendedores
OUSG – Utilizações (operações fiscais)
OSHP – Tipo de envio
OCRD – Cadastro do parceiro de negócios
OCRG – Grupos de parceiros
OITM – Cadastro de itens
OITB – Grupos de itens

DICA: Utilize o Gerador de Consultas do SAP para construir a estrutura inicial da consulta.

Desenvolvimento da consulta

Para elaboração de forma fácil da consulta SQL, uma sugestão é utilizar o “Gerador de consultas” do SAP Business One, a ferramenta ajuda efetuando de forma automática o link (joins) entre as tabelas do sistema, então vamos acessar o client do SAP Business One e em: ” > Ferramentas > Consultas > Gerador de consultas”

Recomendamos que a lista de tabelas seja informada em uma ordem específica, de modo que a ligação entre os dados atinja o objetivo desejado, sendo necessário informar:

OINV
INV1
OSLP
OUSG
OSHP
OCRD
OCRG
OITM
OITB

Como resultado o próprio SAP Business One irá gerar o comando SQL necessário, onde realizamos alguns ajustes, incluindo “apelidos” (aliases) para as colunas e chegando ao resultado final abaixo, com uma consulta válida para SQL e HANA:

Consulta SQL (válida para SQL Server e SAP HANA)

SELECT 
  T6."GroupName" as "Grupo PN", 
  T5."CardCode" as "Cód. PN", 
  T5."CardName" as "Nome do PN", 
  T5."CardFName" as "Nome estrangeiro/fantasia",
  T5."MailCity" as "Cidade", 
  T5."State2" as "UF",
  T4."TrnspName" as "Tipo de envio", 
  T2."SlpName" as "Vendedor", 
  T0."DocNum" as "Num. doc", 
  T0."Serial" as "Num. NF", 
  T0."DocDate" as "Data", 
  MONTH(T0."DocDate") as "Mês",
  YEAR(T0."DocDate") as "Ano",
  T8."ItmsGrpNam" as "Grupo de itens", 
  T7."ItemCode" as "Cód. Item", 
  T7."ItemName" as "Desc. Item", 
  T3."Usage" as "Utilização",
  T1."Quantity" as "Quant.",
  T1."Price" as "Preço unit.", 
  T1."LineTotal" as "Total",
  'Total linhas' as "Total Linhas",
  'Total colunas' as "Total colunas"
FROM OINV T0 
INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry" 
INNER JOIN OSLP T2 ON T0."SlpCode" = T2."SlpCode" 
INNER JOIN OUSG T3 ON T1."Usage" = T3."ID" 
INNER JOIN OSHP T4 ON T0."TrnspCode" = T4."TrnspCode" 
INNER JOIN OCRD T5 ON T0."CardCode" = T5."CardCode" 
INNER JOIN OCRG T6 ON T5."GroupCode" = T6."GroupCode" 
INNER JOIN OITM T7 ON T1."ItemCode" = T7."ItemCode" 
INNER JOIN OITB T8 ON T7."ItmsGrpCod" = T8."ItmsGrpCod"
WHERE T0."CANCELED" = 'N'

Dica: As colunas “Total linhas” e “Total colunas” servem para facilitar a criação de totalizadores automáticos no relatório.

Testando a consulta

Durante a construção da consulta SQL é fundamental testar e verificar se o conjunto de dados está coerente, se os dados estão corretos e não foi gerado nenhum produto cartesiano.

Dica 1 – Gere totalizadores com colunas fixas

Na consulta SQL, são utilizadas duas colunas fixas chamadas "Total linhas" e "Total colunas". Esses campos possuem o mesmo valor para todas as linhas retornadas, e seu objetivo não é exibir dados adicionais, mas sim possibilitar a exibição de totais gerais na tabela dinâmica. Para utilizá-las corretamente:

Adicione o campo Total linhas no início da área Linhas.
Adicione o campo Total colunas no início da área Colunas.

Com isso, a plataforma exibirá automaticamente os totais nas extremidades da tabela, facilitando a leitura e comparação de dados.

Dica 2 – Otimize a performance do relatório

O processamento das informações é realizado diretamente no navegador. Para evitar lentidão na visualização ou travamentos durante a análise, é importante que a consulta SQL esteja otimizada. Recomendações:

Evite retornar grandes volumes de dados em formato bruto;
Utilize filtros na própria SQL para limitar o resultado (por período, tipo de documento, status etc.);
Realize agrupamentos e cálculos dentro da própria SQL sempre que possível. Essas práticas contribuem para que a montagem da tabela dinâmica seja mais ágil e eficiente, além de melhorar a experiência de navegação do usuário.

Configurando no CRM One Web

Executando o Relatório

Clique em Cenário selecionado para abrir os filtros e em seguida clique em Gerar Relatório.

Ao clicar no item de menu será carregada uma nova aba “Análise de vendas, clicar na tarja azul “Cenário selecionado”, para abrir as opções de filtro e disponibilizar o botão “Gerar Relatório”, neste exemplo como não há filtros o usuário deverá clicar para gerar e começar a trabalhar com a sua tabela dinâmica.

Montando sua Tabela Dinâmica

Campos: lista de colunas disponíveis
Filtros: condiciona os dados
Colunas: define o eixo horizontal
Linhas: define o eixo vertical
Valores: define os cálculos (ex: soma)

Primeira execução

Arraste Nome do PN para o quadro Linhas.
Arraste Total para Valores.
No botão de configuração do campo Total:
- Função cálculo: Soma
- Prefixo moeda: R$
- Separador de milhar: .
- Separador de decimais: ,

Esse formato mostrará a soma total de vendas por cliente.

Repita o passo anterior, desta vez localizando o campo “Total”, lembrando que este campo representa “[INV1].[LineTotal] na consulta SQL.

Vamos agora fazer alguns ajustes na apresentação dos “Valores”, para esta configuração, clicar no ícone de três barras horizontais localizado junto ao campo “Total” recentemente colocado na área de valores.

Será apresentado o formulário de “Configuração de campos”.

Na opção “Função cálculo” podem ser selecionadas as opções:

Contagem
Soma
Mínimo
Máximo
Average (média)
Produto

Onde selecionaremos a opção “Soma”

Depois de selecionar a “soma”, vamos informar os seguintes dados:

Em “Prefixo moeda” informar “R$” para a representação da moeda Real.
Em “Separador de milhar”, apagar a vírgula informada no campo e digitar o carácter “ponto final”.
Em “Separador de decimais, informar uma “vírgula”.

Então clicar em “Ok”

Os dados serão processados a primeira visualização está pronta.

O resultado desta visualização é a soma do campo [INV1].[LineTotal] que representa o total da linha em todas as notas fiscais de saída, agrupado por “Nome do PN”.

#Dica 3: Após esta execução, lembramos que na consulta estão sendo apresentados todos os dados encontrados em notas fiscais de saída, ou seja, estão sendo apresentados documento válidos, documentos cancelados e documentos de cancelamento de nota fiscal, o que acabará resultando em um informação incoerente, para corrigir este “erro”, editar a consulta e incluir a condição a baixo na última linha da consulta SQL.

WHERE T0."CANCELED" = 'N'

Com ajuste, os dados sofreram uma pequena alteração, mas agora com informações mais coerentes.

Visualização Avançada

Arraste o campo Ano para Colunas.
Para totalizadores:
- Arraste Total linhas para o topo do quadro Linhas
- Arraste Total colunas para o topo do quadro Colunas

Aplicando Filtros

Arraste Ano para o quadro Filtros
Clique no ícone de barras e selecione os anos desejados (ex: somente 2020)
Adicione novamente o campo Ano em Colunas, se desejar manter a divisão anual

Com o campo “Ano” na área de “filtros”, clicar sobre o ícone de barras horizontais localizado ao lado da descrição do campo.

Será apresentada a janela de filtros e o usuário terá a opção de marcar ou desmarcar as opções de dados que deseja visualizar, neste exemplo vamos desmarcar os anos anteriores e deixar marcado somente 2020.

Após clicar no “Ok” da janela de dados, as informações serão apresentadas com o filtro.

Exemplo de Cenário Complexo

Linhas: Vendedor, Grupo de itens, UF, Nome do PN, Descrição do item
Colunas: Ano, Mês
Filtros: Ano (2019 e 2020), Mês (janeiro, fevereiro, março)

Esse modelo permite comparar os resultados entre trimestres ou anos diferentes com facilidade.