Trying to make an Auto Column Report repeating rows over Ledgers and columns over Stock Items. I want to filter out the Stock Items/remove the columns which are for Stock Items which haven't been sold to any party... Code: [#Menu : Gateway of Tally] Add : Item : Report : Display : AES Item Clmnr [Report : AES ItemClmnr] Form : AES ItemClmnr Variable : SVFromDate,SVToDate, DSPRepeatCollection, DoSetAutoColumn, vAESItemName Set : DoSetAutoColumn : Yes Set : DSPRepeatCollection : AES StkItem Coll Set : SVFromDate : $$MonthStart:##SVFromdate Set : SVToDate : $$MonthEnd:##SVFromdate Repeat : vAESItemName [Form : AES ItemClmnr] Part : AES ItemClmnr ;Bottom Part : AES ItemClmnrBTM Button : Change Period,Print Button,Export Button Background : @@SV_UNYELLOW Option : Set Auto Option : $$SetAutoColumns:vAESItemName [Part : AES ItemClmnr] Part : DSP AccTitles,AES ItemClmnrTitle/*,AES ItemClmnrCol*/,AES ItemClmnr Body Vertical : Yes [Part : AES ItemClmnrTitle] Line : AES ItemClmnrTitle [Line : AES ItemClmnrTitle] Field : Simple Field Local : Field : Simple Field : Set As : "Partywise Item sales - from " + $$String:##SVFromDate +" to " + $$String:##SVToDate Local : Field : Simple Field : Style : Normal Bold [Line : AES ItemClmnr Col] Line : AES ItemClmnr Col1, AES ItemClmnr Col2 [Line : AES ItemClmnr Col1] Use : AES ItemClmnr Det Local : Field : Default : Type : string Local : Field : AES ItemClmnrSlNo : Set as : "Sl" Local : Field : AES ItemClmnrparty : Set As : "Party Name" Local : Field : AES ItemClmnrQty : Set As : "";"Qty" Local : Field : AES ItemClmnrRate : Set As : "";"Rate" Local : Field : AES ItemClmnrAmount : Set As : ##vAESItemName;"";"Value" ;Local : Field : Hitecitemssalesamounttax : Set as : "Sales Inc VAT" Local : Field : Default : Style : Small Bold Border : Thin Top [Line : AES ItemClmnr Col2] Use : AES ItemClmnr Det Local : Field : Default : Type : string Local : Field : AES ItemClmnrSlNo : Set as : "" Local : Field : AES ItemClmnrparty : Set As : "" Local : Field : AES ItemClmnrQty : Set As : "Qty" Local : Field : AES ItemClmnrRate : Set As : "" Local : Field : AES ItemClmnrAmount : Set As : "Value" ;Local : Field : Hitecitemssalesamounttax : Set as : "Sales Inc VAT" Local : Field : Default : Style : Small Bold Border : Double Bottom [Part : AES ItemClmnr Body] Line : AES ItemClmnr Col, AES ItemClmnr Det Repeat : AES ItemClmnr Det : AES ItemClmn Coll Scroll : Both Total : AESItemsClmnrQty,AESItemsClmnrAmount Common Border : Yes [Line : AES ItemClmnr Det] Field : AES ItemClmnrSlNo, AES ItemClmnrParty, AES ItemClmnrItem ;Right Field : ;AES ItemClmnrQty, AES ItemClmnr Rate,AES ItemClmnrAmount Repeat : AES ItemClmnrItem;AES ItemClmnrQty ;Option : AlterOnEnter : $$IsVoucher ;Border : Thin top Local : Field :AES ItemClmnrQty : Set as : $$LedSaleQty:$PartyName:##vAESItemName;$$CollectionFieldByKey:$BilledQty:@ItemSearchKey:AESPartyVouchersColl;$BilledQty Local : Field :AES ItemClmnrAmount : Set as : $$LedSaleValue:$PartyName:##vAESItemName;$$CollectionFieldByKey:$BilledQty:@ItemSearchKey:AESPartyVouchersColl;$BilledQty ;PartySearchKey : $$LedSaleQty: [Field : AES ItemClmnrSlNo] Use : Short Name Field Set as : $$Line Style : Normal Width : 3 Align : Centre Border : Thin Right [Field : AES ItemClmnrParty] Set as : $PartyName Quick Search : Yes Border : Thin Right [Field : AES ItemClmnrItem] Use : Name Field Set as : ##vAESItemName ;Set As : ##vAESItemName;$Stockitem ;Style : Small ;Variable : svStockItem Fields : AES ItemClmnrQty, AES ItemClmnrAmount Border : Thin Right [Field : AES ItemClmnrQty] Use : Qty Primary Field Style : Small Format : "NoSymbol" Set As : $BilledQty width : 15 [Field : AES ItemClmnrRate] Use : Rate Price Field ;Set as : $A Style : Small width : 8 Invisible : Yes [Field : AES ItemClmnrAmount] Use : Amount Field Set As : $Amount Style : Small Width : 15 [Part : AES ItemClmnrBTM] Use : AES ItemClmnr Delete : Repeat Delete : Scroll Delete : Total Border : Totals Local : Field : Default : Style : Small Bold [Collection : AES ItemClmn Coll Src] Type : Vouchers : VoucherType Child of : $$VchTypeSales Belongs to : yes Fetch : Date,Inventory Entries,VATAssessableValue [Collection : AES ItemClmn Coll] Source Collection : AES ItemClmn Coll Src By : PartyName : $PartyLedgerName Sort : Default : $PartyName [Collection : AES PartyVouchers Coll Src] Type : Vouchers : Ledger Child Of : ##pvLedName Parm Var : pvLedName : String : #AESItemClmnrParty Filter : IsSalesVCH [Collection : AES Party Vouchers Coll] Source Collection : AES PartyVouchersColl Src Walk : InventoryEntries By : StockItemName : $StockItemName Aggr Compute : BilledQty : Sum : $BilledQty Aggr Compute : Amount : Sum : $Amount Search Key : $StockItemName [Variable : vAESItemName] Repeat : ##DSPRepeatCollection [Collection : AES StkItem Coll] Type : Stock Item Compute : vAESItemName : $Name Filter : test ;;this filter isn't working [System : Formula] test : ($$CollectionFieldByKey:$BilledQty:##vAESItemName:AESStockVouchers) > 0 [Collection : AES StockVouchers Src] Type : Vouchers : StockItem Child Of : ##pvStkItem Parm Var : pvStkItem : String : ##vAESItemName [Collection : AES StockVouchers] Source Collection : AES StockVouchers Src Walk : InventoryEntries By : StockItemName : $StockItemName Aggr Compute : BilledQty : Sum : $BilledQty Filter : zeroQty Search Key : $StockItemName [System : Formula] zeroQty : $BilledQty > 0 [Collection : AES Sales Coll] Type : Vouchers : VoucherType Child of : $$VchTypeSales Belongs to : Yes Fetch : Date,VoucherTypeName,PartyLedgerName,itemname,Amount,eVATAssessableValue
[Report : AES ItemClmnr] Set : DSPRepeatCollection : AES Items Coll [Collection : AES Items Coll] Source Collection : AES ItemClmn Coll Src Walk : InventoryEntries By : StockItemName : $StockItemName Sort : Default : $StockItemName ;;these above changes will help you filter the stock items which are only sold
Sir, I added two buttons in the report, one to filter the ledger and one for the item, but it's not working.. Code: [#Menu : Gateway of Tally] Add : Item : Reports : Menu : AES Reports;Report : Display : AES Item Clmnr [Menu : AES Reports] Item : Item Columnar : Display : AES Item Clmnr Item : Party wise monthly Sales : Display : AES Month Wise Expenses Item : Partywise Item Sales : Display : AES PrtyItem Sales [Report : AES ItemClmnr] Form : AES ItemClmnr Variable : SVFromDate,SVToDate, DSPRepeatCollection, DoSetAutoColumn, vAESItemName Set : DoSetAutoColumn : Yes Set : DSPRepeatCollection : AES Items Coll Set : SVFromDate : $$MonthStart:##SVFromdate Set : SVToDate : $$MonthEnd:##SVFromdate Repeat : vAESItemName Variable : SLedgerName, StkItemName Set : SLedgerName : "" Set : StkItemName : "" [Form : AES ItemClmnr] Part : AES ItemClmnr ;Bottom Part : AES ItemClmnrBTM Space Top : If $$InPrintMode Then 0.50 Else 0 inches Space Bottom : If $$InPrintMode Then 0.50 Else 0 inches Space Left : If $$InPrintMode Then 0.50 Else 0 inches Space Right : If $$InPrintMode Then 0.50 Else 0 inches Button : ExplodeFlag,Change Period,Print Button,Export Button, AESItemClmnrLedFilter, AES ItemClmnrItemFilter Bottom Buttons : BlankButton, InvReports, AcctReports, Report Operations, DYBKConfigure, FilterButton Bottom Toolbar Buttons : BottomToolBarBtn1, BottomToolBarBtn6, BottomToolBarBtn7,BottomToolBarBtn8,BottomToolBarBtn9,BottomToolBarBtn10,BottomToolBarBtn11, BottomToolBarBtn12 Background : @@SV_UNYELLOW Option : Set Auto Option : $$SetAutoColumns:vAESItemName [!Form : Set AutoOption] [Button : AESItemCLmnrLedFilter] Title : "Filter Party" Key : Alt + T Action : Alter : FilterPrty [Collection : FilterPrty] Type : Ledger Child Of : $$GroupSundryDebtors Belongs To : Yes Title : $$LocaleString:"List of Ledgers" Format : $Name, 20 [Report: FilterPrty] ;;Auto Report Title : $$LocaleString:"Batch Items" Form : FilterPrty Local : Form : FilterPrty : Vertical Align : Top Local : Collection: Default : Align : Right Variable : SLedgerName [Form: FilterPrty] No Confirm : Yes Space Top : 0.5 Space Left : 1 Space Right : 1 Full Width : No Full Height : No Vertical Align : Top Part : FilterPrty [Part: FilterPrty] Line : FilterPrty Title Line : FilterPrty PartyName [Line: FilterPrty Title] Fields : Long Prompt Local : Field : Long Prompt : Info : $$LocaleString:"Select Party" Local : Field : Long Prompt : Full Width: Yes Local : Field : Long Prompt : Alignment: Center Space Bottom: 0.5 [Line: FilterPrty PartyName] Fields : Medium Prompt, FilterPrty PartyName Local : Field : Medium Prompt : Info : $$LocaleString:"Name of Party" Space Bottom: 0.5 [Field: FilterPrty PartyName] Use : Name Field Table : FilterPrty, End Of List Show Table : Always Modifies : SLedgerName Variable : SLedgerName [Variable : SLedgerName] Type : String [System : Variable] SLedgerName [Button : AESItemCLmnrItemFilter] Title : "Filter Item" Key : Alt + B Action : Alter : FilterItem [Collection : FilterItem] Type : StockItem Belongs To : Yes Title : $$LocaleString:"List of Stock Items" Format : $Name, 20 [Report: FilterItem] ;;Auto Report Title : $$LocaleString:"Batch Items" Form : FilterItem Local : Form : FilterItem : Vertical Align : Top Local : Collection: Default : Align : Right Variable : StkItemName [Form: FilterItem] No Confirm : Yes Space Top : 0.5 Space Left : 1 Space Right : 1 Full Width : No Full Height : No Vertical Align : Top Part : FilterItem [Part: FilterItem] Line : FilterItem Title Line : FilterItem ItemName [Line: FilterItem Title] Fields : Long Prompt Local : Field : Long Prompt : Info : $$LocaleString:"Select Item" Local : Field : Long Prompt : Full Width : Yes Local : Field : Long Prompt : Alignment : Center Space Bottom: 0.5 [Line: FilterItem ItemName] Fields : Medium Prompt, FilterItem ItemName Local : Field : Medium Prompt : Info : $$LocaleString:"Name of Item" Space Bottom: 0.5 [Field: FilterGrp ItemName] Use : Name Field Table : FilterItem, End Of List Show Table : Always Variable : StkItemName Modifies : StkItemName [Variable : StkItemName] Type : String [System : Variable] StkItemName [Part : AES ItemClmnr] Part : DSP AccTitles,AES ItemClmnrTitle/*,AES ItemClmnrCol*/,AES ItemClmnr Body Vertical : Yes [Part : AES ItemClmnrTitle] Line : AES ItemClmnrTitle [Line : AES ItemClmnrTitle] Field : Simple Field Local : Field : Simple Field : Set As : "Partywise Item sales - from " + $$String:##SVFromDate +" to " + $$String:##SVToDate Local : Field : Simple Field : Style : Normal Bold [Line : AES ItemClmnr Col] Line : AES ItemClmnr Col1, AES ItemClmnr Col2 [Line : AES ItemClmnr Col1] Use : AES ItemClmnr Det Local : Field : Default : Type : string Local : Field : AES ItemClmnrSlNo : Set as : "Sl" Local : Field : AES ItemClmnrparty : Set As : "Party Name" Local : Field : AES ItemClmnrQty : Set As : "";"Qty" Local : Field : AES ItemClmnrRate : Set As : "";"Rate" Local : Field : AES ItemClmnrAmount : Set As : ##vAESItemName;"";"Value" ;Local : Field : Hitecitemssalesamounttax : Set as : "Sales Inc VAT" Local : Field : Default : Style : Small Bold Border : Thin Top [Line : AES ItemClmnr Col2] Use : AES ItemClmnr Det Local : Field : Default : Type : string Local : Field : AES ItemClmnrSlNo : Set as : "" Local : Field : AES ItemClmnrparty : Set As : "" Local : Field : AES ItemClmnrQty : Set As : "Qty" Local : Field : AES ItemClmnrRate : Set As : "" Local : Field : AES ItemClmnrAmount : Set As : "Value" ;Local : Field : Hitecitemssalesamounttax : Set as : "Sales Inc VAT" Local : Field : Default : Style : Small Bold Border : Double Bottom [Part : AES ItemClmnr Body] Line : AES ItemClmnr Col, AES ItemClmnr Det Repeat : AES ItemClmnr Det : AES ItemClmn Coll Scroll : Both ;Total : AESItemsClmnrQty,AESItemsClmnrAmount Common Border : Yes [Line : AES ItemClmnr Det] Field : AES ItemClmnrSlNo, AES ItemClmnrParty, AES ItemClmnrItem ;Right Field : ;AES ItemClmnrQty, AES ItemClmnr Rate,AES ItemClmnrAmount Repeat : AES ItemClmnrItem;AES ItemClmnrQty ;Option : AlterOnEnter : $$IsVoucher ;Border : Thin top Local : Field :AES ItemClmnrQty : Set as : $$LedSaleQty:$PartyName:##vAESItemName;$$CollectionFieldByKey:$BilledQty:@ItemSearchKey:AESPartyVouchersColl;$BilledQty Local : Field :AES ItemClmnrAmount : Set as : $$LedSaleValue:$PartyName:##vAESItemName;$$CollectionFieldByKey:$BilledQty:@ItemSearchKey:AESPartyVouchersColl;$BilledQty ;PartySearchKey : $$LedSaleQty: [Field : AES ItemClmnrSlNo] Use : Short Name Field Set as : $$Line Style : Normal Width : 3 Align : Centre Border : Thin Right [Field : AES ItemClmnrParty] Set as : $PartyName Quick Search : Yes Border : Thin Right [Field : AES ItemClmnrItem] Use : Name Field Set as : ##vAESItemName ;Set As : ##vAESItemName;$Stockitem ;Style : Small ;Variable : svStockItem Fields : AES ItemClmnrQty, AES ItemClmnrAmount Border : Thin Right [Field : AES ItemClmnrQty] Use : Qty Primary Field Style : Small Format : "NoSymbol" Set As : $BilledQty width : 15 [Field : AES ItemClmnrRate] Use : Rate Price Field ;Set as : $A Style : Small width : 8 Invisible : Yes [Field : AES ItemClmnrAmount] Use : Amount Field Set As : $Amount Style : Small Width : 15 [Part : AES ItemClmnrBTM] Use : AES ItemClmnr Delete : Repeat Delete : Scroll Delete : Total Border : Totals Local : Field : Default : Style : Small Bold [Collection : AES ItemClmn Coll Src] Type : Vouchers : VoucherType Child of : $$VchTypeSales Belongs to : yes Fetch : Date,Inventory Entries,VATAssessableValue Filter : FilterParty [Collection : AES ItemClmn Coll] Source Collection : AES ItemClmn Coll Src By : PartyName : $PartyLedgerName Sort : Default : $PartyName [Collection : AES PartyVouchers Coll Src] Type : Vouchers : Ledger Child Of : ##pvLedName Parm Var : pvLedName : String : #AESItemClmnrParty Filter : IsSalesVCH [Collection : AES Party Vouchers Coll] Source Collection : AES PartyVouchersColl Src Walk : InventoryEntries By : StockItemName : $StockItemName Aggr Compute : BilledQty : Sum : $BilledQty Aggr Compute : Amount : Sum : $Amount Search Key : $StockItemName [Variable : vAESItemName] Repeat : ##DSPRepeatCollection [Collection : AES StkItem Coll] Type : Stock Item Compute : vAESItemName : $Name ;Filter : test ;[System : Formula] ;test : ($$CollectionFieldByKey:$BilledQty:##vAESItemName:AESStockVouchers) > 0 ; [Collection : AES StockVouchers Src] Type : Vouchers : StockItem Child Of : ##pvStkItem Parm Var : pvStkItem : String : ##vAESItemName [Collection : AES StockVouchers] Source Collection : AES StockVouchers Src Walk : InventoryEntries By : StockItemName : $StockItemName Aggr Compute : BilledQty : Sum : $BilledQty Filter : zeroQty Search Key : $StockItemName [System : Formula] zeroQty : $BilledQty > 0 [Collection : AES Sales Coll] Type : Vouchers : VoucherType Child of : $$VchTypeSales Belongs to : Yes Fetch : Date,VoucherTypeName,PartyLedgerName,itemname,Amount,eVATAssessableValue [Collection : AES Items Coll] Source Collection : AES ItemClmn Coll Src Walk : InventoryEntries By : StockItemName : $StockItemName Compute : vAESItemName : $StockItemName Sort : Default : $StockItemName Filter : FilterItem [System : Formula] FilterParty : If $$IsEmpty:##SLedgerName Or $$IsEndOfList:##SLedgerName Then True Else $PartyLedgerName=#FilterPrtyPartyName FilterItem : If $$IsEmpty:##StkItemName Or $$IsEndOfList:##StkItemName Then True Else $StockItemName = #FilterItemItemName
