If you know me, the answer is a big YES. It is a big time saving, eye-opening even mind-expanding tool. Just in case you don’t know, “What am I talking about?” Queries can be created in Excel to pull from the Visual ERP database. They can be used over and over again by refreshing with the click of a button.
But I have come across some people who are a little hesitant to let just any users utilize the power of Excel’s MS Query. I somewhat understand where they are coming from. Visual is a complex database. If someone doesn’t know what they are doing they might cause a problem.
Here are some questions/concerns about security that people have posed. I hope I am able to dispel some of the concerns.
1. Who should be given the ability to use MS Query?A user who has demonstrated:
An aptitude toward managing and manipulating data.
An understanding of how Visual works.
Has had some training or previous experience with MS Query.
- You could even consider creating a test that a user has to pass before they are granted the rights.
2. When an ODBC connection is made to the database, are there any security measures that can be taken?
- ODBC connections would only be provided to individuals who have the knowledge & skill level to work with the tables.
- When creating a query, you can set the Query to be manually refreshed only. That gives the user the ability to create the query and to only run when they are ready. We will cover this in the course.
3. Is it read-only access?
- The only user who has write access would be SYSADM.
- Even with the SYSADM access, another setting would need to set to allow changes.
4. Are there any system restrictions, in place to limit who has access to perform queries?
- You could only provide the ODBC to users to create queries.
- Also, you could restrict who has the MS query tool available.
5. Can you limit/restrict the Visual tables that these individuals have access to pull data from? If there is security in place, is it based on your Visual system access?
- Yes, you can limit the tables someone would have access to. There are actually 2 sets of security within Visual. The 1st one is within the application as to what window you can use. The 2nd is at the database level. Users can be granted rights to tables. You could definitely limit the tables.
6. Do you have any general documentation on how these queries work, that you could possibly send our way (not how to do queries – because that will be covered in your June class)
- Here’s is what I pulled from Excel help menus. “Using Microsoft Query, you can connect to external data sources, select data from those external sources, import that data into your worksheet, and refresh the data as needed to keep your worksheet data synchronized with the data in the external sources. …
Updating your worksheet in one operation Once you have external data in an Excel workbook, whenever your database changes, you can refresh the data to update your analysis — without having to re-create your summary reports and charts. For example, you can create a monthly sales summary and refresh it every month when the new sales figures come in.
How Microsoft Query uses data sources After you set up a data source for a particular database, you can use it whenever you want to create a query to select and retrieve data from that database — without having to retype all of the connection information. Microsoft Query uses the data source to connect to the external database and to show you what data is available. After you create your query and return the data to Excel, Microsoft Query provides the Excel workbook with both the query and data source information so that you can reconnect to the database when you want to refresh the data.”
7. Can you list some possible scenarios or queries that would be helpful for the finance team in their day-to-day functions?
- Queries can be simple 1 tables or use lots of tables. The queries fall into the following categories:
Ones that help to keep the database “clean”.
Queries that can be used for account reconciliation purposes.
Reports to speed up accounting processes and
Reporting and analysis.
Here are some examples of queries I have developed over the years:
KEEPING THE DATABASE CLEAN
- Looking for BAD FIFO layers.
- Inventory Transactions with value greater than $1 million (or whatever # makes sense for the business). If there were any transactions over these amounts then they could be researched prior to creating the manufacturing journals. This would help to speed up month-end.
- Customer Addresses that are missing Sales Tax Codes. Nothing more embarrassing than not charging the correct taxes or not charging them at all when you should be. Not to mention what would happen if you got audited by the government.
- Negative inventory by warehouse location. This is especially important if you are FIFO by Part Location.
RECONCILIATION PURPOSES
- Queries comparing the Inventory Balance Report to the Inventory Valuation Report. If there are differences, then you could have an inventory reconciliation problem.
- WIP Balance Report with extra information such as status, quantity received and closed date. All of this information will help to ensure the report is accurate. I have seen on many occasions, work orders totaling $10K or $20K or even more “stuck” in WIP because manufacturing closed them without receiving. This could be a direct hit to the bottom line.
- Query to support the amount outstanding for Vendor Deposits, which is a control account in Visual.
- Query to support Customer Deposits. Again, another control account.
SPEEDING UP PROCESSES
- Cash Requirements by Payment Type – (i.e. split between Wires, checks and EFT/ACH’s). The standard report does not allow that as a filter.
- Extracting payable information. I had one client that was manually creating an Excel sheet with details about freight. We changed their process a bit. The Accounts Payable person entered the details she wanted to track in the Reference field on the payable line. Then we created a query to pull the payable and payable line information thus eliminating the manual process. They were extremely happy as it saved a couple of hours per week.
- Pulling the Actual and Estimated Materials costs of work orders for the same Part ID over a period of time. Helps to see if there are any problems with the costs.
- Comparison of Operation costs to confirm process improvements. Better than having to pull a bunch of work orders manually.
- The posting entries for a specific transaction. Without the query, you have to scroll through pages of the posting reports until you find it. The query would be faster but also provides extra information. Again, saving more time.
REPORTING & ANALYSIS
- Sales & Cost of Sales for a specific time period. The user can then create pivot tables to analyze them any which way.
- Accounts Payable invoices (posted or unposted) to specific GL accounts. You might want to keep an eye on your marketing expense.
- Or posting to PO Accrual account with no receiver which will put the PO accrual account out of balance. Include the user ID so training opportunities can be identified.
- Inventory adjustments by Reason Code. You could even include the User ID if you want to know who is making the most adjustments.
So, you can see there are a myriad of ways to use Excel to extract from the Visual ERP database. With the right precautions it can be a safe AND powerful tool. What do you think? Should Accountants be Allowed to Use Excel Query with Visual?
You know my answer.
Kim Worrall, is president of Back to Basics, an INFOR Service Partner. Back to Basics provides in-depth and in-person training courses on Visual financial topics including “Under the Covers with Visual ERP & Excel”.
