HELP! Need to export hourly sales data on POS (NOT RMS)

Posted by Mark Kalmus on November 14, 2007, 4:25 pm

Please log in for more thread options

How can I export hourly sales data across a date range? For instance, I
want to show hourly sales for the month of October so I can graph it and
post it in our break room.

If I can't export hourly data, can I export daily sales?

The built-in reports don't address this data format.

Posted by Craig on November 14, 2007, 4:38 pm

Please log in for more thread options

: 7bit

Mark, This should work for you. Keep in mind it takes up to 5-10 minutes to
load the sales data.
Craig

> How can I export hourly sales data across a date range? For instance, I

> want to show hourly sales for the month of October so I can graph it and

> post it in our break room.

> If I can't export hourly data, can I export daily sales?

> The built-in reports don't address this data format.

------=_NextPart_000_008E_01C826DC.CBC512D0
Content-Type: application/octet-stream;
        name="Custom - Detailed Sales Report by Hour.qrp"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
        filename="Custom - Detailed Sales Report by Hour.qrp"

//--- Report Summary --- //

Begin ReportSummary
ReportType =3D reporttypeSales
ReportTitle =3D "Detailed Sales Report"
PageOrientation =3D pageorientationPortrait
OutLineMode =3D True
Groups =3D 1
GroupDescription =3D ""
DisplayLogo =3D True
LogoFileName =3D "MyLogo.bmp"
ProcedureCall =3D ""
TablesQueried =3D <BEGIN>
FROM TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON =
TransactionEntry.TransactionNumber =3D [Transaction].TransactionNumber=20
INNER JOIN Batch WITH(NOLOCK) ON [Transaction].BatchNumber =3D =
Batch.BatchNumber=20
LEFT JOIN Item WITH(NOLOCK) ON TransactionEntry.ItemID =3D =
Item.ID=20
LEFT JOIN Department WITH(NOLOCK) ON Item.DepartmentID =3D =
Department.ID=20
LEFT JOIN Category WITH(NOLOCK) ON Item.CategoryID =3D =
Category.ID=20
LEFT JOIN Supplier WITH(NOLOCK) ON Item.SupplierID =3D =
Supplier.ID=20
LEFT JOIN ReasonCode AS ReasonCodeDiscount WITH(NOLOCK) ON =
TransactionEntry.DiscountReasonCodeID =3D ReasonCodeDiscount.ID=20
LEFT JOIN ReasonCode AS ReasonCodeTaxChange WITH(NOLOCK) ON =
TransactionEntry.TaxChangeReasonCodeID =3D ReasonCodeTaxChange.ID
LEFT JOIN ReasonCode AS ReasonCodeReturn WITH(NOLOCK) ON =
TransactionEntry.ReturnReasonCodeID =3D ReasonCodeReturn.ID
LEFT JOIN        Register WITH(NOLOCK) ON Batch.RegisterID =3D =
Register.ID
LEFT JOIN Customer WITH(NOLOCK) ON [Transaction].CustomerID =
=3D Customer.ID
LEFT JOIN Cashier WITH(NOLOCK) ON [Transaction].CashierID =3D =
Cashier.ID
                        LEFT JOIN QuantityDiscount WITH(NOLOCK) ON =
TransactionEntry.QuantityDiscountID =3D QuantityDiscount.ID

<END>

SelCriteria =3D ""
GroupBy =3D ""
SortOrder =3D ""
End ReportSummary

//--- Title Rows ---//

Begin TitleRow
Text =3D "<Store Name>"
Font =3D "Arial"
FontBold =3D True
FontSize =3D 16
Color =3D "Blue"
End TitleRow

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

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

//--- Filters ---//

Begin Filter
FieldName =3D "TransactionTime"
FilterOp =3D reportfilteropBetween
FilterLoLim =3D "<Today>"
FilterHilim =3D "<Today>"
End Filter

//--- Columns ---//

Begin Column
FieldName =3D "TimeSold"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
StoreIDFieldName =3D ""
Title =3D "Time Sold"
VBDataType =3D vbString
Formula =3D "cast(DatePart(Hh,[Transaction].Time) as nvarchar)+ =
':00'"
ColHidden =3D False
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 900
GroupMethod =3D groupmethodNone
ColFormat =3D ""
End Column

Begin Column
FieldName =3D "Department.Name"
DrillDownFieldName =3D "Department.Name"
DrillDownReportName =3D ""
Title =3D "Department"
VBDataType =3D vbString
Formula =3D ""
ColHidden =3D False
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 2205
GroupMethod =3D groupmethodNone
ColFormat =3D ""
ColAlignment =3D flexAlignLeftCenter
End Column

Begin Column
FieldName =3D "Category.Name"
DrillDownFieldName =3D "Category.Name"
DrillDownReportName =3D ""
Title =3D "Category"
VBDataType =3D vbString
Formula =3D ""
ColHidden =3D False
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 1515
GroupMethod =3D groupmethodNone
ColFormat =3D ""
ColAlignment =3D flexAlignLeftCenter
End Column

Begin Column
FieldName =3D "Supplier.SupplierName"
DrillDownFieldName =3D "Supplier.SupplierName"
DrillDownReportName =3D ""
Title =3D "Supplier"
VBDataType =3D vbString
Formula =3D ""
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 825
GroupMethod =3D groupmethodNone
ColFormat =3D ""
ColAlignment =3D flexAlignLeftCenter
End Column

Begin Column
FieldName =3D "Item.ItemLookupCode"
DrillDownFieldName =3D "Item.ItemLookupCode"
DrillDownReportName =3D ""
Title =3D "Item"
VBDataType =3D vbString
Formula =3D ""
ColHidden =3D False
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 1380
GroupMethod =3D groupmethodNone
ColFormat =3D ""
ColAlignment =3D flexAlignLeftCenter
End Column

Begin Column
FieldName =3D "Item.BinLocation"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "Bin Location"
VBDataType =3D vbString
Formula =3D ""
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 1140
GroupMethod =3D groupmethodNone
ColFormat =3D ""
ColAlignment =3D flexAlignLeftCenter
End Column

Begin Column
FieldName =3D "Item.Description"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "Description"
VBDataType =3D vbString
Formula =3D ""
ColHidden =3D False
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 2115
GroupMethod =3D groupmethodNone
ColFormat =3D ""
ColAlignment =3D flexAlignLeftCenter
End Column

Begin Column
FieldName =3D "Item.WebItem"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "Web Item"
VBDataType =3D vbBoolean
Formula =3D ""
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 1057
GroupMethod =3D groupmethodNone
ColFormat =3D ""
ColAlignment =3D flexAlignCenterCenter
End Column

Begin Column
FieldName =3D "Item.Quantity"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "On Hand"
VBDataType =3D vbDouble
Formula =3D ""
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 810
GroupMethod =3D groupmethodNone
ColFormat =3D ""
End Column

Begin Column
FieldName =3D "Item.Price"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "Price"
VBDataType =3D vbCurrency
Formula =3D ""
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 555
GroupMethod =3D groupmethodNone
ColFormat =3D ""
End Column

Begin Column
FieldName =3D "Item.PriceA"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "Price A"
VBDataType =3D vbCurrency
Formula =3D ""
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 720
GroupMethod =3D groupmethodNone
ColFormat =3D ""
End Column

Begin Column
FieldName =3D "Item.PriceB"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "Price B"
VBDataType =3D vbCurrency
Formula =3D ""
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 660
GroupMethod =3D groupmethodNone
ColFormat =3D ""
End Column

Begin Column
FieldName =3D "Item.PriceC"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "Price C"
VBDataType =3D vbCurrency
Formula =3D ""
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 720
GroupMethod =3D groupmethodNone
ColFormat =3D ""
End Column

