XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/

Difference between revisions of "SQL Data Tables (Administrators)"

Line 38: Line 38:
  
 
== See Also ==
 
== See Also ==
 +
* [[Relational Data Tables (Standard Users)]]
 
* [[Relational Slicers (Administrators)]]
 
* [[Relational 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 15:32, 26 April 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.

Inserting a Relational Data Table

  1. Select the XLCubed > Insert Data Table > SQL... button from the XLCubed ribbon.
    RelationalTable0.png
  2. Select the type of connection you want to create - for this example we are using Microsoft SQL Server:
    QueryTable1a.png
  3. Type the server name and select the authentication type with which to connect.
    QueryTable1.png
  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.
    RelationalTable1.png
  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 relational table will be inserted at the point you selected.
    RelationalTable2.png

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.

Using Stored Procedures

Data-returning stored procedures can be used with relational 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:

  1. When you are satisfied with your query, click the Publish Query toolbar button at the top of the designer (PublishQuery.png)
  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

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