0

In a new sql server report 2012 (ssrs 2012), I have a dashboard report where users export various pages to excel. The users want to be able to sort the data on their excel spreadsheets. However they are getting an error message about merged cells.

To svolve the problem so far, I have changed some test ssrs 20012 reports where there are no report headers. The only type of headers the reports have are column headers. When there are only columns headers, I can export the reports to excel and sort all the data columns.

What I have removed in the test ssrs 2012 reports is the report header that is the same width as the detail lines of the reports. In addition, I have removed the 'sub category' report headers that together make up the same width as the detail lines.

The original report looked like the following:

                        Customer Purchase Order

    customer name: customer name1
    customer number: customer number1

    item       item               quantity       unit
    number     description         ordered        price
     1234         item_kind           2           $10

Can you tell me if there is a way that I can keep any of the existing report headers? If so, can you tell me how I can keep the report headers?

If I am not able to keep the report headers, can you tell me how the customer name, Customer number and name of the report can be exported to excel? The only thing I can think of is to make the additional information as detail line information that. Thus the detail line infomation would contain the following: report name, customer name, customer number, item number, item description, quantity ordered, unit price.

Thus can you tell me if you have any ideas of how I can solve my problem of exporting the ssrs 2012 reports to excel and the users can sort the data?

user1816979
  • 511
  • 4
  • 13
  • 25
  • Can the users export the report as a CSV rather then Excel file? That will get rid of the merged cell issue. – mmarie Nov 10 '13 at 20:13
  • I will ask if that is an option. Can the users export the report as CSV originally and then export the data to excel after that from the comma delimited file? – user1816979 Nov 11 '13 at 02:19
  • The best way is to add an Excel rendering option that strips out the header as detailed here: [SSRS 2005 Set SimplePageHeaders on the report instead of the server?](http://stackoverflow.com/questions/3048363/ssrs-2005-set-simplepageheaders-on-the-report-instead-of-the-server) – Chris Latta Nov 11 '13 at 02:46
  • This would affect every SSRS report on the server. Thus is there a way to allow the existing excel rendering and also allow the user to set the simplepageheaders as you indicated in this link? – user1816979 Nov 11 '13 at 15:14
  • @user1816979 SSRS has the functionality to export a CSV already built in. Excel can open CSV files. Users can continue to work with them as CSV files or save them as Excel files by doing their normal Save As and selecting the file type as .xlsx. – mmarie Nov 11 '13 at 16:08
  • Are you saying that I user can save the file as a CSV file. The user can then save the same file in excel with the save as feature? Would the merged cell problem show up again? – user1816979 Nov 12 '13 at 17:00

1 Answers1

0

As the other users have stated if you export to csv instead of excel it will flatten the data and remove the formatting so the header info should appear as extra columns in your exported data, repeated across each row. You can then open the csv in excel.

Another option available in SSRS 2012 (and possibly 2008) is to use built in variable "RenderFormat.Name" to control what does and does not get exported. Add the customer number and name to the tablix as columns and then set the hidden property on the header textboxes to hide them on an excel export and show otherwise then do the opposite on your newly added fields.

The hidden property for the header items would look like this:

=iif(Globals!RenderFormat.Name = "EXCEL", True, False)

And for the columns switch the true and false round:

=iif(Globals!RenderFormat.Name = "EXCEL", False, True)

Richard
  • 1,124
  • 10
  • 13