July 2020 Product Adoption Tip: Retaining Template Formatting and/or Formulas When Updating Dimension Member Selection(s)

[mark.behar]

This tip below appears in the latest CSP newsletter! Does it work when you try it? Got any thoughts to share about this tip? Let us know in the space below!

---

When updating a template and changing a member, has this ever happened to you? You change one member and your formatting and/or formula is changed, and you must redo this work?

The reason behind this is that the formatting and formula references are inherently connected to a dimension member. In “replacing” a member, you first remove the original member. This removes the reference to the formatting/formula related to it. Then you add a new dimension member. On the template this new member has no reference to the formatting and/or formula that existed previously.

Step by step it works like this…

1. Percentage of Sales from the West Region is calculated in cell C6 with formula C5/C4, and Cell B5 is highlighted in red.

2. Building out the template I notice the wrong dimension member is selected in B5. “East Region Sales” is selected but I really meant to select “West Region Sales”. I go to edit layout to remove “East Region Sales”.
   Notice the red highlighted cell has been removed with the removal of “East Region Sales” and the formula reference to the cell that contained “East Region Sales” is now 0

3. I now add “West Region Sales” in place of “East Region Sales”. This new member selection is treated as a brand-new row. Formatting is set to the default (no formatting).

So, what do you do if you need to update your template but don’t want to worry about shifting or disappearing formatting or formula references? Great question! There is a setting in each data view within a template (in design mode) that will ‘lock’ the formatting and/formula to a cell rather than a dimension member. ‘Map row/column member on selection change’ is found in the properties of the data view.

To enable this setting:

1. In Template Studio, select the data view’s drawer, click on the cog icon and select ‘Properties’

2. Go to the ‘Settings’ tab
3. De-select the two ‘map’ options under ‘Design Mode’.

With these setting de-selected, if I repeat my initial steps…

1. Remove “East Region Sales”. Notice the red highlighted cell and formula reference remain.

2. Add “West Region Sales”.

[katrina.kirchner]

I use this feature often!

[tom.samuelson]

This is a great feature to know about. Thank you.

[kyle.sherrick]

This is a very helpful tip. Thank you

[judy.correia]

Glad you find this useful! Keep an eye out each month in your inbox for an email from your CSM that will have a monthly tip from our Product Adoption Team and other reminders or information for CSP customers.

[jamie.laurinovics]

When Rohit showed me this, I was extremely happy!!! Great news!

[mike.dehart]

This has saved a lot of time restoring formatting.

[kristen.compton]

This is great! I will definitely use this I don’t know how many reports I’ve rebuilt in the past. Lately I’ve been right clicking and changing the one thing I want to update and that usually keeps the formatting but I like this way better.

[natassa.robbins]

Great features that I use to save time from restoring format.

[alexander.kaiser]

This is a great tip! I will definitely use it in the future!

[dongmei.zhao]

Glad to learn a new trick!

[shauntae.eiswerth]

Very helpful information! Thanks for sharing

[maribel.parra]

I’ve used this feature for years! a real time saver!

[denise.feece]

interesting and helpful tip! Thanks!

[xiang.li]

I have used this feature, a very useful function.

[imtiazaq (Imtiaz Qureshi)]

Great to see this feature. I will apply it next time if needed.

[dan]

I have used this feature. Very interesting functionality. Thank you for sharing

[rebecca.broaddus]

I use this feature frequently!!

[monte.southwell]

makes our job so much easier!

[chaya.khannukar (Chaya Astekar)]

This reduces so much of your time as you do not have to format again.