Invantive Query Tool for SQL with Exact Online

Invantive Query Tool for SQL with Exact Online. http://www.invantive.com/products/invantives-free-query-tool-exact-online-edition
This video teaches you how to use the Invantive Query Tool for SQL with Exact Online. Invantive's free SQL Query Tool for Exact Online provides you with real-time Business Intelligence across your entire enterprise. It provides access to your crucial company information stored in the Exact Online cloud. No programming, no coding, no Excel downloads; just use the SQL knowledge you already have. This enables you to organize your business data fast for correct decision making. You are be able to extract, analyze and report business data such as sales leads, project execution, general ledger transactions and accounts payables.

This is a free and maintained product which is available on our website in the downloads section. When the application starts it will first check for newer version of the software from the website.If it does find a newer version, the program will ask you if you want to upgrade or stay with the current version of the software. The first step to connect to Exact Online in the cloud, with SQL, is to choose the right connection. In this case the connection has the name "Invantive Webservice - Exact Online". The connection you'll use will also contain the words "Exact Online".The name you will use contain exact online but it may have an extension like "cloud test".

In the user name field you enter the user name you use to connect to Exact Online on the Internet. Then you log in to Exact Online with the query tool using the green log in button. OK, now the Invantive Query Tool opens. Here you can see the window is divided into two main parts. In the top part of the window you enter the SQL statements that you wish to execute. You can execute them on Exact Online, but you also can run queries on: Microsoft SQL Server, Oracle MySQL, Teradata, Oracle RDBMS or any ODBC source.

Ok, we are going to start simple. Let's write a query for Exact Online. As you can see, as you type, the software provides you with a suggestion for words to use for your SQL queries. These automatic suggestions work for SQL keywords, and they also work for tables and views in Exact Online. To execute the query press CTRL plus Enter or click the "Execute" button here in the top left corner. The results will now be fetched using the Exact Online interface protocols. The results are presented in a grid format. You can customize this grid yourself in the format that you like to use for your reports. And this layout can be saved for future use by clicking on Display - Layout, here you find a save button. Each time you execute the same query again, the same layout will be applied to your query.

Let's choose a column that I would like to group, and while we're at it turn on a few of these fields again. Ok, let's say you want to group the column by Blocking Status of an Administration. And as you can see here you have a count, you can even have subtotals again. And when you export these results to Excel, the grouping stays. So you get a spreadsheet with grouping already in place. When you save your layout, save your queries on Exact Online and then export the results, you get the same sort of report every time.

Let's move on and create another query. Here you also have a list of available tables. Of course they are shown already as suggestions during typing, but you can also here choose one of the possible suggestions in the list at the bottom here. Ok, for this instance let's see if there are any G/L Accounts defined in this system. Press CTRL plus Enter again. And it executes the query on Exact Online. And in this case I would like to have "Type" and "Code" as groups so I drag these columns up to this grey area above where my columns are displayed. Also, let's click some of these fields off using the same button we used earlier to add them. Now I can see easily what the general ledger is composed of. This is another way to work with the data from Exact Online; choose an object from the list of available objects in the system.

---

Closed Caption:


Hello and welcome to our video on using the
Invantive Query Tool for SQL with Exact Online.
Today I'm going to show you how you can use
the Invantive Query Tool to access data that was
stored in the cloud using Exact Online.

First I'm going to start the Invantive Query Tool.
There is a shortcut here on my desktop and you
can see I'm running a stable channel demo
version.
Don't get confused by the label "demo".
This is a free and maintained product which is
available from our website in the "Downloads"
section.
When the application starts, it will first check for
newer versions of the software.
If it does find a newer version, the program will
ask if you want to upgrade or stay with the
current version of the software.
You can always look here in blue shaded area of
the "Log in" box, under the product title, to see
what version of the software you have.
The first step to connecting to Exact Online in
the cloud with SQL, is to choose the right
connection.
In this case the connection has the name
"Invantive Webservice - Exact Online".
The connection you'll use will also contain in
words "Exact Online".
It may also contain a country code since Exact
Online is available in many countries and each
country has their own specific log in codes.

