locked
SSRS 2005 - Insert blank row into table after every n rows RRS feed

  • Question

  • Hi all,

    I need to create a report that after the 8th, 16th & 24th rows of data in a table inserts a blank row.

    The table will have a max of 32 rows of data but a blank row is not needed after data row 32.

    In total, therefore, the table would contain 35 rows (32 data rows & 3 blank rows)

    I have the query written that will bring back the 32 data rows but am struggling to identify how I could insert a blank row between data rows 8 & 9, 16 & 17 & 24 & 25.

    Any suggestions would be appreciated.

    Many Thanks

    Neil

    Wednesday, August 8, 2012 12:59 PM

Answers

  • Hi Neil,

    The suggestion posted by shriprasanna is right on the whole. Considering that you are using SSRS 2005 and you don't want to add the blank row under the 32th row, I suggest that you follow the steps below:

    1. Right click the handle of the Details row, click "Insert Row Below".

    2. Click the handle of the new row, expand the "Visibility" item in the Properties pane.

    3. Set the "Hidden" property expression to:

    =IIF(RowNumber(Nothing) Mod 8 =0, IIF(RowNumber(Nothing)=32, True, False), True)

    If you have any questions, please feel free to let me know.

    Regards,
    Mike Yin

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Mike Yin

    TechNet Community Support


    • Edited by Mike Yin Wednesday, August 15, 2012 5:14 AM typo
    • Marked as answer by Mike Yin Sunday, August 19, 2012 3:42 PM
    Wednesday, August 15, 2012 5:13 AM

All replies

  • Neil

    Not probably by using T-SQL....

    create table #t (id int, name char(1))

    insert into #t values (1,'a')
    insert into #t values (2,'b')
    insert into #t values (3,'c')
    insert into #t values (4,'d')
    insert into #t values (5,'e')

    insert into #t values (6,'f')
    insert into #t values (7,'k')
    insert into #t values (8,'l')
    insert into #t values (9,'m')
    insert into #t values (10,'n')



    select name from (
      select
        name as Position_1,
        1 as Position_2,
        name
        from #t
      union all
      select name,col,'' as t from
      (
      select distinct
        name,
        0 as Position_2,
        case when id%5=0 then'' end col
      from   #t
      ) as der where col=''
    ) Report
    order by Position_1, Position_2


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Wednesday, August 8, 2012 1:16 PM
  • Hi Uri,

    Thanks for your reply.

    I should have said that I was looking to identify if it was possible to add the blank row within the configuration of the tablix on the report itself by setting properties or running a piece of code.

    I have a query that will bring me back the rows from 1 - 32 and that is working fine.

    Wednesday, August 8, 2012 1:20 PM
  • Add a new row to your Tablix (Insert Row -> Inside Group - Below) and set the Row Visibility of the new row as follows

    =IIF(RowNumber(Nothing) Mod 8 = 0,false,true)



    Please mark this post as answer, if it addresses your question


    Wednesday, August 8, 2012 4:26 PM
  • Thanks shriprasanna, I'll give that a try.
    Wednesday, August 8, 2012 4:30 PM
  • Hi Neil,

    The suggestion posted by shriprasanna is right on the whole. Considering that you are using SSRS 2005 and you don't want to add the blank row under the 32th row, I suggest that you follow the steps below:

    1. Right click the handle of the Details row, click "Insert Row Below".

    2. Click the handle of the new row, expand the "Visibility" item in the Properties pane.

    3. Set the "Hidden" property expression to:

    =IIF(RowNumber(Nothing) Mod 8 =0, IIF(RowNumber(Nothing)=32, True, False), True)

    If you have any questions, please feel free to let me know.

    Regards,
    Mike Yin

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Mike Yin

    TechNet Community Support


    • Edited by Mike Yin Wednesday, August 15, 2012 5:14 AM typo
    • Marked as answer by Mike Yin Sunday, August 19, 2012 3:42 PM
    Wednesday, August 15, 2012 5:13 AM