[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1657
  • Last Modified:

Dynamic columns in SSRS reports

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

Thanks
0
PantoffelSlippers
Asked:
PantoffelSlippers
  • 3
  • 2
1 Solution
 
wolfman007Commented:
Have a look at the following previous answer

Dynamic columns with dynamic column names
http://www.experts-exchange.com/Database/Reporting_/Q_23547098.html
0
 
PantoffelSlippersAuthor Commented:
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?
0
 
ValentinoVBI ConsultantCommented:
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: http://www.codeproject.com/KB/reporting-services/DynamicReport.aspx
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
PantoffelSlippersAuthor Commented:
Thanks ValentinoV,

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

Thanks
0
 
PantoffelSlippersAuthor Commented:
Any other ideas?
0
 
ValentinoVBI ConsultantCommented:
If you need it to be really that dynamic, I think the only remaining option is to generate the report dynamically using code.
On http://www.gotreportviewer.com/ 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).
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now