Best in class reporting solutions from ERPs: Sales order pending-Tally to SQL Server

Sales order analysis

The client required faster Sales pending order report from tally using analytics for its entire Tally data over 15 years.

Filter Parameters- The report is filtered by Item Name, Party Name, and Date. These are report level filter and page level filter is Vouchertypeparent= Sales order

Field-

Order Number

Item Name

Party Name

Sales Order Quantity

Preclosed Quantity

Sales Invoiced Quantity

Pending Quantity

Rate

Sales Invoice Order

Pending Sales quantity:

DAX Code

Preclosed_qty =

This shows the preclosed order quantity by partyname, companyname, orderno and stockitemname. We have used the row level and filter level contexts for the same. The analysis has been done at stockitem batch inventory movement details

/*This shows the total purchase quantity per orderno*/

vAR PARTYNAME=Stockitembatchinventorydetails[partyname]

vAR COMPANYNAME=Stockitembatchinventorydetails[Company_Name]

VAR ORDER_NO=Stockitembatchinventorydetails[OrderNo]

VAR STOCKITEMNAME=Stockitembatchinventorydetails[StockItemName]

VAR Purchase_order=Calculate(sum(Stockitembatchinventorydetails[Billedqty]),FILTER(ALL(Stockitembatchinventorydetails),Stockitembatchinventorydetails[partyname]=PARTYNAME && Stockitembatchinventorydetails[Company_Name]=COMPANYNAME &&Stockitembatchinventorydetails[StockItemName]=STOCKITEMNAME && Stockitembatchinventorydetails[OrderNo]=ORDER_NO && Stockitembatchinventorydetails[VoucherTypeParent]="Purchase Order"))

VAR Purchase_qty=Calculate(sum(Stockitembatchinventorydetails[Billedqty]),FILTER(ALL(Stockitembatchinventorydetails),Stockitembatchinventorydetails[partyname]=PARTYNAME && Stockitembatchinventorydetails[Company_Name]=COMPANYNAME &&Stockitembatchinventorydetails[StockItemName]=STOCKITEMNAME && Stockitembatchinventorydetails[OrderNo]=ORDER_NO && Stockitembatchinventorydetails[VoucherTypeParent]="Purchase"))

VAR PREORDER_CLOSE=Calculate(sum(Stockitembatchinventorydetails[Orderpreclosureqty_num]),FILTER(ALL(Stockitembatchinventorydetails),Stockitembatchinventorydetails[partyname]=PARTYNAME && Stockitembatchinventorydetails[Company_Name]=COMPANYNAME &&Stockitembatchinventorydetails[StockItemName]=STOCKITEMNAME && Stockitembatchinventorydetails[OrderNo]=ORDER_NO))

RETURN IF(PURCHASE_QTY>PURCHASE_ORDER,-PURCHASE_QTY+PURCHASE_ORDER,PREORDER_CLOSE)

net qty for pending sales = Here we calculate the quantity which are pending for sale. This is calculated by difference between billed quantity and preclosed quantity

var a = Stockitembatchinventorydetails[Billedqty_credit]

var b = Stockitembatchinventorydetails[Preclosed_qty]

var c= a-b

return c

Sales order pending =

This shows the sales order pending quantity by partyname, companyname, orderno and stockitemname. We have used the row level and filter level contexts for the same. The analysis has been done at stockitem batch inventory movement details

var orderno=Stockitembatchinventorydetails[Orderno]

var company= Stockitembatchinventorydetails[Company_Name]

var itemname= Stockitembatchinventorydetails[STOCKITEMNAME]

var ledger= Stockitembatchinventorydetails[partyname]

Var salesorder=Calculate(sum(Stockitembatchinventorydetails[Billedqty]),Filter(all(Stockitembatchinventorydetails),orderno=Stockitembatchinventorydetails[Orderno] &&

Stockitembatchinventorydetails[partyname]=ledger && Stockitembatchinventorydetails[VoucherTypeParent]="Sales Order" && itemname=Stockitembatchinventorydetails[StockItemName]))

Var sales_var=Calculate(sum(Stockitembatchinventorydetails[Billedqty]),Filter(all(Stockitembatchinventorydetails),orderno=Stockitembatchinventorydetails[Orderno] &&

Stockitembatchinventorydetails[partyname]=ledger && Stockitembatchinventorydetails[VoucherTypeParent]="Sales" && itemname=Stockitembatchinventorydetails[StockItemName]))

