0

Count Matching Fields

Jet Reports Historic Posts

March 22, 2011 17:38

None

Follow

I have 2 tables:
Table "Program Component"
with fields: Program No./Component No./Number of Allowed Brand
Table "Customer Program"
with fields: Program No./Subscription No./Component No./Number of Picked Brand

The "Number of Picked Brand" could be equal or less than the "Number of Allowed Brand".
I am trying to count number of components in "Customer Program" table for a customer where the "Number of Picked Brand" are equal to "Number of Allowed Brand" from "Program Component" table and the component no. are the same between those two tables.

The following formula gives me 0 even if I could manually find the match
=NL("count","Customer Program",,"Program No.","2011","Subscription No.","123"
,"Number of Picked Brand",nl(,"Program Component","Number of Allowed Brand","Program No.","2011","Component No.","=NF(,""Component No."")))


The following gives me error: #NAME?
OR
=NL("count","Customer Program",,"Program No.","2011","Subscription No.","123"
,"Number of Picked Brand",nl(,"Program Component","Number of Allowed Brand","Program No.","2011","Component No.","=Component No."))

How I do this?

Thank you.

• Facebook
• Twitter
• LinkedIn

5 comments

• 
  Jet Reports Historic Posts March 22, 2011 20:58 Official comment

  Oh my apologies. I mistook your username for another community site user that is similar. You can't use SQL with a Navision connector, but you should be able to do this with a calculated filter. Your example is pretty close. I think it should look something like this:
  
  =NL("count","Customer Program","component no.","Program No.","2011","Subscription No.","123","Number of Picked Brand","=nl(,""Program Component"",""Number of Allowed Brand"",""Program No."",""2011"",""Component No."",NF(,""Component No."")")
  
  Does that work for you?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts March 22, 2011 19:28

  You are using a SQL server connection correct? The calculated filter functionality is only available in Navision so something like you have with "=NF(,""Field"")" will not work. However, you can use SQL= functionality to directly execute a SQL query in Jet Reports. I believe you are familiar with SQL so this shouldn't be too hard for you. Basically, the syntax looks like this:
  
  =NL("Rows","SQL=SELECT Field1,Field from Table",{"Field1","Field2"})
  
  Of course, from there you can add a SQL where term to do what you are trying to do. Does this help?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts March 22, 2011 19:57

  Hi,
  I am using Navision and I prefer not to use SQL statement.
  
  The result of this formula is 6
  =nl(,"Customer Program","No of Picked Brands","program no.","2011","subscription no.","123","component no.","Comp1")
  
  The result of the following formula is also 6
  =nl(,"Program Component","No of Allowed Brand","program no.","2011","component no.","Comp1")
  
  In reality, 1 subscription could have more than 1 component.
  So, I want to use some kind of lookup as the following, to count the occurence of any component in "Customer program" table which have "No of Picked Brands" equal to "No of Allowed Brand" from "Program Component" table with the same "component no."
  as the following formula without using SQL Statement:
  =NL("count","Customer Program","component no.","Program No.","2011","Subscription No.","123"
  ,"Number of Picked Brand",nl(,"Program Component","Number of Allowed Brand","Program No.","2011","Component No.","=Component No."))
  
  Thank you.

• 0
  
  Jet Reports Historic Posts March 23, 2011 01:17

  Thanks Hughes,
  It works fine. It means that I have to enclose the second NL in quotes instead of the inner NF.
  Thank you once again for the prompt answer.
  By the way, I am using SQL Reporting Services too for all the big reports, but I love JetReports.

• 0
  
  Jet Reports Historic Posts March 23, 2011 14:55

  Great! I'm glad you like Jet! SQL Reporting Services and Jet Reports are really different products with different use cases. They can definitely be complimentary to one another if you understand the strengths and weaknesses of each. :-)
  
  Regards,
  Hughes