I am having an issue when running a BAQ and pulling all the records for this BAQ. I think its just because there are too many records but I am not sure.
Here is my query:
select
\[ShipHead\].\[ShipDate\] as \[ShipHead_ShipDate\],
\[OrderHed\].\[SalesRepList\] as \[OrderHed_SalesRepList\],
\[Customer\].\[Name\] as \[Customer_Name\],
\[OrderHed\].\[ShipToNum\] as \[OrderHed_ShipToNum\],
\[OrderHed\].\[PONum\] as \[OrderHed_PONum\],
\[ShipDtl\].\[OrderNum\] as \[ShipDtl_OrderNum\],
(OrderDtl.DocUnitPrice - ( OrderDtl.Discount / NULLIF(OrderDtl.SellingQuantity, 0) )) as \[Calculated_totwdis\],
(CASE
WHEN CurrExRate1.CurrentRate > 0 THEN
(totwdis)\*(1/CurrExRate1.CurrentRate)
ELSE (totwdis)
END) as \[Calculated_Convertedtotwdis\],
(PartTran.TranQty \* totwdis) as \[Calculated_etotwdis\],
(CASE
WHEN CurrExRate1.CurrentRate > 0 THEN
(etotwdis)\*(1/CurrExRate1.CurrentRate)
ELSE (etotwdis)
END) as \[Calculated_Convertedetotwdis\],
\[PartTran\].\[ExtCost\] as \[PartTran_ExtCost\],
\[PartTran\].\[TranQty\] as \[PartTran_TranQty\],
\[ShipDtl\].\[LineDesc\] as \[ShipDtl_LineDesc\],
(( ( etotwdis - PartTran.ExtCost ) / NULLIF(etotwdis, 0) ) \* 100) as \[Calculated_grossmargin\],
(( ( Convertedetotwdis - PartTran.ExtCost ) / NULLIF(Convertedetotwdis, 0) ) \* 100) as \[Calculated_convertedGrossMargin\],
\[ShipDtl\].\[PartNum\] as \[ShipDtl_PartNum\],
\[OrderDtl\].\[ProdCode\] as \[OrderDtl_ProdCode\],
\[ShipDtl\].\[JobNum\] as \[ShipDtl_JobNum\],
\[ShipTo\].\[City\] as \[ShipTo_City\],
((case when OrderHed.UseOTS = 1 then OrderHed.OTSCity else ShipTo.City end)) as \[Calculated_sCity\],
\[ShipTo\].\[State\] as \[ShipTo_State\],
((case when OrderHed.UseOTS = 1 then OrderHed.OTSState else ShipTo.State end)) as \[Calculated_sState\],
\[ShipTo\].\[Country\] as \[ShipTo_Country\],
((case when OrderHed.UseOTS = 1 then Country.Description else ShipTo.Country end)) as \[Calculated_sCountry\],
\[ShipHead\].\[Invoiced\] as \[ShipHead_Invoiced\],
\[ShipHead\].\[ReadyToInvoice\] as \[ShipHead_ReadyToInvoice\],
\[OrderHed\].\[Date09\] as \[OrderHed_Date09\],
\[OrderHed\].\[OrderDate\] as \[OrderHed_OrderDate\],
\[PartPlant\].\[LeadTime\] as \[PartPlant_LeadTime\],
\[OrderHed\].\[Company\] as \[OrderHed_Company\],
\[SalesTer\].\[TerritoryDesc\] as \[SalesTer_TerritoryDesc\],
\[SalesTer\].\[TerritoryID\] as \[SalesTer_TerritoryID\],
\[SalesTer\].\[RegionCode\] as \[SalesTer_RegionCode\],
\[OrderHed\].\[RequestDate\] as \[OrderHed_RequestDate\],
\[CustAttr\].\[AttrCode\] as \[CustAttr_AttrCode\],
\[PartTran\].\[TranDate\] as \[PartTran_TranDate\],
\[OrderHed\].\[ReservePriorityCode\] as \[OrderHed_ReservePriorityCode\],
\[OrderHed\].\[InternalSalesRep_c\] as \[OrderHed_InternalSalesRep_c\]
from Erp.ShipHead as \[ShipHead\]
inner join Erp.ShipDtl as \[ShipDtl\] on
ShipHead.Company = ShipDtl.Company
and ShipHead.PackNum = ShipDtl.PackNum
left outer join Erp.OrderHed as \[OrderHed\] on
ShipDtl.Company = OrderHed.Company
and ShipDtl.OrderNum = OrderHed.OrderNum
left outer join Erp.Country as \[Country\] on
OrderHed.Company = Country.Company
and OrderHed.OTSCountryNum = Country.CountryNum
left outer join Erp.CurrExRate as \[CurrExRate1\] on
OrderHed.Company = CurrExRate1.Company
and OrderHed.CurrencyCode = CurrExRate1.TargetCurrCode
and ( CurrExRate1.EffectiveDate >= ALL (select Calculated_LatestExchangeRateDate from ((select
(max(CurrExRate.EffectiveDate)) as \[Calculated_LatestExchangeRateDate\],
\[CurrExRate\].\[SourceCurrCode\] as \[CurrExRate_SourceCurrCode\]
from Erp.Currency as \[Currency\]
inner join Erp.CurrExRate as \[CurrExRate\] on
Currency.Company = CurrExRate.Company
group by
\[CurrExRate\].\[SourceCurrCode\])) as SubQuery2)
and CurrExRate1.SourceCurrCode = 'Base' )
inner join Erp.OrderDtl as \[OrderDtl\] on
ShipDtl.Company = OrderDtl.Company
and ShipDtl.OrderNum = OrderDtl.OrderNum
and ShipDtl.OrderLine = OrderDtl.OrderLine
and ( OrderDtl.ProdCode LIKE 'P%'
or OrderDtl.ProdCode LIKE 'C%' )
left outer join Erp.PartTran as \[PartTran\] on
ShipDtl.Company = PartTran.Company
and ShipDtl.PackNum = PartTran.PackNum
and ShipDtl.PackLine = PartTran.PackLine
and ShipDtl.Plant = PartTran.Plant
and ( (PartTran.TranType = 'STK-CUS'
or PartTran.TranType = 'MFG-CUS' )
and PartTran.RevertStatus = 0 )
left outer join Erp.PartPlant as \[PartPlant\] on
PartTran.Company = PartPlant.Company
and PartTran.PartNum = PartPlant.PartNum
and PartTran.Plant = PartPlant.Plant
inner join Erp.Customer as \[Customer\] on
ShipHead.Company = Customer.Company
and ShipHead.CustNum = Customer.CustNum
left outer join Erp.CustAttr as \[CustAttr\] on
Customer.Company = CustAttr.Company
and Customer.CustNum = CustAttr.CustNum
inner join Erp.SalesTer as \[SalesTer\] on
Customer.Company = SalesTer.Company
and Customer.TerritoryID = SalesTer.TerritoryID
left outer join Erp.ShipTo as \[ShipTo\] on
ShipHead.Company = ShipTo.Company
and ShipHead.ShipToNum = ShipTo.ShipToNum
and ShipHead.ShipToCustNum = ShipTo.CustNum
where ( ShipDtl.OrderNum > 0 )
order by ShipHead.PackNum, ShipDtl.PackLine
Normally this wouldnt be an issue but I am trying to add this to a dashboard and I am having trouble because when I am running the BAQ in app studio it is crashing the app I am working on. I want to run it without filters but I am not too sure how to get around this. All the data I have is needed by the team so any advice?