locked
SSRS query error RRS feed

  • Question

  • We've built a query which sums data from date range and divides 2 values.  However, when the query runs, and the denominator value is 0, it throws an error and the report does not run.  This is because early in the year, there may not be criteria that returns a value.  As soon as 1 query finds data, the report runs fine.  The entire query runs for each month of the year and yearly total.  this is the January portion. 

    ((SUM(CASE WHEN FilteredSynact_Loan.synact_originationdate >= '1/1/' + CAST(@yearNum AS NVARCHAR) AND FilteredSynact_Loan.synact_originationdate <'2/1/' + CAST(@yearNum AS NVARCHAR) THEN FilteredSynact_Loan.synact_yieldspreadusd ELSE 0 END))/(SUM(CASE WHEN FilteredSynact_Loan.synact_originationdate >= '1/1/' + CAST(@yearNum AS NVARCHAR) AND FilteredSynact_Loan.synact_originationdate <'2/1/' + CAST(@yearNum AS NVARCHAR) THEN FilteredSynact_Loan.synact_commitment ELSE 0 END)) )AS JanYieldSpread,

    How can I alter to display a 0 or NA or something as placeholder indicating no records were found that meet the criteria and allow the report to run?


    Ken Compter

    Monday, February 4, 2013 8:18 PM

All replies

  • Put a case when denominator value is 0 just display the value as 0 (before dividing) else divided values..


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful". CRMHUNT http://crmhunt.com

    Tuesday, February 5, 2013 9:03 AM