Sales by State

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.

Attached is the BAQ called SalesSummary.baq
SalesSummary.baq (47.5 KB)

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

…………………………