Looking for a Sales by State report which is needed for income tax returns, so thought there would be a canned report but am not finding anything. Looks like writing a BAQ will be challenging. Does anyone have any suggestions or guidance on quickest/easiest way to pull this data.
I have a BAQ which brings a lot of sales information along with Customer details and State. If you need I can upload it tomorrow morning. You can modify it to suit your needs.
Yes, I would appreciate if you would share your BAQ. Thank you.
@dmoorman, you may find the following topic helpful.
Looks like I owe @utaylor an explanation of the BAQ that I uploaded. I’ll try to do that later today, Utah.
Thank you so much sharing your BAQ. Unfortunately, we are on an older version of BAQ and are unable to import the file. But thank you again, I appreciate your willingness to help,
Thank you so much for the information and sharing the BAQ. The report looks like it will provide valuable data and save time in our current process of extracting and combining various data reports.
I am giving below the Query phrase from BAQ. You can use it to recreate it in your system.
……………………………
select
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
[InvcDtl].[ShipToNum] as [InvcDtl_ShipToNum],
[ShipTo].[Name] as [ShipTo_Name],
[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
[InvcHead].[InvoiceDate] as [InvcHead_InvoiceDate],
[InvcDtl].[PartNum] as [InvcDtl_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[InvcDtl].[OurShipQty] as [InvcDtl_OurShipQty],
[InvcDtl].[IUM] as [InvcDtl_IUM],
[InvcDtl].[UnitPrice] as [InvcDtl_UnitPrice],
[InvcDtl].[DocExtPrice] as [InvcDtl_DocExtPrice],
[Part].[NetWeight] as [Part_NetWeight],
(InvcDtl.OurShipQty * Part.NetWeight) as [Calculated_TotWeight],
(InvcDtl.DocExtPrice / NULLIF(TotWeight, 0)) as [Calculated_perLB],
[InvcHead].[CurrencyCode] as [InvcHead_CurrencyCode],
[InvcHead].[ExchangeRate] as [InvcHead_ExchangeRate],
[SalesTer].[TerritoryDesc] as [SalesTer_TerritoryDesc],
[InvcHead].[OrderNum] as [InvcHead_OrderNum],
[InvcHead].[FiscalYear] as [InvcHead_FiscalYear],
[InvcHead].[FiscalPeriod] as [InvcHead_FiscalPeriod],
[Part].[ProdCode] as [Part_ProdCode],
[Part].[AnalysisCode] as [Part_AnalysisCode],
[AnalysisCd].[Description] as [AnalysisCd_Description],
[InvcHead].[InvoiceSuffix] as [InvcHead_InvoiceSuffix],
[InvcHead].[InvoiceType] as [InvcHead_InvoiceType],
[InvcHead].[ApplyDate] as [InvcHead_ApplyDate],
[InvcHead].[Posted] as [InvcHead_Posted],
[InvcDtl].[SellingShipQty] as [InvcDtl_SellingShipQty],
[InvcDtl].[SalesUM] as [InvcDtl_SalesUM],
[InvcDtl].[MtlUnitCost] as [InvcDtl_MtlUnitCost],
[InvcDtl].[LbrUnitCost] as [InvcDtl_LbrUnitCost],
[InvcDtl].[BurUnitCost] as [InvcDtl_BurUnitCost],
[InvcDtl].[SubUnitCost] as [InvcDtl_SubUnitCost],
[CustIC].[ICCode] as [CustIC_ICCode],
[ICCode].[Description] as [ICCode_Description],
[CustIC].[ICTypeID] as [CustIC_ICTypeID],
[ICType].[Description] as [ICType_Description],
(InvcDtl.UnitPrice * InvcDtl.SellingShipQty / NULLIF(InvcDtl.OurShipQty, 0) - InvcDtl.MtlUnitCost) as [Calculated_UnitContribution],
(UnitContribution / NULLIF(Part.NetWeight, 0)) as [Calculated_ContPerLB],
[InvcHead].[CreditMemo] as [InvcHead_CreditMemo],
[Customer].[GroupCode] as [Customer_GroupCode],
[ShipTo].[State] as [ShipTo_State],
[ShipTo].[Country] as [ShipTo_Country],
[InvcDtl].[ShipToCustNum] as [InvcDtl_ShipToCustNum],
[Customer].[CustNum] as [Customer_CustNum],
[ShipTo].[TerritoryID] as [ShipTo_TerritoryID],
[ShipTo].[City] as [ShipTo_City],
[InvcHead].[TaxRegionCode] as [InvcHead_TaxRegionCode],
[InvcHead].[SalesRepList] as [InvcHead_SalesRepList]
from Erp.InvcHead as InvcHead
inner join Erp.InvcDtl as InvcDtl on
InvcHead.Company = InvcDtl.Company
and InvcHead.InvoiceNum = InvcDtl.InvoiceNum
inner join Erp.ShipTo as ShipTo on
InvcDtl.Company = ShipTo.Company
and InvcDtl.ShipToNum = ShipTo.ShipToNum
and InvcDtl.ShipToCustNum = ShipTo.CustNum
inner join Erp.SalesTer as SalesTer on
ShipTo.Company = SalesTer.Company
and ShipTo.TerritoryID = SalesTer.TerritoryID
left outer join Erp.CustIC as CustIC on
ShipTo.Company = CustIC.Company
and ShipTo.CustNum = CustIC.CustNum
and ShipTo.ShipToNum = CustIC.ShipToNum
left outer join Erp.ICCode as ICCode on
CustIC.Company = ICCode.Company
and CustIC.ICTypeID = ICCode.ICTypeID
and CustIC.ICCode = ICCode.ICCode
left outer join Erp.ICType as ICType on
ICCode.Company = ICType.Company
and ICCode.ICTypeID = ICType.ICTypeID
left outer join Erp.Part as Part on
InvcDtl.Company = Part.Company
and InvcDtl.PartNum = Part.PartNum
left outer join Erp.AnalysisCd as AnalysisCd on
Part.Company = AnalysisCd.Company
and Part.AnalysisCode = AnalysisCd.AnalysisCode
inner join Erp.Customer as Customer on
InvcHead.Company = Customer.Company
and InvcHead.CustNum = Customer.CustNum
left outer join Erp.CustGrup as CustGrup on
Customer.Company = CustGrup.Company
and Customer.GroupCode = CustGrup.GroupCode
where (InvcHead.InvoiceType <> ‘ADV’)
…………………………
I know this is an old thread but you saved my life here, thank you!
1 Like