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>

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

Print

Comments on this entry:

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

Left by poker uitleg at 12/17/2009 3:48 AM
Gravatar
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.

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

Left by north face jackets on sale at 10/23/2010 8:35 AM
Gravatar
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.

columbia jackets | snow boots | snow boots for women | columbia sportswear | columbia sportswear outlet | cheap north face jackets | the north face outlet | mac makeup | cheap makeup

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

Left by nexium vs prilosec at 3/12/2012 8:41 AM
Gravatar
Myapplication has dynamic column reports. Entire application get displayed in a pop window ,hence there is no proble with sql injection.
nexium vs prilosec

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

Left by Vicodin dosage at 3/13/2012 6:08 AM
Gravatar
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.
Vicodin dosage
Comments have been closed on this topic.
«April»
SunMonTueWedThuFriSat
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910