Dynamics Community Forum Thread Details

//--- 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 ---//

Begin TitleRow
   Text = "<Report Title>"
   Font = "Arial"
   FontBold = True
   FontSize = 12
   Color = "Black"
End TitleRow

Begin TitleRow
   Text = "Generated On <Report Date>"
   Font = "Arial"
   FontBold = True
   FontSize = 10
   Color = "Black"
End TitleRow

//--- Filters ---//

//--- Columns ---//

Begin Column
   FieldName = "ItemLookupCode"
   DrillDownFieldName = "[Item].ItemLookupCode"
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Item Lookup Code"
   VBDataType = vbString
   Formula = "MAX(Item.ItemLookupCode)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 2055
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "ItemDescription"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Description"
   VBDataType = vbString
   Formula = "MAX(Item.Description)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 2835
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Name"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = "Store.Name"
   Title = "Store Name"
   VBDataType = vbString
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1095
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Store.ID"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = "[Store].ID"
   Title = "Store ID"
   VBDataType = vbLong
   Formula = "MAX(Store.ID)"
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1275
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "ItemBinLocation"
   DrillDownFieldName = "Item.BinLocation"
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Bin Location"
   VBDataType = vbString
   Formula = "MAX(Item.Binlocation)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1035
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Sales"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Sales"
   VBDataType = vbDouble
   Formula = "SUM(TransactionEntry.Quantity)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1710
   GroupMethod = groupmethodSum
   ColFormat = "#.##"
End Column

Begin Column
   FieldName = ""
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = "<TOT>"
   Title = "Sales"
   VBDataType = vbDouble
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1710
   GroupMethod = groupmethodSum
   ColFormat = "#.##"
End Column