Tender Summary report combining all registers and tender types

Posted by David Moffatt on May 7, 2008, 7:08 pm

Please log in for more thread options

Hi,

I know the Z report breaks down the tender type totals. Is there any report
template to combine all registers into one total for each tender type
(Visa/MC, debit card, Discover card, American Express, check, cash, etc)
thanks

Posted by donphillip on May 7, 2008, 10:59 pm

Please log in for more thread options

: 7bit

David,

The attached report will do if you have HQ.

Hope this helps

Regards,
Don

------=_NextPart_000_0118_01C8B095.F2EA34B0
Content-Type: application/octet-stream;
    name="Custom - Tender Summary Cashier - HQ.qrp"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
    filename="Custom - Tender Summary Cashier - HQ.qrp"

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

Begin ReportSummary
   ReportType =3D reporttypeSales
   ReportTitle =3D "Tender Summary - HQ"
   PageOrientation =3D pageorientationPortrait
   WordWrap =3D True
   ShowDateTimePicker =3D False
   OutLineMode =3D True
   Groups =3D 1
   GroupDescription =3D ""
   DisplayLogo =3D True
   LogoFileName =3D "MyLogo.bmp"
   ProcedureCall =3D ""
   PreQuery1 =3D "IF EXISTS (SELECT TABLE_NAME FROM =
INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME =3D 'VIEWTenders') DROP VIEW =
VIEWTenders"
   PreQuery2 =3D <BEGIN>

CREATE VIEW VIEWTenders AS

SELECT Store.ID as StoreID,
Store.Name as StoreName,
Register.Description AS RegDesc,=20
Register.Number AS Register,=20
Cashier.Name,=20
Cashier.Number,=20
TenderEntry.Description,=20
[Transaction].CustomerID,=20
[Transaction].Time as Date,=20
CONVERT(nvarchar, [Transaction].Time, 8) AS Time,=20
[Transaction].TransactionNumber,=20
TenderEntry.Amount,
'Sale' as TrxType=20

FROM TenderEntry=20
LEFT JOIN Store ON TenderEntry.StoreID =3D Store.ID
LEFT JOIN [Transaction] ON TenderEntry.TransactionNumber =3D =
[Transaction].TransactionNumber=20
  AND Store.ID =3D [Transaction].StoreID
LEFT JOIN Batch ON [Transaction].BatchNumber =3D Batch.BatchNumber=20
  AND Store.ID =3D Batch.StoreID
LEFT JOIN Register ON Batch.RegisterID =3D Register.ID=20
  AND Store.ID =3D Register.StoreID
LEFT JOIN Cashier ON [Transaction].CashierID =3D Cashier.ID
  AND Store.ID =3D Cashier.StoreID

WHERE Register.Number IS not null

UNION

SELECT Store.ID as StoreID,
Store.Name as StoreName,
Register.Description AS RegDesc,=20
Register.Number AS Register, =20
Cashier.Name,=20
Cashier.Number,=20
TenderEntry.Description,=20
Payment.CustomerID,
Payment.Time as Date,=20
CONVERT(nvarchar, Payment.Time, 8) AS Time,=20
Payment.ID as TransactionNumber,=20
TendErentry.Amount,
'Payment' as TrxType=20

FROM TenderEntry=20
LEFT JOIN Store ON TenderEntry.StoreID =3D Store.ID
LEFT JOIN Payment ON Payment.ID =3D Tenderentry.PaymentID
  AND Store.ID =3D Payment.StoreID
LEFT JOIN Batch ON Payment.BatchNumber =3D Batch.BatchNumber=20
  AND Store.ID =3D Batch.StoreID
LEFT JOIN Register ON Batch.RegisterID =3D Register.ID=20
  AND Store.ID =3D Register.StoreID
LEFT JOIN Cashier ON Payment.CashierID =3D Cashier.ID
  AND Store.ID =3D Cashier.StoreID

WHERE Register.Number IS NOT NULL

UNION

SELECT Store.ID as StoreID,
Store.Name as StoreName,
Register.Description AS RegDesc,
Register.Number AS Register, =20
Cashier.Name,
Cashier.Number,
TenderEntry.Description,
0 as CustomerID,
DropPayout.Time as Date,
CONVERT(nvarchar, DropPayout.Time, 8) AS Time,
DropPayout.ID as TransactionNumber,
TenderEntry.Amount,=20
'Drop/Payout' as TrxType =20

