Using SQL=

Related Articles...

Overview

There are two main pieces to using SQL=

• the SQL statement
• and the NL() function

1. We will start with the SQL statement.  A SQL statement is much like an NL() function and has a standard form.

   SELECT FieldName AS 'Alias'
   FROM TableName
   WHERE %Filter1%

2. When using SQL statements with Jet Reports, you will need to preface each SQL statement with SQL= so that your statements will look similar to this:

   SQL= SELECT FieldName AS 'Alias' FROM TableName WHERE %Filter1%

3. We recommend putting the SQL statement in a cell outside the NL() to avoid the maximum character limits of the cell.  All of the SQL keywords are capitalized and the lower case words are variables.

    

   FieldName	This can be a single field within a table.  In this case, simply put the name of the field into the SQL statement You can also sum a single field where the syntax is SUM(FieldName)
   Alias	Using an Alias can make retrieving the information a little easier.  This an optional feature when using SQL= Most commonly used when doing calculation within the SQL statement Allows you to rename the information to be used throughout the rest of the SQL statement as well as the NL()
   TableName	TableName represents the name of the table you are querying to get your information
   %Filter1%	%Filter1% is a variable that will allow you to add filters to the SQL statement from within your NL() statement

    

   You will use the SQL statement along with an NL() function to bring the data into Excel. Here is how you will use the NL() function:

   What	You can use the NL() as data retrieval or as a replicator Usually you will only use the keywords "Rows" or "First" when using SQL=
   Table	The table should be a cell reference to the SQL= statement You can type the SQL= statement directly into the Table parameter but you will run the risk of creating too long of a function (Excel has a 256-character maximum)
   Field	Name of the field you are returning form the SQL statement If you used an alias in the SQL statement, put it here
   FilterField	The name of the field by which to filter You should always qualify the field name to be sure it is filtering on the proper field.  Qualifying simply means to add the table name along with the field name (e.g., 1S=SOP30300.CUSTNUMBER, where SOP30300 is the name of the table and CUSTNUMBER is the name of the field. If you are filtering on a number field, use "N" and if you are filtering on a string/text field, use "S" The number "1" is used because we have %Filter1% in our SQL statement.  if we had %Filter2%, we could use 2S= in our FilterField.  You can use 1S multiple times within the NL statement
   Filter	The value (criteria) of the filter to apply to the Filterfield

The following examples are available in this Example Report

1. Example 1 (Dynamics GP):

   The following is an example of how to use SQL= with a Dynamics GP database.

   We will be summing the extended prices for all items sold.  All of the information is in the SOP30300 table (Sales Transaction Amounts History).

   **Note: The SOP30300 table already has a field for the extended price (XTNDPRCE).  But for demonstration purposes, this example will be calculating the extended price by multiplying the unit price by the quantity.

   • Cell B4 will contain the SQL statement below to calculate the extended price:

     SQL= SELECT SUM(QUANTITY * UNITPRCE) AS "XPrice" FROM SOP30300 WHERE %Filter1%

   • Cell C6 contains the NL() to list out item numbers from all invoices.

     Since we only want items on invoices, there is a filter for the SOPTYPE =3:

     =NL("Rows","SOP30300","ITEMNMBR","SOPTYPE",3)

   • Cell D6 will find the extended price for each of the items listed in column C.  The following NL() statement will use the SQL statement you created above in cell B4:

     =NL("First",$B$4,"XPrice","1N=SOP30300.SOPTYPE",3,"1S=SOP30300.ITEMNMBR",$C6)

     Since the SOPTYPE is a numerical field, we can use 1N= to preface our field name.  The item numbers in the database can have letters in them so we must use 1S= in our FilterField.

2. Example 2:

   This example will show how to join tables together and add filters for both tables.  In the previous example we found the total extended price for items with no consideration for when they were sold.  The date of the invoice is in the header table (SOP30200), not the line (SOP30300).  These two tables are related to each other by the SOPNUMBE (SOP30200.SOPNUMBE = SOP30300.SOPNUMBE). The first thing we need to do is create a SQL statement combining the tables together to get a list of items sold in a certain time period.  There are many ways to join tables together using SQL but this is a nice easy way to do it:

   SELECT FieldName AS "˜Alias"
   FROM TableName1
   JOIN TableName2
   ON TableName1.LinkingField1 = TableName2.LinkingField2
   WHERE %Filter1%

    

   FieldName	This can be a single field within a table.  In this case, you simply put the name fo the field into the SQL statement You can also sum a single field where the syntax is SUM(FieldName)
   Alias	Using an Alias can make retrieving the information a little easier.  This is an optional feature when using SQL= Most commonly used when doing calculations within the SQL statement Allows you to rename the information to be used throughout the rest of the SQL statement as well as the NL()
   TableName1	TableName1 represents the name of the table you are querying to get your information
   TableName2	TableName2 represents the name of the secondary table you are joining with the first table (TableName1)
   TableName.LinkingField1 =TableName2.LinkingField2	The represents the link between the two tables Be sure to fully qualify the field names to be sure the link is set up correctly
   %Filter1%	%Filter1% is a variable that will allow you to add filters to the SQL statement from within your NL() statement Even though we are accessing two tbles, you only need one variable.  This one variable will allow you to filter on both tables

   • For our example, Cell B3 contains the SQL statement listing the items sold:

     SQL= SELECT ITEMNMBR AS "Items" FROM SOP30300 JOIN SOP30200 ON SOP30300.SOPNUMBE = SOP30200.SOPNUMBE WHERE %Filter1%

   • To list out the items sold within a date range (in cell B2), the NL() in cell C6 will look like this:

     =NL("Rows",$B$3,"Items","1N=SOP30300.SOPTYPE",3,"1S=SOP30200.DOCDATE",$B$2)

   • Cell B4 will contain the SQL statement below to calculate the extended price:

     **Note: The SOP30300 table already has a field for the extended price (XTNDPRCE). But for demonstration purposes, this example will be calculating the extended price by multiplying the unit price by the quantity.

     SQL= SELECT SUM(QUANTITY * UNITPRCE) AS "Xprice" FROM SOP30300 JOIN SOP30200 ON SOP30300.SOPNUMBE = SOP30200.SOPNUMBE WHERE %Filter1%

   • Cell D6 will find the extended price for each of the items listed in column C. The following NL() statement will use the SQL statement:

     =NL("First",$B$4,"XPrice","1N=SOP30300.SOPTYPE",3,"1S=SOP30300.ITEMNMBR",$C6,"1S=SOP30200.DOCDATE",$B$2)

Help Topics...