CData Excel Formulas
The add-in provides formulas for executing read, create, update, and delete operations to SAP BusinessObjects BI. The function parameters can refer to specific cells; as these values change, the add-in automatically refreshes the results. Since Excel formulas are stored within the Excel file, you can easily share your dynamic spreadsheets with others.
The examples in this section are based on the range of data J14:L17, shown below. Note that for conciseness, these examples only show the cells with data, which makes it appear as if the data is top-left aligned, but a top-left alignment is not required: the rows of data can exist anywhere on the spreadsheet.
CDATAQUERY=CDATAQUERY(Query, [Connection], [Parameters], [ResultLocation], [Options])
This formula executes a SELECT, INSERT, UPDATE, or DELETE statement or a stored procedure call. By default, the cell where the formula is entered shows the number of rows returned or the number of rows that have been modified and subsequent rows show the results.
|Query|| The Query argument is a string containing the query. For example: |
SELECT * FROM MyCustomReportTo execute a parameterized query, use the standard syntax:
SELECT StoreName, Column1 FROM MyCustomReport WHERE Column2 = @Column2 AND Column1 = @Column1
The add-in reads the parameters from a range. Specify the range in the Parameters property.
|Connection|| A string that defines the connection: either a connection profile you created from the CData ribbon (see Managing Connections) or a connection string. Connection strings must include the Provider property. For example:|
Provider=SAP BusinessObjects BI;User=MyUser;Password=MyPassword;Url=http://myserver:6405/biprws;
The Connection argument is optional if you have used the Data Selection wizard to pull data into the spreadsheet. The formula uses this connection by default.
|Parameters|| The Parameters argument specifies the range of cells that contains the names and values for the parameters. The first row of the range is a header row and specifies the parameter names.
For example, if the range A10:B11 contains the parameters then the cells A10:B10 should contain the parameter names, and the cells A11:B11 should contain the parameter values.
|ResultLocation||A string that specifies the cell where the row-by-row output of the results start. By default, CDATAQUERY outputs the results just below the cell where the formula was entered.|
|Options||A string containing additional options to control the formula's behavior. The default behavior is to return data in a table, where the first row contains the column headers. "Header=False" may be set to return the data without headers. "Scalar=True" may be set to return only a single cell of data, and return it into the same cell as the formula.|
The following formula searches SAP BusinessObjects BI and outputs the results starting at row J14.
=CDATAQUERY("SELECT StoreName, Column1 FROM MyCustomReport WHERE Column2 = 'Bob'","SAPBusinessObjectsBI.Connection1",,"J14")
The following formula selects only the Column1 for a single Column2 from SAP BusinessObjects BI and outputs the result into the formula's cell.
=CDATAQUERY("Select Column1 FROM MyCustomReport WHERE Column2 = " &J15,"SAPBusinessObjectsBI.Connection1",,,"Scalar=True")