Jet Reports is a reporting tool used to report on data available within your ERP system through the use of Microsoft Office’s Excel.  The reports can be used to create your financial statements, such as the income statements and balance sheets, or other reports on other areas of your system such as Account Receivables, Account Payable, Inventory, and much more.  When Jet Reports is installed, activated, and configured, the product is used via the Jet Reports Toolbar as seen and summarized below:

Toolbar

Jet Reporting Modes

• Design – Used to create the design of the report in design mode
• Report – Generates Report based on the design specified
• Refresh – Refreshes the report data with current data
• Publish – Publishes the report to the web. Not available in Jet Basics

Jet Reporting Design Tools

• NL – Non-General Ledger information queried from the database
• GL – General Ledger information query tool
• Table Builder – Tool used for building queries from the ERP database

Report Tools

• Drilldown – Displays records utilized for calculation within the report. Not available in Jet Basics
• Debug – Assistance for troubleshoot Errors found in the report in specific cells
• Schedule – Scheduler utilized to run reports at a future date. Not available in Jet Basics

Settings

• This section is used to configure database access for the ERP system and set up preferences for how the system will function

Information

• Activate Jet Professional – Used to upgrade to advanced edition
• Help – Provides help with Jet Reports
• About – Displays current version information for Jet Reports. Also provides information about your current system configuration via the System Information button to assist with troubleshooting

Jet Report Design Tools Actions

NL Function

The options available with Jet Report Design begin with the NGL function.  The NGL function assists the user in accessing their ERP data through the use of the Jet Function Wizard or the coding of the function in the Excel cell.

Example – To create a report of customers in Maryland with the customer number, customer name, city, and state, use the following from the Jet Function Wizard:

Fields within the wizard:

• What – Pertains what you want to use with the function. The most common element is the Table
• Table – Determines which table you will pull the data from within your ERP system
• Fields – Defines which fields should be pulled within the function
• Filters – Defines what data to sort the data with

Other options within the Jet Function Wizard are:

• JFX NL – Switches function between NL and GL
• Evaluate Data – Allows the user to view the function the wizard will create
• Drill Down – Displays the results of the function and allows for grouping of results
• Nested Jet Function – Allows the insertion of sub function within another function

Or code the following data within a cell.

• =NL(“Table”,”RM00101″,{“CUSTNMBR”,”CUSTNAME”,”CITY”,”STATE”},”state”,”MD”)

The results will be displayed after the Report button is clicked.  The results will have filters that enable the user to sort or filter the data as required.

GL Function

The GL function is the primary tool used to build access your financial information.  To use this function click in the desired cell and click on the GL button of the toolbar.  Not all fields need to be used to create the report.

Example – To create a basic report of General Ledger Accounts:

Fields within the wizard:

• Where – Where do you want the data to be displayed (Cell, Column, Row, Sheet)
• What – What data do you want to appear (Account, Account Categories, Segment 1, Segment 2, etc.)
• Account – Specific Account
• Start Period – Starting Financial Period
• End Period – Ending Financial Period
• Category – General Ledger Account Categories (Cash, Accounts Payable, Inventory, etc.)
• Segment # – Pulls data based on accounting segments
• Include Unposted – Used for work file activity
• Company – Defines which company to utilize for the statement when using multiple ERP companies
• Data Source – Used to pull data from alternative data source

Other options within the Jet Function Wizard are:

• JFX NL – Switches function between NL and GL
• Evaluate Data – Allows the user to view the function the wizard will create
• Drill Down – Displays the results of the function and allows for grouping of results
• Nested Jet Function – Allows the insertion of sub-function within another function

Or code this data within a given cell:

• =GL(“Rows”,”Accounts”)

To add a column for the account name to the report:

• Utilize the wizard and Select

• Click in the cell next to the Account Number field
• Select Cell for the Where
• Choose the Account Name for the What
• Enter the cell used for the account number or click on the cell
• Click Ok

Or code the following data within the cell next to the account number:

• =GL(“Cell”,”AccountName”,B4)

To Enter a column for the Beginning Balance:

• Utilize the wizard and Select

• Click in the cell next to the Account Description field
• Select Cell for Where
• Select Balance for What
• Enter the cell used for the account number or click on the cell
• Start period or date in one of several formats:
  • • YYYY/PPP where YYYY is the 4 digit fiscal year and PPP is the 1 to 3 digit period
    • YYYY/M/D where YYYY is a 4 digit year, M is a month, and D is a day;
    • DDDDD.DD which is a 5+ digit number interpreted as an Excel serial date, and
    • M/D/YY or D/M/YY (depending on your computer’s locale settings) where M is a month, D is a day, and Y is a 2 or 4 digit year.
  • End period or date in one of several formats:
  • • YYYY/PPP where YYYY is the 4 digit fiscal year and PPP is the 1 to 3 digit period
    • YYYY/M/D where YYYY is a 4 digit year, M is a month, and D is a day;
    • DDDDD.DD which is a 5+ digit number interpreted as an Excel serial date, and
    • M/D/YY or D/M/YY (depending on your computer’s locale settings) where M is a month, D is a day, and Y is a 2 or 4 digit year.
• Or code the data within the cell next to the account description:
  • =GL(“Cell”,”Balance”,B4,2018/1)

Using Excel Features to quickly make your report:

You can make report creation faster by using the standard features of Excel. For example, in this report the account number for the first cell is always column B.  We can use the $B4 function so that if we copy the data across columns, it will always refer to the correct account number.

We can further use column headers for the period number.  If we make column headings for 2018/1 and 2018/2, we can copy these across to quickly add several columns of data.  With the cell always pointing to column B for the account number, we can make the Start period point to the cell with the period indicator adding the $ parameter (D$3) to always point to the third row.  Now we can copy the formula across the periods we added.

Now when we click on the report function, we can get the balances for each period and account within our GL.  From this point, to complete the report, we add headings and format the text to the desired look that we wish to use with the standard Excel functionality.

Get In Touch with KTL

Jet Report Basics makes report creation easier and faster than ever. If you would like to implement Jet Report Basics within your company, give KTL Solutions a call. As a Microsoft Certified Partner, we know these programs inside and out. Founded in 1999 by owner and CEO Tim Lally, KTL Solutions Inc. started by providing development services for users of Great Plains Dynamics. KTL Solutions is now a leading firm in technology consulting. We have a dedicated development department, specializing in solutions that provide e-Business growth, application implementation, and design within many sectors of business. We also have knowledgeable consultants who provide consulting and services for a variety of business solution needs, and a dedicated sales and marketing team who continue to build successful relationships with current and potential clients.

Take the first step to transforming your business processes. Contact us today.