CODEWITHSUNDEEP
reporting-servicesdoubleexpressionssrs-2012iif
MIS_Gem
MIS_Gem

Reputation: 117

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

Upvotes: 0

Views: 893

Answers (2)

Steve-o169
Steve-o169

Reputation: 2146

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")))))))

Upvotes: 2

arahman
arahman

Reputation: 585

I am pretty sure you are saying cast the output which would be string value of A* or B etc as double. Which gives the error cannot cast to double.

You would probably want to wrap the reportItems sections in the CDbl() instead to cast that number and then compare it against your static double values.

You would only cast the field this way if it really needed to be changed to a double for comparison.

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

Upvotes: 0

Related Questions