Connect Workday to your favorite reporting tools without moving data.
Learn More →Excel Spreadsheet Automation with the QUERY Formula
Pull data, automate spreadsheets, and more with the QUERY formula.
The CData Excel Add-In for Tally provides formulas that can query Tally data. The following three steps show how you can automate the following task: Search Tally data for a user-specified value and then organize the results into an Excel spreadsheet.
The syntax of the CDATAQUERY formula is the following:
=CDATAQUERY(Query, [Connection], [Parameters], [ResultLocation]);
This formula requires three inputs:
- Query: The declaration of the Tally data records you want to retrieve, written in standard SQL.
Connection: Either the connection name, such as TallyConnection1, or a connection string. The connection string consists of the required properties for connecting to Tally data, separated by semicolons.
Set the following connection properties to connect to Tally Instance:
- Url: Set this to the URL for your Tally instance. For example: http://localhost:9000.
- ResultLocation: The cell that the output of results should start from.
Pass Spreadsheet Cells as Inputs to the Query
The procedure below results in a spreadsheet that organizes all the formula inputs in the first column.
- Define cells for the formula inputs. In addition to the connection inputs, add another input to define a criterion for a filter to be used to search Tally data, such as CompanyNumber.
- In another cell, write the formula, referencing the cell values from the user input cells defined above. Single quotes are used to enclose values such as addresses that may contain spaces.
- Change the filter to change the data.
=CDATAQUERY("SELECT * FROM Company WHERE CompanyNumber = '"&B2&"'","Url="&B1&";Provider=Tally",B3)
