Thursday, 21 November 2013

SSRS 2012: Limit Number of Records Per Page

Scribbled by Teddie Debbie at 11:56
Note: Cuppycakes that came to this entry because it's really urgent to know how, kindly Ctrl+F "Begin:" to get to the point.

* * * * * * * * * * * * * * * * * * * *

Yo! First time doing tutorial here. As erjie and Serena has mentioned, I'm gonna do an experiment.

Aim: To find out which post categories are more popular.
Hypothesis: Tutorial posts are more popular compared to other lifestyle posts.
How to determine: Page views.
Duration: 2 weeks

* * * * * * * * * * * * * * * * * * * *


Assuming you have a report created and you wanted to limit the number of records per page in SSRS. I suppose there's no default way to manage it but to create a workaround. Here's how.

Step 1: Right-click the Tablix handle "Details" at the Row Groups section, select "Add Group" and then "Parent Group..."

Step 2: When new windows appeared, click on the "fx" button at the right

Step 3: In the empty space, insert the formula/set the expression.
There are a few formulas that can be used:
1. =Ceiling((RowNumber(Nothing)/x))
2. =Floor((RowNumber(Nothing)/x))
3. =Int((RowNumber(Nothing)-1/x))
Where x = the number of records to display per report page.
(Source: MSDN Blogger)

There are difference between Ceiling and Floor when comes to double/float type:
a) SELECT CEILING(12.345), CEILING(-12.345) resulting in output of 13, -12;
b) SELECT FLOOR(12.345), FLOOR(-12.345) resulting in output of 12, -13.
(Source: TechNet)

But here you have nothing to worry about, the results will be the same because the parameter used is RowNumber. RowNumber is int, not double/float.

Step 4: When above steps done, the windows will become like this. Do not change anything, just click "OK".

The report should have following changes:
Step 5: Check for the new Group1 as Parent Group for "(Details)"
New column appeared beside the existing report column.

Step 6: Right click on the column of newly formed Group - Group1, and select "Delete Columns"

Step 7: Be extra careful not to select "Delete columns and associated groups". If you carelessly *ahem* deleted it, you've gotta start from Step 1 again.

Step 8: Back to the Row Groups section, right click "(Group1)", and select "Group Properties..."

Step 9: Select tab "Page Breaks", ONLY select option "Between each instance of a group"
Step 10: Select tab "Sorting", click on (not into!) the only column and delete it.

Voila! Now you can view your report with limited records per page!

Additional: As this is just the workaround, the report file exported in Excel format will show the records as per limited/set too - where pages will be displayed as sheets.

* * * * * * * * * * * * * * * * * * * *

Let the fun begin!

Update 2014-07-18:
Turned out this method is able to solve the Excel records limitation issue! Check out this page to get the idea.

Please note that the SSRS report will display the records in the exact same sequence as displayed in Excel sheets, so it is recommended to group them in the SQL statement beforehand.
Categories ,
Share/Like to turn a rhinoceros into a unicorn!

0 scream(s):

Post a Comment


Teddie • Debbie Template by Ipietoon Blogger Template | Gadget Review