Exporting blank report to Excel does not render the headers - MS Reporting Services
I digged the RS books online and I found that there is a noRows property in the report which can be used to display some message when there is no data...
From Books Online :
Empty Data Regions
When the dataset for a data region returns no data, the data region is not rendered. Instead, a text box is rendered that displays the value of the NoRows property. You can edit the NoRows property in the Property window of Report Designer. The appearance properties for the data region (for example, Color, Font, and Padding) apply to the NoRows text box.
I thought of concatenating all the column names and use the NoRows property but that will be exported as 1 column rather than individual columns. I found a couple of hacks for this. Thought it could be useful for someone else...
(1) I can put the columns in the Page header ( Since I can't put table in the header I can throw in some textboxes) with the same width as the column below in the body section. This is a feasible solution.
(2) Modify the stored proc to something like :
SELECT col1, col2...
FROM <table>
UNION ALL
SELECT NULL, NULL
This will return a blank row at the end of the report and RS treats this as a row. So when exported we do get a row with headers and a blank row.
(3) This is not very efficient but it works...
Get the results of the stored proc into a temp table. If there are no rows in the temp table then do a UNION with nulls (like in (2) above) and return the result set else do a SELECT * FROM temptable. This can affect the performance coz it uses temp tables...but for cases where the Report design cannot be modified and the stored proc cannot be modified ..this could be handy.
Edit :
One of my colleagues found another hack to get around this and here's how it goes : (4) Create a table with just header and delete the detail and footer sections. Throw in all the columns that need to be in the report. Then create another table with just the detail section and put the field values appropriately. Finally line them up against each other so they appear to be from the same table header/detail sections. This is probably the most easiest and simplest of all the solutions.
posted @ Wednesday, March 28, 2007 2:53 PM