Difference between revisions of "Query Tables/Version 6.5"
(→See Also) |
m (Text replacement - "XLCubed" to "FluenceXL") |
||
| Line 1: | Line 1: | ||
<noinclude>{{VersionDisambiguation|pageVersion=6.5|disambiguationPage=Query Tables/Versions}}</noinclude> | <noinclude>{{VersionDisambiguation|pageVersion=6.5|disambiguationPage=Query Tables/Versions}}</noinclude> | ||
| − | + | FluenceXL is primarily focused on cube-based data, but can also query relational databases directly. FluenceXL creates a SQL Query object, which can then be updated as required using FluenceXL formulae. The SQL Query can be built into a dynamic report and can be based on user-provided selections. | |
== Insert a SQL Query == | == Insert a SQL Query == | ||
| Line 7: | Line 7: | ||
| − | * Select the {{Menu| | + | * Select the {{Menu|FluenceXL|Insert Formula|SQL Report...}} from the FluenceXL ribbon. |
| Line 46: | Line 46: | ||
== Query Table Control Formulae == | == Query Table Control Formulae == | ||
| − | + | FluenceXL Excel Edition allows you to use formulae to help you manage your Query Tables: | |
; XL3QueryTableGetConnectionString | ; XL3QueryTableGetConnectionString | ||
: Allows you to retrieve the connection details for a Query Table | : Allows you to retrieve the connection details for a Query Table | ||
Latest revision as of 08:32, 4 July 2023
 |
This article refers to FluenceXL version 6.5. For other versions, see Query Tables/Versions. |
FluenceXL is primarily focused on cube-based data, but can also query relational databases directly. FluenceXL creates a SQL Query object, which can then be updated as required using FluenceXL formulae. The SQL Query can be built into a dynamic report and can be based on user-provided selections.
Contents
[hide]Insert a SQL Query
To insert a SQL Query:
- Select the FluenceXL > Insert Formula > SQL Report... from the FluenceXL ribbon.
- Select the type of connection you want to create - for this example we are using Microsoft SQL Server.
- Include the server name and select the authentication type with which to connect.
- Click Connect and you should then see all the databases on the server to which you have access. Select the cube before continuing.
- Select the destination cell for your statement and then build up your query by dragging entities across to the main tab in the window.
- As you pick the fields you will see the SQL statement build up in the lower half of the screen. Click OK when finished.
- The SQL Query formulae will be inserted in the row that you selected, and the SQL Query itself in the row below.
- Right-clicking on the XL3QueryTableSetSQL formulae will allow you to Edit Query Table so that you can make changes.
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
FluenceXL 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
