One of the default Cognos 10 export to Excel settings is to have the Excel worksheet names take the Cognos page name foillowed by underscore (_) and a sequential number. For example, if we used the dafult page name, exporting the report to Excel would result in a worksheet with the name “Page1_1”. If the report turned out several pages, then the worksheet names would be “Page1_1”, “Page1_2”, “Page1_3” and so on. Obviusly, if we named our Cognos page, for example “Sales”, then the exported Excel worksheet name would be “Sales_1” if there was only one sheet or “Sales_1”, “Sales_2”, “Sales_3” and so on if there were several worksheets. This is useful if a report turns out several pages of output but sometimes that is not what is required. For example, if you had a report with several pages with their own names liike “Europe”, “Asia”, etc. The user may request that no sequential number follows, maybe because it bothers them for an Excel macro or purely for layout purposes.
Of course, you can always resort to an Excel macro to do away with the undesired suffix but there is a way to remove that suffix in Cognos.
The BatchReport service has a parameter that allows for elimination of the suffix. The parameter RSVP.EXCEL.NUMBEREDSHEETNAMES has a default value of "true" and we just need to set it to "false".
From that point on, the Excel worksheets will no longer have the suffix if not needed, that is if the name does not repeat itself, like for example when the number of rows per page excedes the number set in the report. In that case, the sequential numbering will be added starting on _2.
The way you set this parrameter is:
Launch Cognos Administration
Go to system:
Change the view to Services -> BatchReport
Go to the services’ properties
Go to Settings -> Environment
Edit Advanced Settings
Check Override the settings aquired from the parent entry so we can change the parameter
When we click on the checkbox, a list will show up enabling us to enter parameters and their values
Enter the parameter and its value
Accept all changes until you are back to the service screen. We need to restart the service in order for the change to take place.
From that moment on, all the exported Excel worksheets will take the page name with no sequential numbering suffix.
