Users may find that when cells start with the characters + (addition), - (hyphen), = (equal), or @ (at sign) they export from Web with an unexpected single quotation mark.
For example, the report below is exported with an unexpected single quotation:
This is working as designed since having operators such as +, -, =, or @ would be a security vulnerability on Excel allowing to pass functions or macros as well.
For Excel export, only the data with text/string data types will be affected. Numeric data is not affected.
For CSV export, both text/string and numeric data are affected.
It is recommended to avoid using these characters if you want to remove the single quotation mark. Instead, use the data wrangling tool to find and replace all the characters + (addition), - (hyphen), = (equal), and @ (at sign) to a suitable substitute that will not interfere with Excel's formatting.
As a solution, users can add the following registry setting to restore the old response be desired.
Registry key Avoid_Code_Injection (dword).
However, the security vulnerability risk will still be persistent in implementing this. It is strongly recommend to adjust the data to omit the characters "+" , "-", "=", "@" in the attribute data.
Adding the Registry Setting on Linux
Create the following entry in the MSIReg.reg file:
[HKEY_LOCAL_MACHINE\SOFTWARE\MicroStrategy\DSS Suite\Export Engine]"Avoid_Code_Injection"=dword:00000000
Adding the Registry Setting on Windows
Add Avoid_Code_Injection to path
"HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\MicroStrategy\DSS Suite\Export Engine"
By default, the key Export Engine doesn't exist. Manually create it first if it is not there.
Set the value to 0, 1, 2, 3, 0x4, or 0x6 for your case, as demonstrated below:
Notes
The following table illustrates whether additional characted will be added depending on Avoid_Code_Injection value, type of export, and presense of an additonal | character.
Avoid_Code_Injection value | CSV - string begins with +, -, =, | CSV - string begins with +, -, =, | Excel - string begins with +, -, |
|---|---|---|---|
not set | N | Y | Y |
0 | N | N | N |
1 | N | Y | N |
2 | N | N | Y |
3 | N | Y | Y |
4 | Y | Y | N |
5 | Y | Y | N |
6 | Y | Y | Y |
7 | Y | Y | N |
Y - Apostrophe added
N - No apostrophe
The Registry key for Export Engine should be applied on Intelligence Server machine.
1. Select Encode CSV for exporting to Excel from Web Preference > Project Defaults > Export Report:
2. Export to a .csv file:
3. Double click the cells with single-quote and the single-quote will hide automatically:
Other than opening the .csv file via Excel, it's possible to try defining the negative number format as (). In this way, the negative numbers won't have prefix '.
KB439219
