Inserting Text for a NULL field in Report Viewer RRS feed

  • Question

  • I need to have a text sting inserted into my report when there is a NULL value from the field the report pulls from in SQL. 

    I have not been able to find a solution this this problem, and I'm hoping to find some advice.


    The solution that I am currently trying to use involves adding some custom code to the report.  I will include the original expression for the field and will then show the new expression and the accompanying custom code.


    Original Expression:  =FormatCurrency(First(Fields!StickerSellingPrice.Value, "MainDealer_Consolidated"))


    New Expression:  =Code.SellingPrice(First(Fields!StickerSellingPrice.Value))


    Custom Code: 

    Public Function SellingPrice(ByVal value As Object) As String
    If IsNothing(value) Then
    Return "See Sales Associate"
    Return FormatCurrency((value),"MainDealer_Consolidated")
    End If
    End Function


    Just to be clear, I am inserting a price onto my report, and if the price is nothing, then I want it to read "See Sales Associate" where the price would normally be.


    Thanks for any help!




    Sunday, February 3, 2008 11:26 PM

All replies

  • I could be wrong, but I think this should work for you also



    =IIF(Cstr(First(Fields!StickerSellingPrice.Value,"MainDealer_Consolidated")) = "","See Sales Associate",FormatCurrency(First(Fields!StickerSellingPrice.Value, "MainDealer_Consolidated")))

    Monday, February 4, 2008 10:06 PM
  • This works if there is a price present, however if there is no price I get #Error in the field.



    Tuesday, February 5, 2008 12:23 AM
  • Hmmm is there anything else in this expression besides the IIF statement?


    paste exactly what you have into the forum....


    Because It must be evaluating it correctly to succeed at placing the price in.  



    Commonly you get #Error if your trying to do string joins to other data types.


    =IIF(Cstr(First(Fields!StickerSellingPrice.Value,"MainDealer_Consolidated")) = "","See Sales Associate",FormatCurrency(First(Fields!StickerSellingPrice.Value, "MainDealer_Consolidated")))

    Tuesday, February 5, 2008 2:57 PM
  • I feel that IIF should not be used here as it evaluates all the expressions.

    a simple IF .. clause is sufficient in the Code segment would do the trick.And how about using dbnull.value instead of isNothing()

    Moreover, why is First caluse being used ? isnt this required for all the rows ?

    let me know if i am wrong Smile
    Thursday, February 7, 2008 6:49 AM
  • Sorry for the delay, other areas in the project demanded my attention last week and over the weekend. 

    I tried using the IIF code again.  Once again it woiuld display the price correctly if there was one available, however if it was NULL it would display #Error.  The code that I used is below:
    =IIF(Cstr(First(Fields!StickerSellingPrice.Value,"MainDealer_Consolidated")) = "","See Sales Associate",FormatCurrency(First(Fields!StickerSellingPrice.Value, "MainDealer_Consolidated")))

    I then tried to use the report custom code again with the suggestion of changing the Nothing to dbnull.  That resulted in an error when compliling the report.

    I am on the verge of giving up, any last suggestions would be greatly appreciated!

    Monday, February 11, 2008 5:12 PM
  • ok, that's kind of Odd, I really don't have any problem handling Null values...weird.   There is a simple answer to this, just need to figure out what's going on.  If statements are the backbone of many simple reporting situations,  lets just do the same thing a few different ways until you figure out where your problem is.


    How about this


    =IIF(IsNumeric(First(Fields!StickerSellingPrice.Value,"MainDealer_Consolidated")),FormatCurrency(First(Fields!StickerSellingPrice.Value, "MainDealer_Consolidated")),"See Sales Associate")



    I think I got all my brackets right....ok, so what I did here was I changed the way were evaluating it to use a different operator... the "IsNumeric" operator...which should return false if it's not a price.   


    So, if this gets an error still, then I think it may be a problem in evaluating the field when it's not an price sent over.   The bad, yet simple answer would be to stop sending Null over, and just send blank or something you can capture easily.  But you shouldn't have to do this, I get Null values back from my sqldatabase all the time and handle them in reports with no problem.  So that's what leads me to the expression.


    So if you still get an error on this.... take out "See Sales Associate" and replace it with our actual value

    First(Fields!StickerSellingPrice.Value,"MainDealer_Consolidated")   and see if you still get an error or if it displays something.


    What this will tell you is if the "#Error" is in what your trying to display or if it's in the actual evaluation of the expression.  Knowing this will narrow down what your trying to troubleshoot.

    Tuesday, February 12, 2008 3:22 PM
  • Thanks for your continuing help! 


    I tried the new expression and it still worked fine when a price was available and still also gave the #Error when there was nothing.  I then used the exact expression below.  It returned the same results.  A price worked fine, but no price resulted in the #Error.



    =IIF(IsNumeric(First(Fields!StickerSellingPrice.Value,"MainDealer_Consolidated")),FormatCurrency(First(Fields!StickerSellingPrice.Value, "MainDealer_Consolidated")),(First(Fields!StickerSellingPrice.Value, "MainDealer_Consolidated")))

    Tuesday, February 12, 2008 4:36 PM
  • Ok, well that tells us that the problem were having is when we evaluate the expression, not in what were displaying... so now we know what we need to do, we just need to evaluate our expression correctly and we got it.



    So the interesting thing is why is your expression failing to evaluate....  and did we already try this?



    =IIF(First(Fields!StickerSellingPrice.Value,"MainDealer_Consolidated") = nothing,"See Sales Associate",FormatCurrency(First(Fields!StickerSellingPrice.Value, "MainDealer_Consolidated")))




    If this errors out too, then I'd have to guess that maybe the problem is associated with trying to use "First" against nothing





    Oh, and I read what swammingo said, and I believe he was referring to you putting what he said in a method on the Code tab.... since a regular IF statement and dbnull won't work in an expression as you noticed when you got the compiler error.




    Tuesday, February 12, 2008 4:48 PM