How to use Excel with reports
Most of the Retailer's Advantage Reports also create a text file containing the report information. Any subheadings are written to the end of the detail lines. Any subtotals are omitted. These files can be opened using a spreadsheet program which allows you to reformat the data using features of the spreadsheet program. The report text files are written to folder "DBS_Archive/DBS_TextOut" on your boot drive. This lesson shows you how to reformat the Stock/Sales by Vendor report using Excel. The same methods may be used on any of the report text files in folder DBS_TextOut.
This screen shows you where to find the .txt file on you computer. Do not double click the file. Instead launch Excel and navigate to your boot drive/DBS_Archive/DBS_TextOut from within Excel and open file StockSales.txt.
Verify the screen is set as shown and click the next button.
Verify the screen is set as shown and click the next button.
In many reports the first column contains both text and numeric data. For example, Item Ids may use numbers for some items and text or numbers and text for other items. To make sure data in the column is handled properly, it must be converted to text.
Select the Text option in the Column format for first column and click the finish button.
This is the data displayed in Excel. First the data will be sorted by Vendor in ascending order and by profits in descending order. Then some formatting to make data more readable. Begin by selecting the portion of the spreadsheet that is to be sorted.
1. Select the upper left column heading cell - in this case Item Id. Press and hold the shift key.
2. Select the last cell in the spreadsheet - most lower right cell.
Use the popups to select Vendor and Profit and set radio buttons as shown. Make sure the Header row checkbox is selected. Click the Ok button.
This is the sorted report. The next screen shows the results of making the headings bold, right aligning headings of numeric data, and placing an underline border under the headings.
This screen shows the column widths reset and numeric decimals set to make the data more readable. Also the headings can be frozen so they don't scroll off the screen by selecting cell A2 and selecting menu option Window/Freeze Pane.
Other sort options may be applied as needed. Remember to select the portion of the spreadsheet to be sorted before selecting the sort option from the menu. This option results in report being sorted from most profitable to least profitable.