And here below you can see some background
information on the SQL connection to Exact
Online.
Please note: the administration that you will
work with is the one that was last used in the
web format of Exact Online.
In the "User name" field you enter the user
name you use to connect to Exact Online on
the Internet.
And the password is also identical to the one
you use online.
Then you log in to Exact Online with the Query
Tool using the green "Log in" button.
Now there is a blue bar below the "Log in"
button, that says "Connecting".
Right now the software is downloading
translations that it will use.
OK, now the Invantive Query Tool opens. Here
you can see the window is divided into two main
parts.
In the top part of the window you enter the SQL
statements that you wish to execute.
You can execute them on Exact Online, but you
can also run queries on:
Microsoft SQL Server, Oracle MySQL, Teradata,
Oracle RDBMS or any ODBC source.
And here at the bottom half of the window there
are a number of tabs with a list of "Available
Objects" in your database.
These are: the "Results" of the last query, a
"History" of previously executed queries, "DBMS
Output", "Explain Plan" and "Trace Activity".
The "DBMS Output", "Explain Plan" and "Trace
Activity" show information when the software is
used with the Oracle RDBMS platform.
OK, we are going to start simple. Let's write a
query for Exact Online.

And now I start typing.
As you can see when you type, the software
provides you with a suggestion for words to use
for your SQL queries.
Click on the "Enter" key if you'd like to use the
proposed word.
These automatic suggestions work for SQL
keywords, and they also work for tables and
views in Exact Online.

For example: when you start entering
"Administrations", which is a table within Exact
Online, it will suggest it.
You choose it just by using the "Enter" key, like
before with word suggestions.
To execute the query press "CTRL+Enter" or
click the "Execute" button here in the top left
corner.
The results will now be fetched using the Exact
Online interface protocols. They are presented in
a grid format.
You can customize this grid yourself in the
format that you like to use for your reports.
You can click "code" to sort the columns,

you can click on the "sum" key to add totals,
summaries, etc.
And you can also apply a "filter" by clicking the
icon here to the right of the "sum" key. This is
especially useful with large sets of results.
And by clicking the "pin", you can pin the
column so it stays fixed when you start
scrolling, like this.
Finally, you can also select the fields that you
wish to display by clicking the button here next
above the column numbers on the left hand side.
I've already removed some fields from my list.
And this layout can be saved for future use by
clicking on "Display Layout". Here you'll find a
"Save" button.
Each time you execute the same query again,
the same layout will be applied to your query.
It gets even better, especially since Exact
Online contains such interesting information with
financial figures.
You can easily make totals and you can also
group them easily.
Let's choose a column that I would like to group,
and while we're at it I'm going to turn on a few of
these fields again.

OK, let's say you want to group the column by
"Blocking Status" of an administration.
And as you can see here you have a "Count",
you can even have subtotals again.
Like this in your groupings.
And when you export these results to Excel, the
grouping stays.
So you get a spreadsheet with grouping already
in place.
When you save your layout, save your queries
on Exact Online and then export the results,
you get the same sort of report every time.
It's handy because you can build most of your
custom business reports in seconds.
OK. Let's move on and create another query.

Here you also have a list of available tables.
Of course they are shown already as
suggestions during typing,
but you can also choose one of the possible
suggestions in the list at the bottom here.
OK, for this instance let's see if there are any
G/L Accounts defined in this system.

Press "CTRL+Enter" again.
And it executes the query on Exact Online.
And in this case I would like to have "type" and
"code" as groups,
so I drag these columns up to this grey area
above where my columns are displayed.
Also, I am going to remove some of these fields
using the same button we used to add them.

That's better.
Now I can see easily see what the general
ledger is composed of.
This is another way to work with data from
Exact Online.
Finally, we have also taken care to provide you
with a large number of queries already here in
the "Samples" menu.

You can choose this sample file and get some
more SQL samples with filtering and sorting, to
assist you in your daily work with Exact Online.
A cool thing is, that when you use the Invantive
Query Tool for Exact Online,
you can use the same queries using Invantive
Composition for Microsoft Word and Invantive
Control for Excel.
You can create your own spreadsheets with risk
models and create contracts, quotes, letters,
etc.,
using data contained in Exact Online combined
with simple Word templates made by using
Microsoft Word.
I hope you learned how to get more out of Exact
Online with the Invantive Query Tool through this
video.
And hopefully you come back to learn more
about using our software from other videos.
Thanks for watching!