Sign Up for Training |
Jet Express Support |
Jet Reports Company Site
Jet 365 Financials
Jet Professional
Jet Enterprise
Downloads
Community
Submit a Request
Give Feedback

Setting Up Your Data Source Connection


Which type of Data Source do you have?

 

Dynamics NAV

Setting up a Dynamics NAV Data Source

Jet Professional can connect to your Dynamics NAV data in a variety of ways - depending upon the version and type of NAV configuration your organization uses.

Here are some helpful knowledgebase articles to help you access your NAV data.

 

NAV 2013 and Higher

SQL Direct

Recommended

Remote Data Service

Recommended method for when
NAV exists on a separate network
than does Excel.

NAV Web Services

This is a great choice if your NAV installation does not match the
Requirement for connection directly through SQL Server

Help me choose

 

NAV 4.0 SP1 through NAV 2009R2

Setting up a NAV Data Source (NAV 2009R2 and earlier)

 

Dynamics GP

 

Setting up a Dynamics GP Data Source using Jet Professional

Before you can create a Data Source connection, you must first download and install Jet Professional from the Jet Professional download website.

Because Jet Professional does not connect to the Dynamics GP client, you will need login credentials for GP's underlying SQL database.  You may need to contact your system administrator to create that for you.


You’ll also need the following files and information:

 

  • For Jet Professional or Jet Essentials 2013 and higher: your Jet License Activation Code
  • For Jet Essentials 2012 R2 and earlier: your Jet License Files (.jlf)
  • Database Information:
    • Server Name or IP Address
    • Dynamics Database Name
    • Database Login Information

 

Watch the Video!

 

 

Step-by-Step

To create a new GP data source connection to your GP database, start by clicking the Data Source Settings button on the Jet ribbon.



On the Data Source Settings window, click the "Add" button.



In the New Data Source dialog, provide a unique name for our GP data source and select "Dynamics GP" for the type.  Click OK.


 

The Data Source Settings window will now display the information for your new (empty) data source.

If the database login - created for you by your system administrator - recognizes your Windows login credentials, on the Authentication tab you can select "Windows Authentication".  Otherwise, you'll need to select "Database Authentication" and specify the user name and password provided to you by your system administrator.

 

On the Connection tab...


You will need to provide the name or IP address of the server on which you database is located.

Versions of GP prior to GP 2013 contained a database with the name "Dynamics".  Laster versions of GP allow system administrator to use a name other than Dynamics. You will want to specify the correct Dynamics database by clicking the pull-down arrow and selecting the database name provided to you by your system administrator.

Finally, click the pull-down arrow for the Company and select the appropriate default company name. Even if you only have one company in your database, you must select it here.

If you database includes multiple companies, you can always change the selected company on this screen, the Jet ribbon, or even within the Jet function itself.


On the Display tab...


You can choose your display preferences, which will determine how your tables and fields display in the various Jet wizards.

You can choose whether or not to display the tables that contain no data and if you want to display how many records are in each table (next to the table name).

Most importantly, if Friendly Names have been added to your database, you can choose to enable them for your use.  This is a very useful feature that can be a significant help in finding your data.

More information about Friendly Names can be found in the related article section below.

 

On the Advanced tab...

It is a best practice to trim blank spaces from fixed-length text fields.

 

Once the Data Source Settings are correct, you can test them by clicking the Test Connection button.


and should see the Test Connection Succeeded message.  If there are any issues, the message should help you in troubleshooting.

If you would like this data source to be the default data source that Jet Professional will use, click the Set Default button.

Click OK and your Dynamics GP data source is now ready for use with Jet Professional.

SQL Server

 

Setting up a Universal SQL Data Source using Jet Professional

You will need the following files and information:

  • For Jet Professional or Jet Essentials 2013 and higher: your Jet License Activation Code
  • For Jet Essentials 2012 R2 and earlier: your Jet License Files (.jlf)
  • Database Information:
    • Server Name or IP Address
    • Authentication Type (Windows or Database)
    • Database Name
    • Version of SQL Server installed

Watch the Video!

 

 

 

Step-by-Step

To create a new Universal data source connection to your SQL database, start by clicking the Data Source Settings button on the Jet ribbon.

 

On the Data Source Settings window, click the Add button.

 

On the New Data Source window, provide a name for your SQL data source and select Universal for the type.  Click OK.


On the Data Source Settings window, click the Configure... button.

 

