Best of Tally Prime TDL Reports | Way to use SQL for Tally reports | Use Narration to get Best Tally Reports

Yes! you are in right place if you use Tally software. In this post we will understand, how to collect best Tally reports and export it into tally with the help of SQL query. We can use SQL query in Tally Narration and collect reports as per our requirement. It is very simple process for all type report collection from tally.

We can retrieve information from Tally ERP9 and Tally Prime by using below SQL method in Tally narration:

1. Press Ctrl+N to go to Tally Calculator

2. In Tally Calculator write SELECT and SQL command to get information.

The syntax to write SQL Query in Tally is:

SELECT [<Method Name/s> / <*>] FROM <Collection/Table> WHERE <Condition> ORDER BY <Method Name/s>

For Example collect name from Ledgers copy below SQL and paste in Narration then press enter to get ledger name details:

SELECT $Name FROM Ledger

or

SELECT $Name FROM ODBCTables

    Method names always will start with $ (dollar) sign – as in above example we used $Name

Report Examples:

We can collect below type reports with SQL query by type below texts in Narration field of Tally and press enter:

1. Collect All Information of Ledgers:

SELECT * FROM Ledger

2. Collect All Information of Stock Items:

SELECT * FROM StockItem

3. Collect All Information of Units:

SELECT * FROM Unit

4. Collect All Information of Voucher Types:

SELECT * FROM VoucherType

5. Collect All Information of Cost Centre:

SELECT * FROM CostCentres

6. Collect All Information of Godown:

SELECT * FROM Godown

7. Collect All Information of Stock Group:

SELECT * FROM StockGroup

8. Collect All Information of Stock Category:

SELECT * FROM StockCategory


NOW WE KNOW ALL METHOD NAMES OF LEDGERS, ITEMS ETC. SO CAN TRY BELOW COLLECTIONS ALSO:

9. To retrieve more than one method from a collection, separate the method names with a comma. For example, to retrieve $Name and $ClosingBalance of Ledger collection

SELECT $Name, $ClosingBalance FROM Ledger


10. Also we can use WHERE method to retrieve Debit Credit or other reports

SELECT $Name FROM Ledger WHERE $$IsDr: $ClosingBalance

11. to get Credit closing balance can use below method with NOT $$IsDr

SELECT $Name FROM Ledger WHERE NOT $$IsDr: $ClosingBalance


12. Get closing balance between two amounts as between 10000 and 11000

SELECT $Name FROM Ledger WHERE $ClosingBalance BETWEEN 10000 AND 11000

13. Collect ledger names that contain names like Sales (As Sales Account, GST Sales etc.)

SELECT $Name FROM ledger WHERE $name LIKE ‘%sales%’


14. Also we can collect reports with sort in order by order keyword : (in below example DESC for descending order) 

SELECT $Name, $ClosingBalance FROM Ledger WHERE $$IsDr: $ClosingBalance ORDER BY $ClosingBalance DESC

Also we can collect information with the help of TDL Collections: (click the below link)

Leave a Comment