FROM TenderEntry=20
LEFT JOIN Store ON TenderEntry.StoreID =3D Store.ID
LEFT JOIN DropPayout ON DropPayout.ID =3D TenderEntry.DropPayoutID
  AND Store.ID =3D DropPayout.StoreID
LEFT JOIN Batch ON DropPayout.BatchNumber =3D Batch.BatchNumber=20
  AND Store.ID =3D Batch.StoreID
LEFT JOIN Register ON Batch.RegisterID =3D Register.ID=20
  AND Store.ID =3D Register.StoreID
LEFT JOIN Cashier ON DropPayout.CashierID =3D Cashier.ID
  AND Store.ID =3D Cashier.StoreID

WHERE Register.Number IS NOT NULL

UNION

SELECT Store.ID as StoreID,
Store.Name as StoreName,
Register.Description AS RegDesc,
Register.Number AS Register, =20
Cashier.Name,
Cashier.Number,
TenderEntry.Description,
[Order].CustomerID,
OrderHistory.Date,
CONVERT(nvarchar, OrderHistory.Date, 8) AS Time,
OrderHistory.OrderID as TransactionNumber,
TenderEntry.Amount,=20
CASE WHEN Type =3D 2 THEN 'Work Order' ELSE CASE WHEN Type =3D 5 THEN =
'Layaway' ELSE 'Back Order' END END as TrxType=20

FROM TenderEntry=20
LEFT JOIN Store ON TenderEntry.StoreID =3D Store.ID
LEFT JOIN OrderHistory ON OrderHistory.ID =3D =
TenderEntry.OrderHistoryID
  AND Store.ID =3D OrderHistory.StoreID
LEFT JOIN [Order] ON OrderHistory.OrderID =3D [Order].ID
  AND Store.ID =3D [Order].StoreID
LEFT JOIN Batch ON OrderHistory.BatchNumber =3D Batch.BatchNumber=20
  AND Store.ID =3D Batch.StoreID
LEFT JOIN Register ON Batch.RegisterID =3D Register.ID=20
  AND Store.ID =3D Register.StoreID
LEFT JOIN Cashier ON OrderHistory.CashierID =3D Cashier.ID
  AND Store.ID =3D Cashier.StoreID

WHERE OrderHistory.TransactionNumber =3D 0
<END>
   TablesQueried =3D "FROM VIEWTenders LEFT JOIN Customer ON =
ViewTenders.CustomerID =3D Customer.ID"
   SelCriteria =3D ""
   GroupBy =3D ""
   SortOrder =3D "VIEWTenders.Amount, VIEWTenders.Date, =
VIEWTenders.Description,VIEWTenders.RegDesc"
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 "ViewTenders.Date"
   FilterOp =3D reportfilteropBetween
   FilterLoLim =3D "<Today>"
   FilterHilim =3D "<Today>"
End Filter

//--- Columns ---//

Begin Column
   FieldName =3D "VIEWTenders.StoreName"
   DrillDownFieldName =3D ""
   DrillDownReportName =3D ""
   StoreIDFieldName =3D ""
   Title =3D "Store Name"
   VBDataType =3D vbString
   Formula =3D ""
   ColHidden =3D False
   ColNotDisplayable =3D False
   FilterDisabled =3D False
   ColWidth =3D 1200
   GroupMethod =3D groupmethodNone
   ColFormat =3D ""
End Column

Begin Column
   FieldName =3D "VIEWTenders.Description"
   DrillDownFieldName =3D ""
   DrillDownReportName =3D ""
   StoreIDFieldName =3D ""
   Title =3D "Tender"
   VBDataType =3D vbString
   Formula =3D ""
   ColHidden =3D False
   ColNotDisplayable =3D False
   FilterDisabled =3D False
   ColWidth =3D 1395
   GroupMethod =3D groupmethodNone
   ColFormat =3D ""
End Column

Begin Column
   FieldName =3D "VIEWTenders.TrxType"
   DrillDownFieldName =3D ""
   DrillDownReportName =3D ""
   StoreIDFieldName =3D ""
   Title =3D "Trx Type"
   VBDataType =3D vbString
   Formula =3D ""
   ColHidden =3D False
   ColNotDisplayable =3D False
   FilterDisabled =3D False
   ColWidth =3D 1290
   GroupMethod =3D groupmethodNone
   ColFormat =3D ""
