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() = {}
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
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.
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.
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.
In the below screenshot, you can see the design layout of the sample SQL Server Reporting Services report.
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
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 :
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.
|