| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Difference between revisions of "Query Tables/Version 6.1"
(Created page with 'Query Tables allow you to bring relational data into the Excel environment, while keeping a dynamic link to the data source. This means that when your data changes, your spreadsh…') |
(Added editing and formulae sections) |
||
| Line 8: | Line 8: | ||
# Select the destination for the Query Table, and confirm the connection string and SQL, then click OK: [[File:QueryTableWizard3.png|center]] | # Select the destination for the Query Table, and confirm the connection string and SQL, then click OK: [[File:QueryTableWizard3.png|center]] | ||
# The Query Table formulae will be inserted in the row that you selected, and the Query Table itself in the row below: [[File:QueryTableWizard4.png|center]] | # The Query Table formulae will be inserted in the row that you selected, and the Query Table itself in the row below: [[File:QueryTableWizard4.png|center]] | ||
| + | |||
| + | == Editing Existing Query Tables == | ||
| + | Editing the XL3QueryTableSetConnectionString and XL3QueryTableSetSQL formulae allow you to edit the connection string and the SQL, respectively. | ||
| + | |||
| + | == Query Table Control Formulae == | ||
| + | XLCubed Excel Edition allows you to use formulae to help you manage your Query Tables: | ||
| + | ; XL3QueryTableGetConnectionString | ||
| + | : Allows you to retrieve the connection details for a Query Table | ||
| + | ; XL3QueryTableSetConnectionString | ||
| + | : Allows you to control the connection details for a Query Table. You can use regular Excel formulae to make the connection string, setting details such as the server or database name, then use this formula to easily change the Query Table | ||
| + | ; XL3QueryTableSetSQL | ||
| + | : Like the XL3QueryTableSetConnectionString formula, XL3QueryTableSetSQL allows you to use Excel formulae to build up the SQL string to bring in user-selected criteria such as the period or the product the query should be run for. The XL3QueryTableSetSQL formula then references this statement, and each time the statement changes, the Query Table is referenced with the new criteria | ||
| + | ; XL3QueryTableSetParam | ||
| + | : Allows you to set parameters on a Query Table | ||
Revision as of 11:20, 4 October 2010
Query Tables allow you to bring relational data into the Excel environment, while keeping a dynamic link to the data source. This means that when your data changes, your spreadsheets can be easily kept up to date.
Insert a Query Table
To insert a Query Table:
- Select the XLCubed > Insert Formula > Query Table... ribbon or menu item:
- Select an existing data source, or define a new one by clicking <New Data Source>
- Use the Query Wizard to construct the SQL query:
- Select the destination for the Query Table, and confirm the connection string and SQL, then click OK:
- The Query Table formulae will be inserted in the row that you selected, and the Query Table itself in the row below:
Editing Existing Query Tables
Editing the XL3QueryTableSetConnectionString and XL3QueryTableSetSQL formulae allow you to edit the connection string and the SQL, respectively.
Query Table Control Formulae
XLCubed Excel Edition allows you to use formulae to help you manage your Query Tables:
- XL3QueryTableGetConnectionString
- Allows you to retrieve the connection details for a Query Table
- XL3QueryTableSetConnectionString
- Allows you to control the connection details for a Query Table. You can use regular Excel formulae to make the connection string, setting details such as the server or database name, then use this formula to easily change the Query Table
- XL3QueryTableSetSQL
- Like the XL3QueryTableSetConnectionString formula, XL3QueryTableSetSQL allows you to use Excel formulae to build up the SQL string to bring in user-selected criteria such as the period or the product the query should be run for. The XL3QueryTableSetSQL formula then references this statement, and each time the statement changes, the Query Table is referenced with the new criteria
- XL3QueryTableSetParam
- Allows you to set parameters on a Query Table
