0

Budget Values Showing Blank, need to show 0.00

Jet Reports Historic Posts

October 14, 2008 20:36

None

Follow

I have a jet report pulling in budget values. I am using an NL looking to the g/l budget entry table and getting the values i need.

When there is no budget for a particular gl account, the cell is blank. I need it to be 0.00

Here is my NL. This GL account doesn't have a budget, so Jet leaves the cell blank.

=NL("rows","g/l budget entry","Amount","G/L Account No.","16207110001")

I have tried formatting the cell, i also tried a conditional format, but i can't get the cell to be 0.00

I even tried the IF statement below, but it doesn't work
=IF(G13="","0.00",G13)

Any ideas??

• Facebook
• Twitter
• LinkedIn

5 comments

• 0
  
  Jet Reports Historic Posts October 15, 2008 02:18

  I haven't touched JetReports in several years but perhaps try the following:
  
  =NL("rows","g/l budget entry","Amount","G/L Account No.","16207110001")+0
  
  This may force the evaluation from nothing to a number.

• 0
  
  Jet Reports Historic Posts October 15, 2008 12:41

  It might just be an Excel-issue:
  Try: Tools - Options - Tab: "Display" - Checkbox: "Null values" (center column, bottom row) in Excel 2003
  Or in Excel 2007: Office button - Options for Excel - Category: "Advanced" - Section: "Display options for this worksheet" - "Display null values as zeroes" (fourth checkbox from the top)
  Note that I'm running a localized version of Excel so I've translated the names of the commands. I may very well be incorrect!

• 0
  
  Jet Reports Historic Posts October 15, 2008 15:38

  DjangoDunn
  That idea didn't work, it didn't force a value.
  
  SchoutenCC
  I use excel 2003, so i am assuming you mean Tools > Options > 'View' Tab. make sure there is a checkmark in the bol 'zero values'. I allready have a check mark in there…
  
  
  I just somehow have to nest this IF statement into my NL.
  =IF(G13="","0.00",G13)

• 0
  
  Jet Reports Historic Posts October 15, 2008 16:03

  Sometimes elegance can be cumbersome. ;) In cases like this I'll sometimes simply create a hidden column that has my NL formula and use the IF statement like you suggested. You have to decide between a perfect JetReport/Excel formula and going to bed before the sun rises. :D

• 0
  
  Jet Reports Historic Posts October 15, 2008 18:13

  Of course !!!! Why didn't i think of that….
  
  It works fine now…i hid the Jet column, did an excel reference, and it works !!!!!