Month By month Sales Report

Mikeh had written this in response to

Mike 800-513-5917 x123

------------------------------------- Philip Gass wrote:

##-----------------------------------------------## Newsgroup Access Courtesy

try this it might work for you Here is a link to the report file

no hits yet

Robert

"Philip Gass" wrote:

Robert - I had seen your previous post but was tied up with "stuff" and never had a chance to reply, had a little downtime tonight so figured I'd see if I could take a crack at adding those columns (item price, item cost, and item profit margin - based on how the item is defined in inventory) - if that is what you want on the report, here's what you need to do

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 - h> try this it might work for you

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 reffers 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> Robert - I had seen your previous post but was tied up with "stuff" and never

Maybe the problem is too much column, I don't know what is the limit but I encountered the same thing before, what I did is I remove some unused and undisplayed columns.

Cheers

Paulo

Robert - emailed the report several days ago....from a "@comcast.net" e-mail...check your in box - hopefully wasn't sent to your junk mail.

"Robert" wrote:

It wouldn't work for me until I put brackets around the 0 in the formula for the Profit Margin column as in - Formula = "CASE WHEN Item.Cost 0 THEN ((Item.Price - Item.Cost) / Item.Price) ELSE (0) END"

responding to

I am looking for the same report in a 'daily' format. (365 days) The report posted here is really well though, I contacted the original developer but I was told that they no longer do customized reports. Then I tried to modify report but somehow the RMS cannot read the QRP format after ~750 lines.

Then I tried to convert it to just one month (30 days) but this time RMS messed up filtering.

Anyway,

if you know someone or a company who does RMS report customization please let me know.

Thank you.

paulo wrote:

------------------------------------- K.O.

Working with RMS since 2004, mostly loving it...