Display Ton N rows in reports and hide the rest

• var05

  Hall of Fame

  Points: 3011

  More actions

  February 21, 2013 at 3:00 am

  #274458

  Hi All,

  I have a report that will have more than 50 rows in the result set. I would like to show top N rows alone in the report and hide the rest of the rows with toggle.

  Is there any way to achieve this? In a same time is there any way I can select rows from 11 and 50 in another grouping....first group would show top 10 rows....and anthr grp from top 11..

  Any ways pls?

  Thanks

• neerajstar

  Old Hand

  Points: 330

  More actions

  February 21, 2013 at 8:01 am

  #1589981

  Hi Var05,

  Try this out.

  suppose your report query is like this.

  Select

  Col1,

  Col2,

  .

  .

  --introducing one more column that define the number of rows in you selection and assign then a numeric value through 1 to 50

  row_number() over(order by (select 1)) as 'r_num'

  From [SomeTableName]

  So, Above query have your result set required for report.

  Create New Report->create your dataset->on report body create a table area.

  1. In grouping pane Create Parent Group to your Default and write the grouping expression.

  =iif(fields!r_num.value<=10,1,2) dividing your data on to two different partition.

  2. Select Detailed Row from table area->go to row visibility->choose "Show and Hide based on expression" and write the below expression

  =iif(Fields!id.Value<=10,false,true)

  and "Display can be toggle by" Select your Parent Group Name.

  3. Select The Grouping Row from your Table in property window go for the property "InitialToggleState" and write expression.

  =iif(Fields!id.Value<=10,true,false)

  if you feel this is as your ans then mark it.

  if you require supportive report file to the above example do let me know.

  Thanks,

  Neeraj

• var05

  Hall of Fame

  Points: 3011

  More actions

  February 21, 2013 at 11:58 am

  #1590088

  Thanks!! Will try and let you know!!