Skip to Content
Author's profile photo Former Member

Problem: Applying Number Formats to multiple measures – Client-side

It’s possible to define Number Formats for key-figures in the query design, but users can also override the number formats on measures within Analysis. That means a user can end up with a Crosstab that has Number Formats that differ from the Query’s intended Number Formats. That’s a good feature for the user, as they might want to see more or less detail in the formats, but there doesn’t seem to be any way to easily apply Number Formats to more than one measure at once, and no way to revert all measures to the default (other than by closing the workbook without saving changes and reopening, or resetting the DataSource with VBA.

Analysis (1.4 SP0) lets you change the Number Format on a single cell’s Measure, and applies the Number Format to all cells for that Measure. Analysis appears to let you change number formats for up to 100 cells at once, so you should be able to apply number formats to up to 100 measures at once, but there are 2 problems:

1. The Number Format is only applied to the first/active cell in the selection, so in reality, it is only possible to vary the Number Formats one measure at a time.

2. Even if the Number Formats were applied to all selected measures, the limitation of 100 concurrent cells restricts this capability, as some Crosstabs will have more than 100 measures.

Do you experience the same issues, or is this fixed in a newer version of Analysis, or is it maybe a problem with my Query?

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Alexey Zimin
      Alexey Zimin

      Hi  Andrew!

      Have the same problem (1.4SP5). We have more than 20 KF in query. very annoying :-((

      Regards, Alex

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Alex...

      I've seen users struggle with queries that have hundreds of key figures, so 20 sounds quite manageable!

      Since I wrote that post, I've upgraded to SP5, and there is now an option to Reset a DataSource (in the context menu), so you can revert to server defaults, but that means you also revert to other DataSource properties, such as dimension layout/position etc.)

      That context menu option is present in SP4 too, but you have to unhide it in the Analysis Options dialog, (but I think it's only unhidable if you have a recent version of BEx.

      I didn't have a recent enough version of BEx installed, so I couldn't unhide the Reset functionality via the options (in SP4). So, I used VBA and CommandBars collection to expose it.... See Granular Control of the Analysis Context Menu - Part 1

      And in Granular Control of the Analysis Context Menu - Part 4, you'll find the Control tag for "Reset Data Source" of "pio78"