If you find Snapshot to Excel appears to hang at the ‘Currently Publishing…’ stage, consider the length of the element names in the title dimensions you have selected to import.

The critical dimension is the last dimension on the list which has been selected – as this determines the name of the tabs in Excel.

The first point to consider is how the names of the tabs in Excel are generated by the ‘Snapshot to Excel’ process:

If only one dimension has been selected, the tab names in Excel will be:
• First_Element_Name
• Second_Element_Name
• Third_Element_Name
• And so on.

If more than one dimension is selected, the names of the elements in the other dimensions do not feature in the tab names at all. The names of the elements in the last dimension are repeated in blocks – with each block having a suffix added (‘_1’, ‘_2’, ‘_3’, etc) – to provide the requisite number of tabs required:
• First_Element_Name
• Second_Element_Name
• Third_Element_Name
• And so on
• First_Element_Name_1
• Second_Element_Name_1
• Third_Element_Name_1
• And so on_1
• First_Element_Name_2
• Second_Element_Name_2
• Third_Element_Name_2
• And so on_2.

Secondly, Microsoft Excel has a limit of how many characters are allowed in a tab name: 31. If the ‘Snapshot to Excel’ process creates a tab name which is longer than 31 characters, the tab name is truncated and the tab name made to end with a tilde, ~.

Now Microsoft Excel also has a rule which says that tab names cannot be duplicated. Because of the way Snapshot to Excel creates the tab names (creating repeating blocks of names which only differ at the end of the name), and the fact that long names get truncated, if any element names in the dimension selected last is over 30 characters in length it will result in tabs with duplicate names and the process will fail to open in Excel (leaving it hanging at the ‘Currently Publishing…’ stage).

Resolution

To resolve the issue, re-arrange the order of the dimensions in the Context panel in the application tab of your TM1 Application: The order in which the selection list of title dimensions to export is presented is determined by the order they are arranged, left to right, in the Context panel. So, by ensuring that any dimension with long names is always left of at least one other dimension which you also want to be included in your output, the tab names should not duplicate and Excel will fire up.

Back to blog