By using this website, you agree to our Terms of Use (click here)
We want to create a GI where we pull GL transaction detail and filter the results with a parameter. The parameter would be a valid segment value from a GL subAccount.
Two challenges:
How to link a parameter to a schema for a specific GL Subaccount Segment (example: SubAccount Segment 3)
How to build the condition to filter by the parameter value. (example: Parameter = {SegPrompt} | Conditions tab: datafield = [SubAccount], condition = equals, value = "???-???-"&{SegPrompt}&"-??")
Any ideas? Am I making this too hard and overlooking something obvious?
Hmmm, good one.
You could use GL.Sub.SubCD to get the full subaccount functionality in the parameter, but you'd have to hit F3 in each subaccount segment and choose something, which isn't what you want. You just want to choose one segment.
I'm not sure how to get just one segment. I tried the SegmentValue table, but that didn't work.
One way I could think of would be to manually duplicate the Segment values into a Combobox parameter (not ideal), then use the Substring function to compare the parameter to only the portion of the Subaccount value that you care about.
This problem has been bugging me for ages. Previously I'd written some nasty GIs with complicated criteria to do matching on ranges of subaccounts. What i really wanted to do was apply criteria to specific segment values, looking for full or partial matches on those segments.
@timrodman I initially tried your combobox solution, which worked but is a little ugly as the combox parameters appear when the GI is run. My next iteration was to use CS.SegmentValue table and apply criteria to that. This works much better as the criteria can be applied directly on the Conditions tab. Then relate SegmentValue to GL.Sub - the trick here is to use a Substring in the join criteria so that you match the specific segment value to it's position within the concatenated Subaccount.
Example:
In this instance ItemSegmentValue has been filtered on DimensionID = SUBACCOUNT and SegmentID = 1, in this case it is our Product Segment which is 3 characters long.
Using this approach I've managed to do a complete rewrite of a really nasty GI I wrote which used pattern matching on the complete Subaccount. Thanks Tim for your inspiration!
I'm seconding @royce-lithgo 's method. I use this to join back onto the SegmentValue table once for each subsegment, I just don't think there is any other way to handle it. Although, I lazily use a nested LEFT and RIGHT, when Substring is the superior method.
Nice @royce-lithgo! Thanks for sharing.
You know it's good when GI wizard @wyatt-erp agrees with it!
I just noticed something that, while not really HELPFUL for this discussion, is at least RELEVANT for this discussion.
Thought I'd point it out.
When you add a Subaccount to a Pivot Table, you can choose which Segment you want to display.
