Thanks for explaining your use case in further detail! I definitely agree with you not approaching the pivot tables in that way since that does not apply well with displaying the financial data. In terms of how many rows can be pulled, users can edit the amount in page size like this and it can go up to 10000.
If that doesn’t work, I would definitely create a support ticket so that AWS Support can look into your case further and potentially see if pivot table behavior can be modified.