Segoe UI 0 SQL ="data source=" & Parameters!ReportSvr.Value & "; Initial Catalog=" & Parameters!ReportDB.Value true Integrated 3df95c93-0bf5-4da7-a246-c7d4a515f89b GatcsDB =Parameters!BEG_DATE.Value =Parameters!BILL_YEAR.Value =Parameters!END_DATE.Value =Parameters!PROPTYPE.Value =Parameters!TD.Value WITH PA AS ( SELECT BILL_NO , BILL_YEAR , SUM(ADV_TAX+ISNULL(FEES,0)+ISNULL(FEES_OTHER,0)) AS PAIDAMT , SUM(TOTAL) AS TOTALPAIDAMT , SUM(CASE WHEN CONVERT(DATE,PAYMENTDATE) BETWEEN @BEG_DATE AND @END_DATE THEN ISNULL(ADV_TAX,0)+ISNULL(FEES,0)+ISNULL(FEES_OTHER,0) ELSE 0 END) AS PERIOD_PAIDAMT , SUM(CASE WHEN CONVERT(DATE,PAYMENTDATE) < @BEG_DATE THEN ISNULL(ADV_TAX,0)+ISNULL(FEES,0)+ISNULL(FEES_OTHER,0) ELSE 0 END) AS PREV_PERIOD_PAIDAMT FROM VPAYMENT WHERE BILL_YEAR=@BILL_YEAR AND CONVERT(DATE,PAYMENTDATE) <= @END_DATE GROUP BY BILL_NO, BILL_YEAR ) SELECT XGROUP, DESCRIP, SUM(ORIG_BILL) ORIG_BILL, SUM(ER_ADJ) AS ER_ADJ, SUM(CURR_BILL) AS CURR_BILL, SUM(COLLECTED) AS COLLECTED , SUM(UNCOLLECTED) AS UNCOLLECTED FROM ( SELECT tt.TAXTYPE AS XGROUP , BILL_NO , ROUND( (M.MILLS/MT.MILLS) * (ORG_ADVTAX + ORG_FEES + ORG_FEES_OTHER),2) AS ORIG_BILL , ROUND( (M.MILLS/MT.MILLS) * (ADV_TAX + FEES + FEES_OTHER - (ORG_ADVTAX + ORG_FEES + ORG_FEES_OTHER)),2) AS ER_ADJ , ROUND( (M.MILLS/MT.MILLS) * (ADV_TAX + FEES + FEES_OTHER),2) AS CURR_BILL , ROUND( (M.MILLS/MT.MILLS) * (PERIOD_PAIDAMT + PREV_PERIOD_PAIDAMT),2) AS COLLECTED , ROUND( (M.MILLS/MT.MILLS) * (CASE WHEN ADV_TAX + FEES + FEES_OTHER - (ISNULL(PERIOD_PAIDAMT,0) + ISNULL(PREV_PERIOD_PAIDAMT,0)) <= 0 THEN 0 ELSE ADV_TAX + FEES + FEES_OTHER - (ISNULL(PERIOD_PAIDAMT,0) + ISNULL(PREV_PERIOD_PAIDAMT,0)) END),2) AS UNCOLLECTED , ROUND( (M.MILLS/MT.MILLS) * (ORG_ADVTAX + ORG_FEES + ORG_FEES_OTHER),2) AS ORIG_ALLOCATE , TT.DESCRIP FROM ( SELECT D.CONAME , B.BILL_YEAR , B.BILL_NO , A.LEGAL_DESC , B.BILL_TYPE , B.ADV_TAX , B.INTEREST , B.PENALTY , B.FIFA , B.GED , B.BREACH , B.TITLE_SEARCH , B.MAIL_FEES , B.ADMINISTRATION , B.ADVERTISING , B.FEES , B.FEES_OTHER , ISNULL(B.COLLECT_FEES,0) AS COLLECT_FEES , B.TOTAL AS TOTAL_BILL , VB7.TOTAL_PAID_YTD , B.BALANCE_DUE , A.PROPERTYKEY--, A.REALKEY, A.PERSKEY, A.MOBILEKEY , PA.PAIDAMT , PA.TOTALPAIDAMT , PA.PERIOD_PAIDAMT , PA.PREV_PERIOD_PAIDAMT , ISNULL(B.ORG_ADVTAX ,0) ORG_ADVTAX , ISNULL(B.ORG_FEES ,0) ORG_FEES , ISNULL(B.ORG_FEES_OTHER ,0) ORG_FEES_OTHER , A.TAXDISTRIC FROM VPROPERTY A LEFT JOIN DEFAULTS D ON 1=1 INNER JOIN VBILL B ON B.PROPERTYKEY=A.PROPERTYKEY AND B.BILL_YEAR=A.DIGYR AND B.BILL_TYPE=A.PROPTYPE LEFT JOIN VOWNER O ON O.BILL_NO=B.BILL_NO AND O.BILL_YEAR=B.BILL_YEAR LEFT JOIN vBILL_BALANCE VB7 ON VB7.BILL_NO=B.BILL_NO AND VB7.BILL_YEAR=B.BILL_YEAR LEFT JOIN PA ON PA.BILL_NO=B.BILL_NO AND PA.BILL_YEAR=B.BILL_YEAR WHERE B.ADV_TAX+ISNULL(B.FEES,0)+ISNULL(B.FEES_OTHER,0)-ISNULL(PA.PREV_PERIOD_PAIDAMT,0)>0 AND B.BILL_YEAR IN (@BILL_YEAR) AND A.PROPTYPE IN ( @PROPTYPE ) AND A.TAXDISTRIC IN (@TD) UNION SELECT D.CONAME , B.BILL_YEAR , B.BILL_NO , A.LEGAL_DESC , B.BILL_TYPE , B.ADV_TAX , B.INTEREST , B.PENALTY , B.FIFA , B.GED , B.BREACH , B.TITLE_SEARCH , B.MAIL_FEES , B.ADMINISTRATION , B.ADVERTISING , B.FEES , B.FEES_OTHER , ISNULL(B.COLLECT_FEES,0) AS COLLECT_FEES , B.TOTAL AS TOTAL_BILL , VB7.TOTAL_PAID_YTD , B.BALANCE_DUE , A.PROPERTYKEY--, A.REALKEY, A.PERSKEY, A.MOBILEKEY , PA.PAIDAMT , PA.TOTALPAIDAMT , 0 AS PERIOD_PAIDAMT , PA.PREV_PERIOD_PAIDAMT , ISNULL(B.ORG_ADVTAX ,0) ORG_ADVTAX , ISNULL(B.ORG_FEES ,0) ORG_FEES , ISNULL(B.ORG_FEES_OTHER ,0) ORG_FEES_OTHER , A.TAXDISTRIC FROM VPROPERTY A LEFT JOIN DEFAULTS D ON 1=1 INNER JOIN VBILL B ON B.PROPERTYKEY=A.PROPERTYKEY AND B.BILL_YEAR=A.DIGYR AND B.BILL_TYPE=A.PROPTYPE LEFT JOIN VOWNER O ON O.BILL_NO=B.BILL_NO AND O.BILL_YEAR=B.BILL_YEAR LEFT JOIN vBILL_BALANCE VB7 ON VB7.BILL_NO=B.BILL_NO AND VB7.BILL_YEAR=B.BILL_YEAR LEFT JOIN PA ON PA.BILL_NO=B.BILL_NO AND PA.BILL_YEAR=B.BILL_YEAR WHERE B.ADV_TAX+ISNULL(B.FEES,0)+ISNULL(B.FEES_OTHER,0)-ISNULL(PA.PREV_PERIOD_PAIDAMT,0)=0 AND B.BILL_YEAR IN (@BILL_YEAR) AND A.PROPTYPE IN ( @PROPTYPE ) AND A.TAXDISTRIC IN (@TD) ) X LEFT JOIN vMILLRATE M ON M.TAXDISTRIC=X.TAXDISTRIC AND M.BILL_YEAR = CASE WHEN X.BILL_TYPE IN ('H','M','T') THEN X.BILL_YEAR-1 ELSE X.BILL_YEAR END LEFT JOIN vTAXTYPE TT ON TT.TAXTYPE=M.TAXTYPE AND TT.BILL_YEAR = CASE WHEN X.BILL_TYPE IN ('H','M','T') THEN X.BILL_YEAR-1 ELSE X.BILL_YEAR END LEFT JOIN (SELECT BILL_YEAR, TAXDISTRIC, SUM(MILLS) MILLS FROM VMILLRATE GROUP BY BILL_YEAR, TAXDISTRIC) MT ON MT.BILL_YEAR= CASE WHEN X.BILL_TYPE IN ('H','M','T') THEN X.BILL_YEAR-1 ELSE X.BILL_YEAR END AND MT.TAXDISTRIC=X.TAXDISTRIC WHERE (CASE WHEN X.BILL_TYPE='R' AND M.USE_REAL=1 THEN 1 ELSE 0 END)= 1 OR (CASE WHEN X.BILL_TYPE='P' AND M.USE_REAL=1 THEN 1 ELSE 0 END)= 1 OR (CASE WHEN X.BILL_TYPE='M' AND M.USE_MH=1 THEN 1 ELSE 0 END)= 1 OR (CASE WHEN X.BILL_TYPE='T' AND M.USE_TIMB=1 THEN 1 ELSE 0 END)= 1 OR (CASE WHEN X.BILL_TYPE='H' AND M.USE_HDE=1 THEN 1 ELSE 0 END)= 1 ) Z GROUP BY Z.XGROUP, Z.DESCRIP ORDER BY Z.XGROUP true XGROUP System.Int16 ORIG_BILL System.Decimal ER_ADJ System.Decimal CURR_BILL System.Decimal COLLECTED System.Decimal UNCOLLECTED System.Decimal DESCRIP System.String GatcsDB =Parameters!BEG_DATE.Value =Parameters!END_DATE.Value =Parameters!BILL_YEAR.Value =Parameters!TD.Value WITH PA AS ( SELECT BILL_NO , BILL_YEAR , SUM(ADV_TAX+ISNULL(FEES,0)+ISNULL(FEES_OTHER,0)) AS PAIDAMT , SUM(TOTAL) AS TOTALPAIDAMT , SUM(CASE WHEN CONVERT(DATE,PAYMENTDATE) BETWEEN @BEG_DATE AND @END_DATE THEN ISNULL(ADV_TAX,0)+ISNULL(FEES,0)+ISNULL(FEES_OTHER,0) ELSE 0 END) AS PERIOD_PAIDAMT , SUM(CASE WHEN CONVERT(DATE,PAYMENTDATE) < @BEG_DATE THEN ISNULL(ADV_TAX,0)+ISNULL(FEES,0)+ISNULL(FEES_OTHER,0) ELSE 0 END) AS PREV_PERIOD_PAIDAMT FROM VPAYMENT WHERE BILL_YEAR=@BILL_YEAR AND CONVERT(DATE,PAYMENTDATE) <= @END_DATE GROUP BY BILL_NO, BILL_YEAR ) SELECT BILL_NO , X.BILL_YEAR , ADV_TAX + FEES + FEES_OTHER - (ISNULL(PERIOD_PAIDAMT,0) + ISNULL(PREV_PERIOD_PAIDAMT,0)) AS UNCOLLECTED , M.TAXTYPE, M.MILLS , TT.DESCRIP , ROUND((M.MILLS/MT.MILLS) * (ADV_TAX + FEES + FEES_OTHER - (ISNULL(PERIOD_PAIDAMT,0) + ISNULL(PREV_PERIOD_PAIDAMT,0))),2) AS TAX FROM ( SELECT D.CONAME , B.BILL_YEAR , B.BILL_NO , A.LEGAL_DESC , B.BILL_TYPE , B.ADV_TAX , B.INTEREST , B.PENALTY , B.FIFA , B.GED , B.BREACH , B.TITLE_SEARCH , B.MAIL_FEES , B.ADMINISTRATION , B.ADVERTISING , B.FEES , B.FEES_OTHER , ISNULL(B.COLLECT_FEES,0) AS COLLECT_FEES , B.TOTAL AS TOTAL_BILL , VB7.TOTAL_PAID_YTD , B.BALANCE_DUE , A.PROPERTYKEY--, A.REALKEY, A.PERSKEY, A.MOBILEKEY , PA.PAIDAMT , PA.TOTALPAIDAMT , PA.PERIOD_PAIDAMT , PA.PREV_PERIOD_PAIDAMT , ISNULL(B.ORG_ADVTAX ,0) ORG_ADVTAX , ISNULL(B.ORG_FEES ,0) ORG_FEES , ISNULL(B.ORG_FEES_OTHER ,0) ORG_FEES_OTHER , A.TAXDISTRIC FROM VPROPERTY A LEFT JOIN DEFAULTS D ON 1=1 INNER JOIN VBILL B ON B.PROPERTYKEY=A.PROPERTYKEY AND B.BILL_YEAR=A.DIGYR AND B.BILL_TYPE=A.PROPTYPE AND B.BILL_YEAR=@BILL_YEAR LEFT JOIN VOWNER O ON O.BILL_NO=B.BILL_NO AND O.BILL_YEAR=B.BILL_YEAR LEFT JOIN vBILL_BALANCE VB7 ON VB7.BILL_NO=B.BILL_NO AND VB7.BILL_YEAR=B.BILL_YEAR LEFT JOIN PA ON PA.BILL_NO=B.BILL_NO AND PA.BILL_YEAR=B.BILL_YEAR WHERE B.ADV_TAX+ISNULL(B.FEES,0)+ISNULL(B.FEES_OTHER,0)-ISNULL(PA.PREV_PERIOD_PAIDAMT,0)>0 AND B.BILL_YEAR IN (@BILL_YEAR) --AND A.PROPTYPE IN ( @PROPTYPE ) AND A.DIGYR=@BILL_YEAR AND A.TAXDISTRIC IN (@TD) ) X LEFT JOIN vMILLRATE M ON M.TAXDISTRIC=X.TAXDISTRIC AND M.BILL_YEAR=X.BILL_YEAR LEFT JOIN vTAXTYPE TT ON TT.TAXTYPE=M.TAXTYPE AND TT.BILL_YEAR=X.BILL_YEAR LEFT JOIN (SELECT BILL_YEAR, TAXDISTRIC, SUM(MILLS) MILLS FROM VMILLRATE GROUP BY BILL_YEAR, TAXDISTRIC) MT ON MT.BILL_YEAR=X.BILL_YEAR AND MT.TAXDISTRIC=X.TAXDISTRIC ORDER BY BILL_YEAR, BILL_NO true BILL_NO System.Int32 BILL_YEAR System.Int16 UNCOLLECTED System.Decimal TAXTYPE System.Int16 MILLS System.Decimal DESCRIP System.String TAX System.Decimal GatcsDB SELECT DESCRIP, TAXDISTRIC FROM TAXDIST ORDER BY TAXDISTRIC DESCRIP System.String TAXDISTRIC System.String 2.2in 1in 1in 1in 1in 0.7in 1in 0.7in 0.22in true true DESCRIP Textbox27 PaleTurquoise 2pt 2pt 2pt 2pt true true ORIG BILL textbox3 PaleTurquoise 2pt 2pt 2pt 2pt true true E&R / NOD / ADJ textbox4 PaleTurquoise 2pt 2pt 2pt 2pt true true CURR BILL textbox5 PaleTurquoise 2pt 2pt 2pt 2pt true true COLLECTED textbox6 PaleTurquoise 2pt 2pt 2pt 2pt true true % Textbox34 PaleTurquoise 2pt 2pt 2pt 2pt true true UNCOLLECTED textbox7 PaleTurquoise 2pt 2pt 2pt 2pt true true % Textbox37 PaleTurquoise 2pt 2pt 2pt 2pt 0.25in true true =Fields!DESCRIP.Value 2pt 2pt 2pt 2pt true true =Sum(Fields!ORIG_BILL.Value) 2pt 2pt 2pt 2pt true true =Sum(Fields!ER_ADJ.Value) 2pt 2pt 2pt 2pt true true =Sum(Fields!CURR_BILL.Value) 2pt 2pt 2pt 2pt true true =Sum(Fields!COLLECTED.Value) 2pt 2pt 2pt 2pt true true =iif(sum(Fields!CURR_BILL.Value)=0,0,sum(Fields!COLLECTED.Value)/iif(sum(Fields!CURR_BILL.Value)=0,1,sum(Fields!CURR_BILL.Value))) 2pt 2pt 2pt 2pt true true =Sum(Fields!UNCOLLECTED.Value) 2pt 2pt 2pt 2pt true true =1-iif(sum(Fields!CURR_BILL.Value)=0,0,sum(Fields!COLLECTED.Value)/iif(sum(Fields!CURR_BILL.Value)=0,1,sum(Fields!CURR_BILL.Value))) 2pt 2pt 2pt 2pt 0.21in true true =Fields!DESCRIP.Value 2pt 2pt 2pt 2pt true true =Fields!ORIG_BILL.Value 2pt 2pt 2pt 2pt true true =Fields!ER_ADJ.Value 2pt 2pt 2pt 2pt true true =Fields!CURR_BILL.Value 2pt 2pt 2pt 2pt true true =Fields!COLLECTED.Value 2pt 2pt 2pt 2pt true true 2pt 2pt 2pt 2pt true true =Fields!UNCOLLECTED.Value 2pt 2pt 2pt 2pt true true 2pt 2pt 2pt 2pt 0.25in true true LightGreen 2pt 2pt 2pt 2pt true true =Sum(Fields!ORIG_BILL.Value) LightGreen 2pt 2pt 2pt 2pt true true =Sum(Fields!ER_ADJ.Value) LightGreen 2pt 2pt 2pt 2pt true true =Sum(Fields!CURR_BILL.Value) LightGreen 2pt 2pt 2pt 2pt true true =Sum(Fields!COLLECTED.Value) LightGreen 2pt 2pt 2pt 2pt true true LightGreen 2pt 2pt 2pt 2pt true true =Sum(Fields!UNCOLLECTED.Value) Lime 2pt 2pt 2pt 2pt true true true LightGreen 2pt 2pt 2pt 2pt true =Fields!DESCRIP.Value =Fields!XGROUP.Value After Detail true Detail_Collection Output true Before DataSet1 0.93in 8.6in FROM 0.4375in 1.81771in 0.25in 3.51041in 2pt 2pt 2pt 2pt true true Unpaid Balance Summary textbox1 0.0625in 0.36in 7in 1 true true ="Bill Type(s): " + Join(Parameters!PROPTYPE.Value,", ") Textbox24 0.6875in 2.24062in 0.25in 2.66493in 2 2pt 2pt 2pt 2pt true true ="Tax District(s): " + Join(Parameters!TD.Value, ", ") Textbox24 0.94583in 2.24254in 0.25in 2.66493in 3 2pt 2pt 2pt 2pt 8.5in 11in 1in 1in 1in 1in