0

Retrieve entries from multiple companies

JP Babin

August 19, 2017 01:54

None

Follow

 Hi , I am new to Jet reports.  

I need to cycle through several companies retrieving entries.  I can create the list with NL("Rows",NP("Companies")) , I can get the report on each sheet, but I need the report to be on one list on one sheet

 F10 Sale tax

F12  Transaction No. 

D3  08/01/16..09/30/16

C13  =NL("Rows",$F$10,$F$12,"Posting date",$D$3)

D13 =NL("Rows","G/L Entry",,"Transaction No.",$C13)

While sheets allowed me to rotate my companies to the sheet and referencing.  I beleive that I need to create array of companies and report on that list, if that makes any sense.  Lost, Wish  I could find a book on Jet report.  

Need help, Nav 2015, Jet Report 2016

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  JaeW_atOnyx August 19, 2017 17:06

  JP, sorry about your frustration, I know that can be a nightmare.

   

  Yes, you can use NL(Rows) with NP(Companies) to make a list of companies.  Typically I'll use NL("Filter", "comapny", "name") when working with NAV, as the Company table in NAV has a list of all your companies.

   

  You can pass the Company= argument to your reports to chose a company.  Ex.  NL("First", "g/l account", "name", "company=", "ABC Co." 

  Obviously the most likely application would be if you were calculating the balance of income statement accounts across multiple companies, NL("sum", "g/l account", "balance at date", "no.", "4000", "company=", "ABC|DEF")

   

  You cannot however pass a multi-company filter value into an NL(Rows) function.  In your case your best bet is NL("rows", "company", "name") followed by NL("rows" ,... , "company=", <cell reference to NL("rows") making list of companies).  Both NL(Rows) should be on the same line.  

   

  Hope that makes sense. 

  Find me on youtube or LinkedIn, Jae@OnyxReporting.com

• 0
  
  JP Babin August 20, 2017 03:20

  HI Jae

  Thanks for your feedback, greatly appreciated. 

  Lowered complexity of the formula

  Cell D4   =NL("Filter","company","Name")

  Cell D8 =NL("Rows","Company Information",,"Company=",$D$4)

  It's not cycling through the companies. I don't know what's going wrong on this one,  I understand SQL with subqueries, but I can't make this work.   

  Can you give me an example as my example, I greatly appreciate your time. If you know a book get me the ISBN number.    

   

• 0
  
  JaeW_atOnyx August 21, 2017 07:18

  NL("Filter") is a multi-value filter.  You cannot replicate across multiple companies in the same NL() function.

  
  Hence why I said you should put two NL("rows") functions on the same line.  First NL(rows company) to make a list of each company.  then NL("rows" "transactions",, "company=" , <>) to extract transactions for that ONE company.

   

  "Version:1.0 StartHTML:000000275 EndHTML:000004636 StartFragment:000004486 EndFragment:000004565 StartSelection:000004486 EndSelection:000004565 SourceURL:https://jetsupport.jetreports.com/hc/en-us/community/posts/115008657588-Retrieve-entries-from-multiple-companies Retrieve entries from multiple companies – Support Topics

  • You cannot however pass a multi-company filter value into an NL(Rows) function.

   

• 0
  
  Reinder Wolting August 21, 2017 07:43

  Not sure if it is available abroad, but I found this very useful:

  https://www.studiebijdehand.nl/p/9789492141125/quick-start-reporting-using-jet-essentials

   

  And for your problem

  Try this:

  C5: =NL("Rows",NP("Companies"))

  D5: =NL("Rows",<what you want from this company>,,"Company=",$C5)

   

  Hope this helps.

  rmw

   

  Edited by Reinder Wolting August 21, 2017 08:00