Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Display Ton N rows in reports and hide the rest Expand / Collapse
Author
Message
Posted Thursday, February 21, 2013 3:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 1, 2014 8:41 AM
Points: 101, Visits: 437
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
Post #1422464
Posted Thursday, February 21, 2013 8:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 7:45 PM
Points: 112, Visits: 101
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




Post #1422608
Posted Thursday, February 21, 2013 11:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 1, 2014 8:41 AM
Points: 101, Visits: 437
Thanks!! Will try and let you know!!
Post #1422738
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse