I am often building pivot table reports for users for products or customers. Users often request that additional information be displayed within the pivot table for those row items. For example, in a pivot table showing item sales by year, they want me to include a column that shows what TYPE of item it is or who the author is. These attributes are additional properties of the item, and they are 1:1 for each item. I don't want them to be grouped. How can I do this?
For example This is a pivot of books by year. How do I add the author attribute without grouping it?
Title | Author | 2019 | 2020 | 2021 | Totals
Awesome Book | J. T. Joel | 100 | 120 | 90 | 320
Crappy Book | Karl Klapp | 50 | 40 | 10 | 100
I'm used to doing this pretty easily in SQL, so I'm confused as to why it is hard in this context? Probably I have some fundamental misunderstanding about how pivots work, but maybe I'm just missing something obvious?
Thanks.