Query Sheets

Users must fill out a query sheet in order to submit queries to the 1010data server using the 1010data Excel Add-in.

Each query in a workbook is defined on a query sheet (or q-sheet). There is no external limit imposed on the number of q-sheets allowed in a workbook (i.e., it is subject only to Excel’s limit on worksheets).

Query Description: (optional) This name will display in the progress bar as the queries are being processed. (Recommended, for user documentation only.)
To be Applied to Table: (required) The full name (including path) of the 1010data table to which the query is to be applied. Example: pub.doc.retail.salesdetail (no uppercase letters, spaces, or special characters)
Note: Table name is not the same as table title. The table name is used when referring to the table in queries and cannot contain any uppercase letters, spaces, or special characters. The table title is a string that is used to help describe the contents of a table (e.g., "Sales Detail by Customer")
1010data Macro Code: (required) The 1010data macro code that defines the query is entered here. Code can be copied from a text file (or from the Edit Actions panel in the 1010data web interface) and pasted into this area. Query code can include Excel formulas that create dynamic code dependent on variables and data entered on the input worksheet. The query will run until the first blank line is encountered. Ensure that there are no unintentional blank lines to guarantee that the entire macro will be processed. Blank lines can be used as breakpoints to assist in debugging erroneous macros.
Note: The text alignment often gets corrupted when text is pasted here. This can be corrected by left-justifying the selected text after it is pasted.
Result Destination: (required) A reference (an address or named range) to the top left cell (one cell only, in the same workbook) of the range where the query results are to be pasted. Example: Sheet1!$A$2
Note: This cell must contain a valid reference to a cell in the same workbook. Also, you should ensure there is sufficient room at the destination so that query results don't overwrite the results of other queries or data.
Max Rows to Retrieve: (optional) Allow result rows to be "capped" at a given number of rows. Where N is specified, the first N rows in the query results will be retrieved; if N exceeds the number of rows in the query results, all rows will be retrieved. If left blank, all the rows resulting from the query will be retrieved.
Note: This entry must be either blank or a number between 1 and the number of rows available on the worksheet at the destination specified.
Column Headers: (required) Defines whether the data will be returned with a header row and, if so, what type of header. Select one of the following from the drop-down list: None, Column Names, or Column Labels.
Note: Select Options from the 1010data menu to change the default value for this cell.
Data Format: (required) Defines what type of formatting will be applied to the query results. The entry must be either Formatted, Unformatted, or Raw. Select the desired option from the drop-down list:
  • Formatted - Formatted data will appear in the number formats defined in the 1010data macro code and, when retrieved, will overwrite the formatting of the destination cells.
  • Unformatted - Unformatted data will appear as Excel values without any formatting (i.e., dates will be in the internal Excel format) and, when retrieved, will not overwrite formatting of destination cells.
  • Raw - Raw data will appear as Excel values without any formatting, but data will be in native 1010data format; formats defined in the 1010data macro code will be ignored (i.e., dates will appear in YYYYMMDD format). Retrieved data will not overwrite formatting of destination cells.
Query Enabled: (required) Allows or prevents a query from running. Must be either TRUE or FALSE. Defaults to TRUE. To prevent a particular query from running, set to FALSE (useful while developing or debugging a template).
Clear range before pasting: (optional) An address range (or defined name) specifying cells to be deleted before the query results are pasted. This is useful for clearing the results of a previous run of the query. Only contents (not formatting) will be cleared.

Example: Sheet1!$A$2:$D$40

Note: If populated, this cell must contain a valid reference to a range of cells in the same workbook.
WARNING: Double-check input before running; the contents of the range referenced here will be deleted without recourse.