End Column

Begin Column
   FieldName =3D "VIEWTenders.Register"
   DrillDownFieldName =3D ""
   DrillDownReportName =3D ""
   StoreIDFieldName =3D ""
   Title =3D "Register #"
   VBDataType =3D vbString
   Formula =3D ""
   ColHidden =3D False
   ColNotDisplayable =3D False
   FilterDisabled =3D False
   ColWidth =3D 1020
   GroupMethod =3D groupmethodNone
   ColFormat =3D ""
End Column

Begin Column
   FieldName =3D "VIEWTenders.Date"
   DrillDownFieldName =3D ""
   DrillDownReportName =3D ""
   StoreIDFieldName =3D ""
   Title =3D "Date"
   VBDataType =3D vbDate
   Formula =3D ""
   ColHidden =3D False
   ColNotDisplayable =3D False
   FilterDisabled =3D False
   ColWidth =3D 990
   GroupMethod =3D groupmethodNone
   ColFormat =3D ""
End Column

Begin Column
   FieldName =3D "VIEWTenders.Amount"
   DrillDownFieldName =3D ""
   DrillDownReportName =3D ""
   StoreIDFieldName =3D ""
   Title =3D "Amount"
   VBDataType =3D vbCurrency
   Formula =3D ""
   ColHidden =3D False
   ColNotDisplayable =3D False
   FilterDisabled =3D False
   ColWidth =3D 1110
   GroupMethod =3D groupmethodSum
   ColFormat =3D ""
End Column

Begin Column
   FieldName =3D "VIEWTenders.TransactionNumber"
   DrillDownFieldName =3D "[Transaction].TransactionNumber"
   DrillDownReportName =3D ""
   StoreIDFieldName =3D ""
   Title =3D "Trx #"
   VBDataType =3D vbString
   Formula =3D ""
   ColHidden =3D False
   ColNotDisplayable =3D False
   FilterDisabled =3D False
   ColWidth =3D 585
   GroupMethod =3D groupmethodNone
   ColFormat =3D ""
End Column

Begin Column
   FieldName =3D "Customer.AccountNumber"
   DrillDownFieldName =3D "Customer.AccountNumber"
   DrillDownReportName =3D ""
   StoreIDFieldName =3D ""
   Title =3D "Customer #"
   VBDataType =3D vbString
   Formula =3D ""
   ColHidden =3D False
   ColNotDisplayable =3D False
   FilterDisabled =3D False
   ColWidth =3D 1095
   GroupMethod =3D groupmethodNone
   ColFormat =3D ""
End Column

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

Begin Column
   FieldName =3D "VIEWTenders.StoreID"
   DrillDownFieldName =3D ""
   DrillDownReportName =3D ""
   StoreIDFieldName =3D ""
   Title =3D "Store ID"
   VBDataType =3D vbString
   Formula =3D ""
   ColHidden =3D True
   ColNotDisplayable =3D False
   FilterDisabled =3D False
   ColWidth =3D 1395
   GroupMethod =3D groupmethodNone
   ColFormat =3D ""
End Column

Begin Column
   FieldName =3D "VIEWTenders.Name"
   DrillDownFieldName =3D ""
   DrillDownReportName =3D ""
   StoreIDFieldName =3D ""
   Title =3D "Cashier Name"
   VBDataType =3D vbString
   Formula =3D ""
   ColHidden =3D True
   ColNotDisplayable =3D False
   FilterDisabled =3D False
   ColWidth =3D 1335
   GroupMethod =3D groupmethodNone
   ColFormat =3D ""
End Column

Begin Column
   FieldName =3D "VIEWTenders.Number"
   DrillDownFieldName =3D ""
   DrillDownReportName =3D ""
   StoreIDFieldName =3D ""
   Title =3D "Cashier #"
   VBDataType =3D vbString
   Formula =3D ""
   ColHidden =3D True
   ColNotDisplayable =3D False
   FilterDisabled =3D False
   ColWidth =3D 960
   GroupMethod =3D groupmethodNone
   ColFormat =3D ""
End Column

Begin Column
   FieldName =3D "VIEWTenders.Time"
   DrillDownFieldName =3D ""
   DrillDownReportName =3D ""
   StoreIDFieldName =3D ""
   Title =3D "Time Sold"
   VBDataType =3D vbString
   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 "VIEWTenders.StoreID"
   DrillDownFieldName =3D ""
   DrillDownReportName =3D ""
   StoreIDFieldName =3D ""
   Title =3D "Store ID"
   VBDataType =3D vbString
   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 "VIEWTenders.Amount"
   DrillDownFieldName =3D ""
   DrillDownReportName =3D ""
   StoreIDFieldName =3D ""
   Title =3D "Max Amount"
   VBDataType =3D vbCurrency
   Formula =3D ""
   ColHidden =3D True
   ColNotDisplayable =3D False
   FilterDisabled =3D False
   ColWidth =3D 1215
   GroupMethod =3D groupmethodMax
   ColFormat =3D ""
End Column

Begin Column
   FieldName =3D "VIEWTenders.Amount"
   DrillDownFieldName =3D ""
   DrillDownReportName =3D ""
   StoreIDFieldName =3D ""
   Title =3D "Min Amount"
   VBDataType =3D vbCurrency
   Formula =3D ""
   ColHidden =3D True
   ColNotDisplayable =3D False
   FilterDisabled =3D False
   ColWidth =3D 1185
   GroupMethod =3D groupmethodMin
   ColFormat =3D ""
End Column

Begin Column
   FieldName =3D "VIEWTenders.Amount"
   DrillDownFieldName =3D ""
   DrillDownReportName =3D ""
   StoreIDFieldName =3D ""
   Title =3D "Avg Amount"
   VBDataType =3D vbCurrency
   Formula =3D ""
   ColHidden =3D True
   ColNotDisplayable =3D False
   FilterDisabled =3D False
   ColWidth =3D 1200
   GroupMethod =3D groupmethodAverage
   ColFormat =3D ""
End Column

Begin Column
   FieldName =3D "VIEWTenders.RegDesc"
   DrillDownFieldName =3D ""
   DrillDownReportName =3D ""
   StoreIDFieldName =3D ""
   Title =3D "Reg. Descrptn."
   VBDataType =3D vbString
   Formula =3D ""
   ColHidden =3D True
   ColNotDisplayable =3D False
   FilterDisabled =3D False
   ColWidth =3D 1380
   GroupMethod =3D groupmethodNone
   ColFormat =3D ""
End Column

Begin Column
   FieldName =3D "Customer.FirstName"
   DrillDownFieldName =3D ""
   DrillDownReportName =3D ""
   StoreIDFieldName =3D ""
   Title =3D "Customer First Name"
   VBDataType =3D vbString
   Formula =3D ""
   ColHidden =3D True
   ColNotDisplayable =3D True
   FilterDisabled =3D False
   ColWidth =3D 840
   GroupMethod =3D groupmethodNone
   ColFormat =3D ""
End Column

Begin Column
   FieldName =3D "Customer.LastName"
   DrillDownFieldName =3D ""
   DrillDownReportName =3D ""
   StoreIDFieldName =3D ""
   Title =3D "Customer Last Name"
   VBDataType =3D vbString
   Formula =3D ""
   ColHidden =3D True
   ColNotDisplayable =3D True
   FilterDisabled =3D False
   ColWidth =3D 840
   GroupMethod =3D groupmethodNone
   ColFormat =3D ""
End Column

------=

Posted by David Moffatt on May 8, 2008, 11:50 am

Please log in for more thread options

Don,

Don't have HQ. ALso I did not see an attachment on your post. thanks

"donphillip" wrote:

Posted by convoluted on May 10, 2008, 4:26 pm

Please log in for more thread options

David  -  run the Tender Summary report in Reports - Miscellaneous; on the
report preview, drag the TENDER column all the way to the left so its the
first column.  You will then see totals by tender for all registers.  Next,
memorize the report, call it TENDER SUMMARY BY TENDER or something like that
- this will retain your change but you will now run this report from Reports
- Memorized.  Hope this helps.....

"David Moffatt" wrote:

Posted by Don on May 27, 2008, 12:34 pm

Please log in for more thread options

Hello.
Yes, this can be done with a custom report. If you would like more
information on how to accomplish your desired task, please call Merchants
Consulting at 253-509-2110. Thank you

"David Moffatt" wrote: