0

NL("Table";...) and Count

Jet Reports Historic Posts

March 27, 2012 07:10

None

Follow

Hello,

is there any possibility to join NL("Table";…) with Count?

An example:
NL("Table";"General Leder";{"Date of creation";"Source Code";"Posting Date"}) will give me a table, but I need to know how many entries are there for this creation date, source code and posting date.

I don't want to use NL("Rows";…) because the next step should be a pivot table to group the data.

Thanks in advance.
cat

• Facebook
• Twitter
• LinkedIn

5 comments

• 
  Jet Reports Historic Posts March 28, 2012 14:52 Official comment

  Hi Cat,
  
  In your example, it looks like you have one too many ; between the table and the filters. It should be like this:
  
  
  =NL("Table";"General Leder";{"Date of creation";"Source Code";"Posting Date";"Formula(NL(""Count"";""General Ledger"";;""DataSource="";$D$5;""Company="";""company1"";""Date of creation"";[@[Date of creationg]];""Source Code"";[@[Source Code]];""Posting Date"";[@[Posting Date]]))"};"DataSource=";$D$5;"Company=";"company1")
  There is also the possibility that to make the cell reference work you need to unquote it like this:
  
  =NL("Table";"General Leder";{"Date of creation";"Source Code";"Posting Date";"Formula(NL(""Count"";""General Ledger"";;""DataSource="";"""&$D$5&""";""Company="";""company1"";""Date of creation"";[@[Date of creationg]];""Source Code"";[@[Source Code]];""Posting Date"";[@[Posting Date]]))"};"DataSource=";$D$5;"Company=";"company1")
  And yes you should add UseLocalFormulas=,True unless you plan on using , instead of ; in your formula.
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts March 27, 2012 17:54

  Hi Cat,
  
  Assuming you are using Jet Essentials (not Jet Express) you can do this using a Formula() field. The code would look something like this:
  
  =NL("Table";"General Leder";{"Date of creation";"Source Code";"Posting Date";"Formula(NL(""Count"";""General Ledger"";;""Date of creation"";[@[Date of creationg]];""Source Code"";[@[Source Code]];""Posting Date"";[@[Posting Date]]))"})
  This example is assuming you are using Excel 2010 since the column references are different in earlier versions of Excel. It is much easier to type the count formula into the Table Builder itself since you don't have the type 2 sets of double quotes each time (in the Table Builder just type the NL("Count") formula as you normally would into Excel), and you can type @ and just get a lookup when referencing other columns in the table. Does that make sense?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts March 28, 2012 13:20

  Hi Hughes,
  
  Thanks for your help. It makes sense and it works - at least a bit.
  
  Your formula needs "UseLocalFormulas=";"True" at the end, right? Or I need it here to get no errors.
  
  And there's another problem. We use multiple data sources and companies, therefore I have to use DataSource= and Company=.
  
  This works fine:
  =NL("Table";"General Leder";{"Date of creation";"Source Code";"Posting Date";"Formula(NL(""Count"";""General Ledger"";;;""DataSource="";""Database1"";""Company="";""company1"";""Date of creation"";[@[Date of creationg]];""Source Code"";[@[Source Code]];""Posting Date"";[@[Posting Date]]))"};"DataSource=";"Database1";"Company=";"company1")
  But using a cell reference for e.g. Datasource does not (the formula ingores the datasource):
  D5: Database1 =NL("Table";"General Leder";{"Date of creation";"Source Code";"Posting Date";"Formula(NL(""Count"";""General Ledger"";;;""DataSource="";$D$5;""Company="";""company1"";""Date of creation"";[@[Date of creationg]];""Source Code"";[@[Source Code]];""Posting Date"";[@[Posting Date]]))"};"DataSource=";$D$5;"Company=";"company1")
  What do I miss?
  
  cat

• 0
  
  Jet Reports Historic Posts March 29, 2012 05:33

  Hi Hughes,
  
  you're right with the posted formula - there is one too many ;, but it wasn't in my tested formula (might have happened during translation to Englisch - therefore ; instead of , :-) )
  
  But it doesn't work - counting is done in the default data source with the first formula.
  With the second one I get an Excel error when validating the formula ("The entered formula contains an error.")
  
  Regards,
  cat

• 0
  
  Jet Reports Historic Posts March 29, 2012 05:59

  Hi,
  
  correction of my previous post: the first formula works.
  (Since Excel doesn't recognize the $D$5 in the inner formula as cell reference, there is a problem with moving cells. => It's better to use named cells than cell references.)
  
  cat