Splitting Duplicates in a Subreport - MSSQL / Crystal Reports

0

I would like to generate a PIS and COFINS tax forecast report, which is calculated based on a CFOP code of the notes issued in a given period, but would like to separate this information in "blocks" / p>

I expect the result this way:

+--------------------------------------------------------------+
|                           CRÉDITO                            |
+---------+----------------+--------------+---------+----------+
|  CFOP   |    Descrição   |  Valor Base  |   PIS   |   COFINS |
+---------+----------------+--------------+---------+----------+


+--------------------------------------------------------------+
|                      DÉBITO - Cumulativo                     |
+---------+----------------+--------------+---------+----------+
|  CFOP   |    Descrição   |  Valor Base  |   PIS   |   COFINS |
+---------+----------------+--------------+---------+----------+

+--------------------------------------------------------------+
|                    DÉBITO - Não-Cumulativo                   |
+---------+----------------+--------------+---------+----------+
|  CFOP   |    Descrição   |  Valor Base  |   PIS   |   COFINS |
+---------+----------------+--------------+---------+----------+

I believe that for this I will need to use SubReports in Crystal Reports, but I can not think of how to organize the groups in the report and subreports. Remember that all data will be based on the same period that will be passed as a parameter.

The SQL used is basically the same in all reports, changing only the percentage value for tax basis depending on CFOP.

Here is the example SQL that returns the issued NFs (which will generate the debit "blocks"):

DECLARE @PIS decimal(5,4) = 0.0165;
DECLARE @COFINS decimal(5,4) = 0.076;
DECLARE @PIS_I decimal(5,4) = 0.0065;
DECLARE @COFINS_I decimal(5,4) = 0.03;

SELECT 
    DataEmissao
    ,CFOP
    ,Base
    ,Natureza
    ,'Categoria' = UPPER(CASE
                    WHEN Base = 'A' THEN 'Imune' 
                    WHEN Base = 'B' THEN 'Deferido'
                    WHEN BASE = 'C' THEN 'Tributado'
                    ELSE '' END)
    ,[Valor Base]
    ,'PIS' = CASE 
                WHEN Base = 'A' THEN [Valor Base]*@PIS_I
                ELSE [Valor Base]*@PIS END
    ,'COFINS' = CASE 
                WHEN Base = 'A' THEN [Valor Base]*@COFINS_I
                ELSE [Valor Base]*@COFINS END

FROM

(SELECT 
    NotasFiscais.DataEmissao
    ,'CFOP' = SUBSTRING(REPLACE(REPLACE(NotasFiscais.cfop1,' ',''),'.',''),1,4)
    ,'Base' = CASE 
                    WHEN SUBSTRING(REPLACE(REPLACE(NotasFiscais.cfop1,' ',''),'.',''),5,1) = 'P' THEN SUBSTRING(REPLACE(REPLACE(NotasFiscais.cfop1,' ',''),'.',''),6,1)
                    ELSE SUBSTRING(REPLACE(REPLACE(NotasFiscais.cfop1,' ',''),'.',''),5,1)
                END
    ,'Natureza' = UPPER((SELECT NatOperacao FROM MT_MAZER.dbo.CFOP CFOP_Desc 
                    WHERE REPLACE(REPLACE(CFOP_Desc.CFOP,' ',''),'.','') = REPLACE(REPLACE(CFOP.CFOP,' ',''),'.','') AND 
                        LEN(CFOP_Desc.NatOperacao) = MAX(LEN(CFOP.NatOperacao))))
    ,'Valor Base' = SUM(NotasFiscais.ValorTotalNota)

FROM 
    DB.dbo.NotasFiscais 
    INNER JOIN DB.dbo.CFOP ON NotasFiscais.cfop1 = CFOP.CFOP

WHERE 
    CFOP.Fatura = 'S' AND
    CFOP.Devolucao = 'N' AND
    NotasFiscais.CodEmpresa = 1 AND
    NotasFiscais.Situacao = 'N' AND
    NotasFiscais.TipoNota = 'S'

GROUP BY 
    NotasFiscais.DataEmissao
    ,REPLACE(REPLACE(NotasFiscais.cfop1,' ',''),'.','')
    ,CFOP.CFOP
    ,NotasFiscais.CodEmpresa) Impostos
    
asked by anonymous 24.02.2015 / 21:07

0 answers