Dinakar Nethi Blog

Dinakar Nethi

Exporting blank report to Excel does not render the headers - MS Reporting Services

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>



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.  

Legacy Comments

poker uitleg
re: Exporting blank report to Excel does not render the headers - MS Reporting Services
I have web based application which used sql reporting services to generate web based reports. Myapplication has dynamic column reports. Entire application get displayed in a pop window ,hence there is no proble with sql injection. But while I do import to excel or any other format it opens another browser where I found the Sql injection (my sql clause).This can be achieved if there is any fascility to use form post method. If this is possible then please let me know the configuration or any other solution.