none
Add n blank rows to a table where n is a field value from a dataset

    Question

  • 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?

    Friday, January 25, 2013 6:04 PM

Answers

  • 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

    1. 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.)
    2. Declare @Counter and @HLC parameters
    3. Set @HLC to the number of rows to generate. This can be using any query that can return a single integer value.
    4. In the WHILE loop, insert a row into the @Results table for each time it loops through.
    5. 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.


    Monday, January 28, 2013 6:49 PM

All replies

  • This is to be acheived in database level or in SSRS?
    Friday, January 25, 2013 8:48 PM
  • This is to be acheived in database level or in SSRS?

    In SSRS.
    Friday, January 25, 2013 9:56 PM
  • 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.

    Friday, January 25, 2013 10:00 PM
  • 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?
    Friday, January 25, 2013 10:13 PM
  • 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.

    Friday, January 25, 2013 10:43 PM
  • 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


    • Edited by me.rtp Friday, January 25, 2013 10:54 PM
    Friday, January 25, 2013 10:53 PM
  • 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

    1. 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.)
    2. Declare @Counter and @HLC parameters
    3. Set @HLC to the number of rows to generate. This can be using any query that can return a single integer value.
    4. In the WHILE loop, insert a row into the @Results table for each time it loops through.
    5. 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.


    Monday, January 28, 2013 6:49 PM