On the Provider tab of the Data Link Properties window, you will want to choose the database driver that corresponds to the version of SQL Server hosting your database.

The choice Microsoft OLE DB Provider for SQL Server should work with most databases for SQL Server 2012 or earlier.  Depending upon your version of SQL Server, you may experience better performance with one of the other choices.

Some database products include their own, specific database driver.  You'll want to check with the software provider of your database to determine the correct driver to use.

 

Depending upon which driver you choose, the appearance of the Connection tab can vary but should look similar to this:

On the Connection tab, specify:

  • The name of the server on which your database is located
  • The logon information: Windows NT or the user name and password (be sure to check the Allow saving password box if you are using a user name and password)
  • The name of the database to which this data source is connecting

 

On the Advanced tab...

It is a best practice to trim blank spaces from fixed-length text fields.

 


Note:  If you find that the password will not save when using more modern versions of the SQL database driver, you may need to ensure that that setting for Persist Security Info is set to True on the ALL tab:


 


You can then click the Test Connection button to verify your settings.  You should receive a confirmation message:

 

 

Click OK and the Data Source Settings window should show that you are licensed for the data source.

{If the text reads that You are not licensed for this data source, click that text to get additional information about the issue.}

You can then click the Test Connection button and should see the Test Connection Succeeded message.

Click OK .

If you would like this data source to be the default data source that Jet Professional will use, click the Set Default button on the Data Source Settings window.

Click OK and your Universal SQL data source is now ready for use with Jet Professional.

 
Jet Data Warehouse

 

Setting up a Universal SQL Data Source using Jet Professional

A Jet Data Warehouse database must have previously been created with the Jet Data Manager.  You will need login permissions for that database.  You may need to contact your system administrator to create that for you.


You’ll also need the following files and information:

  • For Jet Professional or Jet Essentials 2013 and higher: your Jet License Activation Code (including a Universal Connector license)
  • For Jet Essentials 2012 R2: your Jet Universal Connector License File (.jlf)
  • Database Information:
    • Server Name or IP Address
    • Authentication Type (Windows or Database)
    • Database Name
    • Database Login Information

 

Step-by-Step

To create a new  data source connection to your Jet Data Warehouse, start by clicking the Data Source Settings button on the Jet ribbon.

 

On the Data Source Settings window, click the "Add" button.

 

On the New Data Source window, provide a name for your data source and select "Jet Data Warehouse" for the type.  Click OK.


You will first need to specify what type of Authentication you will use to attach to the database.

If the server recognizes your Windows login, you can select "Windows Authentication".  If you need to use a User Name and Password to access the data, you can select "Database Authentication" and specify your login credentials.

 

In the Connection tab, you will provide the name of the server on which your Jet Data Warehouse database resides.

You can then use the pulldown button to see the databases that are available.  From that list, choose the name of your Jet Data Warehouse database.

 

You can then click the Test Connection button and should see the Test Connection Succeeded message.

Click OK .

If you would like this data source to be the default data source that Jet Professional will use, click the Set Default button.

Click OK and y our Jet Data Warehouse data source is now ready for use with Jet Professional.


 
Jet Enterprise Cubes

 

Setting up a Jet Enterprise Cube Data Source using Jet Professional

An OLAP Cube database must have previously been created with the Jet Data Manager. You will need login permissions for that database. You may need to contact your system administrator to create that for you.


You’ll also need the following files and information:

 

  • For Jet Professional or Jet Essentials 2013 and higher: your Jet License Activation Code (including a Universal Connector license)
  • For Jet Essentials 2012 R2: your Jet Universal Connector License File (.jlf)
  • Database Information:
    • Server Name or IP Address
    • Database Name

 

Step-by-Step

To create a new  data source connection to your Cubes, start by clicking the Data Source Settings button on the Jet ribbon.

On the Data Source Settings window, click the "Add" button.

On the New Data Source window, provide a name for your data source and select "Cube" for the type.  Click OK.


On the connection tab, enter the server where your OLAP database is located. In the database drop-down, you will find the name of your OLAP database. If the drop-down does not populate, the Windows login you are using does not have access to the Cubes. If this is the case, see the KB article referenced at the bottom of this page.

You can then click the Test Connection button and should see the Test Connection Succeeded message.

Click OK .

Click OK and y our Cube data source is now ready for use with Jet Professional.

When the connection has been successfully added, the Pivot Table Cube button will light up in the Jet ribbon.

 

 

 

 

 

 

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments