Skip to main content
insightsoftware Docs insightsoftware Docs
{%article.title%}
Published:
Was this article helpful?
0 out of 0 found this helpful

Jet Hub - Feature Support

This information applies to the latest version of Jet Hub.  Older versions may not support all features listed.

Overview

Jet Hub supports the vast majority of all features present in the Jet Excel add-in.

  1. Data Sources

    The following data sources are supported in Jet Hub:

    • Dynamics NAV 2013 to NAV 2018
    • Dynamics GP (On Premises)
    • Dynamics NAV (Remote Data Service)
    • Dynamics GP (Remote Data Service)
    • Dynamics 365 Business Central (Public Cloud)
    • Dynamics 365 Business Central (On-Premise)
    • Jet Analytics Data Warehouse
    • Jet Analytics Cubes
    • Universal
  2. Supported Excel Formats

    All files to be uploaded to Jet Hub must be in .XLSX format.

  3. Supported Excel Functions

    The following Excel functions are supported in the latest release of Jet Hub.  Those not in this list will fail to calculate if they are either dependent upon or a dependency of a Jet function:

    A-C D-H I-L M-Sq St-Z
    • ABS
    • ACOS
    • ACOT
    • ACOTH
    • ACOSH
    • ADDRESS
    • AND
    • ASIN
    • ASINH
    • ATAN
    • ATAN2
    • ATANH
    • AVERAGE
    • AVERAGEA
    • AVERAGEIF
    • AVERAGEIFS
    • CEILING
    • CEILING.MATH
    • CHAR
    • CHOOSE
    • COLUMN
    • COLUMNS
    • CONCATENATE
    • COS
    • COSH
    • COT
    • COTH
    • COUNT
    • COUNTA
    • COUNTBLANK
    • COUNTIF
    • COUNTIFS
    • CSC
    • CSCH
    • DATE
    • DATEVALUE
    • DAVERAGE
    • DAY
    • DAYS
    • DAYS360
    • DCOUNT
    • DCOUNTA
    • DEGREES
    • DGET
    • DMAX
    • DMIN
    • DSUM
    • DVAR
    • DVARP
    • EDATE
    • EOMONTH
    • ERROR.TYPE
    • EXACT
    • EXP
    • FACT
    • FACTDOUBLE
    • FALSE
    • FIND
    • FIXED
    • FLOOR
    • FLOOR.MATH
    • GETPIVOTDATA
    • HLOOKUP
    • HOUR
    • HYPERLINK
    • IF
    • IFERROR
    • IFNA
    • INDEX
    • INDIRECT
    • INT
    • ISBLANK
    • ISERR
    • ISERROR
    • ISEVEN
    • ISLOGICAL
    • ISNA
    • ISNONTEXT
    • ISNUMBER
    • ISODD
    • ISOWEEKNUM
    • ISTEXT
    • LARGE
    • LEFT
    • LEN
    • LN
    • LOG
    • LOG10
    • LOOKUP
    • LOWER
    • MATCH
    • MAX
    • MAXA
    • MEDIAN
    • MID
    • MIN
    • MINA
    • MINUTE
    • MOD
    • MONTH
    • N
    • NA
    • NETWORKDAYS
    • NOT
    • NOW
    • NUMBERVALUE
    • OFFSET
    • OR
    • PI
    • POWER
    • PRODUCT
    • PROPER
    • QUOTIENT
    • RADIANS
    • RAND
    • RANK
    • RANK.EQ
    • RANK.AVG
    • RANDBETWEEN
    • REPLACE
    • REPT
    • RIGHT
    • ROUND
    • ROUNDDOWN
    • ROUNDUP
    • ROW
    • ROWS
    • SEARCH
    • SEC
    • SECH
    • SECOND
    • SIGN
    • SIN
    • SINH
    • SMALL
    • SQRT
    • SQRTPI
    • STDEV
    • STDEVA
    • STDEVPA
    • STDEV.P
    • STDEV.S
    • STDEVP
    • SUBSTITUTE
    • SUBTOTAL
    • SUM
    • SUMIF
    • SUMIFS
    • SUMPRODUCT
    • SUMSQ
    • T
    • TAN
    • TANH
    • TEXT
    • TIME
    • TODAY
    • TRUE
    • TRUNC
    • UPPER
    • VAR
    • VAR.S
    • VAR.P
    • VARA
    • VARP
    • VARPA
    • VLOOKUP
    • WEEKDAY
    • WEEKNUM
    • WORKDAY
    • WORKDAY.INTL
    • YEAR
    • YEARFRAC

    Note:  These functions are supported in the most recent versions.  Older versions may not support a specific function

  4. Using LINK=

    Functions utilizing the LINK= must:

    • include all filters within the function    OR...
    • if using the NL("Link) function within a replicating function [ NL(Rows) or NL(Columns) ] :
      • the NL("Link") must be on the same row as the replicating function  OR...
      • the NL("Link) must be referenced using Excel's absolute referencing ($)
    Supported Examples :
    NL("Rows","Sales Invoice Header","No.","Posting Date",">1/1/2009","LINK=","Sales Invoice Line","Document No.","=No.","Type","Item")

    {Why?  all filters reside within the function}

    OR...

    support_link.png

    {Why?  the LINK= filter in cell B5 includes an absolute cell reference to the NL("Link") function in cell C3}

    OR...

    support_link2.png

    {Why?  Even though the cell reference "C5" is not absolute, cell C5 is contained within the replication area}

    Unsupported Example:

    unsupported_link2.png

    {Why?  the LINK= filter in cell B4 includes a relative reference to the NL("Link") function which is outside the replicated area)

  5. Function and Report Limitations

    Currently, there are some features that are not available for use with Jet Hub.

    The following data sources are notsupported in Jet Hub:

    • Dynamics NAV 2009 and earlier (C/Front)

    Some function arguments are required to be constant.  This means that they must be either hard-coded in the formula or be absolute cell references to cells that are not Report Options.  These include:

    FunctionArguments that must be constant:
    NL
    • What
    • FilterField
    NF
    • Field
    • FlowFilterField
    NP
    • What
    • Formula (must be a text string in the function)
    GL (Dynamics NAV)
    • What
    • View
    • Exclude Close
    GL (Dynamics GP)
    • Where
    • What
    • Include Unposted
    GL (JetBudgets)
    • What
    • Include Unapproved

    Supported Examples :

    {constant - supported }

    {hard-coded cell reference - supported }

    Unsupported Example:

    {Why?  Cell references to report Options are not supported for these parameters}

    Pivot Table Limitations (and unsupported features) :

      • OLAP-Backed Pivot Tables
      • Slicers
      • Conditional Formatting
      • Pivot Field Settings

        Setting Unsupported Value
        Layout > Insert blank line after each item label enabled
        Layout > Repeat Item Labels enabled
        Layout > Show items with no data enabled
        Layout > Include new items in manual filter enabled
      • Pivot Value Field Settings

        • Show Data As:
          • Difference From
          • % Difference From
          • Rank largest to smallest
          • Rank smallest to largest
          • Running total in
          • index
        • Show data as "(next)" and "(previous)" options are not supported.
      • Calculated Items

      • Pivot Table Filters

        • Label Filters:
          • Between - wildcards are not supported.
          • Not Between - wildcards are not supported.
        • Value Filters:
          • Equals
          • Not Equals
          • Greater Than
          • Greater Than or Equal
          • Less Than
          • Less Than or Equal
          • Between
          • Not Between
          • Top 10
        • Report Filters
      • Pivot Table Options

        • Layout and format

          Setting Unsupported Value
          Merge and center cells with labels enabled
          Display fields in report filter area Over, then Down
          Report filter fields per row|column enabled
          For error values show enabled
          Preserve cell formatting on update disabled
        • Totals and Filters

          Setting Unsupported Value
          Allow multiple filters per field enabled
          Use Custom Lists when sorting disabled
          Subtotal filtered page items (only available for pivot tables based on an OLAP data source supporting MXD express subselect syntax) disabled (default)
        • Display

          Setting Unsupported Value
          Show contextual tooltips disabled
          Display field captions and filter dropdowns disabled
          Classic PivotTable layout enabled
          Show the Values row enabled
          Field List Sort A to Z
          PivotTable Options > Display > "Show items with no data on rows" enabled
          PivotTable Options > Display > "Show items with no data on columns" enabled
          Show properties in tool-tips disabled (default)
          Display item labels when no fields are in the values area (only applies to pivot tables created using Excel 2003 or earlier) enabled (default)
        • Data

          Setting Unsupported Value
          Save source data with file disabled
          Enable show details disabled
          Refresh data when opening the file enabled
          Retain items deleted from the data source > Number of items to retain per field

          None Max

    Pivot Table Considerations:

    • Floating-Point Arithmetic

      When values in Excel sum to zero they will often be displayed as a very small value in scientific format (i.e. 163709E-10).  This is due to the precision with which computers can store fractional values and is documented by Microsoft as expected Excel behavior here .

      Reports run in the Jet Hub are affected by the same issue, however small changes in the precision that fractional values are stored that may cause the results to differ slightly from Excel's values.

      For example, Excel may produce a sum of 1.63709E-10, while the Jet Hub value is -7.6761E-10.  These values are both effectively zero (0.000000000163709046319127 and -0.000000000767613528296351, respectively) and should be treated as such because they are likely beyond the significant figures of the values being operated upon.

    • Filters

      Filtering field values will correctly filter data out of a report but may leave rows or columns whose only data was hidden by the deselected values.

      For example, if we hide "Bicycle" from the "Description" field and another field value that intersected with that the Description only had bicycle data in it, the other field value may still be present even though Excel does not show it.  The Bicycle data will not be shown.

  6. The following functions and report features are not currently supported in Jet Hub:

    • ShowQuery=
    • Stored User Data
    • External workbook connections (e.g. OLAP Pivot Tables) cannot be refreshed from Excel online.
    • External workbook connections to other workbooks (Excel > Data > Edit Links) cannot be refreshed in Jet Hub.
    • Use of the VALID and TOOLTIP keywords with the Report Options feature.
    • Using NL("Picture") to retrieve an image from a file.
    • Sparkline Graphs

Published:

Jet Hub - Feature Support

This information applies to the latest version of Jet Hub.  Older versions may not support all features listed.

Overview

Jet Hub supports the vast majority of all features present in the Jet Excel add-in.

  1. Data Sources

    The following data sources are supported in Jet Hub:

    • Dynamics NAV 2013 to NAV 2018
    • Dynamics GP (On Premises)
    • Dynamics NAV (Remote Data Service)
    • Dynamics GP (Remote Data Service)
    • Dynamics 365 Business Central (Public Cloud)
    • Dynamics 365 Business Central (On-Premise)
    • Jet Analytics Data Warehouse
    • Jet Analytics Cubes
    • Universal
  2. Supported Excel Formats

    All files to be uploaded to Jet Hub must be in .XLSX format.

  3. Supported Excel Functions

    The following Excel functions are supported in the latest release of Jet Hub.  Those not in this list will fail to calculate if they are either dependent upon or a dependency of a Jet function:

    A-C D-H I-L M-Sq St-Z
    • ABS
    • ACOS
    • ACOT
    • ACOTH
    • ACOSH
    • ADDRESS
    • AND
    • ASIN
    • ASINH
    • ATAN
    • ATAN2
    • ATANH
    • AVERAGE
    • AVERAGEA
    • AVERAGEIF
    • AVERAGEIFS
    • CEILING
    • CEILING.MATH
    • CHAR
    • CHOOSE
    • COLUMN
    • COLUMNS
    • CONCATENATE
    • COS
    • COSH
    • COT
    • COTH
    • COUNT
    • COUNTA
    • COUNTBLANK
    • COUNTIF
    • COUNTIFS
    • CSC
    • CSCH
    • DATE
    • DATEVALUE
    • DAVERAGE
    • DAY
    • DAYS
    • DAYS360
    • DCOUNT
    • DCOUNTA
    • DEGREES
    • DGET
    • DMAX
    • DMIN
    • DSUM
    • DVAR
    • DVARP
    • EDATE
    • EOMONTH
    • ERROR.TYPE
    • EXACT
    • EXP
    • FACT
    • FACTDOUBLE
    • FALSE
    • FIND
    • FIXED
    • FLOOR
    • FLOOR.MATH
    • GETPIVOTDATA
    • HLOOKUP
    • HOUR
    • HYPERLINK
    • IF
    • IFERROR
    • IFNA
    • INDEX
    • INDIRECT
    • INT
    • ISBLANK
    • ISERR
    • ISERROR
    • ISEVEN
    • ISLOGICAL
    • ISNA
    • ISNONTEXT
    • ISNUMBER
    • ISODD
    • ISOWEEKNUM
    • ISTEXT
    • LARGE
    • LEFT
    • LEN
    • LN
    • LOG
    • LOG10
    • LOOKUP
    • LOWER
    • MATCH
    • MAX
    • MAXA
    • MEDIAN
    • MID
    • MIN
    • MINA
    • MINUTE
    • MOD
    • MONTH
    • N
    • NA
    • NETWORKDAYS
    • NOT
    • NOW
    • NUMBERVALUE
    • OFFSET
    • OR
    • PI
    • POWER
    • PRODUCT
    • PROPER
    • QUOTIENT
    • RADIANS
    • RAND
    • RANK
    • RANK.EQ
    • RANK.AVG
    • RANDBETWEEN
    • REPLACE
    • REPT
    • RIGHT
    • ROUND
    • ROUNDDOWN
    • ROUNDUP
    • ROW
    • ROWS
    • SEARCH
    • SEC
    • SECH
    • SECOND
    • SIGN
    • SIN
    • SINH
    • SMALL
    • SQRT
    • SQRTPI
    • STDEV
    • STDEVA
    • STDEVPA
    • STDEV.P
    • STDEV.S
    • STDEVP
    • SUBSTITUTE
    • SUBTOTAL
    • SUM
    • SUMIF
    • SUMIFS
    • SUMPRODUCT
    • SUMSQ
    • T
    • TAN
    • TANH
    • TEXT
    • TIME
    • TODAY
    • TRUE
    • TRUNC
    • UPPER
    • VAR
    • VAR.S
    • VAR.P
    • VARA
    • VARP
    • VARPA
    • VLOOKUP
    • WEEKDAY
    • WEEKNUM
    • WORKDAY
    • WORKDAY.INTL
    • YEAR
    • YEARFRAC

    Note:  These functions are supported in the most recent versions.  Older versions may not support a specific function

  4. Using LINK=

    Functions utilizing the LINK= must:

    • include all filters within the function    OR...
    • if using the NL("Link) function within a replicating function [ NL(Rows) or NL(Columns) ] :
      • the NL("Link") must be on the same row as the replicating function  OR...
      • the NL("Link) must be referenced using Excel's absolute referencing ($)
    Supported Examples :
    NL("Rows","Sales Invoice Header","No.","Posting Date",">1/1/2009","LINK=","Sales Invoice Line","Document No.","=No.","Type","Item")

    {Why?  all filters reside within the function}

    OR...

    support_link.png

    {Why?  the LINK= filter in cell B5 includes an absolute cell reference to the NL("Link") function in cell C3}

    OR...

    support_link2.png

    {Why?  Even though the cell reference "C5" is not absolute, cell C5 is contained within the replication area}

    Unsupported Example:

    unsupported_link2.png

    {Why?  the LINK= filter in cell B4 includes a relative reference to the NL("Link") function which is outside the replicated area)

  5. Function and Report Limitations

    Currently, there are some features that are not available for use with Jet Hub.

    The following data sources are notsupported in Jet Hub:

    • Dynamics NAV 2009 and earlier (C/Front)

    Some function arguments are required to be constant.  This means that they must be either hard-coded in the formula or be absolute cell references to cells that are not Report Options.  These include:

    FunctionArguments that must be constant:
    NL
    • What
    • FilterField
    NF
    • Field
    • FlowFilterField
    NP
    • What
    • Formula (must be a text string in the function)
    GL (Dynamics NAV)
    • What
    • View
    • Exclude Close
    GL (Dynamics GP)
    • Where
    • What
    • Include Unposted
    GL (JetBudgets)
    • What
    • Include Unapproved

    Supported Examples :

    {constant - supported }

    {hard-coded cell reference - supported }

    Unsupported Example:

    {Why?  Cell references to report Options are not supported for these parameters}

    Pivot Table Limitations (and unsupported features) :

      • OLAP-Backed Pivot Tables
      • Slicers
      • Conditional Formatting
      • Pivot Field Settings

        Setting Unsupported Value
        Layout > Insert blank line after each item label enabled
        Layout > Repeat Item Labels enabled
        Layout > Show items with no data enabled
        Layout > Include new items in manual filter enabled
      • Pivot Value Field Settings

        • Show Data As:
          • Difference From
          • % Difference From
          • Rank largest to smallest
          • Rank smallest to largest
          • Running total in
          • index
        • Show data as "(next)" and "(previous)" options are not supported.
      • Calculated Items

      • Pivot Table Filters

        • Label Filters:
          • Between - wildcards are not supported.
          • Not Between - wildcards are not supported.
        • Value Filters:
          • Equals
          • Not Equals
          • Greater Than
          • Greater Than or Equal
          • Less Than
          • Less Than or Equal
          • Between
          • Not Between
          • Top 10
        • Report Filters
      • Pivot Table Options

        • Layout and format

          Setting Unsupported Value
          Merge and center cells with labels enabled
          Display fields in report filter area Over, then Down
          Report filter fields per row|column enabled
          For error values show enabled
          Preserve cell formatting on update disabled
        • Totals and Filters

          Setting Unsupported Value
          Allow multiple filters per field enabled
          Use Custom Lists when sorting disabled
          Subtotal filtered page items (only available for pivot tables based on an OLAP data source supporting MXD express subselect syntax) disabled (default)
        • Display

          Setting Unsupported Value
          Show contextual tooltips disabled
          Display field captions and filter dropdowns disabled
          Classic PivotTable layout enabled
          Show the Values row enabled
          Field List Sort A to Z
          PivotTable Options > Display > "Show items with no data on rows" enabled
          PivotTable Options > Display > "Show items with no data on columns" enabled
          Show properties in tool-tips disabled (default)
          Display item labels when no fields are in the values area (only applies to pivot tables created using Excel 2003 or earlier) enabled (default)
        • Data

          Setting Unsupported Value
          Save source data with file disabled
          Enable show details disabled
          Refresh data when opening the file enabled
          Retain items deleted from the data source > Number of items to retain per field

          None Max

    Pivot Table Considerations:

    • Floating-Point Arithmetic

      When values in Excel sum to zero they will often be displayed as a very small value in scientific format (i.e. 163709E-10).  This is due to the precision with which computers can store fractional values and is documented by Microsoft as expected Excel behavior here .

      Reports run in the Jet Hub are affected by the same issue, however small changes in the precision that fractional values are stored that may cause the results to differ slightly from Excel's values.

      For example, Excel may produce a sum of 1.63709E-10, while the Jet Hub value is -7.6761E-10.  These values are both effectively zero (0.000000000163709046319127 and -0.000000000767613528296351, respectively) and should be treated as such because they are likely beyond the significant figures of the values being operated upon.

    • Filters

      Filtering field values will correctly filter data out of a report but may leave rows or columns whose only data was hidden by the deselected values.

      For example, if we hide "Bicycle" from the "Description" field and another field value that intersected with that the Description only had bicycle data in it, the other field value may still be present even though Excel does not show it.  The Bicycle data will not be shown.

  6. The following functions and report features are not currently supported in Jet Hub:

    • ShowQuery=
    • Stored User Data
    • External workbook connections (e.g. OLAP Pivot Tables) cannot be refreshed from Excel online.
    • External workbook connections to other workbooks (Excel > Data > Edit Links) cannot be refreshed in Jet Hub.
    • Use of the VALID and TOOLTIP keywords with the Report Options feature.
    • Using NL("Picture") to retrieve an image from a file.
    • Sparkline Graphs

For an optimal Community experience, Please view on Desktop