Begin Column
FieldName =3D "Item.SalePrice"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "Sale Price"
VBDataType =3D vbCurrency
Formula =3D ""
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 945
GroupMethod =3D groupmethodNone
ColFormat =3D ""
End Column

Begin Column
FieldName =3D "Item.MSRP"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "MSRP"
VBDataType =3D vbCurrency
Formula =3D ""
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 945
GroupMethod =3D groupmethodNone
ColFormat =3D ""
End Column

Begin Column
FieldName =3D "Item.SaleStartDate"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "Sale Starts"
VBDataType =3D vbDate
Formula =3D ""
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 1020
GroupMethod =3D groupmethodNone
ColFormat =3D ""
End Column

Begin Column
FieldName =3D "Item.SaleEndDate"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "Sale Ends"
VBDataType =3D vbDate
Formula =3D ""
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 930
GroupMethod =3D groupmethodNone
ColFormat =3D ""
End Column

Begin Column
FieldName =3D "Item.LastSold"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "Last Sold"
VBDataType =3D vbDate
Formula =3D ""
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 900
GroupMethod =3D groupmethodNone
ColFormat =3D ""
End Column

Begin Column
FieldName =3D "TransactionEntry.Quantity"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "Qty Sold"
VBDataType =3D vbDouble
Formula =3D ""
ColHidden =3D False
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 810
GroupMethod =3D groupmethodSum
ColFormat =3D "#.##"
End Column

Begin Column
FieldName =3D "TransactionEntry.Price"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "Sold Price"
VBDataType =3D vbCurrency
Formula =3D ""
ColHidden =3D False
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 970
GroupMethod =3D groupmethodNone
ColFormat =3D ""
End Column

Begin Column
FieldName =3D "Total"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "Total Sales"
VBDataType =3D vbCurrency
Formula =3D "TransactionEntry.Price * TransactionEntry.Quantity"
ColHidden =3D False
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 1275
GroupMethod =3D groupmethodSum
ColFormat =3D ""
End Column

Begin Column
FieldName =3D "[Transaction].TransactionNumber"
DrillDownFieldName =3D "[Transaction].TransactionNumber"
DrillDownReportName =3D ""
Title =3D "Transaction"
VBDataType =3D vbLong
Formula =3D ""
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 1245
GroupMethod =3D groupmethodNone
ColFormat =3D ""
End Column

Begin Column
FieldName =3D "TransactionTime"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
StoreIDFieldName =3D ""
Title =3D "Date Sold"
VBDataType =3D vbDate
Formula =3D "CONVERT(datetime, CONVERT(nvarchar, [Transaction].Time, =
101), 101)"
ColHidden =3D False
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 900
GroupMethod =3D groupmethodNone
ColFormat =3D ""
End Column

Begin Column
FieldName =3D "TransactionEntry.Cost"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "Cost"
VBDataType =3D vbCurrency
Formula =3D "TransactionEntry.Quantity * TransactionEntry.Cost"
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 900
GroupMethod =3D groupmethodSum
ColFormat =3D ""
End Column

Begin Column
FieldName =3D "Profit"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "Profit"
VBDataType =3D vbCurrency
Formula =3D "(TransactionEntry.Price - TransactionEntry.Cost) * =
TransactionEntry.Quantity"
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 900
GroupMethod =3D groupmethodSum
ColFormat =3D ""
End Column

Begin Column
FieldName =3D "ProfitMargin"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "Profit Margin"
VBDataType =3D vbDouble
Formula =3D "CASE WHEN TransactionEntry.Price <> 0 THEN CASE WHEN =
TransactionEntry.Quantity > 0 THEN (TransactionEntry.Price - =
TransactionEntry.Cost) / TransactionEntry.Price ELSE CASE WHEN =
TransactionEntry.Quantity < 0 THEN (TransactionEntry.Price - =
TransactionEntry.Cost) / (TransactionEntry.Price*-1) ELSE 0 END END ELSE =
0 END"
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 900
GroupMethod =3D groupmethodAverage
ColFormat =3D "0.00%"
End Column

Begin Column
FieldName =3D "ReasonCodeDiscount.Description"
DrillDownFieldName =3D "Reasoncode.Description"
DrillDownReportName =3D ""
Title =3D "Discount Reason Code"
VBDataType =3D vbString
Formula =3D ""
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 2280
GroupMethod =3D groupmethodNone
ColFormat =3D ""
ColAlignment =3D flexAlignLeftCenter
End Column

Begin Column
FieldName =3D "ReasonCodeTaxChange.Description"
DrillDownFieldName =3D "Reasoncode.Description"
DrillDownReportName =3D ""
Title =3D "Tax Change Reason Code"
VBDataType =3D vbString
Formula =3D ""
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 2280
GroupMethod =3D groupmethodNone
ColFormat =3D ""
ColAlignment =3D flexAlignLeftCenter
End Column

Begin Column
FieldName =3D "ReasonCodeReturn.Description"
DrillDownFieldName =3D "Reasoncode.Description"
DrillDownReportName =3D ""
Title =3D "Return Reason Code"
VBDataType =3D vbString
Formula =3D ""
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 2280
GroupMethod =3D groupmethodNone
ColFormat =3D ""
ColAlignment =3D flexAlignLeftCenter
End Column

Begin Column
FieldName =3D "PriceSource"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "Price Source"
VBDataType =3D vbString
Formula =3D "CASE TransactionEntry.PriceSource WHEN 1 THEN 'Regular =
Price' WHEN 2 THEN (CASE WHEN TransactionEntry.QuantityDiscountID <> 0 =
THEN QuantityDiscount.Description ELSE 'Quantity Discount' END) WHEN 3 =
THEN 'Buydown Discount' WHEN 4 THEN 'Price Level Disc.' WHEN 5 THEN =
'Sale Price' WHEN 6 THEN 'Disc. from Reg. Price' WHEN 7 THEN 'Disc. from =
Cur. Price' WHEN 8 THEN 'Cost Markup Disc.' WHEN 9 THEN 'Profit Margin =
Disc.' WHEN 10 THEN 'Cashier Set' WHEN 11 THEN 'Component' WHEN 12 THEN =
'Price Level A Disc.' WHEN 13 THEN 'Price Level B Disc.' WHEN 14 THEN =
'Price Level C Disc.' WHEN 15 THEN 'Disc. from Reg. Price' WHEN 16 THEN =
'Disc. from Cur. Price' WHEN 17 THEN 'Cost Markup Disc.' WHEN 18 THEN =
'Profit Margin Disc.' ELSE 'Unknown' END"
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 2280
GroupMethod =3D groupmethodNone
ColFormat =3D ""
ColAlignment =3D flexAlignLeftCenter
End Column

Begin Column
FieldName =3D "Register.Number"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "Register"
VBDataType =3D vbLong
Formula =3D ""
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 1245
GroupMethod =3D groupmethodNone
ColFormat =3D ""
End Column

Begin Column
FieldName =3D "CustomerName"
DrillDownFieldName =3D ""
DrillDownReportName =3D ""
Title =3D "Customer"
VBDataType =3D vbString
Formula =3D "Customer.LastName + ', ' + Customer.FirstName"
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 1350
GroupMethod =3D groupmethodNone
ColFormat =3D ""
ColAlignment =3D flexAlignLeftCenter
End Column

Begin Column
FieldName =3D "Customer.AccountNumber"
DrillDownFieldName =3D "Customer.AccountNumber"
DrillDownReportName =3D ""
Title =3D "Account #"
VBDataType =3D vbString
Formula =3D ""
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 1035
GroupMethod =3D groupmethodNone
ColFormat =3D ""
ColAlignment =3D flexAlignLeftCenter
End Column

Begin Column
FieldName =3D "Cashier.Name"
DrillDownFieldName =3D "Cashier.Name"
DrillDownReportName =3D ""
Title =3D "Cashier"
VBDataType =3D vbString
Formula =3D ""
ColHidden =3D True
ColNotDisplayable =3D False
FilterDisabled =3D False
ColWidth =3D 1350
GroupMethod =3D groupmethodNone
ColFormat =3D ""
ColAlignment =3D flexAlignLeftCenter
End Column
------=

Posted by Mark Kalmus on November 14, 2007, 6:24 pm

Please log in for more thread options

Craig,

You advised placing the attached .qrp file in my Reports folder under the
Microsoft Point of Sale directory; however, there is no Reports folder under
that directory. How do I import this custom report?

All my default reports are .xml files.

Remember, this is Point of Sale, not RMS or RMS POS.

Mark

> Mark, This should work for you. Keep in mind it takes up to 5-10 minutes

> to

> load the sales data.

> Craig

>> How can I export hourly sales data across a date range? For instance, I

>> want to show hourly sales for the month of October so I can graph it and

>> post it in our break room.

>> If I can't export hourly data, can I export daily sales?

>> The built-in reports don't address this data format.

Posted by STEP2000 on November 14, 2007, 8:33 pm

Please log in for more thread options

> Craig,

> You advised placing the attached .qrp file in my Reports folder under

> the Microsoft Point of Sale directory; however, there is no Reports

> folder under that directory. How do I import this custom report?

> All my default reports are .xml files.

> Remember, this is Point of Sale, not RMS or RMS POS.

> Mark

You will need to provide way more info then you have!
Product Name, Version # is the min.

The other would be what operating system, and some basic structure of
directories will be required.

You may need to contact the manufacturer of the POS software for real
help....remember you get what you pay for!

www.genesis-pos.com
Perishable Scale Software for RMS

>> Mark, This should work for you. Keep in mind it takes up to 5-10

>> minutes to

>> load the sales data.

>> Craig

>>> How can I export hourly sales data across a date range? For

>>> instance, I want to show hourly sales for the month of October so I

>>> can graph it and post it in our break room.

>>>

>>> If I can't export hourly data, can I export daily sales?

>>>

>>> The built-in reports don't address this data format.

>>>

Posted by Mark Kalmus on November 14, 2007, 10:03 pm

Please log in for more thread options

Okay.

I am using MICROSOFT POINT OF SALE v.1.0 Binaries Version 2.0.1112.0

I am NOT using MICROSOFT RETAIL MANAGEMENT SYSTEM.

I want to create or import a report I can export to Excel. This report
should itemize sales by hour in a given range (for example, one day, one
week or one month).

I was provided by respondent Craig a .QRP file containing a query for
producing a report such as I require; however, instructions for using the
.QRP file directed me to paste the file into the Reports folder withint the
Point of Sale folder on the same hard drive on which POINT OF SALE is
installed. MICROSOFT POINT OF SALE does NOT have a folder titled Reports
(installed folders are: Addins, Backups, Content, Database, eLearning, Logs,
OPOS, Refresh, and Temp --- no subfolders titled Reports either), and the
default reports included with the product are .XML files, not .QRP files.

When we run our X Report, sales by hour is included at the base of the
report. We can view via the POS application sales by hour for any date
simply by changing the date parameter to the given date we wish to view.
What I require is a method to export such hourly sales data in a format I
can import into Excel for producing at-a-glance graphs.

If POINT OF SALE can display this info when you enter the appropriate date,
how do I export this data?

> You will need to provide way more info then you have!

> Product Name, Version # is the min.

> The other would be what operating system, and some basic structure of

> directories will be required.

> You may need to contact the manufacturer of the POS software for real

> help....remember you get what you pay for!

> www.genesis-pos.com

> Perishable Scale Software for RMS