如何合并多个Microsoft Store Operations SQL报表？

Question

我的SQL知识非常有限，我正在尝试在Microsoft Store Operations中合并两个报告。理想情况下，我想知道每一次销售都是通过哪一次招标。

他们有一份招标报告，但没有显示税收或销售细节，他们有一份销售报告，但没有显示招标。

销售报告：

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

招标报告：

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

可以合并这些报告吗？我主要需要的是在销售报告上加标书。

提前谢谢。

Answer 1

sql非常简单，第二个报告显示了如何将招标表格连接到第一个表格中：

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

如果我按照设置的方式阅读，这意味着您只需将第一个报告更新为：

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

然而，这并没有调用任何列，所以这似乎是您用于报告的任何应用程序的下游处理内容。所以我有80%的把握这是可行的。