Introduction
A report contains N number of fields. Some users want to see only 3 fields, some users 2 fields, and others may want to see 20 fields. Our idea is display selected columns dynamically in SSRS reports. We can control the Columns from ASP web page.
Note
For basic report creation, please refer to other articles. There are many here on The Code Project.
Using the Code
Step 1
Create a report with required dataset. Drag and drop table control and select dataset fields. In my example, I have the following fields in the dataset: Id
, Functionality
, Status
, Activity
.
Step 2
Simply create Dataset dsDynamicColumns
using this query:
SELECT 1 ID, 'Id' AS ColumnName UNION
SELECT 2 ID, 'Functionality' AS ColumnName UNION
SELECT 3 ID, 'Status' AS ColumnName UNION
SELECT 4 ID, 'Activity' AS ColumnName
Step 3
Create a new parameter with name pDisplayFields
and prompt DisplayFields
as shown below:
In available values of Report Parameter Properties wizard, select Get values from a query, select dsDynamicColumns
in Dataset, ColumName
in value field and label field.
In Default Values of Report Parameter Properties wizard, select Get values from a query, select dsDynamicColumns
in Dataset, ColumName
in value field.
Step 4
Now, you have to set the expression to display the columns which are selected in the pDisplayColumn
parameter. Right click on First Column (Id in our example) and click Column Visibility...
Write the following expression in Show or hide based on an expression of Column Visibility wizard:
=IIF(InStr(JOIN(Parameters!pDisplayFields.Value,","),"Id")>0,False,True)
Then repeat this expression for all the columns by modifying the expression for the respective column name accordingly.
Now, we can see the preview of the report.
We can select the required Column that we want:
The challenge is we need to control the Display Columns from ASP page.
Step 5
Create an ASP page with Check Box List And Button Click Control.
In Check Box List Control, add Lists like:
Step 6
In Button Click Event, the Selected Items as concatenated string
s using the ',' separator:
var selectedValues = chkColumnList.Items.Cast< ListItem >()
.Where(li => li.Selected)
.Select(li => li.Value)
.ToArray();
string dynamiColumns = string.Join(",", selectedValues);
Step 7
Get the Report Values from the database using SPC Or Query.
Pass the Report data Value and Parameters in such a way:
rptViewer.Visible = true;
rptViewer.LocalReport.Refresh();
string path = HttpContext.Current.Server.MapPath("~/Reports/");
rptViewer.Reset();
rptViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local;
Microsoft.Reporting.WebForms.LocalReport r = rptViewer.LocalReport;
r.Refresh();
r.DataSources.Clear();
r.ReportPath = path + "reportName.rdlc";
Microsoft.Reporting.WebForms.ReportDataSource rds;
DataTable dt = "Report Data from Data Base" as DataTable;
rds = new ReportDataSource("DataSet1", dt);
r.DataSources.Add(rds);
r.SetParameters(dynamiColumns);
r.Refresh();
That's all! Now just run the application. We can get the column combination that we want.