SQL SHELL Function

The SHELL function creates a dynamic cross-application functionality in Query Designer. Using the text SHELL in the SQL panel, followed by an external program or file name, adds versatility to the SQL statement, by launching the external program and/or opening the file, or image, providing access to additional data. This function allows the user to process anything which could be processed in the RUN command in Windows. The SHELL statement may also be used as part of a query detail drill down.

Example: SHELL Statement Used to Access a Vendor Voucher in a Word Document

In this example, the query contains two parameters: Vendor # and Voucher #, and the Gross Amt field is an GEXQ summary field.

1. In the SQL Panel, switch to manual entry mode, by unselecting the Auto Generated check box.
2. Type SHELL, press the Enter key, then type the additional text required to run the command, or launch the external program. In this example, C:\PO\&PARM02.docx has been entered, indicating:
• C:\PO\: the network directory location of the target file.
• &PARM02: the Voucher # parameter defined in Query Designer, and used in Excel cell reference.
• docx: the file extension of the target file.

3. From the Spreadsheet Server Ribbon, select Home > Save Query.
4. In Excel, add the GEXQ formula. Use cell references for the parameters.

5. To execute the SHELL statement, select the cell containing the GEXQ formula, then, from the Spreadsheet Server Ribbon, select Drill Down, or, right-click, and, select Spreadsheet Server > Drill Down. The SHELL statement is executed (opens the Word document, C:\PO\12324.docx, in this example).