Solved

Generic Inquiry Unable to Pivot Table

  • 19 September 2023
  • 17 replies
  • 190 views
Michaelh
Rank
Userlevel 5
Badge

I have a GI that refuses to output a Pivot Table. What types of things mght block pivot tables? I have full admin rights in this system.

 

I can MAKE the pivot, but the display is empty. Here is an example:
 

 

 

Without any parameters etc, I have data and I know that every record has a BRANCH associated with it, so at the very least, I should get a list of branches here. I have the dates rounding to YEAR, so I should see something like 2019 <> 2020 <> 2021 <> 2022 <> 2023 across the columns too. But no matter what fields I use to make the pivot, I get this empty pivot table.
 

Anyone know what types of things inside a GI might create a block on pivots like this? Side Panels? Groupings? Mixed Aggregates?

 

Thanks,

icon

Best answer by Michaelh 23 September 2023, 01:15

View original
Permanent Newb, don't let the posts fool you.

17 replies

C
Rank
Userlevel 7
Badge +9

 

 

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

  1. Generic Inquiry

 

Generic Inquiry result (AR Invoices)
 

 

  1. Pivot configuration:
Pivot Configuration for the Generic Inquiry

3. Pivot table results showing the results.

Pivot results showing the Invoice totals by branch

 

 

2 Attachments
Chandra M
C
Rank
Userlevel 7
Badge +9

Hi @Michael Hansen Kindly review.

Chandra M
Michaelh
Rank
Userlevel 5
Badge

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?

Permanent Newb, don't let the posts fool you.
C
Rank
Userlevel 7
Badge +9

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.

 

Chandra M
Michaelh
Rank
Userlevel 5
Badge

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”.

Permanent Newb, don't let the posts fool you.
C
Rank
Userlevel 7
Badge +9

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

 

 

Chandra M
C
Rank
Userlevel 7
Badge +9

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

Chandra M
C
Rank
Userlevel 7
Badge +9

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:

 

Chandra M
C
Rank
Userlevel 7
Badge +9

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

Chandra M
Michaelh
Rank
Userlevel 5
Badge

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. 

Permanent Newb, don't let the posts fool you.
Michaelh
Rank
Userlevel 5
Badge

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.

1 Attachment
Permanent Newb, don't let the posts fool you.
C
Rank
Userlevel 7
Badge +9

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

Chandra M
Michaelh
Rank
Userlevel 5
Badge

I can make a GI with or without the custom tables and get pivots to work. It seems to be JUST this inquiry in specific. I suppose I can try rebuilding it one step at a time and testing at every step. I was just hoping we had a commonly known issue or something. 

Permanent Newb, don't let the posts fool you.
C
Rank
Userlevel 7
Badge +9

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.

Chandra M
Chris Hackett
Rank
Userlevel 7
Badge

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

Chris ~ Let us know how we're doing: https://forms.office.com/r/xmJUdZ1QE5 - A member helped you, please *Like* their post. A member provided an answer to your question, please mark it as *Best Answer*.
D
Rank
Userlevel 4
Badge +1

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.

R
Rank

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. 

Reply


Terms & ConditionsCookie settings
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