How to combine multiple Microsoft Store Operations SQL reports?

Question

I've got very limtied SQL knowledge and I'm attempting to combine two reports in Microsoft Store Operations. I ideally want to know which tender each sale went through.

They have a tender report but it doesn't show tax or sales details, and they have a sales report but it doesn't show the tender.

The sales report:

Begin ReportSummary
ReportType = reporttypeSales
ReportTitle = "Detailed Sales Report (Tax Included in Sales)"
PageOrientation = pageorientationPortrait
OutLineMode = True
Groups = 1
GroupDescription = ""
DisplayLogo = True
LogoFileName = "MyLogo.bmp"
ProcedureCall = ""
TablesQueried = <BEGIN>
  FROM     TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber 
     INNER JOIN  Batch WITH(NOLOCK) ON [Transaction].BatchNumber = Batch.BatchNumber 
     LEFT JOIN   Item WITH(NOLOCK) ON TransactionEntry.ItemID = Item.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   ReasonCode AS ReasonCodeDiscount WITH(NOLOCK) ON TransactionEntry.DiscountReasonCodeID = ReasonCodeDiscount.ID 
     LEFT JOIN   ReasonCode AS ReasonCodeTaxChange WITH(NOLOCK) ON TransactionEntry.TaxChangeReasonCodeID = ReasonCodeTaxChange.ID
     LEFT JOIN   ReasonCode AS ReasonCodeReturn WITH(NOLOCK) ON TransactionEntry.ReturnReasonCodeID = ReasonCodeReturn.ID
     LEFT JOIN  Register WITH(NOLOCK) ON Batch.RegisterID = Register.ID
     LEFT JOIN   Customer WITH(NOLOCK) ON [Transaction].CustomerID = Customer.ID
     LEFT JOIN   Cashier WITH(NOLOCK) ON [Transaction].CashierID = Cashier.ID
     LEFT JOIN   QuantityDiscount WITH(NOLOCK) ON TransactionEntry.QuantityDiscountID = QuantityDiscount.ID
<END>

SelCriteria = ""
GroupBy = ""
SortOrder = ""
End ReportSummary

The tender report:

Begin ReportSummary
ReportType = reporttypeSales
ReportTitle = "Tender Summary"
PageOrientation = pageorientationPortrait
WordWrap = True
OutLineMode = True
Groups = 2
GroupDescription = ""
DisplayLogo = True
LogoFileName = "MyLogo.bmp"
ProcedureCall = ""
TablesQueried = "
  FROM TenderEntry 
     LEFT JOIN [Transaction] ON TenderEntry.TransactionNumber = [Transaction].TransactionNumber 
     LEFT JOIN Batch ON [Transaction].BatchNumber = Batch.BatchNumber 
     LEFT JOIN Register ON Batch.RegisterID = Register.ID"
SelCriteria = ""
GroupBy = ""
SortOrder = "TenderEntry.Amount, [Transaction].Time, TenderEntry.Description, Register.Description"
End ReportSummary

Is it possible to combine these reports? The main thing I need is the tender added onto the sales report.

Thanks in advance.

score 0 · Answer 1 · answered Oct 12 '15 at 16:51

The sql is pretty easy the second report shows how to join the tender table to the first:

left join [TenderEntry] on [TenderEntry].TransactionNumber = [Transaction].TransactionNumber

Which if I'm reading the way this is set up means that you'll just update the first report to:

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

SelCriteria = ""
GroupBy = ""
SortOrder = ""
End ReportSummary

However this doesn't call out any columns, so that appears to be something handled downstream in whatever app you are using for the reports. So I'm 80% sure this will work.

Worked perfectly, I seemed to overcomplicate it. I had to call the column underneath as mentioned, it was just the main joining of the reports that I struggled with. — Joe A, Oct 13 '15 at 09:55

How to combine multiple Microsoft Store Operations SQL reports?

1 Answers1