I am novice trying to setup a report, shown below is the requirements. Any help will be appreciated.
//--- Report Summary --- //
//--- In prequery1, I wanted to take total of quantity(WAREHOUSE QTY) for each ITEMLOOKUPCODE and store it somewhere for calculation and print in the report ---//
//-- In the TablesQueried, I wanted to find total of sales(SALE QTY) from store for the same ITEMLOOKUPCODE, then calcuate & print the %age of sale Sort the % age in ascending order in the final report--//
PreQuery1 = "SELECT ITEMLOOKUPCODE, (SUM(QUANTITY)) FROM VIEWITEMMOVEMENT WHERE (QUANTITY > 0) AND (STORENAME = 'WareHouse') GROUP BY STORENAME, ITEMLOOKUPCODE"
PreQuery2 = "" TablesQueried = "FROM Item LEFT JOIN TransactionEntry WITH(NOLOCK) ON Item.ID = TransactionEntry.ItemID LEFT JOIN [Transaction] WITH(NOLOCK) ON ((TransactionEntry.TransactionNumber = [Transaction].TransactionNumber) AND (TransactionEntry.StoreID = [Transaction].StoreID)) LEFT JOIN Store WITH(NOLOCK) ON TransactionEntry.StoreID = Store.ID"
GroupBy = "Item.ItemLookupCode, Store.Name, TransactionEntry.Quantity" SortOrder = "Item.ItemLookupCode, Store.Name" SelCriteria = ""
End ReportSummary
//--- Title Rows ---//