0

Count and Sort

Jet Reports Historic Posts

August 24, 2011 13:11

None

Follow

I'm new to JetReports and getting tripped up by something that I think is possible:

I am pulling a data dump from Navision of customer calls for a month. I'd like to do two things with that data: count the number of calls per customer (No) field and then sort by the highest number. Can this be done in Jet?

Here's my initial query: =NL("Rows","Call",,"Open Date",Options!$C$2,"Category Main","COLLISIONLINK-DEALER")

• Facebook
• Twitter
• LinkedIn

5 comments

• 0
  
  Jet Reports Historic Posts August 24, 2011 22:01

  Hi,
  
  Yes this can be done but it's a little complicated. The first thing we need to know is, how is the Call table related to the Customer table? Is there a field for Customer No. on the Call table?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts August 26, 2011 18:01

  Thanks for the response! There is, the Customer No field is part of the Call table.

• 0
  
  Jet Reports Historic Posts August 26, 2011 22:02

  Okay I created a quick example for you. I don't have a Nav database with the Call table in it, so I can't actually test it myself; hopefully I got it all right. ;-)
  
  Basically in B5, there is a quoted sort by sum formula which is being referenced by the NL(Rows) in D5 and this make the NL(Rows) sort the customers by the count of the calls. Then there's an NL(Link) in cell C5 which is also being referenced by the NL(Rows) in D5 and that makes the NL(Rows) only display Customer who have calls with the category of COLLISIONLINK-DEALER and an Option Date which you're specifying on the Option sheet. Then I'm replicating rows from the Customer table and returning the customer No. and Name with NF functions. Then finally I'm retrieving the count of calls for each customer.
  
  Does this do what you are wanting? If not, you may be able to take the concepts and apply them to make it exactly what you want.
  
  Regards,
  Hughes

  • Calls by Customer.xlsx
• 0
  
  Jet Reports Historic Posts August 29, 2011 20:52

  Thanks for buliding this! All looks right, but for some reason I am getting an "Empty Filter Not Allowed" error in G5. I am thinking it is related to the Callback to E5. Do you have any idea what may be causing?
  
  Thanks!

• 0
  
  Jet Reports Historic Posts August 29, 2011 21:12

  Well that has got to mean that either the number in column E is blank or else the value in C2 on the Options sheets is blank. You can't put blank on the options sheet (use either 2 single quotes if you want to return blank values or else use * to return all values). If the value in E5 is blank, then you'll need to put "@@" in front of the cell reference of the formula in G5 like this:
  
  =NL("Count","Call",,"Customer No.","@@"&E5,"Open Date",Options!C2,"Category Main","COLLISIONLINK-DEALER")
  
  Does that help?
  
  Regards,
  Hughes