Click here to Skip to main content
Click here to Skip to main content

Tagged as

Displaying Dynamic Columns in SSRS Report From ASP Page

, 28 Aug 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
How to display 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.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Santhosh Babu Mahimairaj
Software Developer (Junior)
India India
Hello,
This is Santhosh Babu Mahimairaj i m currently Working as Junior Software Associate

Comments and Discussions

 
QuestionNice Article PinmemberMember 1028790628-Aug-14 3:54 
AnswerRe: Nice Article PinprofessionalMember 1095234728-Aug-14 4:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.1411022.1 | Last Updated 28 Aug 2014
Article Copyright 2014 by Santhosh Babu Mahimairaj
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid