IFF Expression to return 0 if field's have no value

Question

IFF Expression to return 0 if field's have no value

LOOKUP_BI-756009

SSCertifiable

Points: 7286
More actions
May 28, 2008 at 8:36 am

#207760

Hi All,
I have a table on my report that reads from a datasets, This dataset queries a table and returns 3 columns. [Month Name/ Monthly Count /Total].This report table works fine and returns me the month name together with each month count.The footer of the table,would display the Total[addition/sum of Monthly Count].
Problem Is :- When the Report Table is empty , no Month Name and Monthly Count.It returns an empty Total result.
I want it to return a '0' when there is no Monthly Count.
I have tried the following expressions,but none worked.It does not return a 0.Any advise.Thanks
Example of Expressions:-
=IIF(Fields!MonthlyCount.Value=0,0,SUM(Fields!Total.Value))
=IIF(Fields!MonthlyCount.IS NOTHING,0,SUM(Fields!Total.Value))
Error [Hidden Expression return a data type value that is not Valid]

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

It sounds like you are getting an empty dataset returned. SSRS tables have a NoRows property that allows you to specify a message to display in place of the table when there is no data returned to the table. You may want to use this.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

LOOKUP_BI-756009 SSCertifiable Points: 7286 More actions · Answer 2

Hi Jack,

Thanks for your response.I tried the NoRows on table Property.But what I need is to include a "0" in the total txtbox of the table.Any idea?

I have attached the img for a better understanding.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

I have never tried to do what you are doing, but using any of the dataset fields in the expression will fail when there is no data returned. You may be able to get away with CountRows or RowNumber like:

=IIF(RowNumber(Nothing)=0,0,SUM(Fields!Total.Value)) or

=IIF(CountRows(Nothing)=0,0,SUM(Fields!Total.Value))

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

LOOKUP_BI-756009 SSCertifiable Points: 7286 More actions · Answer 4

Jack,

I tried the 2 fIIF you have mentioned above,but it gives me an error of invalid data type returned.Any other suggestions?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Okay, I had a minor bug in the CountRows function. You either need to put in the dataset name or leave it like this CountRows().

I have a table and in the footer I have these 2 functions which both are returning 0 when there are no rows returned:

=IIF(RowNumber(Nothing) = 0, RowNumber(Nothing), Count(Fields!persons.Value))

=IIF(CountRows() = 0, CountRows(), Count(Fields!persons.Value))

I would think that either should work for you as well.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

LOOKUP_BI-756009 SSCertifiable Points: 7286 More actions · Answer 6

Jack,

I tried both the IIF's.Here is what I get

[rsFieldReference] The Hidden expression for the textbox ‘textbox42’ refers to the field ‘total’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Build complete -- 1 errors, 0 warnings

Here is how it looks:

=IIF(RowNumber(Nothing) = 0, RowNumber(Nothing), Sum(Fields!Total.Value))

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

Are you sure the error is in the expression? Is the expression in textbox42?

Granted my report is simple, but it basically duplicates your situation and the expression works for me. Are you in SSRS 2000 or 2005?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

LOOKUP_BI-756009 SSCertifiable Points: 7286 More actions · Answer 8

Jack,

It is SQL 2005 and I am using the correct textbox for the expression.I have written a simple VB function that would convert a IS NOTHING value to 0 and added format as ##,###0 on my textbox.This works for me now.

Thanks For your help.I will try to check the IIF ststment again as if it works for you it should work for me too.