Hi, I need to add 'n' blank rows to a table depending on a field value. For instance in the below table 3 blank rows are added as dataset1.field1.value is 3. How do I achieve this. Also I need to display row number in the 1st column concatenated to order no?

This is to be acheived in database level or in SSRS?

This is to be acheived in database level or in SSRS? In SSRS.

The dynamic rows in a tablix are generated for each row of data in the assigned dataset as filtered by any filter statements applied to the tablix. I have done something similar by creating a dummy dataset that returns one row for each number up to a set parameter. Below is the TSQL: DECLARE @Results TABLE ( PH1 NVARCHAR(25), PH2 NVARCHAR(25), PH3 NVARCHAR(25), PH4 NVARCHAR(25) ) DECLARE @HLC INT, @COUNTER INT SELECT @HLC = Table.RowCountField FROM Table WHERE Table.FilterField=@Parameter SET @COUNTER=0 WHILE @COUNTER<@HLC BEGIN INSERT INTO @Results VALUES ( 'Ref_Title'+CAST(@COUNTER+1 AS VARCHAR), 'Ref_Desc'+CAST(@COUNTER+1 AS VARCHAR), 'Ref_Owner'+CAST(@COUNTER+1 AS VARCHAR), 'Ref_Location'+CAST(@COUNTER+1 AS VARCHAR)) SET @COUNTER=@COUNTER+1 END SELECT * FROM @Results The query assigns the value of RowCountField to the parameter @HLC and represents the number of dummy rows to generate. I don't know of anyway to do this in the SSRS tablix however. You have to have a dataset row to generate a tablix row.Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

The dynamic rows in a tablix are generated for each row of data in the assigned dataset as filtered by any filter statements applied to the tablix. I have done something similar by creating a dummy dataset that returns one row for each number up to a set parameter. Below is the TSQL: DECLARE @Results TABLE ( PH1 NVARCHAR(25), PH2 NVARCHAR(25), PH3 NVARCHAR(25), PH4 NVARCHAR(25) ) DECLARE @HLC INT, @COUNTER INT SELECT @HLC = Table.RowCountField FROM Table WHERE Table.FilterField=@Parameter SET @COUNTER=0 WHILE @COUNTER<@HLC BEGIN INSERT INTO @Results VALUES ( 'Ref_Title'+CAST(@COUNTER+1 AS VARCHAR), 'Ref_Desc'+CAST(@COUNTER+1 AS VARCHAR), 'Ref_Owner'+CAST(@COUNTER+1 AS VARCHAR), 'Ref_Location'+CAST(@COUNTER+1 AS VARCHAR)) SET @COUNTER=@COUNTER+1 END SELECT * FROM @Results The query assigns the value of RowCountField to the parameter @HLC and represents the number of dummy rows to generate. I don't know of anyway to do this in the SSRS tablix however. You have to have a dataset row to generate a tablix row. Please Mark posts as answers or helpful so that others can more easily find the answers they seek. Kinda confused. Where do I create this query? 1. In the report builder in a dummy dataset or 2. create in sql a SPROC and call this thru' a report builder dataset?

Yes, the query would be in a dataset. You can be either added as a text query or add the TSQL to a stored proc and call it that way. In my case I just created a dataset in the report and added the TSQL as a text query.Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

Thnaks Tim. i did create a dataset in the report as U did. Most of them worked except this line SELECT @HLC = Table.RowCountField FROM Table WHERE Table.FilterField=@Parameter. I tried assigning a direct value like 5 for @HLC and it worked. But, my case @HLC need to get value from a field from another dataset in the report? How can I acheive this? can I refer another dataset field value in this query of yours? can i call a report variable in this query? I'm stuck only here. For eg, is this valid? - @HLC = Dataset1.fieldQuantity.value

This is just pseudocode. In the below statement, substitute The field name of the field that holds the number you wish to create empty rows for in place of Table.RowCountField, The table that field is in for Table and any filter criteria in place of Table.FilterField=@Parameter: SELECT @HLC = Table.RowCountField FROM Table WHERE Table.FilterField=@Parameter The same applies for 'Ref_Title'+CAST(@COUNTER+1 AS VARCHAR). In your case it would be CAST(Table.OrderNumber AS VARCHAR)+' '+CAST(@COUNTER+1 AS VARCHAR). And you probably don't need 4 fields in your dataset. Adjust the query for your needs. The important pieces are to Declare your @Results table - If you only need the OrderNumber field then declare the table with a single field of appropriate data type (i.e. INT, BIGINT, etc.)Declare @Counter and @HLC parametersSet @HLC to the number of rows to generate. This can be using any query that can return a single integer value.In the WHILE loop, insert a row into the @Results table for each time it loops through.Select * from @Results to return the desired data. Please Mark posts as answers or helpful so that others can more easily find the answers they seek.