Giving the
monthly summary of the ledger Vouchers report for the sales entries.
Contact us
for Tally spreadsheets automation
Name: Apoorv
Email: support@turbodatatool.com;support@mndatasolutions.com
Phone:+91-8802466356
Marketplace:
https://www.indiamart.com/mnbusinessintelligenceindia/
The end
client requires ledger vouchers view for all the partyledgernames associated
with the sales entries.
That is the
client is requiring a summary of ledger vouchers for each of the rolling
months. 1-aug-2024 to 30-Aug-2024
The client
format is as follows
We first
handle the sales row.
The Tally
api sales register extract is as follows:
<ENVELOPE>
<HEADER>
<VERSION>1</VERSION>
<TALLYREQUEST>Export</TALLYREQUEST>
<TYPE>Data</TYPE>
<ID>Sales
Register</ID>
</HEADER>
<BODY>
<DESC>
<STATICVARIABLES>
<EXPLODEFLAG>Yes</EXPLODEFLAG>
<SVEXPORTFORMAT>$$SysName:XML</SVEXPORTFORMAT>
</STATICVARIABLES>
</DESC>
</BODY>
</ENVELOPE>
The sample
output is as follows:
<ENVELOPE>
<DSPPERIOD>April</DSPPERIOD>
<DSPACCINFO>
<DSPDRAMT>
<DSPDRAMTA></DSPDRAMTA>
</DSPDRAMT>
<DSPCRAMT>
<DSPCRAMTA></DSPCRAMTA>
</DSPCRAMT>
<DSPCLAMT>
<DSPCLAMTA></DSPCLAMTA>
</DSPCLAMT>
</DSPACCINFO>
Limitations:
·
Need
python coding to set the svfromdate and svtodate(to one year)
·
Lack
of flexibility in the Tally api output
·
Single
company only
Implementation
in Power BI through json api:
a.) Unlimited companies
b.) Flexibility in time granularity if
required.
Column
added for sales in DAX(sundry debtors debit amount):
debit_amount_debtors =
var masterid1 =
SELECTCOLUMNS(
FILTER(
Ledger_view,
Ledger_view[IsoptionalLedger]
= "No" &&
Ledger_view[Iscancelled] =
"No" &&
Ledger_view[Isvoid] =
"No" &&
Ledger_view[isactive_vouchertype] = "Yes" &&
Ledger_view[Affects Stock new]
= "No" &&
Ledger_view[LedgerPrimary] =
"Sales Accounts"
),
"MasterId", Ledger_view[MasterId]
)
var masterid2= Ledger_view[MasterId]
var c=
CALCULATE(SUM(Ledger_view[SaleAmounDebit])-SUM(Ledger_view[SaleAmounCredit]),FILTER(ALL(Ledger_view),
Ledger_view[LedgerPrimary] = "Sundry Debtors" &&
Ledger_view[MasterId]=masterid2
/*&& Ledger_view[IsoptionalLedger] =
"No" &&
Ledger_view[Iscancelled] =
"No" &&
Ledger_view[Isvoid] =
"No" &&
Ledger_view[isactive_vouchertype]
= "Yes" &&
Ledger_view[Affects Stock new]
= "No" &&
Ledger_view[LedgerPrimary] =
"Sales Accounts" */
&&
Ledger_view[MasterId] in masterid1))
return c
For the
collections we need to calculate the credit amount for all the partyledgernames
associated with the given sales entry.
Solution
with Tally api:
<ENVELOPE>
<HEADER>
<VERSION>1</VERSION>
<TALLYREQUEST>Export</TALLYREQUEST>
<TYPE>Data</TYPE>
<ID>Ledgervouchers</ID>
</HEADER>
<BODY>
<DESC>
<STATICVARIABLES>
<EXPLODEFLAG>Yes</EXPLODEFLAG>
<SVEXPORTFORMAT>$$SysName:XML</SVEXPORTFORMAT>
<ledgername>AAFIYA LEATHER REX</ledgername>
</STATICVARIABLES>
<TDL>
<TDLMESSAGE>
<REPORT
NAME="Ledgervouchers" ISMODIFY="Yes">
<ADD>Set
: SV From Date:"20240401"</ADD>
<ADD>Set
: SV To Date :"20250430" </ADD>
<ADD>Set
: ExplodeFlag : Yes</ADD>
</REPORT>
</TDLMESSAGE>
</TDL>
</DESC>
</BODY>
</ENVELOPE>
For each
ledger entry one will have to get the debit amount and the credit amounts at
ledger voucher level on a day by day basis.
<ENVELOPE>
<DSPVCHDATE>25-Aug-24</DSPVCHDATE>
<DSPVCHLEDACCOUNT>GC
Works</DSPVCHLEDACCOUNT>
<NAMEFIELD></NAMEFIELD>
<INFOFIELD></INFOFIELD>
<DSPVCHTYPE>Sale</DSPVCHTYPE>
<DSPVCHDRAMT>-1606200.00</DSPVCHDRAMT>
<DSPVCHCRAMT></DSPVCHCRAMT>
<DSPVCHDATE>25-Aug-24</DSPVCHDATE>
<DSPVCHLEDACCOUNT>GC
Works</DSPVCHLEDACCOUNT>
<NAMEFIELD></NAMEFIELD>
<INFOFIELD></INFOFIELD>
<DSPVCHTYPE>Sale</DSPVCHTYPE>
<DSPVCHDRAMT>-2129484.00</DSPVCHDRAMT>
<DSPVCHCRAMT></DSPVCHCRAMT>
</ENVELOPE>
Solution
through JSON api:
Benefits:
a.) Unlimited ledgers
b.) Unlimited companies
c.) Flexible time frame.
DAX CODE is
as follows:
Credit_amount_debtor =
var masterid1 =
DISTINCT(SELECTCOLUMNS(
FILTER(
Ledger_view,
Ledger_view[IsoptionalLedger]
= "No" &&
Ledger_view[Iscancelled] =
"No" &&
Ledger_view[Isvoid] =
"No" &&
Ledger_view[isactive_vouchertype]
= "Yes" &&
Ledger_view[Affects Stock new]
= "No" &&
Ledger_view[LedgerPrimary] =
"Sales Accounts"
),
"MasterId", Ledger_view[MasterId]
))
var masterid2=Ledger_view[MasterId]
var
partyledgername=distinct(SELECTCOLUMNS(FILTER(ledger_view,ledger_view[masterid]
in
(masterid1)),"partyledgername",ledger_view[partyledgername_sales]))
Var
creditor_amount=calculate(sum(Ledger_view[SaleAmounCredit]),filter(all(ledger_view),
Ledger_view[Name] in partyledgername && Ledger_view[MasterId] =
masterid2))
RETURN creditor_amount
No comments:
Post a Comment