Jet Reports Homepage |  Community Forum |  Downloads |  Submit A Ticket |  Jet Express Support
Feedback

Query Tables


Overview

The query table feature is available for the SQL and Oracle data sources as well as the Dynamics NAV and Dynamics AX adapters.

Process

Adding a Query Table

To add a query table, follow the steps below.

  1. Connect to one of the supported data sources.
  2. Right-click on the data source, click on Advanced and click on Query Table Setup.

The Query Table Setup window opens.

3. Click on Add. A new table is added to the list.

4. In the Name box, type a name for the table.

5. (Optional) In the Schema box, type a schema to use.

6. In Query, enter the query you wish to use for creating the table. The query should contain a SELECT statement and follow the syntax required by the source.

7. Select Subquery needed if you are using an alias in your query or if the joined tables have ambiguous field names. Otherwise, selection rules will fail.

8. Repeat step 3-7 to add the tables you need and click on OK.

9. Right click on the data source and click on Read Objects from Data Source. The tables are listed in the panel in the right-hand side of the Jet Data Manager and can be included in the project like any other table.

Handling Accounts in Dynamics NAV

When you create query tables for Dynamics NAV, you will have to consider how you handle accounts.

Single Account: 

To get data from one account, remember the account in the FROM part of your statement:

SELECT * FROM [dbo].[MyCompany$MyTable] 

Multiple Accounts:

For NAV each company has a separate table, so if you have Company A, B, and C then your customer tables in NAV look like:

  • CompanyA$Customer
  • CompanyB$Customer
  • CompanyC$Customer

The Jet Data Manager handles this behind the scenes by building a UNION into the SSIS package to pull data from all the companies the user has selected in the adapter setup.

To get data from multiple accounts, in the same way the Dynamics NAV Adapter does it, you can use placeholders:

SELECT * {0} FROM [dbo].[{1}$MyTable]

Digits:

The Jet Data Manager will replace the digits in curly brackets during execution to create the following statement for each account. 

{0} will add a DW_Account field in the select statement.

{1} will be replaced with all of the companies that have been selected

SELECT * ,CAST(‘MyCompany’ AS nvarchar(30)) AS (DW_Account) FROM [dbo].[MyCompany$MyTable]

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

Comments