Dynamic columns in SSRS reports

Posted on 2009-05-01
Last Modified: 2012-05-06
Hi Experts,

I'm writing an application that populates a SQL Server table every hour or so.  I will also develop a SQL Server Reporting Services report that will display information from this table.

The problem is that the columns in this table will need to change every week or so and I would like to have the maintenance of the solution be so easy that the support team could make adjustments without having to require help from developers.

I have already written the application to be fully dynamic.  The support team will adjust the application's config file and the application will alter and populate the database table on the fly.

My question is:  is it possible to now create a report file that will display the data from the SQL Table in a tabular style dynamically?  My requirement is that it will display whatever columns are in the table (or view) and that the report still has a professional look.

Surely it must be possible.

Please help

Question by:PantoffelSlippers
    LVL 14

    Expert Comment

    Have a look at the following previous answer

    Dynamic columns with dynamic column names

    Author Comment

    Thanks wolfman007,

    It doesn't really help me.  The reports solution simply stated that the developer hides certain columns depending on the user selection.  I can't do this in my report as I don't know which columns there might be.

    I need my report to take the view/table it's given and display its data in tabular format.  If there are 9 columns it must display 9 columns.  If another column is added to the table, it must then display 10 columns without me having to change the report definition.

    Is this possible?
    LVL 37

    Expert Comment

    The only solution that I can think of without trying it out is to:

      1. build a stored procedure that uses (possibly dynamic) queries to get the results from your table, no matter what the columns are.  This allows you to hide certain columns, like ID or whatever columns that the report user is not supposed to see.
    A precondition is that all columns should be of type char/varchar.

      2. Use the stored proc in your dataset.  Your Table should then use expression to hide columns that are not present.
    A precondition here is that you have to know the maximum number of columns that your data table can contain at any time and set up the report table accordingly.

    A similar approach is shown here:

    Author Comment

    Thanks ValentinoV,

    Same problem as the other solutions though - I wouldn't know the columns or even the maximum amount of columns.


    Author Comment

    Any other ideas?
    LVL 37

    Accepted Solution

    If you need it to be really that dynamic, I think the only remaining option is to generate the report dynamically using code.
    On there's an example on how to do this in the client (search for "Generate RDLC dynamically" - it's located at the bottom of the rectangle on the right).

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
    I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now