SSRS Possible to toggle Header visibility based on export type? 2008-R2

Question

I'm familiar with dynamically showing/hiding other report objects (textboxes/tablix/columns etc...) based on a variable or parameter value - but is there a way to do this with the header? I don't see any "visibility" tab on the header object to set an expression.

Further more, is there a way to do hide the header based on the export style. For example, I want to show the header when exported to PDF, but want to hide it when exporting to XLS (to prevent cell merging).

I've seen examples of when the "header" is replaced by a "rectangle" which can be toggled. I've also seen articles refering to =Globals!RenderFormat.IsInteractive = “EXCEL”), but I don't know where that is set? On a server config file? Is there a way to have it done just for that one specific rdl report file?

Thanks in advance!

score 7 · Accepted Answer · answered Oct 28 '16 at 19:13

Answering your questions:

Is there way to dynamically show/hide the header - no. Unfortunately, you can't set visibility for whole header/footer in RDL reports.

Is there way to hide the header based on the export style - not quite. As I stated, you can't set visibility for whole header, but you can set visibility for separate report items in header based on the export style.

Where that is set - it is set in report, in usual Hidden expressions. There is nothing to do with server config file to achieve this (the only point is to take a look at rsreportserver.config - it contains section with rendering extensions, it's useful to know their names, f.e. name "EXCEL" is for old binary XLS format, while "EXCELOPENXML" is for modern XLSX).

To set visibility based on the export style, you can use built-in report field RenderFormat.Name (notice that it is Name, not IsInteractive!). Just type the following expression for the Hidden property:

=Globals!RenderFormat.Name = "PDF"

In conclusion, you can't set visibility for whole header, you can show/hide report items inside header based on different conditions you want, but header will still take some place. To really hide the header so it will not consume the space of report, you can simulate the header with rectangle and page breaks, this is the workaround.

Thank you for the feedback. I ended up replacing the header with a rectangle for the time being. As far as your post about "Just type the following expression for the Hidden Property: =Globals!RenderFormat.Name = etc.." --- where exactly am I supposed to put that? Are you referencing the spot under Visibility: Show or Hide based on an expression ? Thanks again — Jay, Nov 01 '16 at 18:09
Actually I got it, I put that expression in the tablix Visibility tab under "Show or Hide based on an expression" expression, and it worked like a charm. Thanks! — Jay, Nov 01 '16 at 18:20

score 0 · Answer 2 · answered Feb 27 '19 at 19:50

The report requirement I had before me was of similar nature.

NEED: Hide the headers when exported to CSV file.

The report is going to be exported to CSV and then uploaded into another system (a system that doesn't want headers). To be more user-friendly, I wanted the report to show the headers but not export them in the CSV file.

I tried some things that did not work. Then I found an article describing how you can create a new export format, Labeled: "CSV No Headers", which solved the export requirement.

SOLUTION: rsreportserver.config

FILE: D:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config

XML:

    <Extension Name="CSV (No Header)" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
    <OverrideNames>
       <Name Language="en-us"> CSV No Header</Name>
    </OverrideNames>
    <Configuration>
       <DeviceInfo>
          <NoHeader>true</NoHeader>
       </DeviceInfo>
    </Configuration>

SOLUTION DEMO

NEW OPTION:

EXPORTED> Report Name (CSV No Header).csv:

EXPORTED> Report Name (Excel).xlsx:

SOURCE: Charanmandya Blogspot: Export Csv Without Header in SSRS

Things that did not work...

I read through this article, Blog, Beer Intelligence by Valentino Vranken: "Hide/Show Items Dependant On Export Format (SSRS)". The article was helpful with lots of user comments and suggestions. But I tried these things but they did not work for CSV export without header row.

Row Visibility > Expression > =Globals!RenderFormat.Name = "CSV"

This worked for "Excel" or "EXCELOPENXML" format. For some reason "CSV" does not work.

Select Row> Properties: Data Only> [Data Element Output=NoOutput]

Some related stackoverflow questions...

SSRS Possible to toggle Header visibility based on export type? 2008-R2

2 Answers2

SOLUTION: rsreportserver.config

SOLUTION DEMO

Things that did not work...