By using this website, you agree to our Terms of Use (click here)
A pivot table counts the values as stored in the database, I want to count them individually.
In this example, the counts should be as follows:
Value 1 = 3
Value 2 = 2
Value 3 = 3
Is this an Attribute with a Control Type of Multi Select Combo? If so, I've run into this same problem. I think this could be handled in Excel, using Power Query to transform the data, but I can't think of a way to do it in an Acumatica Pivot Table off the top of my head without feeding the Generic Inquiry with a SQL View.
Hi @jbeiler,
I posted a response to your question on community.acumatica.com:
I think you could make this work by doing a cross join between CSAnswers and CSAttributeDetail.
Replace ‘TEST’ with the id of your attribute.
Because of the cross join, each of the possible choices (from CSAttributeDetail) will be compared with each of the answers (from CSAnswers). The InStr function returns the position where the text is found, or 0 if not found.
The hidden parameter makes it easier to build the formula into the Condition tab.
This will filter to return just the records that match.
This is what the results of the Generic Inquiry look like:
Then you can build the pivot something like this:
End result:
Hope this helps!
Laura