Tender Summary Report at HQ

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 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

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 = "" Font = "Arial" FontBold = True FontSize = 16 Color = "Blue" End TitleRow

Begin TitleRow Text = "" Font = "Arial" FontBold = True FontSize = 12 Color = "Black" End TitleRow

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

//--- Filters ---// Begin Filter FieldName = "ViewTenders.Date" FilterOp = reportfilteropBetween FilterLoLim = "" FilterHilim = "" 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: