If you slice, snapshot or 'active form' data out of a cube and bring it to Excel, the row dimensions (if any) do not have a header. Given the layout of dimensions in titles, rows, columns, come up with a solution to determine what are the row dimension names and fill in the names, just above the first element in the row area. Not easy I presume.

Wim Gielis wrote:Here's a challenge for those of us with too much time on their hands.

If you slice, snapshot or 'active form' data out of a cube and bring it to Excel, the row dimensions (if any) do not have a header. Given the layout of dimensions in titles, rows, columns, come up with a solution to determine what are the row dimension names and fill in the names, just above the first element in the row area. Not easy I presume.

I know that you and active forms hate each other with a passion but I would think that that would be the only type where this would be (relatively) easy; you'd just need a formula or function to parse the relevant TM1RptRow function. For the others, short of some convoluted and processor cycle-wasting method that looks up the elements in the rows and compares them to the cube's dimensions (and this assumes that there aren't elements with the same names in multiple dimensions), the only other way I can think of is to parse out one of the DBRW formulas and read the dimension from the argument position. You could potentially use a UDF for this but it wouldn't be pretty... (Nor would it work with a snapshot, obviously.)

True Steve, we could do that. The idea could be to have a menu item in the ribbon in Excel that would enter these missing headers.

When you snapshot or slice to Excel, it's often to check with for example an autofilter or a pivot table. Then you would need the headers filled in.

If I would look in the Server Explorer, I wouldn't have called it a challenge

I guess that in VBA we can come up with logic to determine what are the missing dimensions, but there is always a slight possibility that elements with the same name occur in multiple dimensions. In that case, only a DBRW formula would help us (which isn't there in a snapshot).

So yes it won't be easy and I leave it to those who want and have the time for it

It should not be too hard actually. All the required ingredients are available, just a matter of mixing them in the right manner to get to your required result.

With slice and active form you have the DBRW formulas which you can have a look at to determine the dimensions in your 'row columns'. No issues there.
In case of a snapshot you have all the information, except for the dimension names on your 'row columns'.

So, the next steps will get you what you want:

Check B1 to find out the relevant cube

Check A2 to Ax to find out which dimensions are not on the 'row columns', now you know the missing dimension/column names

Check if the first element of your first 'row column' (Arx) is in any of the missing dimensions.

In case of just one match, yay. On to the next column.

In case of multiple matches, check for Ar+1 until you run out of populated rows. Odds are you will end up with a unique dimension name before this happens.

In case of no unique dimension name yet, continue with the next column and repeat. You should end up with a smaller list of potential dimensions.

Continue checking until you can not longer find any uniques. This is the best you can do, yet in 99.9% of the cases you should be done now.

In my opinion this should not be too complex to write (unless you're also making a total mess of your column headers, which again might complicate stuff) but I cannot be bothered at the moment to invest time into this