When using the BS_SQL function of Add-in A-tools as a dynamic report, standardizing the data before doing the report is the first and most important condition of a process, to make error-free report. It is as important as placing the bricks as a foundation when building a building, the foundation is solid, the house is solid too. Today, this article will show you how to standardize the data table before using the BS_SQL function.
1. Format the table
- The table / data from the head row to the data rows must not contain any cells in the Merge state.
Reason:
A data table is defined by the columns of the rows, the intersection of the columns and rows is the cell. Merge multiple cells into a cell that will query the errors data.
In the below table example, cell B10 is in error because of the Merge status.
(pic)
- The title of the data table should be named short, written in succession, should only use A-Z, _, 0-9. Numbers are always behind the words.
(pic)
Reason:
Column headings with marks are easy to mistake, making formulas take time because of many compilation.
The column headings of the data table should be renamed as follows:
(pic)
No need to use the square bracket [], compile will be faster and reduce editor error.
2. Format data types in the table columns before importing data:
- In the Excel worksheet, format cell is set to the general type, the data type is determined when the data is entered. In some situations, the data entered is difficult for A-Tools or ADO data query objects to determine the data type. Assuming we enter 1111, Excel understands it's Number, user wants it is Text, and in conditional formulas with text values leading to false results.
Example MA_VLSPHH = '1111' will be wrong because Excel is considering MA_VLSPHH as Number type.
So we need to format the MA_VLSPH column in TEXT form first, then enter 1111 and formula comparison MA_VLSPHH = '1111' is correct because column MA_VLSPHH is interpreted by Excel as TEXT format.
Value 1111 before formating does not make sense. We need to re-enter after formatting.
If you do not want to re-enter value, you run the "Number into Text" function in the A-Tools menu.
The following is a hint on formatting the columns in the table before importing the data.
(pic)
- In the data table there should be a first line to enter a hypothetical value that does not affect the results of the report.
Columns are text (TEXT) '(single quotes). Columns with date, quantity, amount, percentage, rate (number), enter 0.
Assuming the above assumption makes A-Tools or ADO objects understand the data type of the data columns in the table is clearer.
3. Name the data area in the Excel worksheet.
If we name the data table, A-Tools can receive the data area in the spreadsheet and retrieve the data field correctly in the table.
For example: Filter the data rows in the sheet "KHO", range A3: K68.
Set the formula:
(pic)
If the data area is named KHO (= KHO! A3: K68) then the formula is as follows:
(pic)
Name is the most effective use of the formula. For the A-Tools add-in, the name of a KHO field is treated as the name of a table in the Excel database.
How to create a name for a data area
Users do the following steps:
+ 1: Select the data area. The data area must be from the table header row to the last row. For example, in the "KHO" sheet, black the area from A3: K68.
+ 2: Choose "Formula" from menu (For Excel 2007 or higher)
+ 3: Select "Insert -> Name" (For Excel 2003 or below)
+ 4: Select "Define Name". Name it at "Name"
+ 5: Select "OK" to agree.
In steps 2 and 3 we can replace by the keyboard shortcut CTRL + F3
We have a screen named NAME - below:
(pic)
If you receive a message that already exists, go to "Name Manager", then select Edit to edit.