Using a reporting services model/report builder we have two related tables:- Fundings, (parent)- Draws, (child) Report Builder reports that subtract "Total Fundings.Amount", (which is SUM(FundingAmount))from "Total Draw Amount", (which is SUM(DrawAmount)) to get a balance work as expected except when there are no Draw rows, in which case a NULL is returned. Obviously we want to convert NULL values of "Total Draw Amount"tozero so that when subtracted from "Total Fundings.Amount" the correct value is displayed. I've searched for a function similar to COALESCE (Transact-SQL) in report builder but found nothing. Can anybody help me with this? Thanks Bruce

how u try giving an iif statements to check for null and givethe default value as 0 regards, Karen

IIF is not a valid function in report builder. When I use IF,I get a syntax error in the expression, (it doesn't like the word NULL) and there appears to be no isnull() function, ie. the following do not work: IF(TotalAmt Is NULL, ... IF(TotalAmt = NULL, ... IF(ISNULL(TotalAmt), ... Anybody else? Bruce

instead of Null try it like this iif(Fields!TotalAmt = " ", 0,Fields!TotalAmt)

NO, the amount is numeric. When using the following expression IF(SUM(Draw Amount)="",0,SUM(Draw Amount)) the error generated: "The arguments to the following function are not valid: = (Equal to). This is report builder, not report designer. Your responses regarding IIF lead me to believe that you providing report designer solutions. I need a solution that will work with report builder. Bruce

Hello Bruce, Try to use the LEN function instead, something like this. IF(LEN(DrawAmount) = 0, 0, DrawAmount) Hope this helps. Jarret

The easiest approach would be to set the value in the query results before rendering with an ISNULL check, such as: ISNULL(some_field, 0)But if this may violate practices or you are handed this query result and limited to the report design itself then perhaps an IIF expression such as the following may work: IIF(IsNothing(Fields!some_field.Value), 0, Fields!some_field.Value)This will most likely need to be a calculated field added to your dataset in order to evaluate for every record returned then can be aggregated within the report. If this is used as a cell expression (such as a sum on a grouping) then only the last record would be evaluated, thus an error would result if a null value is present in the field so I believe a calculated field would be necessary.Hope this helps.Paul R.

how bout u try to set the default value to 0 in the database it self (if u can do it), and u will not have any problems Regards Karen

I stumbled on a workable solution: IF(Total Draw Amount = EMPTY, 0, Total Draw Amount) Not the best implementation since "Total Draw Amount" is a subquery sum of a field in another table which theexpression will evaluate twice for every row in the parent table, (a waste of system resources). A better implementation would be a function like COALESCE() which would only cause a single subquery. Also, the EMPTY keyword is not adequately documented. A suggestion, document this in BOL and put in a keywords sectionin the define formula dialog, functions tab. Microsoft, are you listening? Bruce

Use CASE WHEN i IS NULL THEN 'Null Result' -- This will be returned when i is NULL WHEN i = 0 THEN 'Zero' -- This will be returned when i = 0 WHEN i = 1 THEN 'One' -- This will be returned when i = 1 END

The easiest approach would be to set the value in the query results before rendering with an ISNULL check, such as: ISNULL(some_field, 0) But if this may violate practices or you are handed this query result and limited to the report design itself then perhaps an IIF expression such as the following may work: IIF(IsNothing(Fields!some_field.Value), 0, Fields!some_field.Value) This will most likely need to be a calculated field added to your dataset in order to evaluate for every record returned then can be aggregated within the report. If this is used as a cell expression (such as a sum on a grouping) then only the last record would be evaluated, thus an error would result if a null value is present in the field so I believe a calculated field would be necessary. Hope this helps. Paul R. Can't believe that this is not the answer...Edward