Skip to main content

Use SQL Views

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 of fields inside curly brackets: { "field1", "field2", "field3" }
  • or a range of cell references: $B$2:$G$2.

cache1.png

To add another field to the Field Cache, either add a comma and then the name of the field in quotes to the end of the list inside the curly brackets (in the first case) or extend the cell range and type the name of the field into the last cell (in the second case).

When using a Field Cache, the Jet add-in will treat the unique combination of fields included in the Field Cache as the primary key to the specified table, regardless of whether or not those fields accurately represent a composite key to the underlying data.

No Primary Keys

SQL views do not contain a primary key and this affects how Jet Reports queries these views.

A primary key is a field in a table that uniquely identifies each record (row) in a database table.  Primary keys can consist of one or more fields that, taken together, create a unique, non-NULL value identifying each record in the table.

When building detail list Record Keys against standard SQL tables, Jet automatically detects and references the table’s primary key.  Building detail lists using views requires the creation of a Field Cache listing each field you wish to display.  This article explains in detail the information summarized in the above information and demonstrates how to use Field Cache data to return both complete and summary table data.

  • Detail Lists from SQL Tables

    To understand how to use a Field Cache, it is useful to first understand how Detail Lists work with standard SQL tables.

    As explained in the article Using a Detail List or a Summary List , a Detail List will produce a list of records based on the filters chosen.  The replicating function returns a Record Key rather than a field from the database.  Users create a record key by dragging a table into Excel from the Jet Browser or by building an NL(Rows) or NL(Columns) function using the Jet Function Wizard.  In either case, leaving the Field blank indicates that Jet Reports should create a Record Key.

    For example, in GP, use the Jet Browser to drag and drop first the GL00100 (Account Master) table and then the ACTINDX (Account Index) field from that same table into Excel.

    Notice that the NL function created simply indicates a list of rows from the table, i.e., =NL(“Rows”,”GL00100”):

    sqlview01.png

    When building detail list keys against standard SQL tables, the Jet add-in automatically detects and references the table’s primary key.

    In this example, the Record Key automatically references the ACTINDX field, which is the primary key to the GL00100 table:

    sqlview02.png
  • Detail Lists from SQL Views

    When building reports against a view, on the other hand, Jet Reports has no primary key to reference. If you drag the Jet GL Account table into Excel, the function which Jet creates, =NL(“Rows”,”Jet GL Account”), will return the error “Table ‘Jet GL Account’ does not have a primary key.  You must specify one or more fields”:

    sqlview03.png

    Instead, use the Browser to select all fields from Jet GL Account and drag them into Excel. You will notice that the Field argument populates with an array of Field names, since the table does not contain a primary key.

    When an NL Function has an array in the Field parameter, it returns a value called a Field Cache into the cell. This is a text value that identifies all the field values and their data types.  If you use the NF function with the cell address containing a Field Cache as the first argument and one of the field names contained in it as the second argument, the NF function will return the value of the field.  You must include a field in your Field Cache if you wish to display that field using the NF function.

    Selecting all the fields in a view using the Jet Browser is a simple way to be sure that you have created a unique Field Cache that can accurately function as a primary key to the data in your table:

    sqlview04.png

    If you do this, simply delete any columns the Jet Browser creates that you do not wish to include in your report.  Alternatively, if you know the field or fields that can function as a primary key on a view, simply ensure that those fields are included in your Field Cache, regardless of whether you wish to display those fields.

  • Building a Summary List Using a Field Cache

    In some circumstances you may wish to use a field cache that deliberately does not include all information in the underlying table.  In this case, you are using the Field Cache to create a summary table. GP users, for example, sometimes want to list the unique combination of only two segments from their chart of accounts.  For example, say you are a GP user whose chart of accounts is as follows: Segment 1 - Entity

    Segment 2 - Natural Account

    Segment 3 - Department

    You wish to list every unique combination of Natural Account and Department on a separate row. By creating a Record Key whose Field Cache contains only Segments 2 and 3, you can create a list of unique natural accounts and departments:

    sqlview05.png

    sqlview06.png

    Notice that each combination of Segments 2 and 3 appears only once.

    If you add Segment 1 to you Field Cache, the record key will create a row for every unique combination of Segment 1 (Entity) with Segments 2 (Natural Account) and 3 (Department). The function is =NL(“Rows”,”Jet GL Account”,{“Segment 1”,”Segment 2”,”Segment 3”}). Now when you display Segments 2 and 3 using the NF function, the unique combination of these two values repeats:

    sqlview07.png

    The repetition is created by the presence of Segment 1 in the Field Cache, regardless of whether or not Segment 1 is displayed using an NF function.

Related Articles

Was this article helpful?

We're sorry to hear that.