I have a dashboard with several tabs. Where we are trying to get all of the quotes that are between 10,000 and 19,999 on one, all quotes that have certain task id’s.
I created a calculated field to get the sum for the total amount that should let me get values between the two totals.
Then did a SubQuery Criteria with having.
The issue I’m having is when I put the SubQuery Criteria on the BAQ it doesn’t pull all of the quotes back.
Here is the BAQ with the SubQuery Criteria.
Here is the BAQ without the SubQuery Criteria, its the only thing I removed. Quote number 325794 should show up on the BAQ. If you total all three lines it’s 13,786.
Summary
select
[QuoteDtl].[QuoteNum] as [QuoteDtl_QuoteNum],
[QuoteDtl].[ProdCode] as [QuoteDtl_ProdCode],
[QuoteHed].[ChangedBy] as [QuoteHed_ChangedBy],
[QsalesRP1].[QSalesRP_Name] as [QSalesRP_Name],
[TaskSet].[TaskSet1_TaskSetDescription] as [TaskSet1_TaskSetDescription],
[Tasks].[Task_Conclusion] as [Task_Conclusion],
[Tasks].[Task_ReasonCode] as [Task_ReasonCode],
[QuoteCnt].[Name] as [QuoteCnt_Name],
[QuoteHed].[EntryDate] as [QuoteHed_EntryDate],
[QuoteHed].[DueDate] as [QuoteHed_DueDate],
(QuoteHed.EntryDate) as [Calculated_enddate],
(sum((QuoteDtl.OrderQty * QuoteDtl.DocExpUnitPrice) - QuoteHed.DocTotalDiscount)) as [Calculated_total]
from Erp.QuoteDtl as QuoteDtl
inner join Erp.QuoteHed as QuoteHed on
QuoteDtl.Company = QuoteHed.Company
and QuoteDtl.QuoteNum = QuoteHed.QuoteNum
and ( QuoteHed.QuoteClosed = 0 )
left outer join Erp.QuoteCnt as QuoteCnt on
QuoteHed.Company = QuoteCnt.Company
and QuoteHed.QuoteNum = QuoteCnt.QuoteNum
inner join (select
[QSalesRP].[Name] as [QSalesRP_Name],
[QSalesRP].[Company] as [QSalesRP_Company],
[QSalesRP].[QuoteNum] as [QSalesRP_QuoteNum]
from Erp.QSalesRP as QSalesRP) as QsalesRP1 on
QuoteDtl.Company = QsalesRP1.QSalesRP_Company
and QuoteDtl.QuoteNum = QsalesRP1.QSalesRP_QuoteNum
inner join (select distinct
[Task].[Company] as [Task_Company],
[Task].[Key1] as [Task_Key1],
[Task].[Conclusion] as [Task_Conclusion],
[Task].[ReasonCode] as [Task_ReasonCode],
[Task].[TaskDescription] as [Task_TaskDescription],
[Task].[TaskSetID] as [Task_TaskSetID]
from Erp.Task as Task
where (Task.RelatedToFile = 'QuoteHed' and Task.Complete = 0 and Task.TaskID in ('CSFU1', 'CSFU2', 'CSFU3', 'CSFU4', 'CSFUL1', 'CSFUL3'))) as Tasks on
QuoteDtl.Company = Tasks.Task_Company
and QuoteDtl.QuoteNum = Tasks.Task_Key1
inner join (select
[TaskSet1].[Company] as [TaskSet1_Company],
[TaskSet1].[TaskSetID] as [TaskSet1_TaskSetID],
[TaskSet1].[TaskSetDescription] as [TaskSet1_TaskSetDescription]
from Erp.TaskSet as TaskSet1) as TaskSet on
Tasks.Task_Company = TaskSet.TaskSet1_Company
and Tasks.Task_TaskSetID = TaskSet.TaskSet1_TaskSetID
group by [QuoteDtl].[QuoteNum],
[QuoteDtl].[ProdCode],
[QuoteHed].[ChangedBy],
[QsalesRP1].[QSalesRP_Name],
[TaskSet].[TaskSet1_TaskSetDescription],
[Tasks].[Task_Conclusion],
[Tasks].[Task_ReasonCode],
[QuoteCnt].[Name],
[QuoteHed].[EntryDate],
[QuoteHed].[DueDate]
having (sum((QuoteDtl.OrderQty * QuoteDtl.DocExpUnitPrice) - QuoteHed.DocTotalDiscount)) >= 10000 and (sum((QuoteDtl.OrderQty * QuoteDtl.DocExpUnitPrice) - QuoteHed.DocTotalDiscount)) <= 19999
