Group Not Working When Field Value Is Null

[lesleycampbell]

In my report, I am grouping on a Purchase Order field. Not all Orders require Purchase Orders. Even if the purchase order field is null, I still want the order to show.

I tried the following formula to group on:
IF (ISNULL({OrderMaster.PurchaseOrder}) OR {OrderMaster.PurchaseOrder} = " ") THEN "Purchase Order: NONE" ELSE "Purchase Order: " & {OrderMaster.PurchaseOrder}

I have tried several variations of this formula and none work. It only returns the orders that have purchase orders attached to them.

Help?

 

[Charliy]

try this:
ELSE ("Purchase Order: " & {OrderMaster.PurchaseOrder})

 

[hilfy]

Your formula looks like it should work. However, if you're not using a command, stored procedure, or universe to provide the data for your report, you could try creating a "SQL Expression" for this instead of using a formula. SQL Expressions use the same syntax as your database. Since you don't mention which DB you're connecting to, here are two examples:

SQL Server
IsNull("OrderMaster"."PurchaseOrder", 'Purchase Order: ' + "OrderMaster"."PurchaseOrder")

Oracle
Nvl("OrderMaster"."PurchaseOrder", 'Purchase Order: ' + "OrderMaster"."PurchaseOrder")

You would then use this to group on.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com