I am trying in vain to produce a value for a Resource Availability report.  Here is my formula entered in the Data Item Expression window:

[Relational View].[Capacity Hours]-[Relational View].[Nonworking Time Hours]-[Relational View].[Scheduled Work Hours]-[Relational View].[Allocations].[Allocation Hours]

My report results are really flaky.  Users always have Capacity Hours present.  They do NOT always have positive values in the other fields.  IF all three subtracting fields are greater than zero, the formula works perfectly.  If ANY of the subtracting fields are zero, they show as zero on the report and the formula also returns a zero, as if the fields themselves are NOT numeric and cause some sort of non-numeric abend for the formula when they have no value.  Please let me know what I am doing wrong or if I have to write a convaluted "is null" set of nested "if" statements to accomplish this simple formula. 

The way that you will want to handle this depend on the type of repro that you are building (List vs. Crosstab).  In simple list reports and in using data items in a query (but not necessarily displaying that data item) using a case statement at the data item level to handle missing and/or null values is fairly easy to do.  The format of the statement should be as follows:

case

when [Data Item Name] is null then 0
when [Data Item Name] is missing then 0
else [Data Item Name]

end

I usually put both the null and missing handling statement in a case statement such as this, as I am not sure how this data exists or does not exist in the database.  By handling both simultaneously, this generally works.

By assigning a value at the data item level you ensure that your formula (to give you the result of non-working time minus scheduled work hours minus allocation hours) should work.

Matthew Puvogel | Solutions Consultant
Daptiv

I created new fields on my query used to populate the list report I am generating.  I used the Case statements as shown, used these fields in the calculation field and still get the same results - blank values and zeroes for the calculated field when all three fields are not greater than zero.  Do I need to set the mysterious "Aggregate" fields to some non-Automatic settings for these fields?  The report is grouped by Member Type and Member Name and displays by Resource Month.