Tabelas dinâmicas
O uso de tabelas dinâmicas (ou pivot tables) é uma excelente forma de fazer análises gerenciais de forma ágil e dinâmica. Visando a disponibilização de uma funcionalidade prática foi implementado no CRM One web este novo recurso, que permitirá a execução de relatórios no formato de tabela dinâmica (pivot table).
Cenário de exemplo
O primeiro passo para um bom relatório é definir os dados necessários, neste caso vamos pensar em um cenário relacionado a notas fiscais de saída, sendo assim vamos precisar extrair informações do SAP Business One das tabelas de nota fiscal de saída.
- OINV – nota fiscal de saída cabeçalho
- INV1 – linhas da nota fiscal de saída
Para saber quem foi o vendedor da nota fiscal, será realizada a ligação com a tabela que carrega o nome dos vendedores.
- OSLP – vendedores
Devido as particularidades da localização Brasil, a tributação é carregada nos documentos com base no campo utilização, então vamos incluir a tabela ligada as linhas do documento que poderá apresentar o nome da operação/utilização.
OUSG – utilizações para nota fiscal
Em alguns cenários os clientes desejam fazer o agrupamento de pedidos em uma espécie de roteiros, neste exemplo vamos usar o campo “tipo de envio” do documento para definir esta informação, para isto em nosso relatório a respectiva tabela será necessária.
- OSHP – tipo de envio
Com o objetivo de carregar a cidade, estado e o grupo do cliente vamos incluir as tabelas que compõem estas informações vindas do cadastro de clientes.
- OCRD – dados do cabeçalho do cadastro do parceiro de negócios
- OCRG – grupos de parceiros de negócios
Para finalizar é desejável saber o grupo que o item pertence, então precisamos da tabela de cadastro de item e a tabela de grupos de itens, sendo assim adicionaremos as duas tabelas finais.
- OITM – cadastro de itens
- OITB – grupos de itens
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:
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"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: Se observar no detalhe, na imagem acima as duas últimas colunas apresentam uma informação curiosa, em ambas as colunas para todas as linhas são apresentadas as informações “Total linhas”, “Total colunas” cujo nome das colunas é respectivamente igual ao conteúdo das linhas. Esta informação será usada para gerar “totalizadores” e mais adiante será possível verificar o efeito que elas podem proporcionar.
#Dica 2: Evite o uso de grandes volumes de informação em um formato bruto, lembre-se que os dados para exibição serão processados dentro de um navegador de internet (browser), realize sempre que possível agrupamentos prévios e alguns cálculos dentro da consulta SQL, assim o processamento será sempre mais leve na tela do CRM One web.
Configuração
Com a consulta pronta será necessário configurar no CRM One web, acessar o menu > Ferramentas > Configuração de consultas
Na aba “Importação de consultas” serão realizadas as configurações:
- No campo “Nome no menu” preencher com a descrição que deverá ser exibida, neste exemplo “Análise de vendas”.
- Em “Tipo consulta” selecionar a árvore de menu desejada para exibição do novo menu.
- Selecionar “Sim” em “Consulta ativa”.
- Selecionar “Sim” em “Todos usuários”.
- Em “Tipo consulta selecionar a opção “Tabela dinâmica”.
- Informar a consulta SQL desenvolvida.
- Não deixe de executar o “Testar consulta”.
- Clique em “Adicionar” para concluir.
Executando a tabela dinâmica
Após a conclusão da configuração, será possível executar a consulta criada, localize a nova opção dentro do menu definido na configuração, neste exemplo em: > Menu > Vendas > Tabela dinâmica
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.
Com a execução do relatório, será apresentada a área de “personalização/montagem” da tabela dinâmica, que é composto por 5 áreas principais:
- “Campos tabela dinâmica” é o local onde são exibidos todos os campos/colunas selecionados na consulta SQL.
- “Filtros” é uma área destinada a seleção de quais campos deseja-se que sejam usados para condicionar as informações.
- “Colunas” irá determinar os campos que serão apresentado no eixo horizontal.
- “Linhas” irá determinar os campos que serão apresentados no eixo vertical.
- “Valores” é o local aonde são determinados os campos que serão utilizados em cálculos.
Primeira execução
Na primeira execução vamos fazer uma exibição simples somente “vertical” das informações.
Nesta execução clicar na área “Campos tabela dinâmica” sobre o campo “Nome do PN” e segurar o clique do mouse, arrastar até o quadro “Linhas”, então solte o botão do mouse.
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.
Funcionalidades avançadas
Nesta segunda execução realizaremos os passos da primeira execução, porém esta visualização contará com duas dimensões, explorando o “plano vertical” e o “plano horizontal”.
Para isto será selecionado o campo “Ano” e arrastado para o quadro “Colunas”.
Como resultado, podemos visualizar o “Total” de notas fiscais de saída emitidas por cliente, desconsiderando os documentos cancelados, com uma divisão anual.
Neste ponto do relatório temos um campo selecionado em cada eixo, no eixo vertical “Nome do PN”, no eixo horizontal “Ano”. Mas observando a imagem anterior, não há mais um “Total geral” tanto para as linhas quanto para as colunas, então agora vamos utilizar o macete/truque destacado na “#Dica 1”.
O que vamos fazer é arrastar o campo “Total linhas” para o quadro “Linhas” na primeira posição dos campos do quadro.
Agora arrastar o campo “Total colunas” para o quadro “Colunas” colocando também na primeira posição.
Agora ao clicar no sinal de “+” em qualquer um dos eixos, será possível visualizar os dados detalhados.
Para realizar um filtro devemos selecionar o campo que deseja filtrar e arrastar da área “Campos tabela dinâmica” para o quadro “Filtros”, poderão ser arrastados campos para os filtros, mesmo que estes campos não estejam nas linhas ou colunas.
Vamos arrastar o campo “Ano”, para os filtros e filtrar somente as informações de 2020. Ao colocar o campo “Ano” em filtros ele será “retirado” do quadro colunas, então é importante selecionar e arrastar de volta “Ano” para colunas, mantendo exatamente como antes.
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.
Lembre-se que é possível colocar diversos campos em linhas e também em colunas, cada novo campo vinculado aumentará a hierarquia vertical ou horizontal. A ordem dos campos dentro de cada quadro pode ser alterada a qualquer momento, gerando a cada modificação uma nova possibilidade de análise.
Abaixo podemos visualizar um cenário mais complexo, onde:
- Nas linhas (eixo vertical) foram colocados os campos: Vendedor, Grupo de itens, UF, Nome do PN, Descrição do item.
- Nas colunas (eixo horizontal) foram colocados os campos: Ano e mês.
- Na parte de filtros foram informados: Ano e mês.
- Realizado filtro dos anos de 2019 e 2020.
- Realizado filtro dos meses de janeiro, fevereiro e março.
Como resultado podemos comparar as vendas entre o primeiro trimestre de 2019 contra o primeiro trimestre de 2020.
Pre-requistos:
CRM One Web versão 2020.07.X.X