Hello everyone,
Has anyone tried to use a Parameter in GI Grouping? I want to do something very simple. Using a Boolean Parameter I would like to add a grouping. To do so:
- I have created a <checkbox> parameter called “GroupByTask” allowing users to check/uncheck on GI as a parameter.
- I have added a grouping as “=IIf([GroupByTask]=True, [PMTask.TaskCD], '')”, In the result grid I have the exact formula as a column.
When I run the inquiry I get an error saying “Must declare the scalar variable "@P0".” When I trace the query in SQL Profiler I see Acumatica generates a query that in Group By section has “CASE WHEN @P0 = 1 THEN [PMTask].[TaskCD] ELSE '' END” but the platform doesn’t declare any parameter and pass to the query for it.
Any idea how I can make this work?
Not sure if is going to be of any use but below I have copied the query that platform generates.
SELECT SUM( [InnerQuery].[PMCostProjectionLine_usrHCLBudgetOriginalQty]) AS [PMCostProjectionLine_usrHCLBudgetOriginalQty], SUM( [InnerQuery].[PMCostProjectionLine_usrHCLBudgetOriginal]) AS [PMCostProjectionLine_usrHCLBudgetOriginal], COUNT( *)
FROM (
SELECT MAX( [PMCostProjection].[ProjectID]) AS [PMCostProjection_ProjectID], [PMCostProjection].[RevisionID] AS [PMCostProjection_RevisionID], [PMCostProjection].[ClassID] AS [PMCostProjection_ClassID], [PMCostProjection].[Status] AS [PMCostProjection_Status], [PMCostProjection].[Description] AS [PMCostProjection_Description], [PMCostProjection].[Date] AS [PMCostProjection_Date], MAX( [PMCostProjectionLine].[ProjectID]) AS [PMCostProjectionLine_ProjectID], MAX( [PMCostProjectionLine].[RevisionID]) AS [PMCostProjectionLine_RevisionID], MAX( [PMCostProjectionLine].[LineNbr]) AS [PMCostProjectionLine_LineNbr], SUM( [PMCostProjectionLine].[UsrHCLBudgetOriginalQty]) AS [PMCostProjectionLine_UsrHCLBudgetOriginalQty], SUM( [PMCostProjectionLine].[UsrHCLBudgetOriginal]) AS [PMCostProjectionLine_UsrHCLBudgetOriginal], MAX( [PMTask].[ProjectID]) AS [PMTask_ProjectID], MAX( [PMTask].[TaskCD]) AS [PMTask_TaskCD], MAX( [PMCostCode].[CostCodeCD]) AS [PMCostCode_CostCodeCD], MAX( [PMAccountGroup].[GroupCD]) AS [PMAccountGroup_GroupCD], MAX( [InventoryItem_InventoryItem].[InventoryCD]) AS [InventoryItem_InventoryCD]
FROM [PMCostProjection] [PMCostProjection]
INNER JOIN [PMCostProjectionLine] [PMCostProjectionLine] ON ( [PMCostProjectionLine].[CompanyID] = 2) AND ( [PMCostProjection].[ProjectID] = [PMCostProjectionLine].[ProjectID] AND [PMCostProjection].[RevisionID] = [PMCostProjectionLine].[RevisionID])
LEFT JOIN [PMTask] [PMTask] ON ( [PMTask].[CompanyID] = 2) AND ( [PMCostProjectionLine].[TaskID] = [PMTask].[TaskID])
LEFT JOIN [PMCostCode] [PMCostCode] ON ( [PMCostCode].[CompanyID] = 2) AND ( [PMCostProjectionLine].[CostCodeID] = [PMCostCode].[CostCodeID])
LEFT JOIN [PMAccountGroup] [PMAccountGroup] ON ( [PMAccountGroup].[CompanyID] = 2) AND ( [PMCostProjectionLine].[AccountGroupID] = [PMAccountGroup].[GroupID])
LEFT JOIN (
[InventoryItem] [InventoryItem_InventoryItem]
LEFT JOIN [FSxEquipmentModel] [InventoryItem_FSxEquipmentModel] ON ( [InventoryItem_FSxEquipmentModel].[CompanyID] = 2) AND [InventoryItem_FSxEquipmentModel].[DeletedDatabaseRecord] = 0 AND [InventoryItem_InventoryItem].[InventoryID] = [InventoryItem_FSxEquipmentModel].[InventoryID]
LEFT JOIN [FSxService] [InventoryItem_FSxService] ON ( [InventoryItem_FSxService].[CompanyID] = 2) AND [InventoryItem_FSxService].[DeletedDatabaseRecord] = 0 AND [InventoryItem_InventoryItem].[InventoryID] = [InventoryItem_FSxService].[InventoryID]
) ON ( [PMCostProjectionLine].[InventoryID] = [InventoryItem_InventoryItem].[InventoryID]) AND ( [InventoryItem_InventoryItem].[CompanyID] = 2) AND [InventoryItem_InventoryItem].[DeletedDatabaseRecord] = 0
WHERE ( [PMCostProjection].[CompanyID] = 2)
GROUP BY [PMCostProjection].[RevisionID], [PMCostProjection].[Date], [PMCostProjection].[Description], [PMCostProjection].[ClassID], [PMCostProjection].[Status], CASE WHEN @P0 = 1 THEN [PMTask].[TaskCD] ELSE '' END
) [InnerQuery] OPTION(OPTIMIZE FOR UNKNOWN)