Dynamic SUM Cell Range

[Former Member]

Hello,

I need to create a dynamic SUM that will accommodate column members changing (added or removed) and the cell (row) that contains this calculation will shift up and down the report because the users will be selecting different hierarchies and drill down.

So cell D280 should go all the way across to the right as the column members expand and collapse. Also, when the hierarchy changes this calculation could move to cell D80 (as an example). How can this be accomplished? I got pretty close using a local member formula but couldn't get a consist result.

[alessio_pulejo2]

Have you tried to use EPMCOPYRANGE?

You can apply EPMCOPYRANGE in a new report, that shares the column axis with your current report.

[former_member186338]

Sorry, but I don't understand the logic of your SUM... In the sample it's not actually a sum, just single member...

[Former Member]

SUM(D281:D289)

[former_member186338]

And you don't have parent member?

May be it's better to use dimension member formula?

Please explain the business case!

[Former Member]

Sorry for the short response - Explorer was crashing on me. If I could hardcode the member IDs from column A, there are like 7 or 8, that would help my formula stay consist. Unfortunately this wouldn't function well with a parent because they are individual members. I'm thinking about that dimension member formula, that might be a good option, I'm just not sure how to mock one up.

[former_member186338]

"Unfortunately this wouldn't function well with a parent because they are individual members" - not clear!

The best way is to have parent in the hierarchy. If it's absolutely not possible then you can use dimension member formula with a direct sum of required members.

P.S. Strange idea to change a normal name to some abbreviation...

[Former Member]

The reason behind this is because we are using a string to create a custom hierarchy because of the way business wants to present their information. DEP(IS111000),IS111000,,IS112100,IS112200,IS112210,IS112220,IS112300,IS112400,IS112410,IS112420,IS112500,IS112600,IS112700,IS112000,,IS110000,,DEP(IS121000),IS121000,,IS122000,,IS123000,,IS124000,,IS100000,,,408105,408119,408153,408172,408174,408307,408309,408311,664219 Just noticed the name

[former_member186338]

Absolutely strange idea

- You can reorganize your existing hierarchy (changing order of children on the same level)

- You can create extra hierarchy

But you need to have parents!

[former_member186338]

By the way the link to your profile is /people/andrey.leskiv

May be it's better to register a new user?