0

Error using array-function

Jet Reports Historic Posts

September 04, 2013 13:10

None

Follow

I think the error is coming from the NL(Filter) formula in cell D2. You have an empty filter for "Usage" which is not valid. If you want to filter for blanks, you should change it to use 2 single quotes or "@@". If you want to filter for all values of Usage, change it to *. Does this help?

Regards,
Hughes

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Jet Reports Historic Posts September 04, 2013 13:10

  For a report I want to produce a matrix showing the sum of hours booked. Columns show Global dimension 1 Code related to jobs, Rows show Global Dimension 1 Code related to resources. Each cell of the matrix shows the number of hours that was produced by resources of a department, for all jobs of a department.
  What do I do?
  Filter #1 is on the Job table for all records with Global Dimension 1 Code "031"; Field "No."
  Filter #2 is on the Job Ledger Entry table for all records of type Resource , Posting Date 010113..310713; Field "Job No."
  Filter #3 is on the Resource table for all records per Global Dimension 1 Code
  
  In Cell D2: Filter #1||"NL","AllUnique","Job Ledger Entry","Job No.","posting date","010113..310713","Type","Resource","Entry Type","Usage","","MARKT|JIP","","","","","","","","","","","","" In Cell D3: Filter #2 ||"NL","AllUnique","job","no.","Global Dimension 1 Code","031","Global Dimension 2 Code","MARKT|JIP","","","","","","","","","","","","","","","","" In Cell C6: A list of CostCenters =NL("rows";"Dimension Value";"Code";"Global Dimension No.";"1";"Dimension Value Type";"Standard";"Blocked";"False")In Cell B6: Filter #3, Resources per CostCenter =NL("filter";"Resource";"No.";"Global Dimension 1 Code";$C6)In Cell D6: Matrix, Sum of hours per CostCenter on projects per CostCenter
  =NL("sum";"Job ledger Entry";"Quantity";"Job No.";NP("Intersect";D$2;D$3);"No.";$B6;"Type";"Resource";"Entry Type";"Usage";"Posting Date";"010113..310713";"Global Dimension 2 Code";"MARKT|JIP")The result is an error saying "FilterField is Missing. FilterField of 0 or blank is not allowed."
  Where is my problem?

• 0
  
  Jet Reports Historic Posts September 05, 2013 07:55

  :oops: Sometimes I feel sooooo stupid…. This was really obvious, wasn't it?
  Thanks, I just missed it (about a 100 times). :!:
  Now I get the results I was hoping for.
  
  rgds,
  
  Willem