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
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 |
Report Examples:
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
|
SELECT * FROM CostCentres |
6. Collect All Information of Godown:
SELECT * FROM Godown
|
SELECT * FROM Godown |
7. Collect All Information of Stock Group:
SELECT * FROM StockGroup
|
SELECT * FROM StockGroup |
8. Collect All Information of Stock Category:
SELECT * FROM StockCategory
|
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 |
|
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 |
WHERE $$IsDr: $ClosingBalance
why is the above condition used can you elaborate