| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Difference between revisions of "SQL Data Tables (Administrators)"
(Added Category:Relational Reporting) |
(added parameters and publishing) |
||
| Line 1: | Line 1: | ||
| − | 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 [[Relational Slicers]] and dynamic Excel ranges. |
== Inserting a Relational Data Table == | == Inserting a Relational Data Table == | ||
| − | + | # 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]] | |
| − | + | # Type the server name and select the authentication type with which to connect. [[File:QueryTable1.png|300px|center]] | |
| − | + | # 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. [[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. | |
| − | + | # The relational table will be inserted at the point you selected. [[File:RelationalTable2.png|center]] | |
| + | |||
| + | == 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 [[Relational Slicers|Relational Slicer]]. | ||
| + | |||
| + | == 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 {{Menu|Publish Query}} toolbar button at the top of the designer ([[File:PublishQuery.png]]) | ||
| + | # Type the address of the [[Web Edition|XLCubed Web Edition]] site and click the {{Menu|Connect}} button | ||
| + | # Select a folder to publish the query to, give it a name and optionally a more verbose description | ||
| + | # Click {{Menu|OK}} to publish the query | ||
| + | |||
| + | The query will now be available to both [[SQL Designers]] and normal users, for data tables and [[Relational Slicers|Slicers]]. Normal 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 {{Menu|XLCubed|Favourites|Published SQL Queries...}} button from the XLCubed ribbon | ||
| + | # Type the address of the [[Web Edition|XLCubed Web Edition]] site and click the {{Menu|Connect}} button | ||
| + | # Select the desired query | ||
| + | # Click {{Menu|OK}} to insert the table | ||
| + | |||
== See Also == | == See Also == | ||
Revision as of 16:31, 19 March 2012
XLCubed can query your relational databases directly. Similar to Grids, the relational query can be seamlessly built into an interactive report using Relational Slicers and dynamic Excel ranges.
Contents
[hide]Inserting a Relational 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 relational 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 Relational Slicer.
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 Designers and normal users, for data tables and Slicers. Normal 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
- Query Tables for a similar feature in XLCubed 6.5 and previous versions.