VAR PREORDER_CLOSE=Calculate(sum(Stockitembatchinventorydetails[Orderpreclosureqty_num]),FILTER(ALL(Stockitembatchinventorydetails),Stockitembatchinventorydetails[partyname]=ledger && Stockitembatchinventorydetails[Company_Name]=company &&Stockitembatchinventorydetails[StockItemName]=itemname && Stockitembatchinventorydetails[OrderNo]=orderno && itemname=Stockitembatchinventorydetails[StockItemName]))

return (salesorder-sales_var+PREORDER_CLOSE)*-1

Salesinvoice_order =

vAR PARTYNAME=Stockitembatchinventorydetails[partyname]

vAR COMPANYNAME=Stockitembatchinventorydetails[Company_Name]

VAR ORDER_NO=Stockitembatchinventorydetails[OrderNo]

VAR STOCKITEMNAME=Stockitembatchinventorydetails[StockItemName]

Var Trackingnumber=Stockitembatchinventorydetails[Trackingnumber_voucherinventory]

var Sale_qty=Calculate(sum(Stockitembatchinventorydetails[Billedqty]),FILTER(ALL(Stockitembatchinventorydetails), Stockitembatchinventorydetails[Company_Name]=COMPANYNAME &&Stockitembatchinventorydetails[StockItemName]=STOCKITEMNAME && Stockitembatchinventorydetails[Trackingnumber_voucherinventory]=Trackingnumber && Stockitembatchinventorydetails[VoucherTypeParent]="Sales"))

VAR Sale_rate=Calculate(AVERAGE(Stockitembatchinventorydetails[Rate]),FILTER(ALL(Stockitembatchinventorydetails), Stockitembatchinventorydetails[Company_Name]=COMPANYNAME &&Stockitembatchinventorydetails[StockItemName]=STOCKITEMNAME && Stockitembatchinventorydetails[Trackingnumber_voucherinventory]=Trackingnumber && Stockitembatchinventorydetails[VoucherTypeParent]="Sales"))

Return abs(Sale_qty)

Tally development language code

Collections

;; Sri Ganeshji : Sri Balaji : Sri Pitreshwarji : Sri Durgaji : Sri Venkateshwara

<DefType: Collection>

collection PendingStkGrpSalesOrderExtractSrc

used in Sale Order Stock Group/Category outstanding report

to calculate Opening, Inward, Outward and Closing Orders

[Collection: PendingStkGrpSalesOrderExtractSrc]

Source Collection : PendingStkGrpSalesOrderDetailsSrc, OpeningStkGrpPendingSalesOrderDetailsSrc

Source Var : svOpeningBalance : Quantity : If $IsOpeningColl Then $OpeningBalance Else $$InitValue:"Quantity"

Source Var : svOpeningValue : Amount : If $IsOpeningColl Then $OpeningValue Else $$InitValue:"Amount"

Source Var : svInwardQty : Quantity : If $IsOpeningColl Then $$InitValue:"Quantity" Else $InwardQty

Source Var : svInwardValue : Amount : If $IsOpeningColl Then $$InitValue:"Amount" Else $InwardValue

Source Var : svClosingBalance : Quantity : If $IsOpeningColl Then $$InitValue:"Quantity" Else $ClosingBalance

Source Var : svClosingValue : Amount : If $IsOpeningColl Then $$InitValue:"Amount" Else $ClosingValue

