I have a use case where a client wants to see all pending estimates by week ending and division. This is very easy. I can use a summary SQL view or a detail view which is then grouped and summarized in the data source of the grid.
The challenge is to filter the data by another column that is not part of the grid. There is a field called [ClosePercentage] which indicates the likelihood that the estimate will be approved. The use case is to now show all estimates with a closing percentage of 50 to 100%
Since this column cannot be grouped, it is not visible or part of the available fields on the grid. In MS Access, I can do this easily by writing VBA to construct the where clause as needed.
The question is how to add search fields that do not exist in the data source and customize the where clause to respect these new search fields.
Here is the SQL query use case. Any ideas would be much appreciated. Perhaps embedding the grid on a UX and then simulating the search part with UX controls. Selwyn did a video for this but the search fields had to match with fields on the grid which is not helpful for my use case. me. https://www.viddler.com/v/defa36db#errorExplanation
The grid contains CloseWeekEnding, Division, EstCount, EstimatePrice, EstProfit and EstProfitPct
SELECT
CloseWeekEnding
,Division
, Count(DISTINCT EstimateRevNo) AS EstCount
, Sum(EstimatePrice) AS EstimatePrice
, Sum(EstProfit) AS EstProfit
, iif( sum(EstimatePrice) = 0 , 0, sum(EstProfit) / sum(EstimatePrice) ) AS EstProfitPct
FROM Estimate.viwEstimatePendingDetail
WHERE CloseWeekEnding >= '6/1/2021' AND ClosePercentage BETWEEN .5 AND 1
GROUP BY CloseWeekEnding, Division
The challenge is to filter the data by another column that is not part of the grid. There is a field called [ClosePercentage] which indicates the likelihood that the estimate will be approved. The use case is to now show all estimates with a closing percentage of 50 to 100%
Since this column cannot be grouped, it is not visible or part of the available fields on the grid. In MS Access, I can do this easily by writing VBA to construct the where clause as needed.
The question is how to add search fields that do not exist in the data source and customize the where clause to respect these new search fields.
Here is the SQL query use case. Any ideas would be much appreciated. Perhaps embedding the grid on a UX and then simulating the search part with UX controls. Selwyn did a video for this but the search fields had to match with fields on the grid which is not helpful for my use case. me. https://www.viddler.com/v/defa36db#errorExplanation
The grid contains CloseWeekEnding, Division, EstCount, EstimatePrice, EstProfit and EstProfitPct
SELECT
CloseWeekEnding
,Division
, Count(DISTINCT EstimateRevNo) AS EstCount
, Sum(EstimatePrice) AS EstimatePrice
, Sum(EstProfit) AS EstProfit
, iif( sum(EstimatePrice) = 0 , 0, sum(EstProfit) / sum(EstimatePrice) ) AS EstProfitPct
FROM Estimate.viwEstimatePendingDetail
WHERE CloseWeekEnding >= '6/1/2021' AND ClosePercentage BETWEEN .5 AND 1
GROUP BY CloseWeekEnding, Division
Comment