Jiwa – Integration with Excel

I have created a video below on how to extract data from Jiwa Financials to Excel.

There are two methods, a simple copy and paste method and a more complicated direct SQL connection method

Extract Data from Jiwa Financials – Simple Method

This first method is a simple copy and paste from any of the Jiwa Transaction Grids.

Just right click on the grid header and choose “Select All”

Then use Ctrl-C (the Control Key and the “C” Key together) to copy the selected data

Now open Excel, right click on a cell and select to paste.

The contents of the Jiwa Transaction grid will now be available to you inside Excel

Extracting Data from Jiwa Financials via a Report – Simple Method

Another way to extract data from Jiwa is to use the report system which is based on Crystal Reports

Select a report from the menu, tick “Print to Screen” and press the printer icon

From the report tab click on the export button

Select a location, a file name and choose a type of “Microsoft Excel”

Now locate the file you saved and open it. Your data will now be in Excel

Extract Data from Jiwa Financials – Advanced Method

Another way to extract data from MYOB Exo or indeed any other Microsoft SQL data source is to us the data connectivity features within Excel to create a direct connection to the Microsoft SQL database.

In Excel select the data tab and the “from Other Sources” button

Select “From SQL Server”

Enter in your SQL Server Name and Credentials

Select your Jiwa SQL database and the table you want data from and press “Finish”

Select the cell you want the data to be copied into and press “OK”

Enter in your credentials again and press “OK”

The data from the chosen table will automatically flow into Excel

To update your data just right click anywhere on the data and select “Refresh”

Modifying the SQL Query

If you know a bit of SQL you can edit the Query. Right click on your data and select “Table” and “Edit Query”

Change the “Command Type” to “SQL” and enter in your SQL statement in the “Command Text” box below

Enter in your Credentials and press “OK”

The data returned will now be modified in line with your new SQL Query

This video is best seen on the full screen. Just click on the icon at the bottom right of the video (see image below).

To exit full screen just press escape

This entire document, including design, text, videos, graphics, and photographs are Copyright © 2014 - Opal Logic Pty Ltd, All Rights Reserved.
No part of this document may be reproduced in any way or by any means for commercial or any other purposes, without prior written permission of Opal Logic Pty Ltd.
Use of any data for the purpose of creating promotional materials or producing a printed or electronic catalog of any kind is expressly forbidden without prior written permission of Opal Logic Pty Ltd.