I am trying to create a BAQ from Part Transaction that Groups by Part Number and Summarizes (Sums Part Transactions from the summary) in a Calculated Column rather than having to perform Group By and Summarize column. I don’t need a drill down but rather just a top level. I followed another article similar for three subqueries to accomplish a similar result but it really did not apply to part transactions. I am getting confused somewhere on their description of aggregating.
Yes I understand this can somewhat be done in a dashboard but I am not getting the results wanted from this method either.
Using 10.1.500.36
Here is what I have so far. Missing a calc field for the summary.
select
[PartTran].[PartNum] as [PartTran_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[Part].[TypeCode] as [Part_TypeCode],
[PartWhse].[OnHandQty] as [PartWhse_OnHandQty],
[PartWhse].[DemandQty] as [PartWhse_DemandQty],
[PartClass].[Description] as [PartClass_Description],
[Part].[PartDecor_c] as [Part_PartDecor_c],
[PartTran].[InvtyUOM] as [PartTran_InvtyUOM],
[PartTran].[TranClass] as [PartTran_TranClass],
[PartTran].[TranType] as [PartTran_TranType],
[PartTran].[TranDate] as [PartTran_TranDate],
[Part].[ClassID] as [Part_ClassID],
[PartWhse].[SalesDemandQty] as [PartWhse_SalesDemandQty],
(case
when Part.PartDecor_c = ‘TB-Explorer Old’ then 1
when Part.PartDecor_c = ‘TB New Image’ then 1
when Part.PartDecor_c = ‘TB Explorer 3.0’ then 1
else 0
end) as [Calculated_SlowMoving],
[PartTran].[TranQty] as [PartTran_TranQty],
(sum( PartTran.TranQty )over (partition by PartTran.PartNum)) as [Calculated_SumTransQty]
from Erp.PartTran as PartTran
inner join Erp.Part as Part on
PartTran.Company = Part.Company
And
PartTran.PartNum = Part.PartNum
and ( Part.PartDecor_c = ‘TB Modern Explorer’ or Part.PartDecor_c = ‘TB-Explorer 3.0’ or Part.PartDecor_c = ‘TB Innovation’ or Part.PartDecor_c = ‘TB-Explorer Old’ or Part.PartDecor_c = ‘TB Modern Explorer’ or Part.PartDecor_c = ‘TB New Image’ or Part.PartDecor_c = ‘TB Heritage’ and Part.TypeCode = ‘M’ )
inner join Erp.PartWhse as PartWhse on
Part.Company = PartWhse.Company
And
Part.PartNum = PartWhse.PartNum
and ( PartWhse.OnHandQty > 0 )
inner join Erp.PartClass as PartClass on
Part.Company = PartClass.Company
And
Part.ClassID = PartClass.ClassID
where (PartTran.TranType = ‘STK-CUS’ and PartTran.TranDate >= ‘06/01/2019’)
group by [PartTran].[PartNum],
[Part].[PartDescription],
[Part].[TypeCode],
[PartWhse].[OnHandQty],
[PartWhse].[DemandQty],
[PartClass].[Description],
[Part].[PartDecor_c],
[PartTran].[InvtyUOM],
[PartTran].[TranClass],
[PartTran].[TranType],
[PartTran].[TranDate],
[Part].[ClassID],
[PartWhse].[SalesDemandQty],
[PartTran].[TranQty]