Home > Home, MSDN, SSRS > SSRS : Page break after certain number of group

SSRS : Page break after certain number of group

This question I got from MSDN, Click here to get the original source of this question.

In brief :-

page break after certain number of group display

how can I do a page break after displaying 10 group collapsed lines ?

i am showing collapsed groups and want to page break after displaying 10 group lines (in collapsed view)

So user want like page break should happen  after 10 groups. Unfortunately there are no function to get the group counts. So better to bring group count number in the query itself.

Let say you have a sample data query as given below:-

select ‘Fruits’ category, ‘Apple’ product, 50 cost
UNION
select ‘Fruits’ category, ‘Grapes’ product, 40 cost
UNION
select ‘Fruits’ category, ‘Peach’ product, 30 cost
UNION
select ‘Vegetables’ category, ‘Tomato’ product, 50 cost
UNION
select ‘Vegetables’ category, ‘Potato’ product, 20 cost
UNION
select ‘Vegetables’ category, ‘Capsicum’ product, 40 cost
UNION
select ‘Grocery’ category, ‘Rice’ product, 100 cost
UNION
select ‘Grocery’ category, ‘Wheet’ product, 120 cost
UNION
select ‘Grocery’ category, ‘Oil’ product, 180 cost
UNION
select ‘Cleaning’ category, ‘Detol’ product, 80 cost
UNION
select ‘Cleaning’ category, ‘Floor cleaner’ product, 50 cost
UNION
select ‘Cleaning’ category, ‘Dish washer’ product, 40 cost

Dataoutput

add the  Dense_Rank function to add one more column which will give the group number as shown below:-

select *, dense_rank() over( order by category)pagebreakrow from
(
select ‘Fruits’ category, ‘Apple’ product, 50 cost
UNION
select ‘Fruits’ category, ‘Grapes’ product, 40 cost
UNION
select ‘Fruits’ category, ‘Peach’ product, 30 cost
UNION
select ‘Vegetables’ category, ‘Tomato’ product, 50 cost
UNION
select ‘Vegetables’ category, ‘Potato’ product, 20 cost
UNION
select ‘Vegetables’ category, ‘Capsicum’ product, 40 cost
UNION
select ‘Grocery’ category, ‘Rice’ product, 100 cost
UNION
select ‘Grocery’ category, ‘Wheet’ product, 120 cost
UNION
select ‘Grocery’ category, ‘Oil’ product, 180 cost
UNION
select ‘Cleaning’ category, ‘Detol’ product, 80 cost
UNION
select ‘Cleaning’ category, ‘Floor cleaner’ product, 50 cost
UNION
select ‘Cleaning’ category, ‘Dish washer’ product, 40 cost

)T

denseRankoutput

Now go to report design and group properties as shown below:-

GroupProperties

let say I want page break after 2 groups then the expression should be like below:-

=iif(Fields!pagebreakrow.Value mod 2 = 0,False ,True )

run the report, output is like below:-

ReportOutput.png

Let say , I want page break after 3 groups then expression should be as given below:-

=iif(Fields!pagebreakrow.Value mod 3 = 0,False ,True )

run the report

 

ReportOutput

Hope you like this post.

Please provide your valuable feedback through liking the post or adding comments. It will help to encourage to do lot of similar stuff.

Thanks

Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools

Advertisements
Categories: Home, MSDN, SSRS Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: