Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011
    Posts
    2

    Unanswered: Reporting Services Report Builder

    Hi all
    Sorry if this question has been asked before. I am a "newbie" to Reporting Services Report Builder and am having difficulties in creating an expression to exclude blank rows in a single name/address text box.

    I have 7 fields in my database:
    Name1
    Name2
    Name3
    Address
    City
    State
    Postcode

    An example is:
    Name1 field: Professor Fred Flintstone
    Name2 field: Director of Technology
    Name3 field: University of New South Wales
    Address field: 100 Test Road
    City: Sydney
    State: NSW
    Postcode: 2000

    But some address might also be:
    Name1 field: Professor Barney Rubble
    Name2 field: no value
    Name3 field: no value
    Address field: PO Box 1234
    City: Sydney
    State: NSW
    Postcode: 2000

    In Report Builder I want to the address field to not display where there is no value.

    I am using this expression in the text box:

    =IIF(IsNothing(First(Fields!NAME1.Value, "DataSet2")),"",First(Fields!NAME1.Value, "DataSet2") + Chr(13) + Chr(10) ) +
    IIF(IsNothing(First(Fields!NAME2.Value, "DataSet2")),"",First(Fields!NAME2.Value, "DataSet2") + Chr(13) + Chr(10) ) +
    IIF(IsNothing(First(Fields!NAME3.Value, "DataSet2")),"",First(Fields!NAME3.Value, "DataSet2") + Chr(13) + Chr(10) ) +
    First(Fields!ADDRESS.Value, "DataSet2") + Chr(13) + Chr(10) + First(Fields!CITY.Value, "DataSet1")+ " " +
    First(Fields!STATE.Value, "DataSet2") + " " +
    First(Fields!PCODE.Value, "DataSet2")

    The fields NAME2 and NAME3 don't always have a value and where this is no value the report is displaying the address field with empty rows, e.g.

    Professor Barney Rubble


    PO Box 1234
    Sydney NSW 2000

    What I want is the address to display with the empty rows removed, e.g.

    Professor Barney Rubble
    PO Box 1234
    Sydney NSW 2000

    Any assistance would be greatly appreciated.
    thanks

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Move your carriage returns into the IIF statement. At the moment, you're instructing the report to insert a carriage return regardless of whether the line has data. If you move them into the IIF statement, they will only be included in the report if there is data for that line.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Aug 2011
    Posts
    2
    Thanks Weejas for your quick response, I tried your suggestion to include the Chr(13) within the IIF statement, as below, but still get blank rows on the report

    =IIF(IsNothing(First(Fields!NAME1.Value, "DataSet2")),"",First(Fields!NAME1.Value, "DataSet2") + Chr(13)) + Chr(10) +
    IIF(IsNothing(First(Fields!NAME2.Value, "DataSet2")),"",First(Fields!NAME2.Value, "DataSet2") + Chr(13)) + Chr(10) +
    IIF(IsNothing(First(Fields!NAME3.Value, "DataSet2")),"",First(Fields!NAME3.Value, "DataSet2") + Chr(13)) + Chr(10) +
    First(Fields!ADDRESS.Value, "DataSet2") + Chr(13) + Chr(10) + First(Fields!CITY.Value, "DataSet1")+ " " +
    First(Fields!STATE.Value, "DataSet2") + " " + First(Fields!PCODE.Value, "DataSet2")

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    You need to include the Chr(10) in there as well. The two characters between them form the carriage return.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •