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
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
ASKER
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?
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?
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
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
ASKER
Thanks ValentinoV,
Same problem as the other solutions though - I wouldn't know the columns or even the maximum amount of columns.
Thanks
Same problem as the other solutions though - I wouldn't know the columns or even the maximum amount of columns.
Thanks
ASKER
Any other ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Dynamic columns with dynamic column names
https://www.experts-exchange.com/questions/23547098/Dynamic-columns-with-dynamic-column-names.html