Jet from insightsoftware

Useful Excel Date Functions
Useful Excel Date Functions Overview Five date related functions in Excel make working with dates much easier. The functions are as follows. =Date(Year, Month, Day) =Day(Date) =Month(Date) =Year(Date) =Text(date, format) Examples To find the first day of the month from a date entered in D4, you could enter the following formula. ...
Jet Reports Dashboard Mode
Jet Reports Dashboard Mode Overview Enabling Dashboard Mode for a Jet Reports worksheet will retain all Jet formulas when in Report Mode so that those formulas can be recalculated on the fly by changing filter values. How-To Video Using Jet Reports Dashboard Mode Using Dashboard Mode Adding +DASHBOARD To enable Dashboard Mode for a partic...
Grouping and Subtotaling Tutorial
Grouping and Subtotaling Tutorial Moderate Related Articles... How-To Videos Adding Filters, Lists, and Totals Adding Groupings Adding Columns and Dimensions Adding Sheet Grouping Adding Report Options The videos demonstrate ...
Filtering Based on Data from Another Table
Filtering Based on Data from Another Table Beginner Related Articles... Overview Sometimes you will want to filter one table based on data from a related table.  When using the Dynamics GP and the Universal connectors, the Jet Excel add-in provides NL("Filter") for this circumstance.  When using the Dynamics NAV connector, the Jet add-in also provides additional "LINK=" f...
Building a Report From Multiple Data Sources
Building a Report From Multiple Data Sources Overview If you want to build a report that uses multiple databases of any type, you need to create a data source for each database (refer to instructions in this Help Center to find information on configuring your specific type of data sources). Once your data sources are configured, you can specify separate connections in both the NL...
Referencing a Blank cell (with @@)
Referencing a Blank cell (with @@) Overview Sometimes you want to filter an NL function based on the contents of another cell or the output of another NL function, where either of which could be a blank value. The NL function does not allow you to use blank filters, and you will get a #VALUE error message if you fill in a FilterField parameter and leave the corresponding filter blank...
Conditionally Hiding Rows, Columns or Sheets
Conditionally Hiding Rows, Columns or Sheets Beginner Overview Automatically hiding entire rows or columns can be accomplished by including the HIDE keyword in the first cell of the column or row you want to hide: Always hiding a worksheet (once a report has been run) can be accomplished by using the keywords AUTO+HIDE+HIDESHEET in cell A1 of that worksheet. ...
Sort by Sum (or count) with Dynamics NAV and Business Central
Sort by Sum (or count) with Dynamics NAV and Business Central Moderate Overview Report designers often find themselves needing to create a report that is sorted, based on the sum or count of some field in a Dynamics NAV table. This "sort by sum" technique is an advanced capability - and, once you understand the process, is one that the Jet Excel add-in can easily perform...
Sort by Sum for data sources other than NAV
Sort by Sum for data sources other than NAV When working with a data source other than Dynamics NAV (for example, a SQL database), sorting by the Sum of a field is quite simple. Let's assume the following data is in the DEPT table of our database: and we want to list our department numbers, sorted by the decreasing value of the sum of the Amount field. Our functions ...
Using the Excel IF function with Jet Reports (Conditional Replication)
Using the Excel IF function with Jet Reports (Conditional Replication) Overview You cannot wrap any replicating NL function (Rows, Columns, Sheets) inside of an Excel IF() function. Replicating functions must stand alone in a cell . To make conditional replication work we simply place our IF() function in the Table argument of the replicator and return either the na...
Limiting the Number of Records in a Query
Limiting the Number of Records in a QueryHelp Topics... Overview You can place a limit on the number of records that NL will return by entering "Limit=" in one of the FilterField parameters and entering a maximum number in the corresponding Filter parameter. Examples With Dynamics NAV, the following example displays the Customers with the ten largest sales. ...
SQL Views
SQL ViewsRelated Articles... Overview If you are using SQL Views, it is possible to access these views as if they are standard tables in your database. SQL Views cannot be used with Dynamics NAV data sources as NAV only supports Dynamics NAV table objects. Understanding Field Caches A Field Cache may appear in an NL() function in 2 forms: either a comma delimited list o...
Using NL(Filter)
Using NL(Filter) Beginner Related Articles... The NL(Filter) function returns a string value that can be used as a filter in another NL function.  Its intended use is for filtering the contents of one table based on the contents of another table. Example of the NL(Filter) function The following formula will retrieve a list of records from the Orders table: =...
Using Link=
Using Link=Help Topics... Overview LINK= gives users advanced filtering capabilities in Jet Reports.  Using LINK= allows users to tie together information from different tables. LINK= is available for the following connector types: Dynamics 365 Business Central, Dynamics NAV 2013 - 2018, Dynamics NAV 2009R2 and earlier, Dynamics GP, Other (Universal), and Jet Data Warehouse....
Using InclusiveLink=
Using InclusiveLink= Overview The InclusiveLink= filter for the NL(Table) function allows you to retrieve fields or sums from a linked table, while not forcing the primary table to be filtered by the linked records. Using InclusiveLink For example, suppose you had the following tables: Customer No. 1 2 3 Cust. Ledger Ent...
Using SQL=
Using SQL= This article covers just the basics and we recommend exploring SQL statements from books and other reference materials. **Note: SQL= can only be used with a Universal Jet Reports license. This functionality is *not* available to Dynamics NAV users.Related Articles... Overview There are times when you will find performance issues with complicated table filtering...
Selecting the Key Search Algorithm
Selecting the Key Search Algorithm This topic is for advanced users who have a strong understanding of Dynamics NAV and the impact that keys can have on performance. Selecting the Key Search Algorithm Jet Reports selects the key to apply by analyzing the filters to be set on the table against the available list of keys. While the default algorithm is designed to select the key that p...
Selecting the Specific Key by Function ( Key= )
Selecting the Specific Key by Function ( Key= ) This topic is for advanced users who have a strong understanding of Dynamics NAV and the impact that keys can have on performance. Selecting the Specific Key by Function Jet Reports selects the key to apply by analyzing the filters to be set on the table against the available list of keys. While the default algorithm is designed to sele...
Using "Schema=" to return specific data
Using "Schema=" to return specific data Overview The special filter field "Schema=" can be used to override the default database schema (that will be used in the query) with the one specified in the Filter parameter following "Schema=" This special filter is designed to be used with NL() functions that return specific values. In NL() functions where the "Field" par...
SQL= Reference
SQL= Reference Expert Related Articles...This feature is for power users in special situations only and should not be used regularly Overview =NL (What,"SQL=...",Field,FilterField1,Filter1,FilterField2,Filter2,FilterField10,Filter10) Purpose: A special type of NL function that uses the power of SQL to allow complete flexibility in retrieving data from a database. Argum...
Using a Detail List or a Summary List
Using a Detail List or a Summary List Overview A common design question when using the replicator functions NL("Rows") or NL("Columns") is whether to create a "Detail List" or a "Summary List". Summary List A Summary List is always a unique list of values from the field chosen. If the value is repeated in the table, you...
Compare one NAV field against another field
Compare one NAV field against another field This feature is supported with Dynamics NAV connectors, only Overview If you want to compare the value in one field to the value in another field in the same record, you can use a "Calculated Field" within your Jet Reports formula. Examples You need a list of sales invoice numbers where the "Bill-to Customer No." is equal to...
Using GP SmartList SQL Views to speed up reporting
Using GP SmartList SQL Views to speed up reporting Overview Dynamics GP’s out-of-the-box SmartLists run on underlying SQL views of the same name.  Jet users can build reports in the Jet Excel add-in against any SmartList SQL view.  These views simplify report building by linking multiple tables together and using intuitive field names. insightsoftware has also built cust...
Retrieving Special Fields with NL(Table)
Retrieving Special Fields with NL(Table)Related Articles... Overview The NL("Table") function creates an Excel table which can be used as the basis for pivot tables and charts. Where working with multiple Dynamics NAV database tables, there are some powerful techniques you can use to get exactly the data you want. Special fields can only be retrieved from NL(Table) when using Dyna...
Array Calculations
Array CalculationsRelated Articles... Overview Arrays are lists of data values.  You can obtain a string representing such a list from Jet using "Filter" as the What parameter in an NL function.  The values in arrays returned by Jet are guaranteed to be unique. The resulting array might be a list of Customers or a list of Invoice Document numbers or any other list of data that match a s...
Adding a Date and Time stamp to a Report
Adding a Date and Time stamp to a Report You can easily add a Date and Time stamp to a Report to indicate when a report was last ran by using a combination of Excel and the NP("Eval") function. Add this function to a cell =NP("EVAL","=Now()") and format the cell with the Date and/or Time format that you wish to use.
Table Cache Optimization
Table Cache Optimization This information applies to Jet Reports versions 2015 and higher Overview Table Cache optimization benefits users by improving the report performance of many reports - especially for Web Services data source connections. The purpose of this optimization is to retrieve (at one time) as much of the data that will be required by the replicating functi...
Evaluating Formulas
Evaluating Formulas Overview There are some cases where you want to evaluate a function only once each time you run your report. A good example of this is the Now() function.  You generally want the Now() function to run once when you update the report, and otherwise remain inert.  If Now() is allowed to behave normally, it will cause the sheet it is on (including Jet formulas tha...
Loading Pictures
Loading Pictures The NL("Picture") function is supported for on-premises installations of Dynamics 365 Business Central and Dynamics NAV 2013 and higher (for which the Jet Reports NAV Business Objects have been installed, but not the NAV apps for Jet Reports or Jet Reports for Financials. ) Overview The Jet NL("Picture") fu...
Introducing Stored User Data in Reports
Introducing Stored User Data in ReportsRelated Articles... Overview Suppose you want to allow Report Viewers to create and edit data on reports.  Normally any changes made in Report mode will be lost when you go to Jet | Report to re-run the report. However, with the stored user data feature, it is possible to designate sections of a report where users may enter and change data which will b...
First Page
Back
For an optimal Community experience, Please view on Desktop