I have this 12 month unit sales report that is helpful in my business. It deos great job but would be much more helpful if i could have option to see few more fields
Here is a link to the report fil
I would like to add the option to add following fields /columns
Price Cost Margin
Any help would be appreciated
Robert snipped-for-privacy@usa.com
Edit the report with notepad and join the ITEM table to ViewItemMovementHistory so it reads:
TablesQueried = "FROM ViewItemMovementHistory LEFT JOIN Department ON ViewItemMovementHistory.Department = Department.ID LEFT JOIN Category ON ViewItemMovementHistory.Category = Category.ID LEFT JOIN Supplier ON ViewItemMovementHistory.Supplier = Supplier.ID LEFT JOIN Item ON ViewItemMovementHistory.ItemLookupCode = Item.ItemLookupCode"
Then add Item.Price and Item.Cost to your GROUP BY clause so it reads:
GroupBy = .....ViewItemMovementHistory.subDescription2, ViewItemMovementHistory.subDescription3, ViewItemMovementHistory.Inactive, Item.Price, Item.Cost"
Then add these three columns towards the end of your report:
Begin Column FieldName = "Item.Price" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Price" VBDataType = vbCurrency Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1000 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "Item.Cost" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Cost" VBDataType = vbCurrency Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1000 GroupMethod = groupmethodNone ColFormat = "" End Column
Begin Column FieldName = "ProfitMargin" DrillDownFieldName = "" DrillDownReportName = "" Title = "Margin" VBDataType = vbDouble Formula = "CASE WHEN Item.Cost 0 THEN ((Item.Price - Item.Cost) / Item.Price) ELSE 0 END" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1100 GroupMethod = groupmethodNone ColFormat = "0.00%" End Column
See if that works for you - hope this helps......
"Robert" wrote:
Thanks for the reply,
I have tried to add those in but i get an error once i try to run the report,
It gives me a line number but than i don't see anything wrong with the line and and its content The line it refers to does not even contain the formulas and text contained in error message.
Any chance you can add those and email me the report. snipped-for-privacy@usa.com
Thank you Much Robert
"c> Edit the report with notepad and join the ITEM table to
Have something to add? Share your thoughts — no account required.
Ask the community — no account required