Sending a report from Visual to a file and then using it in Excel is a handy feature that allows you to work with the Visual data in Excel. It’s a great way to really get a better understanding of how Visual works, and you get to take advantage of tried and true reports that pull the information from Visual. The thing is, I get a lot of questions about these reports!
I’m here to make your Visual life easier, so I’m sharing with you my top 7 questions and answers. Here we go!
- Question: I sent my report to file and I have all these extra columns that do not appear on my Visual print out.
Answer: Visual sends lots of fields (input items) to the reports, just in case you need them. For example, the Sales Journal sends over 70 fields but the standard report displays less than 20. Why would Visual do this? Well, every company that uses Visual ERP is unique. Some companies use a certain field while others don’t. It all comes down to using the flexibility of Visual ERP to manage their businesses. Visual wants to make it easy to add these fields to the printed report, therefore they give you lots of choices.
- Question: I just sent my Trial Balance to file and it does not show the “Prior Period Ending Balance” or “Current Period Ending Balance” columns. Why is it missing?
Answer: Some of the information displayed on the Visual reports is created with a calculation from the Input Items that are sent to the report. For example, Prior Period Ending Balance = Prior_Debit_Amount – Prior_Credit_Amount. If you open the report in the Report Builder, you can see the formula. So all you need to do is to add the formula in Excel.
- Question: I sent the Inventory Valuation Report to file and when I open in Excel, the columns get misaligned. What causes this?
Answer: There are two symbols that could cause this.
- Comma – When a Visual report is sent to Excel, it is sent as a text or comma separate value file. If there is a comma in the description, Visual will split the description into two columns instead of one, then all the fields are shifted over one column.
- Quotation – Commonly used for Inches, this can end up merging fields together.
- Question: The dates show up with all these 00’s after it and I can’t format to a date with Excel.
Answer: Visual is sending the time stamp with the field. A quick way to convert it back to a date format is to copy the time portion from one of the date fields. Then go to search and replace, and paste the time portion into the “Find What” field and “Replace With” nothing. Just as I’ve shown below. Then click on Replace All. Any date fields will now be a proper date format that you can use in Excel.
- Question: My GL accounts are separated by a period to split the account code with the department. For example, 6010.10, 6010.11. When I export any report containing a GL Account I lose the 0 from the department. I now have 6010.1. This really messes me up.
Answer: If the export file is created .txt or .csv extension when it is opened, Excel looks at the field, sees numbers, and converts this column to a number field. Then it will drop the ending zero and it will drop leading zeros as well. Check out the answer to the next question to give you a way to overcome this.
- Question: I have been opening my text export file and keep having to delete the columns I do not need. Is there a faster way to get rid of all those extra columns?
Answer: Yes, there is a quick way to do this. Instead of using a .csv or .txt extension on your export file, do not enter an extension. When you open in Excel you have the options of skipping the import of the column. You can even set the column format to text. This will ensure that you will not lose the leading or trailing zero.
- Question: Every month, I am sending the sub-ledger reports to Excel, opening and massaging the numbers so I can use them for month-end. Is there any way around this?
Answer: Sure thing. If the same reports are being used every month, then consider creating a report that can automatically pull the data from Visual. You have lots of reporting tool options but I prefer Excel. I have an Excel file that I use for Inventory Reconciliation. It pulls the Inventory Balance Report, Inventory Valuation Report and even the GL Balances. After I have posted the Manufacturing Journals, I refresh the file and immediately have my reconciliation. Talk about a time saver!
Would you like some more information? I have a detailed procedure that you can use to gives you the “how to” for sending Visual ERP reports to file and manipulating in Excel. Click HERE to find out more!
Kim Worrall is a CPA, CA with over 30 years of business consulting and financial management experience. Kim has been consulting with Visual ERP for over 20 years. She knows the tables like the back of her hand – something to do with her excitement over solving number problems. Kim’s love of numbers has made her the go to person for solving complex issues within Visual ERP.