How to Copy/Paste into a Reporting Tree (and Live to Tell About It)

Use Excel to Create Reporting Trees in Management Reporter

Finicky. Persnickety. Difficult. Sweet. All adjectives I’ve used to describe FRx reporting trees. Usually when trying to build a tree in Excel and copy paste into a tree.

But the reporting trees in Management Reporter are waaayyy better than the ones in FRx. Far less finicky.

When you need a tree, the first approach should be to auto build it using Edit>Insert Reporting Units from Dimensions. If there’s lots of data, it’s the fastest safest surest bet. Same thing if you’re only using a single dimension.

So why would you want to build a tree in Excel anyway?

To get more reporting units when you’re combining 2 or more dimensions. In the case of someone who hasn’t gone live yet, there’s usually not much data. And the auto build doesn’t yield many reporting units at all because it only combines segments with posted data.

There’s even a comment in the Insert Reporting Units dialog that says “when building a reporting tree using multiple dimensions the built reporting units will only contain values from posted accounts”.

Last week, I needed to build a tree with hundreds of reporting units from two different dimensions. The auto-build tree gave me about 6 reporting units, gotta love it. It would’ve taken all day to manually build what I needed. So that’s when I turned to excel.

Caveat—if you need just a single dimension, try the auto build first and you should get every value.

So back to the tree I was building. My tree needed multiple dimensions and numerous rollups.

Pasting in from Excel, I got a red exclamation point in Column E (which was blank! hello!) along with an obscure, off-point message that “The same row definition must be selected for all reporting units.” (For techies, this has something to do with the behind-the-scenes H-code, but it’d be great if Microsoft could change the error message to something more descriptive.)

So here’s what you can do to avoid or fix this error:

1. Paste in batches; don’t try to do every unit at the same time unless it’s a short tree or you’re a glutton for punishment
2. Make sure you have enough empty rows in the tree (I don’t know that this is necessary but I didn’t want to test it)
3. Build it in Excel
4. Copy from Excel
5. Paste into the tree
6. In the left-hand pane, highlight all the units you just pasted in and Promote all the way to the left
7. BTW, I love the Promote / Demote arrows circled in the screenshot below. I usually right click, but when you have multiple Promotes to do at once, the arrow is much faster.
8. Delete and reenter just the first unit you pasted in (this is key to getting rid of the error if you already have it)
9. The rest of this work will be in the left-hand pane, called “Reporting Tree Definition” below
10. That unit you just reentered? Highlight it on the left and Promote it all the way to the left
11. Highlight every unit underneath the unit you just reentered
12. Drag them all to the unit you just reentered. I don’t care that they shouldn’t all roll up to it; I’ll fix that in a minute.
13. While you’re at it, notice that you can select and drag more than one reporting unit at a time! This makes me very happy.
14. Now Save! The red error will disappear
15. Now you can fix the rest of the hierarchy, making sure the rollups are correct
16. Make sure the top unit in the hierarchy is correct
17. Save
18. Go back to excel and prepare the next ‘batch’
19. Repeat

This looks like a lot of steps but it’s so much faster than trying to build this manually. If you have trouble, don’t email me :)…just start over and try it again.

I’m guessing consultants will like this post better than end users, but hope this saves everyone some time at some point.

Cheers! Jan

PS. Alternatively, you can paste in a long list of reporting units and add the rollups manually. Might be easier and simpler in some situations.