SQL Data Tables (Administrators)

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.

Inserting a SQL Data Table

1. Select the XLCubed > Grids & Tables > SQL... button from the XLCubed ribbon

2. Select the type of connection you want to create - for this example we are using Microsoft SQL Server:

3. Type the server name and select the authentication type with which to connect.

4. 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.
5. Select the destination cell for your statement and then build up your query by dragging tables and views into the main tab.

6. 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.
7. 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. See Using SQL Parameters for more details.

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

From Version 9.2 there is a Stored Procedure toggle button in the toolbar. If you turn this on it tells XLCubed that the SQL statement is a stored procedure which can effect the generated query. For example for SQL Server the procedure will be called directly, not via sp_executesql.

Publishing Queries

Relational queries designed as described in this article can be published for reuse by all users. To do this:

1. When you are satisfied with your query, click the Publish Query toolbar button at the top of the designer ()
2. Type the address of the XLCubed Web Edition site and click the Connect button
3. Select a folder to publish the query to, give it a name and optionally a more verbose description
4. Click OK to publish the query

Alternatively, select the XLCubed > Web > Publish to Web > Publish SQL Query... ribbon item or the XLCubed > Publish SQL Query... menu item in Excel 2003 and lower, and select the item you would like to publish.

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:

1. Select the XLCubed > Favourites > Published SQL Queries... button from the XLCubed ribbon
2. Type the address of the XLCubed Web Edition site and click the Connect button
3. Select the desired query
4. Click OK to insert the table

See Also

• SQL Data Tables (Standard Users)
• SQL Slicers (Administrators)
• Driving a SQL Connection from an Excel range
• Query Tables for a similar feature in XLCubed 6.5 and previous versions.
• Tables