Report Builder Expressions

Question

Post reply

Report Builder Expressions

slimchance99

SSCrazy

Points: 2661
More actions
January 21, 2010 at 8:22 am

#219735

I'm looking for some assistance with the syntax for Report Builder expression syntax - there seems to be very little detailed information online that I can find.
Specifically, what is the syntax/function used to test a field for a null value. The VB function used in a standard SSRS report (IsNothing(field)) does not work in a Report Builder expression.
Our users need to test a field value for null in an if statement that calculates the time between 2 dates. if the end date is null, the calculation will use a literal date, otherwise it is the time between the start date and the end date.
If anyone is aware of a decent reference online for the report builder expression syntax, I'd appreciate that as well.
Thanks in advance.
Tim

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 1

Why not perform that in the stored procedure or the query. I think that would be the absolute easiest way.

onclick:if_IFCode('

ISNULL ( check_expression , replacement_value )

');

That would be much more flexible.

Andrew SQLDBA

slimchance99 SSCrazy Points: 2661 More actions · Answer 2

The query is not accessible by the users - it is captured as a named query in the DSV of the Report Model.

The replacement value has to be user configurable, as it changes depending on the user requirement. The calculation for duration has to be available as an editable formula field for the users, as per the project requirements.

There must be a way in the expression syntax to do this, you would think.

Tim

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 3

I did not see anything in the SQL BOL about this.

Andrew SQLDBA

slimchance99 SSCrazy Points: 2661 More actions · Answer 4

Me neither - or anywhere else.

Leave it to Microsoft to implement a proprietary expression syntax different from all others in the BI stack (SSRS and SSIS use different expression syntaxes) then not document it anywhere.

Tim

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 5

Ok, may be completely missing the exact location for usage here, but taken from MSDN example for Expression Examples (Report Builder 2.0) -->

Test the value of the PhoneNumber field and return "No Value" if it is null (Nothing in Visual Basic); otherwise return the phone number value. This expression can be used to control the value of a text box in a report item.

=IIF(Fields!PhoneNumber.Value Is Nothing,"No Value",Fields!PhoneNumber.Value)

referenced from -> http://msdn.microsoft.com/en-us/library/dd283099.aspx

there is a 3.0 version of the docs also.

Is it an expression in the report or the report model??

Steve.

slimchance99 SSCrazy Points: 2661 More actions · Answer 6

This is for Report Builder 1.0 (SQL 2005). It looks like Report Builder 2 uses the same expression syntax as used to build .rdl reports in Visual Studio (which makes sense and would be no problem). RB 1.0 uses a different expression syntax altogether for ad-hoc reports, and this example will not work in that case.

Thanks for looking though.

Tim

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 7

appears to hinge on the keyword 'EMPTY', e.g. below....

IF(myField = EMPTY, "its null", "its not")

tested with success on RB1.0

HTH,

Steve.

slimchance99 SSCrazy Points: 2661 More actions · Answer 8

Yep - that's it. Did you find that somewhere or just figure it out with trial and error. That's probably the only combination I didn't try (= nothing, is nothing, is empty, etc.)

Thanks for your help.

Tim

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 9

found here - basically used the syntax from the Filter syntax.

Steve.

RGilmore Newbie Points: 7 More actions · Answer 10

Hello All,

I know that this post is dated, but I'm using Business Intelligence Development Studio and I'm running across an error sisimilaro the one above. If possible would you give more details so that I can resolve my issue. Currently when the field is not available instead of being able to use IsNothing to catch the value before an exception is thrown, my logic is ignored and an error is thrown anyway. I'm hoping the solution provided above gets me around this.

Thank You

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 11

Not quite following... so you've tried one of the approaches above, and they didn't work? Or you're yet to try one of the approaches and you'll let us know?

Assuming the former, can you detail the version of SSRS that you're working with? Is it 2005?

Steve.