Question

Using Parameter in GI Grouping

  • 31 January 2023
  • 9 replies
  • 357 views

Userlevel 7
Badge +8

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?

@Gabriel Michaud

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)

 


9 replies

Userlevel 7
Badge +10

You can’t setup conditional grouping this way - you will need to setup different GIs with a different grouping setup.

Userlevel 7
Badge +8

  @Gabriel Michaud  Thanks for the reply. I have 24 potential combinations. That is why I was trying to use dynamic grouping. Otherwise, I have to create 24 GIs.

On a side note it is funny to have parameters available in GI grouping but not be able to use them. 😵

Badge +10

Would it work to group by a different field, essentially not grouping, instead of ‘’ ?

Userlevel 7
Badge +8

I could catch the question but typical grouping by fields work. I want to perform conditional grouping. When I creat formula it passes the validity check but runtime platform can’t pass the parameter to body of group by

Userlevel 5
Badge

Anyone have a solution on this? I have a report that MIGHT want sub-groups and might NOT want subgroups. You would think calling parameters in the report would do the trick since those are clearly pre-processing values, but for some reason we don’t allow this.

My grouping is set as: =iif(@Color=’1’,[InventoryItem.COLOR2_Attributes],’’)

This should return an empty set if the Color box is unchecked. Instead it returns a big fat error. How do we go about fixing this without having MASSIVE report bloat (8 iterations here). This also compounds if a user wants to change anything in the report, since it would need a revision in 8 reports.

Is there any way to force conditional groupings in Report Designer using parameters or something similar? 

Userlevel 7
Badge +8

@Michael Hansen GI engine is not customizable (at least not recommended) so you can’t force GI but in RD sure you can pass parameters and show/hides sections (groups/details) as you wish.

Userlevel 5
Badge

Oh, I can hide a SECTION, but I’m asking to block a GROUPING.

Example: Grouped on Length and Color

5foot

      Green

6foot

       Green

       Blue

 

If I hide the segment for “length” I would get: Grouped on Length and Color, with Length HIDDEN

Green

Green

Blue

 

I want my output to be: Grouped on Color (ignore length altogether)
Green

Blue

 

I need to not only HIDE the section about the length, but also stop that section’s grouping. This should be easy with a Grouping Expression like:

=iif(@Length=’1’, [InventoryItem.Length], ‘’)   

But this gives me a big fat error. This is a PARAMETER, literally the type of object we are supposed to pass to the report to get things done, no idea why it hates me or how to block this grouping.

Userlevel 7
Badge +8

You will need to show grouped values on one section and detailed one on another. Then depending on the passed parameter you can hide either one as you wish. When you hide the Detailed Section, then your result will be grouped and when you hide the Grouped Section, then your result will be like not grouped.

Userlevel 5
Badge

Dang, this is 8 distinct segments then. I was worried that might be our only out. I hope they add using parameters to groupings. 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved