Hi there. Am I missing something in terms of reporting on size / color (colour) from our system?
We are running RMS 2.0 and many of our items are matrix (clothing/footwear). I want to be able to run reports that show me the size / colour of the item - and not just the description.
For example: We have an item: M running shoe - it comes in 2 colours and 8 sizes (16 sku's). We have sold 600 units. I want to know which colour / size is the most popular.
I can't seem to find a report that shows it. The only thing I can think of is to extract this information in 2 separate files from the database and create a join via access (or lookup in excel).
Surely there is a better way - a standard report that I am missing - an easy way to customise a standard report that I can do via notepad - a cheap pre-prepared report that I could purchase and download asap..
You could modify an existing RMS report to add those columns. Open the detailed sales report, configure it how you want it, then memorize to a new report. Files are found in C:\Program Files\Microsoft Retail Management System\Headquarters\Reports. Open the .qrp file in notepad and add this to the bottom then save and run:
Hi Ollie - I think version 1.3 would add color/size (or any matrix attributes) to subdescriptions 1, 2, 3 - version 2.0 changed that and it tracks those attributes in a different table than the item table.
Here's an old report I created some time ago - see if this gives you what you need. Copy it onto a notepad and save as "Custom - Matrix Summary Sales.qrp" or something like that. - Hope this helps....
//--- Report Summary --- //
Begin ReportSummary ReportType = reporttypeSales ReportTitle = "Matrix Summary Sales Report" PageOrientation = pageorientationPortrait OutLineMode = True Groups = 1 GroupDescription = "" DisplayLogo = True LogoFileName = "MyLogo.bmp" ProcedureCall = "" PreQuery1 = "" PreQuery2 = "" TablesQueried FROM TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber LEFT JOIN Item WITH(NOLOCK) ON TransactionEntry.ItemID = Item.ID LEFT JOIN ItemClassComponent on Item.id = ItemClassComponent.ItemID LEFT JOIN ItemClass on ItemClassComponent.ItemClassID = ItemClass.ID LEFT JOIN Department WITH(NOLOCK) ON Item.DepartmentID Department.ID LEFT JOIN Category WITH(NOLOCK) ON Item.CategoryID = Category.ID LEFT JOIN Supplier WITH(NOLOCK) ON Item.SupplierID = Supplier.ID LEFT JOIN Customer WITH(NOLOCK) ON [Transaction].CustomerID Customer.ID
Hi there convoluted ? thanks for sending that report over. For some reason, when I pasted the info into a report and ran it, I encountered several errors relating to the following fields/data: ItemClass.ItemLookupCode, ItemClassComponent.Detail1, ItemClassComponent.Detail2, TransactionEntry.Quantity
I presume these are incorrect references to data in our tables. I wonder where I could search to find the correct field refefrences to update the report so it runs correctly (from the report detail, it looked like it was going to be exactly what we need.)
I tried removing all components of the report relating to those errors. After doing this, resaving and re-running the report, I encountered one other error: ?The report data could not be loaded... Incorrect syntax near the keyword ?SELECT??. I couldn?t find this keyword anywhere in the report..?
If you have any quick suggestions, I would be very grateful. Thank you again for sending that response through. Greatly appreciated.
Ollie
formatting link
"c> Hi Ollie - I think version 1.3 would add color/size (or any matrix
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.