I’m trying to create a BAQ to display the total number of POs along with the total amount by vendor.
In addition to that, I would also like to create a filter to allow sorting by POHeader.OrderDate, any idea on how to achieve this?
Adding orderdate to the BAQ would return 0 result which is expected as i was grouping by vendor.
select
[Vendor].[Name] as [Vendor_Name],
(count(POHeader.PONum)) as [Calculated_NoOfPOs],
(sum(POHeader.DocTotalOrder)) as [Calculated_TotalSGD]
from Erp.POHeader as POHeader
inner join Erp.Vendor as Vendor on
POHeader.Company = Vendor.Company
and POHeader.VendorNum = Vendor.VendorNum
group by [Vendor].[Name]
select
[Vendor].[Name] as [Vendor_Name],
(SUM(POHeader.DocTotalOrder)) as [Calculated_SumOrders],
(COUNT(POHeader.PONum)) as [Calculated_CountOrders],
(MAX(POHeader.OrderDate)) as [Calculated_LastOrderDate]
from Erp.POHeader as POHeader
inner join Erp.Vendor as Vendor on
POHeader.Company = Vendor.Company
and POHeader.VendorNum = Vendor.VendorNum
group by [Vendor].[Name]
I’m planning to use this BAQ on a dashboard, and subsequently create a data range filter to display the total amount of PO and total amount of the vendor based on the selected date range
In order to do that, I would need to add the orderdate to the display column in BAQ, but doing so would ruin my BAQ as it would not return any result if I do that. However if I don’t pull it out, I can’t filter the orderdate in dashboard
select
[Vendor].[Name] as [Vendor_Name],
[POHeader].[PONum] as [POHeader_PONum],
[POHeader].[OrderDate] as [POHeader_OrderDate],
[POHeader].[DocTotalOrder] as [POHeader_DocTotalOrder],
[SubQuery2].[Calculated_TotalSumPOForVendor] as [Calculated_TotalSumPOForVendor],
[SubQuery2].[Calculated_TotalCountPOForVendor] as [Calculated_TotalCountPOForVendor]
from Erp.POHeader as POHeader
inner join Erp.Vendor as Vendor on
POHeader.Company = Vendor.Company
and POHeader.VendorNum = Vendor.VendorNum
inner join (select
[Vendor1].[VendorNum] as [Vendor1_VendorNum],
(sum(POHeader1.DocTotalOrder)) as [Calculated_TotalSumPOForVendor],
(count(POHeader1.PONum)) as [Calculated_TotalCountPOForVendor]
from Erp.POHeader as POHeader1
inner join Erp.Vendor as Vendor1 on
POHeader1.Company = Vendor1.Company
and POHeader1.VendorNum = Vendor1.VendorNum
group by [Vendor1].[VendorNum]) as SubQuery2 on
Vendor.VendorNum = SubQuery2.Vendor1_VendorNum
Subquery 1:
Vendor_Name Name
POHeader_PONum
POHeader_OrderDate
POHeader_DocTotalOrder
From Subquery2 --> Calculated_TotalSumPOForVendor
From Subquery2 --> Calculated_TotalCountPOForVendor
Linked On Subquery1 Vendor_VendorNum → Subquery2 Vendor1_VendorNum →
Subquery 2:
Vendor1_VendorNum [GROUP BY CHECKED]
Calculated_TotalSumPOForVendor
Calculated_TotalCountPOForVendor
