SSRS 2012 csv export - dynamic column header name

Question

I need to export my SSRS report to csv format. Issue I am facing is that few column header names change with country code (parameter) and I want to show the same name in my exported csv. I have gone through other related questions and topic, specifically this one. It suggested that there is a work around by setting data value to null. I have tried this by adding columns and hiding these based on country code, along with setting data value to null for that dataset field. But it did not work. I still get the hidden column in my export, with no values in it.

Can someone confirm that this workaround works or is there any other way apart from creating different reports for each country?

UPDATE: (added report screenshot and description for clarification)

Based on apporoach I have taken, I need to only show Column 'Town' for Country A and only 'Suburb' for Country B. This is easily done by hiding columns based on Country Parameter( and works fine for EXCEL export), but when exported to CSV, both columns are exported.

UPDATE 2

Found this link which is similar to what I have been trying to figure out and looks like there is no solution which can be achieved using only one report.

Answer 1

According to this post

MSDN Social: Hide CSV columns conditionally in SSRS report

The XML and CSV renderers use the DataElementOutput property to control visibility. We can select which item we want to hide in the report, and set the “DataElementOutput” property with value “NoOutput” to work around the issue.

Alternatively, we can add below expression to control the item’s visibility. Please refer to the expression below: =IIF(Globals!RenderFormat.Name="CSV",True,False)

This, incidentally, is the second answer in the thread that you posted. That answer links to the following article which explains how to do exactly what you need

Hide/Show Items Dependant On Export Format

UPDATE

It appears that you cannot programmatically set DataElementOutput for CSVs. However, according to this post SSRS - Programatically controlling the DataElementOutput property

in RS 2005 / 2008, you should be able to get the desired effect by adding a filter on the tablix directly (in addition to the visibility condition):

Filter expression: =(Parameters!DataPeriod.Value = "DAY")
Filter operator:    =
Filter value:     =true

Thereby, for the cases where the tablix is not visible, you are also filtering out all the data.

You may be able to show/hide country specific columns this way instead.

Answer 2

My approach would be to do this in SQL. I'm not sure how you determine which Countries require the town to be returned and which require the suburb to be returned but here's a couple of approaches that hopefully cover your scenario.

In either case, the idea is to return the town/suburb data in the same column and then an additional column that will contain a a caption that we can use as the column header.

a. Only one of either the town or suburb column in your table is populated. In this case it's pretty simple.

SELECT 
    Country 
    , ISNULL(TownOrCity, StreetSuburb) AS TownSuburb
    , CASE WHEN TownOrCity IS NULL THEN 'Street-Suburb' ELSE 'Town-City' END AS Caption
FROM myTable

b. You know upfront which Countries require what and you can pass a parameter in to get the correct column. In this example we'll use a parameter called @TS and pass in either a T or and S

SELECT 
    Country 
    , CASE @TS WHEN 'T' THEN TownOrCity ELSE StreetSuburb END as TownSuburb
    , CASE @TS WHEN 'T' THEN 'Town-City' ELSE 'Street-Suburb' END AS Caption
FROM myTable

Whichever approach we take you will end up with a simple table

Country    TownSuburb    Caption
Testland   TownA         Town
Testland   TownB         Town
Testland   TownC         Town

In you report, you don;t need to do anything except make the town/suburb column caption an expression something like =FIRST(Fields!Caption.Value)

That's it, the report is now nice and simple and should export without any issues.

UPDATE to method:

--
-- Dump data into a temp table
--

SELECT 
        Country 
        , CASE @TS WHEN 'T' THEN TownOrCity ELSE StreetSuburb END as TownSuburb
    INTO #t
    FROM myTable
--
--rename the column
--
DECLARE @NewColumnname sysname = CASE @TS WHEN 'T' THEN N'Town-City' ELSE N'Street-Suburb' END
EXECUTE tempdb..sp_rename  N'tempdb..#t.[TownSuburb]', @NewColumnname, 'COLUMN' 
--
-- finally get the result
--
SELECT * FROM #t