Jet Express: using =INDIRECT to create perpetual references to tables

By Ian Ray posted Jun 28, 2017 07:57 PM

  

What is Jet Express?


Jet Express is a reporting/data extraction tool for Dynamics NAV and Excel. NAV users have been able to access Jet Express since NAV 2009, so you most likely already have access to Jet Express. To download the latest Jet Express plugin for Excel, visit the Jet Express for NAV download page.


Why reference a Jet Express table?


Manipulating data generated by Jet Express can provide more flexibility in reporting than using tables alone. One solution is to copy and paste the table data as values to a different worksheet, but this requires a manual step each time a report is run. Another option is to reference the Jet Express table in a pivot table, but this is limited by what you can do with a pivot table. Referencing the structured table data allows the most freedom for manipulation, allowing use of Excel formulas to summarize and transform the Jet table data.


Can I just put the reference in directly, e.g. =Report!D13 or =Table_Name[#All]?


No. As soon as you refresh the report, these cell references will change to #REF! and need to be input again.


For example



changes on Jet refresh to #REF!



The same thing occurs with =Report!D13





How do I use INDIRECT to reference the table generated by Jet?


My method begins with creating a separate worksheet containing the table name created by Jet. In this case "Sales_Invoice_Line"



The sample data I used in this post is drawn from the Sales_Invoice_Line table input into the Table Builder as shown below



Referencing all of the table data into cells


Making a new worksheet to store all of the table data is an easy way to start working with the table data. The drawbacks of this method are file size and speed.


On a different worksheet, select a range corresponding to the data you wish to reference.



Enter an INDIRECT formula, referencing the table name from the worksheet containing the table name.



Press ctrl-shift-enter to make this formula an array formula.



Using a large range like "C2:F999999" will create a large file. In this case, the file size is 25 megabytes to reference nearly 1 million rows with 4 columns. If you don't expect your Jet Table results to be nearly this large, you can use a smaller range, greatly reducing file size. I have found the comfortable range to be around 100,000 total cells.


Having a large file size also makes the "restoring formulas..." portion of refreshing a report take a long time. This is much quicker with a smaller range.

Because of the performance issues, I would only recommend referencing all table data in a different sheet for practice manipulating a subset of the data you intend to analyze. I personally use this method to see how (or if) a week's worth of data could be transformed into a useful report while intending to use formulas to generate a different, more detailed report in another workbook.


Referencing data columns for calculations


Using the data columns directly in calculations requires some work to set up the formulas, but is optimal for performance and file size. Even if you are referencing a million rows, your file size and speed will change very little using this method.

The first thing I do in this case is to define a name for "TableName!$B$2" to avoid typing this over and over. This can be found in Formulas > Defined Names > Define Name.


As a basic example, we can use a SUM formula with an indirect column reference. In this case, I have typed the column name into cell E2 and am using the formula =SUM(INDIRECT(Table&"[[#Data,["&$E$2&"]]'))
(Note that "Table" was defined in the previous step)

It can be noted that using an indirect reference to the table name and column name as shown results in no change to the formula upon pressing "Report" or "Refresh" in the Jet menu. That is, a formula like this doesn't get converted to "#REF!"

Formulas such as SUMIFS can also be used for applying multiple criteria to referenced table columns.
An example of SUMIFS is as follows

C2, D2, and E2 contain column names. C3 and D3 contain filter values.

The formula
=SUMIFS(INDIRECT(Table&"[[#Data],["&$E$2&"]]"),INDIRECT(Table&"[[#Data],["&$C$2&"]]"),$C$3,INDIRECT(Table&"[[#Data],["&$D$2&"]]"),$D$3)
sums the quantity column for entries in column "Bill-to Customer No." that match C3 and in "No." that match D3. 
 
This formula can be further extended to limit by date. For example, using "Posting Date" as a column name in F2, a start date in F3, and an end date in G3, we can use
=SUMIFS(INDIRECT(Table&"[[#Data],["&$E$2&"]]"),INDIRECT(Table&"[[#Data],["&$C$2&"]]"),$C$3,INDIRECT(Table&"[[#Data],["&$D$2&"]]"),$D$3,INDIRECT(Table&"[[#Data],["&$F$2&"]]"),">="&F3,INDIRECT(Table&"[[#Data],["&$F$2&"]]"),"<="&G3)
to sum only matching values between the dates in F3 and G3.



Jet Express is a very powerful tool for manipulating NAV data in Excel. Although I like the flexibility offered by Jet Professional, you don't need Jet Professional to make simple calculations as long as the number of rows you pull does not exceed Excel's row limit (1,048,576). With a few uses of INDIRECT, you can make valuable reports using Jet Express' Table Builder for Excel.


UPDATE: Also available for NAVUG members is a webinar recording about using INDIRECT() to reference Jet Express tables.



#Excel
0 comments
138 views

Permalink