Nee Help Customizing a 12 month sales report

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

formatting link

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

Reply to
Robert
Loading thread data ...

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:

formatting link

Reply to
convoluted

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

formatting link
>

Reply to
Robert

formatting link
> >

Reply to
convoluted

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.