Hi,
I have a question regarding the Universal Connector and Jet Reports. I'm new to the Universal Connector so I'm having problem understanding exactly how to extract data from an excel sheet to use with my Jet Report.
I have a Master budget and I would like to import values into a jet report that I'm working on that is taking data from my Nav system and a particular excel workbook that has budget information. I have the Universal Connector ODBC setup and have access to the excel workbook through Jet so I'm ok there. I need a little push on how the forumla should work. In the spread sheet I have attached I have my custmer numbers which will link my report via filter and I need to pull the data from a particular field. So for instance I need the data from cell "H6,(95000)" which is "Net Sales 1,(G6)" for the month of "Jan,(H4)" filtered by customer number which is listed in cell (D6) and also listed on my jet report to populate in my jet report. What would be a starting formula for that using the universal connector?
Any help is gretaly appreciated.
Thanks,
Chris Gill
IT Director
Sexy Hair LLC
Date
Votes
4 comments
-
Jet Reports Historic Posts Official comment Hi Chris,
I believe that in order for an Excel sheet to be read correctly, the field names (division, sales, customer, etc.) must start in cell A1 of the worksheet. They must also be unique, so for instance you will have to rename one of the "division" columns. Each worksheet represents a table. So, once you have the sheet setup correctly, you would pull the January budget information for account 3000 of Customer 3260 with a formula like the following:=NL("First","database","Jan","account","3000","customer","3260")If you wanted the total for all accounts for the same customer, you would do something similar using an NL(Sum):=NL("Sum","database","Jan","account","*","customer","3260")However, in order for the sum to work you would have to clean up the workbook a bit by removing the rows that contain non-numeric data (i.e. row 5).
Hope this helps!
Regards, -
Jet Reports Historic Posts Sam,
Thank you very much. I will give this a shot and let you know how it works.
I appreciated your help.
Take care, -
Jet Reports Historic Posts The formula and setup you suggested is working, thank you.
Question-When I run the report it seem's I can only run and get data from one data source. Is there a way to pull data from my Nav database and my excel sheet on the same jet report?
Thanks,
Chris -
Jet Reports Historic Posts Disregard my last question I got it using the datasource= formula.
Thank You,
Please sign in to leave a comment.