Generic Inquiry Unable to Pivot Table

Hi @Michael Hansen Please find attachments in which the Picot works for me.

1. Generic Inquiry

Generic Inquiry result (AR Invoices)
 

2. Pivot configuration:

3. Pivot table results showing the results.

Hi @Michael Hansen Kindly review.

Thank you for your reply @ChandraM. I know how to make a pivot. I’m curious what things might block a pivot from functioning. This is the ONLY GI I know of where I cannot make a Pivot. There are records for this GI and of course there is a Branch for every one of those records along with a date. I’m curious what inside of a GI could possibly block the pivots from functioning?

Hi @Michael Hansen On the below screenshot you posted, I see the field caption as generated. Please try having a simple “Caption” on the Results Grid of the Generic Inquiry.

Can you describe a simple caption please? Is it the whitespace that is the issue? If I do not apply a caption, the full name is a garbled mess because it is a formula. I have it captioned “Total Invoiced”.

Hi @Michael Hansen I just verified a GI with a formula field. Thats working fine. So let me check the other possibilities..

Hi @Michael Hansen I think I figured a possibility. Please stay and will keep you posted in couple of minutes..

Hi @Michael Hansen The reason could be that the Branch Access Roles are not assigned to the current user. 

Please find the below testing.

Branch Access role removed:

Result when the Branch Access role is removed:

Result after the branch Access role is provided to the user:

Hi @Michael Hansen Just seen your comment that your GI is showing the data, so that might be a different issue than the Branch access.

Is it possible to post the GI here.

Thanks

That empty table you showed is exactly how mine are outputting (regardless of what fields I choose for row/column/value). I have full branch access rights. Thank you for that one, it got me reviewing my access and membership roles, which I didn’t validate. But, if I don’t have access to the objects, I shouldn’t see the raw data, let alone the pivots. Below is some of the raw data.

For example, if I choose STATUS by END DATE showing the values for PAYMENTS. I get the same empty pivot table:

I have a feeling it is the GI itself, because I can pivot off Invoices, Sales Orders, the GL, etc.  Only this GI is giving me a hard time. I will admit, it has custom tables supporting it, but I can join those tables in a new inquiry and get a pivot just fine. I also tried copying this inquiry and making a pivot, still getting an empty table. 

Sorry, just saw your last post, I can't post the GI here because I’m using 3 custom tables in it. Although I’m happy to post it if that won’t matter for your purposes.

Edit: attached the GI just in case.

Hi @Michael Hansen Did you try creating a seperate GI without the Custom table and did it work? Please advise.

Hi @Michael Hansen Thanks for the update. Yes your next steps sounds good. Possibily verify the DAC definitions on the Custom Table. And if that doesn't work and since you have custom tables, a support case would help.

Thanks for your patience.

Hi @Michael Hansen were you able to find a solution? Thank you!

Acumatica pivot tables do not handle DATES in the VALUES area of a pivot table.  I just tried and it will not display.  I converted the dates to integers in the GI, and that works, but now I cannot figure out how to get the pivot table to display the integer values as dates again.

Just thinking about how the GI calculates, there are some fields that cannot be Aggregated as a Sum. Is it possibly the Schema that is applied to the Results Grid is not Sum compatible? You could try changing the Pivot aggregate to a Min/Max or validating the GI Schema.