Displaying Dynamic Columns in SSRS Report From ASP Page

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.

• http://www.codeproject.com/Articles/621332/Building-Your-First-SSRS-Report
• http://www.codeproject.com/Articles/675762/Call-SSRS-Reports-by-using-Csharp

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 strings 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); //values from check box list
    r.Refresh();

That's all! Now just run the application. We can get the column combination that we want.