none
Issue running with DECLARE statement in SSRS report

    Question

  • I am trying to use Query Type as Text under my DataSet Properties, with below query and receiving error for SSRS report.

    Please advise what am I doing wrong. I am using VS2015 SQL Sever Data tools

    Error : 

    Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index

    DECLARE
    	@StartDate VARCHAR(08)= CONVERT(CHAR(10),DATEADD(wk,DATEDIFF(wk,7,GETDATE()),-1),112) ,
    	@EndDate VARCHAR(08) = CONVERT(CHAR(10),DATEADD(wk,DATEDIFF(wk,7,GETDATE()),5),112)
    
    ;WITH CTE1 AS (
    	select insurance_CODE , @StartDate as StartDate ,@EndDate as EndDate, SUM(OTAL_AMOUNT) AS InvoiceTotalAmount 
    	from Testdb.InsuranceTable with (nolock)
    	where insurance_code in ('AIA','CPM', 'PLZ') AND STATUS_ID IN (1) AND INVOICE_DATE  between @StartDate and @EndDate 
    	group by INSURANCE_code
    ),
    CTE2 AS (
    	select INSURANCE_code, SUM(TOTAL_AMOUNT) AS TotalExceptionAmount 
    	from Testdb.InsuranceTable (nolock)
    	where insurance_code in ('AIA','CPM', 'PLZ') AND STATUS_ID in (2) AND INVOICE_DATE  between @StartDate and @EndDate 
    	group by INSURANCE_code 
    ),
    CTE3 AS (
    	select INSURANCE_code, SUM(TOTAL_AMOUNT) AS HoldAmount 
    	from Testdb.InsuranceTable (nolock)
    	where insurance_code in ('AIA','CPM', 'PLZ') AND STATUS_ID in (3) AND INVOICE_DATE between @StartDate and @EndDate 
    	group by INSURANCE_code
    )
    
    SELECT C1.insurance_CODE, C1.StartDate, C1.EndDate, C1.TotalAmount, C2.TotalExceptionAmount, C3.HoldAmount 
    FROM CTE1 AS C1
    INNER JOIN CTE2 AS C2 ON C2.INSURANCE_code = C1.insurance_CODE
    INNER JOIN CTE3 AS C3 ON C3.INSURANCE_code = C1.insurance_CODE

    Monday, May 20, 2019 5:58 PM

All replies