Search code examples
reporting-servicesdoubleexpressionssrs-2012iif

Performing IIF on a Double value in SSRS

I currently have a column in my report that is calculating an Average:

=FORMAT(Avg(Fields!intGradeTransposeValue.Value),"#.#")

I am currently trying to use the result of that calculation to then perform an IIF statement, but I am getting an error:

The Value expression for the textrun 'Textbox20.Paragraphs[0].TextRuns[0]' contains an error: [BC30311] Value of Type 'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItem' cannot be converted to 'Double'

My expressions is:

=CDbl(IIF(ReportItems!Textbox6 <= 1.4, "A*", 
        IIF(ReportItems!Textbox6 <= 2.4 AND >= 1.5, "A",
            IIF(ReportItems!Textbox6 <= 3.4 AND >= 2.5, "B",
                IIF(ReportItems!Textbox6 <= 4.4 AND >= 3.5, "C",
                    IIF(ReportItems!Textbox6 <= 5.4 AND >= 4.5, "D"
                        IIF(ReportItems!Textbox6 <= 6.4 AND >= 5.5, "E"
                            IIF(ReportItems!Textbox6 <= 7.4 AND >= 6.5, "U", "Error"))))))))

How can I do the calculation I need?

ADDITIONAL

I have added below where the error is coming - this is confusing me as it seems to be accepting the first double of 1.4. I am using the suggested answer below, but this is also not working.

Double Issue

Solution

  • Based on the error you are receiving, the issue is that you are attempting to convert the ReportItems!Textbox6 itself to a double, not the value within it. In order to reference the value of the Textbox, you'll need to reference it as ReportItems!Textbox6.Value. So I believe the answer you'll need will combine arahman's answer with this change:

    =IIF(CDbl(ReportItems!Textbox6.Value) <= 1.4, "A*", 
    IIF(CDbl(ReportItems!Textbox6.Value) <= 2.4 AND >= 1.5, "A",
        IIF(CDbl(ReportItems!Textbox6.Value) <= 3.4 AND >= 2.5, "B",
            IIF(CDbl(ReportItems!Textbox6.Value) <= 4.4 AND >= 3.5, "C",
                IIF(CDbl(ReportItems!Textbox6.Value) <= 5.4 AND >= 4.5, "D"
                    IIF(CDbl(ReportItems!Textbox6.Value) <= 6.4 AND >= 5.5, "E"
                        IIF(CDbl(ReportItems!Textbox6.Value) <= 7.4 AND >= 6.5, "U", "Error")))))))

    Source

    Based on the edit to the original post, I missed the other issue with the expression. You can't compare the values like that. You'll need to add additional references to the Textbox value after the AND.

    =IIF(CDbl(ReportItems!Textbox6.Value) <= 1.4, "A*", 
    IIF(CDbl(ReportItems!Textbox6.Value) <= 2.4 AND CDbl(ReportItems!Textbox6.Value) >= 1.5, "A",
        IIF(CDbl(ReportItems!Textbox6.Value) <= 3.4 AND CDbl(ReportItems!Textbox6.Value) >= 2.5, "B",
            IIF(CDbl(ReportItems!Textbox6.Value) <= 4.4 AND CDbl(ReportItems!Textbox6.Value) >= 3.5, "C",
                IIF(CDbl(ReportItems!Textbox6.Value) <= 5.4 AND CDbl(ReportItems!Textbox6.Value) >= 4.5, "D",
                    IIF(CDbl(ReportItems!Textbox6.Value) <= 6.4 AND CDbl(ReportItems!Textbox6.Value) >= 5.5, "E",
                        IIF(CDbl(ReportItems!Textbox6.Value) <= 7.4 AND CDbl(ReportItems!Textbox6.Value) >= 6.5, "U", "Error")))))))