0

Jet Essentials Report using the SQL= statement

Jet Reports Historic Posts

December 02, 2014 07:51

None

Follow

Good day

I have linked 2 databases together by changing the datasource , using the datasource= option in the function wizard. the link was successfull , now what i would like to do is select a table field from the database i have linked to the main database.
The main database contains the main table i am using "G/L Entry table and the table from the second database is "Brand Class" . I want to select a field from the Brand Class table which is the second database. I am unfirmiliar with the NL() function and SQL= statement , i have also read the Knowledge base article but still confused how to use it on my Jet report


Thanks in advance for your advise

Jason

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Jet Reports Historic Posts December 02, 2014 11:54

  Hi Jason
  
  First the SQL statement, you will need to write specific SQL Queries with help of a developer to use this. A co-worker in the development team did try and explain this to me, but I got lost halfway through.
  
  I think your best option is to use a FL Filter function to link to the secondary database and use the result in G/L query.
  
  Filter: =NL("Filter","Table","Value.","DataSource=","Secondary Data Source", <Filters>)
  
  Use the results of the filter in to G/L Rows
  
  MJ

• 0
  
  Jet Reports Historic Posts December 02, 2014 15:44

  And to clarify a statement in your posting. "I have linked 2 databases together by changing the datasource , using the datasource= option in the function wizard."
  
  You technically aren't "Linking" the two data sources together, you are simply telling that specific function to use that data source instead of the default data source. You wouldn't be able to pull information from both data sources in one function.
  
  You would need to use a technique described by mjohnson where you use the NL(Filter) to filter information in one data source and use that as a filter in a separate function pulling from the other data source.

• 0
  
  Jet Reports Historic Posts December 03, 2014 09:10

  Thank you for your replies , i am trying to understand your suggestion but i am a bit lost
  JET-mtr what do you mean by "use the NL(Filter) to filter information in one data source and use that as a filter in a separate function pulling from the other data source."
  
  @mjohnson : do you mean i need 2 functions or will the one you psoted be the only one =NL("Filter","Table","Value.","DataSource=","Secondary Data Source", <Filters>)
  
  i will simply be doing it like this adding my details: =NL("Filter","Brand Class","Code.","DataSource=","Cubes", <Filters>) , what do i add where it says filters

• 0
  
  Jet Reports Historic Posts December 29, 2014 23:39

  The data is not linked by using the Datasource parameter. When you use this you are simply overwriting the default data source selected in the Jet ribbon. I believe that is what JET-BTR was trying to say.
  
  If I am reading the question correctly then you simply want the Brand Class from the second data source that is related to the G/L code you are pulling? I would pull a listing from your main table and then reference the other data source for the information you are looking for.
  
  If I have a listing of my GL account starting in cell D5 then my formula would look like this:
  =NL("First","TABLE NAME","BRAND CLASS","DataSource=","SECOND DATABASE","ACCOUNT NUMBER",D5)
  
  This formula will pull the first record in the second data source with the GL Account listed in D5.
  
  Hope this helps.