As Regras no KPI são as peças importantes para o melhor desempenho da ferramenta.

Saber desenvolvê-las é fundamental!

Alguns exemplos:

• SQL Simples
• Firebird
• SQL Server
• PostgreSQL
• Relacional
• KPI
• Excel

SQL simples

SELECT * FROM TABELA WHERE CAMPO = 'VALOR'

Firebird

EXECUTE BLOCK
RETURNS (PesFat NUMERIC(15,2))
AS
DECLARE DatCur Date; 
BEGIN
    SELECT PesFat FROM FATURAMENTO
    INTO PesFat;
SUSPEND;
END;

SQL Server

DECLARE @RESULT TABLE (CODCRE VARCHAR(3), DATPRG DATE, 
                       PRGURG VARCHAR(1), CODAGR INTEGER, 
                       NUMPED INTEGER, SEQIPD INTEGER, 
                       CODPRO VARCHAR(10), CODDER VARCHAR(10), 
                       PESPRG FLOAT)

DECLARE @CURENT CURSOR;
DECLARE @DATATU DATETIME;
DECLARE @PRGURG VARCHAR(1);
DECLARE @DATPRG DATE;
DECLARE @CODAGR INTEGER;
DECLARE @CODPRO VARCHAR(10);
DECLARE @CODDER VARCHAR(10);
DECLARE @NUMPED INTEGER;
DECLARE @SEQIPD INTEGER;
DECLARE @PESPRG FLOAT;
DECLARE @CODCRE VARCHAR(3);

SET @DATATU = GETDATE();

-- Programação de Produção
SET @CURENT = CURSOR FOR
	SELECT PRI.USU_CODCRE,
          PRI.USU_DATPRG, 
          PRI.USU_PRGURG, PRI.USU_CODAGR, 
          PRI.USU_NUMPED, PRI.USU_SEQIPD,
          I.CODPRO, PRI.USU_CODDER, 
          PRI.USU_QTDPED          
  	  FROM USU_T900PRI PRI, E120IPD I       
    WHERE PRI.USU_CODEMP = I.CODEMP 
      AND PRI.USU_CODFIL = I.CODFIL
      AND PRI.USU_NUMPED = I.NUMPED
      AND PRI.USU_SEQIPD = I.SEQIPD 
      AND PRI.USU_DATPRG >= PRI.USU_DATPRG - 7   
OPEN @CURENT;  

FETCH NEXT FROM @CURENT   
INTO @CODCRE, @DATPRG, @PRGURG, @CODAGR, @NUMPED, @SEQIPD, @CODPRO, @CODDER, @PESPRG 
  
WHILE @@FETCH_STATUS = 0  
BEGIN  
   
   INSERT INTO @RESULT VALUES ( @CODCRE, @DATPRG, @PRGURG, @CODAGR, @NUMPED, @SEQIPD, @CODPRO, @CODDER, @PESPRG);
  
   FETCH NEXT FROM @CURENT       
   INTO @CODCRE, @DATPRG, @PRGURG, @CODAGR, @NUMPED, @SEQIPD, @CODPRO, @CODDER, @PESPRG;
END  

CLOSE @CURENT;  
DEALLOCATE @CURENT;   

SELECT * FROM @RESULT
GO

PostgreSQL

--DROP FUNCTION IF EXISTS Regra35();
CREATE OR REPLACE FUNCTION Regra35() 
RETURNS TABLE(CODIND VARCHAR(2), 
             CODUSU VARCHAR(2), 
             NOMUSU VARCHAR(50), 
             VLRUSU FLOAT,
             KPI_CHANGE_COLOR VARCHAR(10) )
AS 
$$
DECLARE
     CURSOR_Indicadores record;
     CURSOR_UsuariosXIndicadores record; 
BEGIN 

-- Lista de Indicadores
FOR CURSOR_Indicadores IN
     SELECT I."CODIND" CODIND 
         FROM "public"."e000ind" I 
LOOP
     
     -- Usuários que possuem os indicadores
     FOR CURSOR_UsuariosXIndicadores IN
         SELECT I."CODIND" CODIND,
                 I."CODUSU" CODUSU, 
                 U."NOMUSU" NOMUSU
            FROM "public"."E100IND" I, 
                 "public"."E000USU" U 
         WHERE I."CODIND" = CURSOR_Indicadores.CODIND
             AND I."CODUSU" = U."CODUSU"
     LOOP
                                    
         KPI_CHANGE_COLOR := '';
        
         CODIND := CAST(CURSOR_UsuariosXIndicadores.CODIND AS VARCHAR(2)); 
         CODUSU := CAST(CURSOR_UsuariosXIndicadores.CODUSU AS VARCHAR(2)); 
         NOMUSU := CAST(CURSOR_UsuariosXIndicadores.NOMUSU AS VARCHAR(50)); 
         VLRUSU := 100.58;
         
         IF (CODIND = '1') THEN
             KPI_CHANGE_COLOR := '00000';
         END IF;
     
         RETURN NEXT;
         
     END LOOP;
     
END LOOP;

RETURN;
END
$$
LANGUAGE 'plpgsql';
SELECT * FROM Regra35();

Relacional

Relacional[Regra1,Regra2]

SELECT A.COLUNA1, B.COLUNA1 
  FROM Regra1 A, 
       Regra2 B
 WHERE A.CODIGO = B.CODIGO 

KPI

GetRegra/{código da regra}

Excel

{
    "Planilha":1,
    "LinhaInicial":2,
    "ColunaInicial":1,
    "ColunaFinal":10,
    "QuantidadeDeLinhas":100
}
SELECT * FROM Arquivo