Advanced Easify reporting using Excel [EASIFY V3 ONLY]
Please note that it is now possible to connect Excel to Easify V4 to read data from your Easify Server if you have an Easify Cloud Server subscription.
It is not possible to connect Excel to an in-premises Easify V4 Server.
If you would like more information about this feel free to email support@easify.co.uk.
Click here for the forum post about connecting Easify V4 to Excel...
How to connect Excel to the Easify database and execute SQL statements.
This guide explains how to build more advanced Excel reports.
What we're going to be doing is executing SQL (the database query language) statements in Excel against the Easify database.
Step 1. To connect Excel to the Easify database, from the top menu go to Data > Get External Data > From Other Sources > From SQL Server
Step 2. If you are working on the same computer that hosts the Easify database computer, you can enter these details and click Next.
If you are on a client computer that connects to the Easify database computer you can get the Server name from Easify in the bottom left corner:
(My server name is JOHN-MSI\Easify in the above example)
Then change the radio button to Use the following User Name and Password and enter these details:
Username: sa
Password: Eas1fyPeas1fyLemonSqueaz1fy
Step 3. Select your company database from the drop down list, mine is TestCoLtd in this example.
Then click Next, and Finish on the next screen.
Step 4. Make sure on this screen you have these settings selected then click Properties.
Step 5. Go to the Definition tab on this screen, change Command type to SQL from Table and copy the following text into the Command text field and click OK:
SELECT *
FROM ORDERS o
JOIN ORDER_ELEMENTS oe ON o.ORDER_NO = oe.ORDER_NO
JOIN Customers c ON o.CUSTOMER_NO = c.CustomerId
LEFT JOIN Payments p ON o.ORDER_NO = p.OurRef
WHERE
o.DATE_INVOICED IS NOT NULL
AND o.INVOICED = 1
(This is the SQL statement that returns, row by row, all related Orders > Order Items > Customers > Payments. We explain lower down how to filter this data by date and how to hide unwanted columns.)
Step 6. Then click OK on this window.
NOTE: This information only applies to Easify V3.
You should now have a Work Sheet full of all possible data.
I’d suggest right clicking the columns you do not need and selecting Hide.
If you want to filter the data for a date range, I’d suggest using the DATE_ORDERED column:
Happy to help - John (Easify Support)