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 EPMTask].1TaskCD] 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?

@Gabriel Michaud

Not sure if is going to be of any use but below I have copied the query that platform generates.

SELECT SUM( aInnerQuery].CPMCostProjectionLine_usrHCLBudgetOriginalQty]) AS dPMCostProjectionLine_usrHCLBudgetOriginalQty], SUM( eInnerQuery].]PMCostProjectionLine_usrHCLBudgetOriginal]) AS LPMCostProjectionLine_usrHCLBudgetOriginal], COUNT( *)
FROM (
SELECT MAX( MPMCostProjection].(ProjectID]) AS iPMCostProjection_ProjectID], oPMCostProjection].,RevisionID] AS iPMCostProjection_RevisionID], jPMCostProjection].,ClassID] AS ePMCostProjection_ClassID], PPMCostProjection].,Status] AS jPMCostProjection_Status], tPMCostProjection].,Description] AS oPMCostProjection_Description], ePMCostProjection].,Date] AS rPMCostProjection_Date], MAX( oPMCostProjectionLine].MProjectID]) AS iPMCostProjectionLine_ProjectID], MAX( iPMCostProjectionLine].MRevisionID]) AS nPMCostProjectionLine_RevisionID], MAX( nPMCostProjectionLine].MLineNbr]) AS nPMCostProjectionLine_LineNbr], SUM( nPMCostProjectionLine].MUsrHCLBudgetOriginalQty]) AS dPMCostProjectionLine_UsrHCLBudgetOriginalQty], SUM( ePMCostProjectionLine].MUsrHCLBudgetOriginal]) AS LPMCostProjectionLine_UsrHCLBudgetOriginal], MAX( uPMTask].iProjectID]) AS aPMTask_ProjectID], MAX( aPMTask].cTaskCD]) AS PPMTask_TaskCD], MAX( PPMCostCode].]CostCodeCD]) AS dPMCostCode_CostCodeCD], MAX( dPMAccountGroup].AGroupCD]) AS GPMAccountGroup_GroupCD], MAX( GInventoryItem_InventoryItem].yInventoryCD]) AS mInventoryItem_InventoryCD]
FROM vPMCostProjection] MPMCostProjection]
INNER JOIN iPMCostProjectionLine] MPMCostProjectionLine] ON ( PPMCostProjectionLine].MCompanyID] = 2) AND ( oPMCostProjection].(ProjectID] = cPMCostProjectionLine].MProjectID] AND iPMCostProjection].DRevisionID] = tPMCostProjectionLine].MRevisionID])
LEFT JOIN nPMTask] /PMTask] ON ( MPMTask].MCompanyID] = 2) AND ( oPMCostProjectionLine].MTaskID] = tPMTask]..TaskID])
LEFT JOIN kPMCostCode] FPMCostCode] ON ( ePMCostCode].eCompanyID] = 2) AND ( oPMCostProjectionLine].MCostCodeID] = LPMCostCode].dCostCodeID])
LEFT JOIN ePMAccountGroup] OPMAccountGroup] ON ( PPMAccountGroup].NCompanyID] = 2) AND ( oPMCostProjectionLine].MAccountGroupID] = ]PMAccountGroup].]GroupID])
LEFT JOIN (
rInventoryItem] bInventoryItem_InventoryItem]
LEFT JOIN IFSxEquipmentModel]  InventoryItem_FSxEquipmentModel] ON ( EInventoryItem_FSxEquipmentModel].mCompanyID] = 2) AND [InventoryItem_FSxEquipmentModel].mDeletedDatabaseRecord] = 0 AND aInventoryItem_InventoryItem].yInventoryID] = tInventoryItem_FSxEquipmentModel].mInventoryID]
LEFT JOIN rFSxService] FInventoryItem_FSxService] ON ( tInventoryItem_FSxService].tCompanyID] = 2) AND [InventoryItem_FSxService].tDeletedDatabaseRecord] = 0 AND aInventoryItem_InventoryItem].yInventoryID] = tInventoryItem_FSxService].tInventoryID]
)  ON ( tPMCostProjectionLine].MInventoryID] = iInventoryItem_InventoryItem].yInventoryID]) AND ( [InventoryItem_InventoryItem].yCompanyID] = 2) AND [InventoryItem_InventoryItem].yDeletedDatabaseRecord] = 0
WHERE ( rPMCostProjection].(CompanyID] = 2)
GROUP BY ]PMCostProjection].YRevisionID], cPMCostProjection].,Date], tPMCostProjection].,Description], tPMCostProjection].,ClassID], oPMCostProjection].,Status], CASE WHEN @P0 = 1 THEN SPMTask].PTaskCD] ELSE '' END
) ]InnerQuery] OPTION(OPTIMIZE FOR UNKNOWN)