Search code examples
sqlsql-serverreporting-servicesssrs-2008

SSRS 2008R2 Custom value for #Error

SSRS 2008R2 Custom value for #Error The attached picture is the output of my SSRS 2008R2 report. I want to change the #Error to “ON Target”. I have used different functions without luck. The code I used is THE QUESTION IS IF Percentage>= 0.92 THEN RETURN "ON TARGET" ELSE RETUN Grand_Total-Under_2_months

 =iif(ReportItems!Year.value>=0.92,"On Target",ReportItems!Textbox12.value-ReportItems!Under18Total.value)

enter image description here This produce the attached report for TARGET TO MEET Column. Can someone help to return the custom code (“ON Target”.)

THIS IS THE DATA THAT GENERATED THE REPORT

DECLARE @Types TABLE
([Type] VARCHAR(20),    Under_2_months INT, Over_2_months INT,  Grand_Total INT,    Percentage DECIMAL(10,4))
INSERT INTO @Types VALUES
('Types1',  '1401', '969',  '2370', '0.591139240506329'),
('Types2',  '23487',    '1942', '25429',    '0.92363050060954'),
('Grand Total', '24888',    '2911', '27799',    '0.895284003021691')
SELECT * FROM @Types
Solution

  • You can't change the error value. This is thrown by the compiler, in my experience it is usually a math related issue such as trying to divide by zero. Some in the comments also mentioned it might be a data type mismatch.

    I've attempted to detect #Error in the ReportItem and hide it but that doesn't work either. Your best bet is to fix your code so it doesn't error.

    Check that the data type for your "Target to Meet" textbox is not set to numeric. If that's not the problem, break your IIF down to the basics and build it back bit by bit until it throws the error and you can identify what the problem code is.