Source Var : svOutwardQty : Quantity : $$NettQty:($$NettQty:##svOpeningBalance:##svInwardQty):$$Negative:##svClosingBalance

Source Var : svOutwardValue : Amount : $$NettAmount:($$NettAmount:##svOpeningValue:##svInwardValue):$$Negative:##svClosingValue

By : Parent : $Parent

Aggr Compute : OpeningBalance : Sum : ##svOpeningBalance

Aggr Compute : OpeningValue : Sum : ##svOpeningValue

Aggr Compute : InwardQty : Sum : ##svInwardQty

Aggr Compute : InwardValue : Sum : ##svInwardValue

Aggr Compute : OutwardQty : Sum : ##svOutwardQty

Aggr Compute : OutwardValue : Sum : ##svOutwardValue

Aggr Compute : ClosingBalance : Sum : ##svClosingBalance

Aggr Compute : ClosingValue : Sum : ##svClosingValue

Search Key : $Parent

The Collections

1. PendingStkGrpSalesOrderDetailsSrc

2. PendingStkGrpSalesOrdersSrc

3. PendingStkGrpSalesPendingOrderDetails

4. PendingStkGrpSalesClearedOrderDetails

used in Sale Order Stock Group/Category outstanding report

to calculate Inward and Closing Orders

[Collection: PendingStkGrpSalesOrderDetailsSrc]

Source Collection : PendingStkGrpSalesOrdersSrc

Source Var : SvInwardQty : Quantity : If $$IsEmpty:$SaleOrderQty then $$InitValue:"Quantity" Else $SaleOrderQty

Source Var : SvInwardValue : Amount : If $$IsEmpty:$SaleOpeningActualValue then $$InitValue:"Amount" Else $SaleOpeningActualValue

Source Var : SvClBal : Quantity : If $$IsEmpty:$ClosingBalance then $$InitValue:"Quantity" Else $ClosingBalance

Source Var : SvClValue : Amount : If $$IsEmpty:$ClosingValue then $$InitValue:"Amount" Else $ClosingValue

By : Parent : $Parent

Compute : IsOpeningColl : No

Aggr Compute : InwardQty : Sum : If @@ForOrderPeriod Then ##SvInwardQty Else $$InitValue:"Quantity"

Aggr Compute : InwardValue : Sum : If @@ForOrderPeriod Then ##SvInwardValue Else $$InitValue:"Amount"

Aggr Compute : ClosingBalance : Sum : ##SvClBal

Aggr Compute : ClosingValue : Sum : ##SvClValue

The Collections

1. PendingStkGrpSalesOrderDetailsSrc

2. OpeningStkGrpPendingSalesOrdersSrc

3. StkGrpSalesPendingOrderDetails

4. StkGrpSalesClearedOrderDetails

used in Sale Order Stock Group/Category outstanding report

to calculate opening Orders

[Collection: OpeningStkGrpPendingSalesOrderDetailsSrc]

Source Collection : OpeningStkGrpPendingSalesOrdersSrc

Source Var : SvOpBal : Quantity : If $$IsEmpty:$ClosingBalance then $$InitValue:"Quantity" Else $ClosingBalance

Source Var : SvOpValue : Amount : If $$IsEmpty:$ClosingValue then $$InitValue:"Amount" Else $ClosingValue

By : Parent : $Parent

Compute : IsOpeningColl : Yes

Aggr Compute : OpeningBalance : Sum : ##SvOpBal

Aggr Compute : OpeningValue : Sum : ##SvOpValue

[Collection: PendingStkGrpSalesOrdersSrc]

Collection : PendingStkGrpSalesPendingOrderDetails, PendingStkGrpSalesClearedOrderDetails

[Collection: OpeningStkGrpPendingSalesOrdersSrc]

Parm Var : SVToDate : Date : @@OpeningOrderToDate

Collection : StkGrpSalesPendingOrderDetails

[Collection: PendingStkGrpSalesPendingOrderDetails]

Source Collection : SalesOrderSourcePnd

Fetch : Date, Parent, ClosingBalance, ClosingValue, SaleOrderQty, SaleOpeningActualValue

[Collection: PendingStkGrpSalesClearedOrderDetails]

Source Collection : SalesOrderSourceCld

Fetch : Date, Parent, ClosingBalance, ClosingValue, SaleOrderQty, SaleOpeningActualValue

[Collection: StkGrpSalesPendingOrderDetails]

Parm Var : SVToDate : Date : @@OpeningOrderToDate

Source Collection : SalesOrderSourceop

Fetch : Date, Parent, ClosingBalance, ClosingValue, SaleOrderQty, SaleOpeningActualValue

[Collection: SalesOrderSourcePnd]

Type : Sales Orders

Cleared : No

Filter : IsItemBelongstoSelectedStkGrp, PositiveClosingBalance

[Collection: SalesOrderSourceCld]

Type : Sales Orders

Cleared : Yes

Filter : IsItemBelongstoSelectedStkGrp, PositiveClosingBalance

;; @Note : For getting, Sales Order Qty Method value, it requires sales order voucher walk. Voucher walk happens only once and

;; it's kept in the memory.

[Collection: SalesOrderSourceOp]

Type : Sales Orders

Parm Var : SVToDate : Date : @@OpeningOrderToDate

Cleared : No

Filter : IsItemBelongstoSelectedStkGrp, PositiveClosingBalance

collection PendingSalesOrderGroupDetailsExtract

used in Sale Order Group outstanding report

to calculate Opening, Inward, Outward and Closing Orders

[Collection: PendingSalesOrderGroupDetailsExtract]

Source Collection : PendingGroupSalesOrderDetailsSrc, OpeningGroupPendingSalesOrderDetailsSrc