| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Difference between revisions of "SQL Data Tables (Administrators)"
| Line 1: | Line 1: | ||
| − | {{imbox|text=This page describes | + | {{imbox|text=This page describes SQL Data Tables for [[SQL Administrators]]. For [[Standard SQL Users]], please see [[SQL Data Tables (Standard Users)]].}} |
| − | XLCubed can query your relational databases directly. Similar to [[Grids|Grids]], the relational query can be seamlessly built into an interactive report using [[ | + | XLCubed can query your relational databases directly. Similar to [[Grids|Grids]], the relational query can be seamlessly built into an interactive report using [[SQL Slicers]] and dynamic Excel ranges. |
| − | == Inserting a | + | == Inserting a SQL Data Table == |
# Select the {{Menu|XLCubed|Insert Data Table|SQL...}} button from the XLCubed ribbon. [[File:RelationalTable0.png|300px|center]] | # Select the {{Menu|XLCubed|Insert Data Table|SQL...}} button from the XLCubed ribbon. [[File:RelationalTable0.png|300px|center]] | ||
# Select the type of connection you want to create - for this example we are using Microsoft SQL Server: [[File:QueryTable1a.png|300px|center]] | # Select the type of connection you want to create - for this example we are using Microsoft SQL Server: [[File:QueryTable1a.png|300px|center]] | ||
| Line 10: | Line 10: | ||
# Select the destination cell for your statement and then build up your query by dragging tables and views into the main tab. [[File:RelationalTable1.png|300px|center]] | # Select the destination cell for your statement and then build up your query by dragging tables and views into the main tab. [[File:RelationalTable1.png|300px|center]] | ||
# As you pick the fields you will see the SQL statement build up in the lower half of the screen. You may also type the SQL statement directly. Click OK when finished. | # As you pick the fields you will see the SQL statement build up in the lower half of the screen. You may also type the SQL statement directly. Click OK when finished. | ||
| − | # The | + | # The SQL table will be inserted at the point you selected. [[File:RelationalTable2.png|center]] |
== Using Parameters == | == Using Parameters == | ||
| − | When designing a query, parameters will be automatically discovered by XLCubed and made available for editing. The parameter values can be supplied as a static value, pulled in from an Excel range, or driven by a [[ | + | When designing a query, parameters will be automatically discovered by XLCubed and made available for editing. The parameter values can be supplied as a static value, pulled in from an Excel range, or driven by a [[SQL Slicers|SQL Slicer]]. |
== Using Stored Procedures == | == Using Stored Procedures == | ||
| − | Data-returning stored procedures can be used with | + | Data-returning stored procedures can be used with SQL data tables. When designing the query, use the {{Code|EXEC}} command to run the desired stored procedure. As with [[#Using Parameters|SQL queries using parameters]] above, when Stored Procedures with parameters are used, XLCubed will automatically detect the parameters when they are typed in the query. For example, |
* Enter the command in the query designer: | * Enter the command in the query designer: | ||
EXEC MyStoredProcedure @Param1, @Param2 | EXEC MyStoredProcedure @Param1, @Param2 | ||
| Line 28: | Line 28: | ||
# Click {{Menu|OK}} to publish the query | # Click {{Menu|OK}} to publish the query | ||
| − | The query will now be available to both [[SQL Administrators]] and [[Standard SQL Users]], for data tables and [[ | + | The query will now be available to both [[SQL Administrators]] and [[Standard SQL Users]], for data tables and [[SQL Slicers|Slicers]]. Standard users will not be able to edit the query, but will be able to edit any parameters defined when it was published. |
To quickly insert a new data table based on the query: | To quickly insert a new data table based on the query: | ||
| Line 35: | Line 35: | ||
# Select the desired query | # Select the desired query | ||
# Click {{Menu|OK}} to insert the table | # Click {{Menu|OK}} to insert the table | ||
| − | |||
== See Also == | == See Also == | ||
| − | * [[ | + | * [[SQL Data Tables (Standard Users)]] |
| − | * [[ | + | * [[SQL Slicers (Administrators)]] |
* [[Query Tables]] for a similar feature in XLCubed 6.5 and previous versions. | * [[Query Tables]] for a similar feature in XLCubed 6.5 and previous versions. | ||
[[Category:Relational Reporting]] | [[Category:Relational Reporting]] | ||
Revision as of 14:02, 27 April 2012
 |
This page describes SQL Data Tables for SQL Administrators. For Standard SQL Users, please see SQL Data Tables (Standard Users). |
XLCubed can query your relational databases directly. Similar to Grids, the relational query can be seamlessly built into an interactive report using SQL Slicers and dynamic Excel ranges.
Contents
[hide]Inserting a SQL Data Table
- Select the XLCubed > Insert Data Table > SQL... button from the XLCubed ribbon.
- Select the type of connection you want to create - for this example we are using Microsoft SQL Server:
- Type 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 desired database and click OK to continue.
- Select the destination cell for your statement and then build up your query by dragging tables and views into the main tab.
- As you pick the fields you will see the SQL statement build up in the lower half of the screen. You may also type the SQL statement directly. Click OK when finished.
- The SQL table will be inserted at the point you selected.
Using Parameters
When designing a query, parameters will be automatically discovered by XLCubed and made available for editing. The parameter values can be supplied as a static value, pulled in from an Excel range, or driven by a SQL Slicer.
Using Stored Procedures
Data-returning stored procedures can be used with SQL data tables. When designing the query, use the EXEC command to run the desired stored procedure. As with SQL queries using parameters above, when Stored Procedures with parameters are used, XLCubed will automatically detect the parameters when they are typed in the query. For example,
- Enter the command in the query designer:
EXEC MyStoredProcedure @Param1, @Param2
- The parameters appear in the parameters grid on the left of the form
Publishing Queries
The relational queries designed as described above can be published for reuse by all users. To do this:
- When you are satisfied with your query, click the Publish Query toolbar button at the top of the designer (
) - Type the address of the XLCubed Web Edition site and click the Connect button
- Select a folder to publish the query to, give it a name and optionally a more verbose description
- Click OK to publish the query
The query will now be available to both SQL Administrators and Standard SQL Users, for data tables and Slicers. Standard users will not be able to edit the query, but will be able to edit any parameters defined when it was published.
To quickly insert a new data table based on the query:
- Select the XLCubed > Favourites > Published SQL Queries... button from the XLCubed ribbon
- Type the address of the XLCubed Web Edition site and click the Connect button
- Select the desired query
- Click OK to insert the table
See Also
- SQL Data Tables (Standard Users)
- SQL Slicers (Administrators)
- Query Tables for a similar feature in XLCubed 6.5 and previous versions.
