Tender Summary Report at HQ

Posted by Jose Lugo on March 12, 2007, 9:52 am

Please log in for more thread options

Hi, can anyone inform if it's possible to get the "Tender Summary Report" in
HQ ??? We have different stores and I would like to get the tender
information at HQ lever for all the stores.
Thanks in advance !!

Posted by Akber Alwani on March 13, 2007, 2:04 am

Please log in for more thread options

HI Jose.
below is the report please review this:
//--- Report Summary --- //

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

CREATE VIEW VIEWTenders AS

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

FROM TenderEntry
LEFT JOIN Store ON TenderEntry.StoreID = Store.ID
LEFT JOIN [Transaction] ON TenderEntry.TransactionNumber =
[Transaction].TransactionNumber
AND Store.ID = [Transaction].StoreID
LEFT JOIN Batch ON [Transaction].BatchNumber = Batch.BatchNumber
AND Store.ID = Batch.StoreID
LEFT JOIN Register ON Batch.RegisterID = Register.ID
AND Store.ID = Register.StoreID
LEFT JOIN Cashier ON [Transaction].CashierID = Cashier.ID
AND Store.ID = 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,
Cashier.Name,
Cashier.Number,
TenderEntry.Description,
Payment.CustomerID,
Payment.Time as Date,
CONVERT(nvarchar, Payment.Time, 8) AS Time,
Payment.ID as TransactionNumber,
TendErentry.Amount,
'Payment' as TrxType

FROM TenderEntry
LEFT JOIN Store ON TenderEntry.StoreID = Store.ID
LEFT JOIN Payment ON Payment.ID = Tenderentry.PaymentID
AND Store.ID = Payment.StoreID
LEFT JOIN Batch ON Payment.BatchNumber = Batch.BatchNumber
AND Store.ID = Batch.StoreID
LEFT JOIN Register ON Batch.RegisterID = Register.ID
AND Store.ID = Register.StoreID
LEFT JOIN Cashier ON Payment.CashierID = Cashier.ID
AND Store.ID = 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,
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,
'Drop/Payout' as TrxType

FROM TenderEntry
LEFT JOIN Store ON TenderEntry.StoreID = Store.ID
LEFT JOIN DropPayout ON DropPayout.ID = TenderEntry.DropPayoutID
AND Store.ID = DropPayout.StoreID
LEFT JOIN Batch ON DropPayout.BatchNumber = Batch.BatchNumber
AND Store.ID = Batch.StoreID
LEFT JOIN Register ON Batch.RegisterID = Register.ID
AND Store.ID = Register.StoreID
LEFT JOIN Cashier ON DropPayout.CashierID = Cashier.ID
AND Store.ID = 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,
Cashier.Name,
Cashier.Number,
TenderEntry.Description,
[Order].CustomerID,
OrderHistory.Date,
CONVERT(nvarchar, OrderHistory.Date, 8) AS Time,
OrderHistory.OrderID as TransactionNumber,
TenderEntry.Amount,
CASE WHEN Type = 2 THEN 'Work Order' ELSE CASE WHEN Type = 5 THEN 'Layaway'
ELSE 'Back Order' END END as TrxType

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

WHERE OrderHistory.TransactionNumber = 0
<END>
TablesQueried = "FROM VIEWTenders LEFT JOIN Customer ON
ViewTenders.CustomerID = Customer.ID"
SelCriteria = ""
GroupBy = ""
SortOrder = "VIEWTenders.Amount, VIEWTenders.Date,
VIEWTenders.Description,VIEWTenders.RegDesc"
End ReportSummary

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

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

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

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

//--- Filters ---//
Begin Filter
FieldName = "ViewTenders.Date"
FilterOp = reportfilteropBetween
FilterLoLim = "<Today>"
FilterHilim = "<Today>"
End Filter

//--- Columns ---//

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

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

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

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

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

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

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

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

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

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

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

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

Begin Column
FieldName = "VIEWTenders.Time"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Time Sold"
VBDataType = vbString
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 900
GroupMethod = groupmethodNone
ColFormat = ""
End Column

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

Begin Column
FieldName = "VIEWTenders.Amount"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Max Amount"
VBDataType = vbCurrency
Formula = ""
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1215
GroupMethod = groupmethodMax
ColFormat = ""
End Column

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

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

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

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

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

I appreicate if you rate please.

"Jose Lugo" wrote:

> Hi, can anyone inform if it's possible to get the "Tender Summary Report" in

> HQ ??? We have different stores and I would like to get the tender

> information at HQ lever for all the stores.

> Thanks in advance !!