Easify Forums

Forums -> Reporting

 Advanced Easify reporting using Excel [EASIFY V3 ONLY]

Post Reply
Page 1 of 1 1
Posted by John

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)

Like our facebook page and always get the latest updates!

Posted by Richard

Re: Advanced Easify reporting using Excel

.


Regards - Richard (Easify Developer)

Posted by MarkNBrewer

Re: Advanced Easify reporting using Excel

This is very helpful, but when I run it I only get the column headings and no data returned. I suspect I may have selected the wrong table when choosing the company and table. Which table(s) should I select?

Mark

Posted by John

Re: Advanced Easify reporting using Excel

Could you be more specific with what data you are trying to get from the database?


Happy to help - John (Easify Support)

Like our facebook page and always get the latest updates!

Posted by MarkNBrewer

Re: Advanced Easify reporting using Excel

Hi there

I am trying to obtains sales information by customer and product.

Regards

Mark

Posted by John

Re: Advanced Easify reporting using Excel

For this information you will need to reference the following database tables:

ORDERS

ORDER_ELEMENTS (this tables records products that have been added to orders)

Customers

ITEMS (this is the products table)

You might also want to reference the Payments table too.


Happy to help - John (Easify Support)

Like our facebook page and always get the latest updates!

Posted by Richard

Re: 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.


Regards - Richard (Easify Developer)

Posted by kurt

Re: Advanced Easify reporting using Excel [EASIFY V3 ONLY]

Hi all

will there ever be a point that you can connect excel to a on-premises V4 server.

Posted by Richard

Re: Advanced Easify reporting using Excel [EASIFY V3 ONLY]

kurt said:

Hi all

will there ever be a point that you can connect excel to a on-premises V4 server.

No, it's a limitation of the way Microsoft Excel handles OData security. We can only support it in a cloud environment.


Regards - Richard (Easify Developer)

Post Reply
Page 1 of 1 1

Easify Website V 5.0    © 2024 Easify Ltd ® All rights reserved.     Privacy Policy