Tabelas dinâmicas

Tabela Dinâmica no CRM One Web

O uso de tabelas dinâmicas (ou pivot tables) é uma excelente formamaneira de fazerrealizar análises gerenciais decom forma ágilrapidez e dinâmica.flexibilidade. VisandoCom a disponibilização de uma funcionalidade prática foi implementado no CRM One web este novoesse recurso, queé permitirápossível a execução decriar relatórios noque formatopodem ser montados diretamente na tela pelo usuário, sem depender de tabelanovas dinâmicaconsultas (pivotpara table).cada tipo de análise.

Cenário de exemploExemplo

ONeste primeiroexemplo, passoserá paracriado um bom relatório é definir os dados necessários, neste caso vamos pensarbaseado em um cenário relacionado a notas fiscais de saída,da. sendoPara assimisso, vamossão precisarutilizadas extrairas informaçõesseguintes tabelas do SAP Business One das tabelas de nota fiscal de saída.One:

• OINV – nota fiscal de saída cabeçCabeçalho
• INV1 – linhas da nota fiscal de saída

INV1

–

Para saber quem foi o vendedorLinhas da nota fiscal, será realizada a ligação com a tabela que carrega o nome dos vendedores.

fiscal
• OSLP – vendedoresVendedores

 

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çUtilizações para(operações 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.

fiscais)
• OSHP – tipoTipo 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 cadastroCadastro do parceiro de negócios
• OCRG – gruposGrupos 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 – cadastroCadastro de itens
• OITB – gruposGrupos 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:

1. OINV
2. INV1
3. OSLP
4. OUSG
5. OSHP
6. OCRD
7. OCRG
8. OITM
9. 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:

Dica Se1 observar– noGere detalhe,totalizadores nacom imagemcolunas acimafixas

as

Na consulta SQL, são utilizadas duas últimas colunas apresentamfixas umachamadas informaçã"Total linhas" e "Total colunas". Esses campos possuem o curiosa,mesmo em ambas as colunasvalor para todas as linhas sãretornadas, e seu objetivo não apresentadasé asexibir 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 “Totalé linhas”,realizado “Totaldiretamente colunas”no cujonavegador. nomePara dasevitar colunaslentidão na visualização ou travamentos durante a análise, é respectivamente igual ao conteúdo das linhas. Esta informação será usada para gerar “totalizadores” e mais adiante será possível verificar o efeitoimportante que elasa podemconsulta proporcionar.SQL esteja otimizada. Recomendações:

#Dica

2:
• Evite o uso deretornar grandes volumes de informaçãodados em um formato bruto,bruto;
lembre-se
• Utilize quefiltros osna dadosprópria SQL para exibiçãlimitar o serãoresultado processados(por período, tipo de documento, status etc.);
• Realize agrupamentos e cálculos dentro deda umprópria navegador de internet (browser), realizeSQL sempre que possívelvel. agrupamentosEssas préviospráticas contribuem para que a montagem da tabela dinâmica seja mais ágil e algunseficiente, cálculosalém dentrode damelhorar consultaa SQL,experiência assimde navegação processamento será sempre mais leve na tela do CRMusuário.
One web.

Configuração

Com a consulta pronta será necessário configurarConfigurando no CRM One web,Web acessar

1. Acesse o menu > Ferramentas > Configuração de consultas
   Consultas.



2. Na aba “Importação de consultas” serão realizadas as configurações:
   Consultas:
   1. No campo “Nome no menu”menu: preencher com a descrição que deverá ser exibida, neste exemplo “Análise de vendas”.vendas
   2. Em “Tipo consulta”de selecionarconsulta: selecione a árvorecategoria desejada no menu
   3. Consulta ativa: Sim
   4. Todos usuários: Sim
   5. Tipo de menuconsulta: desejadaTabela para exibição do novo menu.dinâmica
   6. SelecionarCole “Sim”a consulta SQL no campo indicado
   7. Clique em “ConsultaTestar ativa”.consulta
   8. SelecionarPor “Sim”fim, clique em “Todos usuários”.Adicionar
   9. Em
   “Tipo
consulta

Executando o Relatório

Acesse o menu onde o relatório foi criado. Ao selecionar a opçãoo, “Tabelauma dinâmica”.

nova

Informaraba aserá consultaaberta SQL desenvolvida.

Não deixe de executar o “Testar consulta”.

Clique em “Adicionar” para concluir.

Executandocom a tabela dinâmica

mica.

ApósClique aem conclusãoCenário daselecionado configuração,para seráabrir possívelos executarfiltros ae consultaem criada,seguida localizeclique aem novaGerar opção dentro do menu definido na configuração, neste exemplo em: > Menu > Vendas > Tabela dinâmicaRelató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)

Com

a

Primeira execução

1. Arraste Nome do PN para o quadro Linhas.
2. Arraste Total para Valores.
3. No botão de configuração do relatório,campo será apresentada a área de “personalização/montagem” da tabela dinâmica, que é composto por 5 áreas principais:
   Total:
   • “Campos tabela dinâmica” é Função localcálculo: onde são exibidos todos os campos/colunas selecionados na consulta SQL.Soma
   • “Filtros”Prefixo émoeda: umaR$
   área destinada a seleção
   • Separador de quaismilhar: campos deseja-se que sejam usados para condicionar as informações..
   • “Colunas”Separador iráde determinardecimais: 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

NaEsse primeiraformato execuçãomostrará vamosa fazersoma umatotal exibiçãode simplesvendas somentepor “vertical” das informações.cliente.

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çVisualização realizaremosAvançada 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

1. Arraste o campo “Ano”Ano epara arrastadoColunas.
2. Para totalizadores:
   • Arraste Total linhas para o topo do quadro Linhas
   • Arraste Total colunas para o topo do quadro Colunas

Aplicando Filtros

1. Arraste Ano para o quadro “Colunas”.
   Filtros



2. Clique

   Comono resultado, podemos visualizar o “Total”ícone de notasbarras fiscaise de saída emitidas por cliente, desconsiderandoselecione os documentosanos cancelados,desejados com(ex: umasomente divisão2020)

anual.
3. Adicione

   

   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 é arrastarnovamente 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 “+”Ano em qualquerColunas, umse dosdesejar eixos,manter seráa possíveldivisão visualizaranual
   

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

Exemplo de cadaCenário quadro pode ser alterada a qualquer momento, gerando a cada modificação uma nova possibilidade de análise.

Complexo

Abaixo podemos visualizar um cenário mais complexo, onde:

• Nas linhas (eixo vertical) foram colocados os campos:Linhas: Vendedor, Grupo de itens, UF, Nome do PN, Descrição do item.item
• NasColunas: colunasAno, (eixoMês
horizontal) foram colocados os campos:
• Filtros: Ano e mês.
• Na parte de filtros foram informados: Ano e mês.
• Realizado filtro dos anos de (2019 e 2020.
2020),
• RealizadoMês filtro dos meses de (janeiro, fevereiro efevereiro, março.o)

ComoEsse resultadomodelo podemospermite comparar asos vendasresultados entre otrimestres primeiroou trimestreanos dediferentes 2019com contra o primeiro trimestre de 2020.facilidade.