David Hockenbroch · Jul 20

Changes to Pivot Tables at Drill Down Levels

I work on an ERP system and am trying to set up a dashboard according to a customer request. It's a pivot table with a few controls and filters, nothing too difficult, but I'm having some issues with drill down.

We've got item sales data. Quantity sold, sales dollars, cost, gross profit as both dollars and a percentage. It's starting out with one row per item. From there, we can drill down to see which salesmen have sold that item and get all of those numbers for each salesman. After that, we can drill down to that salesman's customer accounts and see which of their customers have been buying the item, too.

I have the initial pivot table sorting the rows by quantity sold, but when I drill down, it doesn't stay sorted. I have to click column headers to redo the sort after every drill down operation. Also, when I drill down to the customer level, I can see the salesman's name between the drill down buttons, but the item number is no longer visible, so if the pivot is printed, it will be lacking some important context.

I thought maybe I could go into the analyzer and change the sort orders at various drill levels there, then the save the pivot table, but that doesn't keep those changes.

Is there a way to fix those two things?

Product version: Caché 2018.1
3 0 3 52
Log in or sign up to continue

Hi David,

When you selected the "Sort members" option in the pivot table, did you do it from the gear icon next to the "Rows" header, or from the one next to the specific level that you had put on rows? On IRIS 2019.1, I'm finding that the sort order remains when I drill down if I do the former, but not if I do the latter.

Sam, thanks, that solves the first part! Any idea how to make it show the item number somewhere no matter what drill down level I'm at?

Depending on how specific you need to make your sorting, you can also modify the custom drilldown spec with ORDER functions. If needed, you could change the measure and the direction for each level. For example:

ORDER([Product].[P1].[Product Category].Members,Measures.[%COUNT],BDESC)
ORDER([Channel].[H1].[Channel Name].Members,Measures.[%COUNT],BDESC)