CODEWITHSUNDEEP
sqlreporting-services
xChaax
xChaax

Reputation: 323

How to create table dynamically in SSRS

I am a beginner in SSRS and maybe this may sound easy, But I want to know on how to create table dynamically based on number of rows return in my dataset(select query).

If I have 5 rows it should be shown in 5 different table.

Example: Select query:

ID | Name | Age
01 | Mike | 10
02 | Mary | 12

Then in my ssrs:

ID   | 01
Name | Mike
Age  | 10

ID   | 02
Name | Mary
Age  | 12

Upvotes: 0

Views: 2920

Answers (2)

IgorM
IgorM

Reputation: 1356

You can do that with SQL CURSOR

DECLARE @table_name as nvarchar(100)
DECLARE @count int = 0
DECLARE @id, @name as nvarchar(255)
DECLARE @age as int
-- declare cursor

DECLARE tbl_cursor CURSOR FOR     
   SELECT [id], [name], [age] FROM [SourceTable]

OPEN tbl_cursor    
   FETCH NEXT FROM tbl_cursor INTO @id, @name, @age
   WHILE @@FETCH_STATUS = 0     -- ok 
      BEGIN
         -- create table with temp name
         CREATE TABLE temp_table (
            [id] nvarchar(255),
            [name] nvarchar(255),
            [age] int
         );
         INSERT INTO temp_table ([id], [name], [age]) VALUES (@id, @name, @age)
         -- rename temp table
         SET @count = @count + 1
         SET @table_name = 'tblTable' + CAST(@count as varchar)
         exec sp_rename temp_table, @table_name

         FETCH NEXT FROM tbl_cursor INTO @id, @name, @age
       END        
       -- close cursor 
CLOSE tbl_cursor        
DEALLOCATE tbl_cursor

If SELECT [id], [name], [age] FROM [SourceTable] returns five rows, the code will create five tables [tblTable1..5]

Upvotes: 1

Alan Schofield
Alan Schofield

Reputation: 21683

There are several ways to do this, this simplest thing would be to do a normal table and add extra rows to the detail group to separate the 'tables' out and add a detail row for each field. The design would look something like this

enter image description here

And the output like this enter image description here

The other way, which will give you complete control and will be useful if the table is complex would be to create a subreport that displays a single entry and takes ID as a parameter, then the main report would just be a single column table, in the column you would insert the subreport you just created and pass in the ID field as the parameter. It's less complicated than it sounds but if you want to go that route, post a comment and I will edit the answer.

Upvotes: 2

Related Questions