Help needed SQL Server 2008 Reporting Service Building Dynamic Table from Dynamic dataset
Hello, I need to make a report that will show the fields a user selects, after selecting a database and table. I queries on the metadata to get available values for the parameters database, table and fields.With fields being a multi valued parameter. Databases SELECT name FROM sys.sysdatabases Table ="select distinct (i.TABLE_SCHEMA + '.' + i.TABLE_NAME) as name " & "FROM " & Parameters!Database.Value &".information_schema.columns i " & "INNER JOIN " & Parameters!Database.Value &".dbo.sysobjects so " & "ON so.name = i.TABLE_NAME " & "WHERE so.xtype = 'U'" This expression will give me the security_scheme.tablename(s) from the selected database. Fields ="SELECT column_name FROM " & (Parameters!Database.Value) & ".information_schema.columns WHERE table_name = " & "SUBSTRING(@Tables, CHARINDEX('.', @Tables) + 1, LEN(@Tables))" This expression gives me the fields of the selected table, off course I had to remove the security scheme from the parameter table. Now I'm faced with 2 problems: A) Getting the data from the selected fields from the selected database/table B) Displaying the data in a table The following article http://www.codeproject.com/KB/reporting-services/DynamicReport.aspx is not in the line of what I need, since I won't know how many columns there will be needed I can't set columns and hide them based on selection.(Well I could but the tables in our databases can range from 3-4 fields to over 50-60). My search for answers on this one have so far been futile so any help would be appreciated.
November 17th, 2010 8:35am
Hi Resender, This requirement is really rarely met in real business word. We can design a dynamic datasource for a report, for example, ="data source=" & Parameters!ServerName.Value & ";initial catalog=" & Parameters!DatabaseName.value. Also, we can use store proceduce to process dynamic query in the dataset. But, we can't let the new fields be filled in the Tabix columns automatically. To work this porblem around , we can use subreports, which might be a big workload because you need create a subreport with database and table parameters for each table in each database. If using subreports, on the main report, we could select database/table parameters to see that table report. thanks, Jerry
November 19th, 2010 3:13am