Auto Group content from Cube via Pivot Table

[SisterMable]

Hi there. I have a cube that delivers data via a connection to a pivot table. The data looks something like this:


Country1 name | Country1 Total 1 | Country1 Total 2 | Country1 Total 3 | Country1 Grand Total
Sub-item X | Sub-item X1 ttl | Sub-item X2 ttl | Sub-item X3 ttl | Sub-item X Grand Total
Sub-item Y | Sub-item Y1 ttl | Sub-item Y2 ttl | Sub-item Y3 ttl | Sub-item Y Grand Total
Sub-item Z | Sub-item Z1 ttl | Sub-item Z2 ttl | Sub-item Z3 ttl | Sub-item Z Grand Total
Country2 name | Country2 Total 1 | Country2 Total 2 | Country2 Total 3 | Country2 Grand Total
Sub-item X | Sub-item X1 ttl | Sub-item X2 ttl | Sub-item X3 ttl | Sub-item X Grand Total
Sub-item Y | Sub-item Y1 ttl | Sub-item Y2 ttl | Sub-item Y3 ttl | Sub-item Y Grand Total
Sub-item Z | Sub-item Z1 ttl | Sub-item Z2 ttl | Sub-item Z3 ttl | Sub-item Z Grand Total
Grand Totals across bottom


If I use commands to collapse entries under the Country level to make the report I send out neat, the end users, who have no access to the original cube, can't drill down into the report to see detailed data as desired (and they do often drill in to see activity from various countries).


I've tried subtotal, but that doesn't really apply (and the option is actually greyed out).


My only option at this point seems to be to hand group all the sub-items rows manually, but it takes forever, and this is a daily report. I'm trying to reduce manual work, not increase it!


There has to be a better way.


Can someone please provide a golden ticket?