Bom dia.
Eu crio views no banco de dados e puxo destas views para o Power BI.
Alguns abaixo:
1. Absenteismo:
SELECT DISTINCT
dbo.PFUNC.CODCOLIGADA, dbo.GCOLIGADA.NOME AS COLIGADA, dbo.PFUNC.NOME AS FUNCIONARIO, RIGHT(LEFT(dbo.PFUNC.CODSECAO, 6), 3) AS CODSECAO,
dbo.PSECAO.DESCRICAO AS SECAO, dbo.PFFINANC.REF AS FALTAS, dbo.PFFINANC.MESCOMP AS MES, dbo.PFFINANC.ANOCOMP AS ANO, dbo.PFFINANC.DTPAGTO,
UPPER(dbo.PSECAO.CIDADE) AS FILIAL, dbo.PFUNCAO.NOME AS FUNCAO
FROM dbo.PFUNC WITH (NOLOCK) INNER JOIN
dbo.GCOLIGADA WITH (NOLOCK) ON dbo.PFUNC.CODCOLIGADA = dbo.GCOLIGADA.CODCOLIGADA INNER JOIN
dbo.GFILIAL WITH (NOLOCK) ON dbo.PFUNC.CODCOLIGADA = dbo.GFILIAL.CODCOLIGADA INNER JOIN
dbo.PSECAO WITH (NOLOCK) ON dbo.GFILIAL.CODCOLIGADA = dbo.PSECAO.CODCOLIGADA AND dbo.PFUNC.CODSECAO = dbo.PSECAO.CODIGO AND
dbo.PFUNC.CODSECAO = dbo.PSECAO.CODIGO AND dbo.GFILIAL.CODFILIAL = dbo.PSECAO.CODFILIAL INNER JOIN
dbo.PFFINANC WITH (NOLOCK) ON dbo.PFUNC.CODCOLIGADA = dbo.PFFINANC.CODCOLIGADA AND dbo.PFUNC.CHAPA = dbo.PFFINANC.CHAPA INNER JOIN
dbo.PEVENTO WITH (NOLOCK) ON dbo.PFFINANC.CODEVENTO = dbo.PEVENTO.CODIGO INNER JOIN
dbo.PFUNCAO WITH (NOLOCK) ON dbo.PFUNC.CODCOLIGADA = dbo.PFUNCAO.CODCOLIGADA AND dbo.PFUNC.CODFUNCAO = dbo.PFUNCAO.CODIGO AND
dbo.GCOLIGADA.CODCOLIGADA = dbo.PFUNCAO.CODCOLIGADA
WHERE ( dbo.PEVENTO.CODIGOCALCULO = 8 )
2. Saida Funcionários:
SELECT TOP (100) PERCENT dbo.GCOLIGADA.NOME AS COLIGADA, dbo.PSECAO.DESCRICAO AS SECAO, dbo.PFUNCAO.NOME AS FUNCAO, COUNT(dbo.PFUNC.CHAPA)
AS QUANTIDADE_DEMISSOES, DATEPART(MM, dbo.PFUNC.DATADEMISSAO) AS MES, DATEPART(YEAR, dbo.PFUNC.DATADEMISSAO) AS ANO,
dbo.PSECAO.CIDADE AS FILIAL, dbo.PFUNC.DATADEMISSAO, dbo.PFUNC.CODSECAO
FROM dbo.PFUNC WITH (NOLOCK) INNER JOIN
dbo.GCOLIGADA WITH (NOLOCK) ON dbo.PFUNC.CODCOLIGADA = dbo.GCOLIGADA.CODCOLIGADA INNER JOIN
dbo.PSECAO WITH (NOLOCK) ON dbo.GCOLIGADA.CODCOLIGADA = dbo.PSECAO.CODCOLIGADA AND dbo.PFUNC.CODSECAO = dbo.PSECAO.CODIGO INNER JOIN
dbo.PFUNCAO WITH (NOLOCK) ON dbo.GCOLIGADA.CODCOLIGADA = dbo.PFUNCAO.CODCOLIGADA AND dbo.PFUNC.CODFUNCAO = dbo.PFUNCAO.CODIGO
WHERE (dbo.PFUNC.DATADEMISSAO IS NOT NULL)
GROUP BY dbo.GCOLIGADA.NOME, dbo.PSECAO.DESCRICAO, dbo.PFUNCAO.NOME, DATEPART(MM, dbo.PFUNC.DATADEMISSAO), DATEPART(YEAR,
dbo.PFUNC.DATADEMISSAO), dbo.PSECAO.CIDADE, dbo.PFUNC.DATADEMISSAO, dbo.PFUNC.CODSECAO
ORDER BY DATEPART(MONTH, dbo.PFUNC.DATADEMISSAO)
3. Entrada Funcionário:
SELECT dbo.FN_FORMATAR_TEXTO(REPLACE(dbo.GCOLIGADA.NOME, '/', '')) AS COLIGADA, dbo.PFUNC.CODSECAO, dbo.PSECAO.DESCRICAO AS SECAO,
dbo.PFUNCAO.NOME AS FUNCAO, COUNT(DISTINCT dbo.PFUNC.CHAPA) AS QUANTIDADE_ADMISSOES, DATEPART(MM, dbo.PFUNC.DATAADMISSAO) AS MES,
DATEPART(YEAR, dbo.PFUNC.DATAADMISSAO) AS ANO, dbo.PSECAO.CIDADE AS FILIAL, dbo.PFUNC.DATAADMISSAO, dbo.GCOLIGADA.CODCOLIGADA
FROM dbo.PFUNC WITH (NOLOCK) INNER JOIN
dbo.GCOLIGADA WITH (NOLOCK) ON dbo.PFUNC.CODCOLIGADA = dbo.GCOLIGADA.CODCOLIGADA INNER JOIN
dbo.PSECAO WITH (NOLOCK) ON dbo.GCOLIGADA.CODCOLIGADA = dbo.PSECAO.CODCOLIGADA AND dbo.PFUNC.CODSECAO = dbo.PSECAO.CODIGO INNER JOIN
dbo.PFUNCAO WITH (NOLOCK) ON dbo.PSECAO.CODCOLIGADA = dbo.PFUNCAO.CODCOLIGADA AND dbo.PFUNC.CODFUNCAO = dbo.PFUNCAO.CODIGO
GROUP BY dbo.GCOLIGADA.NOME, dbo.PFUNC.CODSECAO, dbo.PSECAO.DESCRICAO, dbo.PFUNCAO.NOME, DATEPART(MM, dbo.PFUNC.DATAADMISSAO), DATEPART(YEAR,
dbo.PFUNC.DATAADMISSAO), dbo.PSECAO.CIDADE, dbo.PFUNC.DATAADMISSAO, dbo.GCOLIGADA.CODCOLIGADA
4. Folha Completa:
SELECT dbo.GCOLIGADA.NOME AS COLIGADA, dbo.PFUNC.CODCOLIGADA, dbo.PFUNC.CHAPA, dbo.PFUNC.CODFILIAL, dbo.PFUNC.NOME AS NOMEFUNCIONARIO,
dbo.PFUNC.SALARIO, RIGHT(LEFT(dbo.PFUNC.CODSECAO, 6), 3) AS CODSECAO, dbo.PFUNC.CODHORARIO, dbo.AHORARIO.DESCRICAO,
dbo.PSECAO.DESCRICAO AS SECAO, dbo.PFUNC.CODSITUACAO, dbo.PFUNCAO.NOME AS FUNCAO, dbo.PEVENTO.PROVDESCBASE AS PDB,
dbo.PEVENTO.DESCRICAO AS EVENTO, dbo.PFFINANC.ANOCOMP AS ANO, dbo.PFFINANC.MESCOMP AS MES, dbo.PFFINANC.HORA, dbo.PFFINANC.NROPERIODO,
CASE WHEN PEVENTO.PROVDESCBASE = 'B' THEN PFFINANC.VALOR * 0 WHEN PEVENTO.PROVDESCBASE = 'D' THEN PFFINANC.VALOR * - 1 WHEN PEVENTO.PROVDESCBASE
= 'P' THEN PFFINANC.VALOR END AS VALOR, dbo.PFCOMPL.ADM, dbo.PFCOMPL.ADOB, dbo.PFFINANC.DTPAGTO, dbo.PSECAO.CIDADE AS FILIAL,
dbo.PFFINANC.CODEVENTO
FROM dbo.PEVENTO WITH (NOLOCK) INNER JOIN
dbo.PFFINANC WITH (NOLOCK) ON dbo.PEVENTO.CODCOLIGADA = dbo.PFFINANC.CODCOLIGADA AND
dbo.PEVENTO.CODIGO = dbo.PFFINANC.CODEVENTO INNER JOIN
dbo.PFUNC WITH (NOLOCK) INNER JOIN
dbo.PSECAO WITH (NOLOCK) ON dbo.PFUNC.CODCOLIGADA = dbo.PSECAO.CODCOLIGADA AND dbo.PFUNC.CODSECAO = dbo.PSECAO.CODIGO INNER JOIN
dbo.PFUNCAO WITH (NOLOCK) ON dbo.PFUNC.CODCOLIGADA = dbo.PFUNCAO.CODCOLIGADA AND dbo.PFUNC.CODFUNCAO = dbo.PFUNCAO.CODIGO INNER JOIN
dbo.GCOLIGADA WITH (NOLOCK) ON dbo.PFUNC.CODCOLIGADA = dbo.GCOLIGADA.CODCOLIGADA AND
dbo.PFUNCAO.CODCOLIGADA = dbo.GCOLIGADA.CODCOLIGADA AND dbo.PSECAO.CODCOLIGADA = dbo.GCOLIGADA.CODCOLIGADA ON
dbo.PFFINANC.CODCOLIGADA = dbo.PFUNC.CODCOLIGADA AND dbo.PFFINANC.CHAPA = dbo.PFUNC.CHAPA AND
dbo.PFFINANC.CODCOLIGADA = dbo.GCOLIGADA.CODCOLIGADA AND dbo.PEVENTO.CODCOLIGADA = dbo.GCOLIGADA.CODCOLIGADA INNER JOIN
dbo.AHORARIO WITH (NOLOCK) ON dbo.PFUNC.CODHORARIO = dbo.AHORARIO.CODIGO AND
dbo.GCOLIGADA.CODCOLIGADA = dbo.AHORARIO.CODCOLIGADA INNER JOIN
dbo.PFCOMPL WITH (NOLOCK) ON dbo.PFUNC.CHAPA = dbo.PFCOMPL.CHAPA AND dbo.PFUNC.CODCOLIGADA = dbo.PFCOMPL.CODCOLIGADA
WHERE (dbo.AHORARIO.CODCOLIGADA = dbo.AHORARIO.CODCOLIGADA) AND (dbo.PFFINANC.ANOCOMP >= 2015) AND (dbo.PEVENTO.PROVDESCBASE <> 'B')
5. Hora Extra:
SELECT DISTINCT
dbo.GCOLIGADA.NOME AS COLIGADA, dbo.PFUNC.NOME AS FUNCIONARIO, RIGHT(LEFT(dbo.PFUNC.CODSECAO, 6), 3) AS CODSECAO,
dbo.PSECAO.DESCRICAO AS SECAO, dbo.PFFINANC.REF AS EXTRA, dbo.PFFINANC.MESCOMP AS MES, dbo.PFFINANC.ANOCOMP AS ANO, dbo.PFFINANC.DTPAGTO,
dbo.PSECAO.CIDADE AS FILIAL, dbo.PFUNCAO.NOME AS FUNCAO
FROM dbo.PFUNC WITH (NOLOCK) INNER JOIN
dbo.GCOLIGADA WITH (NOLOCK) ON dbo.PFUNC.CODCOLIGADA = dbo.GCOLIGADA.CODCOLIGADA INNER JOIN
dbo.GFILIAL WITH (NOLOCK) ON dbo.PFUNC.CODCOLIGADA = dbo.GFILIAL.CODCOLIGADA INNER JOIN
dbo.PSECAO WITH (NOLOCK) ON dbo.GFILIAL.CODCOLIGADA = dbo.PSECAO.CODCOLIGADA AND dbo.PFUNC.CODSECAO = dbo.PSECAO.CODIGO AND
dbo.PFUNC.CODSECAO = dbo.PSECAO.CODIGO AND dbo.GFILIAL.CODFILIAL = dbo.PSECAO.CODFILIAL INNER JOIN
dbo.PFFINANC WITH (NOLOCK) ON dbo.PFUNC.CODCOLIGADA = dbo.PFFINANC.CODCOLIGADA AND dbo.PFUNC.CHAPA = dbo.PFFINANC.CHAPA INNER JOIN
dbo.PEVENTO WITH (NOLOCK) ON dbo.PFFINANC.CODEVENTO = dbo.PEVENTO.CODIGO INNER JOIN
dbo.PFUNCAO ON dbo.PFUNC.CODCOLIGADA = dbo.PFUNCAO.CODCOLIGADA AND dbo.PFUNC.CODFUNCAO = dbo.PFUNCAO.CODIGO AND
dbo.GCOLIGADA.CODCOLIGADA = dbo.PFUNCAO.CODCOLIGADA
WHERE (dbo.PEVENTO.CODIGO = '0054') OR
(dbo.PEVENTO.CODIGO = '0055') OR
(dbo.PEVENTO.CODIGO = '0191') OR
(dbo.PEVENTO.CODIGO = '0372')
6. Faixas Salariais:
SELECT TOP (100) PERCENT A.CHAPA, A.NOME, B.NOME AS funcao, A.SALARIO, CASE WHEN SALARIO < 1000 THEN '< 1000' WHEN SALARIO >= 1000 AND
salario < 2000 THEN 'De 1000 a 2000' WHEN SALARIO >= 2000 AND salario < 3000 THEN 'De 2000 a 3000' WHEN SALARIO >= 3000 AND
salario < 5000 THEN 'De 3000 a 5000' WHEN SALARIO >= 5000 AND salario < 10000 THEN 'De 5000 a 10000' ELSE '> 10000' END AS [Faixa Salarial]
FROM dbo.PFUNC AS A INNER JOIN
dbo.PFUNCAO AS B ON A.CODCOLIGADA = B.CODCOLIGADA AND A.CODFUNCAO = B.CODIGO
WHERE (A.CODSITUACAO <> 'D') AND (A.CODSITUACAO <> 'I')
ORDER BY A.SALARIO