0

Filtering on a calculated Field (SOLVED)

Jet Reports Historic Posts

June 10, 2009 10:41

None

Follow

Hi there,

Looking at Fixed assets here,

I have created a calculated field called Net Book Value which is the sum of Acquisition cost less accumulated deprectiation. Is it possible to get my Jet Report to Filter on this new calculated field? I know how to hide the rows based on value returned (in this case I want shot of "0") However these hidden rows are still incorperated when I run a Pivot table on the back of the report.

Appreciate I could just sort and delete the offending lines however I am lazy! Also my Crystal designing friend (in the losest sense) is excited as he thinks he has one up on me! Any advice in this reporting tool war would be appreciated!

Thanks in advance

Dean :D
Dean

• Facebook
• Twitter
• LinkedIn

6 comments

• 0
  
  Jet Reports Historic Posts June 10, 2009 16:29

  Dean,
  
  Any chance we could get a copy of the report. Or an example of what you are trying to sort/filter?
  
  I think what you are asking is possible, but I don't have enough information to give you an example.

• 0
  
  Jet Reports Historic Posts June 10, 2009 17:25

  Here is a quick example (I think) using the customer and "Cust. Ledger Entry" table filtered multiple ways.
  
  =NL("Rows","Customer",,"-=Local(NL(""Sum"",""Cust. Ledger Entry"",""Amount"",""Customer No."",NF(,""No.""),""Document Type"",""Invoice"")+NL(""Sum"",""Cust. Ledger Entry"",""Amount"",""Customer No."",NF(,""No.""),""Document Type"",""Payment""))","<>0")

• 0
  
  Jet Reports Historic Posts June 10, 2009 20:01

  Cheers Chuck,
  
  your simple! example has just blown my mind! I will send through my simpler one tomorrow if you don't mind looking.
  
  Appreciate your time again today,
  
  Cheers
  
  Dean

• 0
  
  Jet Reports Historic Posts June 11, 2009 07:19

  Chuck,
  
  Please see attached spread sheet, as you will see 3 columns contain Navision fields and one (NBV) is a calculated field (one Nav field less another).
  
  I would like the report to filter so that it removes all records where NBV is calculated as "0". As before I know how to hide the records, I just want shot of them completly!
  
  Thanks in advance
  
  Dean
  
  :)

  • Fixed Asset Test.xls
• 0
  
  Jet Reports Historic Posts June 11, 2009 16:04

  You need to change your NL() formula to
  
  =NL("Rows","FA Depreciation Book",,"=NF(,""Acquisition Cost"")-NF(,""Depreciation"")","<>0")

• 0
  
  Jet Reports Historic Posts June 15, 2009 14:09

  Chuck,
  
  Great stuff, worked (nearly) perfectly! Just had to change the - to a + in the formula and I was away!
  
  Thanks for your help (again)
  
  Dean