Recently I had been requested by a client to look into one of their SSRS reports issues. They had one report with a text box that contained some padding from the left side. All seems to be OK, report showed indented text values both in RPL and PDF rendering extensions; however when the report was exported to Excel those indentions were lost.
I removed the Left Padding information and substituted it with hard coded extra spaces in text box expression. This tweak didn't solve the problem; now my Report server didn't show those extra spaces (i.e. Excel Exported version of the report didn't show them either).
Then I removed extra spaces from the SSRS text box expression and added those spaces into a data set that sourced that text box. Now Report Server showed all the indented text boxes, however Excel exported report didn't show those extra spaces again; what ?!
Let’s be crazy, and then I removed all extra spaces from data query and built my text box expression using a conditional check for Globals!RenderFormat.Name="EXCELOPENXML" to only add extra spaces for my text box when a report gets exported to Excel; and this… solved the problem: all RPL, PDF and EXCELOPENXML versions of the reports showed me the left padded text box as it was intended.
Then I noticed another text box attribute (LeftIndent) that I didn’t try to adjust. After adjusting the LeftIndent value all my rendering extensions worked perfectly, so I no longer needed to use the conditional check for Excel rendering extension.
It’s funny that I’ve dedicated the whole blog post to EXCELOPENXML rendering extension which I didn’t need to check at all; but … now I know how to solve this problem differently 🙂
I removed the Left Padding information and substituted it with hard coded extra spaces in text box expression. This tweak didn't solve the problem; now my Report server didn't show those extra spaces (i.e. Excel Exported version of the report didn't show them either).
Then I removed extra spaces from the SSRS text box expression and added those spaces into a data set that sourced that text box. Now Report Server showed all the indented text boxes, however Excel exported report didn't show those extra spaces again; what ?!
Let’s be crazy, and then I removed all extra spaces from data query and built my text box expression using a conditional check for Globals!RenderFormat.Name="EXCELOPENXML" to only add extra spaces for my text box when a report gets exported to Excel; and this… solved the problem: all RPL, PDF and EXCELOPENXML versions of the reports showed me the left padded text box as it was intended.
Then I noticed another text box attribute (LeftIndent) that I didn’t try to adjust. After adjusting the LeftIndent value all my rendering extensions worked perfectly, so I no longer needed to use the conditional check for Excel rendering extension.
It’s funny that I’ve dedicated the whole blog post to EXCELOPENXML rendering extension which I didn’t need to check at all; but … now I know how to solve this problem differently 🙂
