Use Array in Reporting Services to Populate Table Column Header Names from DataSet

I have been using arrays in custom code in reports I have been developing using MS SQL Server Reporting Services.
And I populate the string array by using a dataset in the data tab of the report.
I declare the string array I want to use as a public shared array in the custom code section of the report.
Of course during the declaration of the string array, the array has no items in it.


Public Shared ColumnNamesList As String() = {}

Code

And I use the following sql text query in order to populate a reporting services dataset which I will use for source of the string array.


DECLARE @StartDate Datetime

DECLARE @Period int

SELECT @StartDate = DATEADD(dd, -30, GETDATE()), @Period = 30



SELECT CONVERT(nvarchar(20), DATEADD(dd, Number, @StartDate), 103) AS Date

FROM master..spt_values 

WHERE Type = 'P' AND Number < @Period

ORDER BY Number

Code

And I drag and drop a table from the tools windows on to the design layout of the report.
And I set the above sql query and its dataset as the source of the table.
In the detail section of the data table, I placed the only selected field "Date" of the sql query.
Since this table is not required to be displayed on the report, I set the visibility property of the table to hidden.

custom code function adds to shared string array

I hope you have realized that I edited the default field value to Code.AddToColumnNamesList() .
The AddToColumnNamesList() function takes the row field value as an input parameter and adds or appends this value to the existing shared string array ColumnNamesList.

You can see the source code of the AddToColumnNamesList() function in the Reporting Services Custom Code screen as shown below.

reporting services custom code section with sample source

Then I place the main table which has the real data that I want to display to the report users or report viewers.
This table is visible and I place it above the hidden table.
In the header section of each column, I edit the header name expression as Code.GetColumnName() with an integer value beginning from 0 which corresponds to the item index in the string array object.

custom function to get dynamic data column name

In the below screenshot, you can see the design layout of the sample SQL Server Reporting Services report.

read column names from table rows sample report layout

And the full code source of the used Custom Code is placed below :


Public Shared ColumnNamesList As String() = {}




Public Shared Function AddToColumnNamesList(txt as String) As string



  dim L as integer = ColumnNamesList.Length

  ReDim Preserve ColumnNamesList(L)

  ColumnNamesList(L)=txt



  return ColumnNamesList(L)



End Function




Public Shared Function GetColumnName(i as integer) As string



  return ColumnNamesList(i)



End Function

Code

After editing the code, defining the data section and the data select queries, designing the report and table layouts the output of the report by switching to the preview tab is as follows :

SQL Server Reporting Services Sample Report Preview

Note that the table which is used to populate the string array ColumnNamesList is customized by configuring its Visibility property by setting by Hidden to True.
So although the data table on the report is executed and populates the shared string array with its row values using the Code.AddToColumnNamesList() custom code method, the table itself is not displayed on the rendered report view.

You can download the sample Reporting Services report from Reporting Services Sample Report demonstrating Read Table Column Headers from Array Populated from Dataset.