Expression to group the records in a table to more accurately control the number of records per page finally we'll talk about how you can use parameters to allow your users to control how many.
Records they get on each page of a report so let's get started when you first create a report in reporting services you don't really have any control over how many records you'll see.
On each page to demonstrate this I have a simple report here which shows me a list of film names their release dates and in the first column an expression which calculates the row number of each.
Record if I right click this text box and choose expression you'll see that we're using the row number function to do this so if I choose okay and preview my report we'll get an idea of how many.
Report where any remaining records are shown one way to modify the number of Records on a page is to change the page size if the report so if I wanted to do that I can head back to the design view.
Head to the report menu and choose report properties on the page setup tab of this dialog box I have the opportunity to change the paper size now I can choose one of the preset standard.
Paper sizes like a4 a5 etc or I can even choose a custom size and type in my own dimensions width and height I'm going to type in the height as 20 centimeters and click OK when I preview the report again.
Should see the reporting services has changed the number of records on each page so now I'm down to 32 rather than previously 48 so that's one way to modify the number of records per page.
But what if you wanted to control this in a bit more detail the most accurate way to control the number of records on each page is to use a combination of an expression with a grouping level so to.
Posts Related:
Add a grouping level to my table I'm
Going to use the groups panel now in the bottom of the screen and in the row groups section I can find the details item if I right click on this I can.Choose to add a group and then parent group now the dialog box that appears ordinarily what you would do is select one of the fields from the data set that your table is based on but that's not.
What I want to do in this case I want to calculate a value in this table that I'm then going to use to group the rows so I'm going to do that by clicking the effects button to launch the expression.
Builder I'm going to use two functions to generate my expression and the first one I'm going to show you is one that you've already seen it's the function called row number that we used earlier.
On to calculate the number of each row in the table so Row 1 will be Row 1 Row 2 will be the number 2 and so on the next step is to divide that number by the number of records you'd like to see.
On each page so I'd like to see 10 records per page if you'd like to see 20/20 and so on the last step is to wrap this expression up inside the sealing function I can spell sealing that would.
Help sealing open brackets and finally close round brackets now what the sealing function does is gives you the lowest whole number the smallest integer that is greater than or equal to.
Whatever number you pass inside so I can try to explain this in a bit more detail row number for the first row in my table row number nothing gives the value 1 divide 1 by 10 that gives me.
An answer 0.1 the sealing function then gives me the smallest whole number that is greater than or equal to 0.1 so that will generate the value 1 row number 2 2 divided by 10 is 0.2 the highest hole.
The smallest whole number greater than
That is 1 row number 3 3 divided by 10 is zero point 3 and so on and so on and so on so that will generate the value 1 all the way up to row number 10 10.Divided by 10 is 1 unsurprisingly and the ceiling function gives me the smallest whole number that is greater than or equal to see that so it calculates one as well when we move on.
To row number 11 11 divided by 10 is 1.1 and ceiling gives me the next whole number which is 2 and that will work all the way up to row number 20 and so on and so on and so on so that's what that.
Expression does it calculates a single whole number that we can then use to group our records so I'm going to choose OK at this point and choose ok again then a couple of irritating things.
Happen first of all an extra column gets added to my table which I'm going to remove shortly I'll leave it in there for now the other irritating thing that's happened is something that's not.
Very obvious at this stage but if I try to preview my report I'm going to generate an error message and I get an error message related to a sort expression for my table I head back to.
My design view one sneaky thing that happens when you add groups in the group's pane is a reporting services try to add an automatic sort so the group one that I've generated down here I need.
To modify I'm going to right-click on it and choose group properties if I head to the sorting tab I can find that I've added this inadvertence or this automatic sort so I'm going to get rid.
Of it by clicking somewhere on that item and then choosing to delete it I can then choose ok and when I preview the report now I'll be able to see the results it won't be quite exactly what I.
Want to see just yet it proves that my expression is working so here we go you can see for the first 10 rows the group is the value 1 that the second 10 rows is number 2 and so on.
And so on and so on so there are two more things I think that I want to do at this point first of all back in the design view I'm going to get rid of this column I don't.
Need to see this column in order to add to create my groups so I'm going to right-click on the top and choose it elite columns I'm going to delete the column only not the column and the group.
Which is quite important so you can see that the column has disappeared my grouping levels still remains the last step is I want to make sure that my page breaks appear between my groups so to do.
That I need to right-click on the group again in the group's panel choose group properties head to the page breaks tab and choose to add a page break between each instance of a group if I choose ok.
When I finally preview my reports I'll get the results that I wanted each page contained in just 10 records so we've seen how you can accurately control how many records you get on each page of a.
Report but what if you wanted to go one step further and let your users control how many records they see per page we can do this by using a parameter which allows you to type in a number and then.
Map them all up using our expression to control the number of records per page so to start with you need to add a parameter to your report you knew this in the report data window in the.
Parameters folder start by right-clicking on the parameters folder and choose two and a parameter on the general tab there's a few things we should change first of all the name of.
The parameter let's change the name from its standard name to something like row numbers or row number the prompt of the parameter will be what the user will see so let's tell them to type in the number.
Of rows per page I wasn't descriptive the next option is quite important are the data type of the parameter we want to make sure that the user is only allowed to type in a whole.
Number and integer so that's what I'm gonna select from the list and in in simple terms that's all we need to do I think it's also worthwhile adding a default value to this parameter so that.
Before the user types something in there is already a value there so on the default values tab I'm going to choose to specify a value then I'm going to click the Add button and then I'm going.
To replace the null with the number of records I would like to see on each page by default so and then we when the user first browses to the reports they'll see ten records per page if I choose okay.
Now you can see what the parameter will look like by heading to the preview and you should see that your parameter appears at the top of the page in this little gray area you can type in a.
Different number if you like I've been five instead of ten and there's a button here that says view report at this point clicking the button will do absolutely nothing because I hadn't yet linked this.
Parameter to the number of records per page so that's the next step to do that head back to the design view what I need to do is alter my expression to use the parameters value rather than a number.
That I typed in earlier so I need to right click on my group choose group properties and on the general tab I need to modify the expression that my table is being grouped on so to do that head.
To the F expert so here's our expression that we created earlier on what I need to do is replace the number 10 this constant number 10 with the value of my parameter so I've highlighted the number.
10 here in the bottom left hand corner of your expression builder you can find a category for parameters so if I click on that I can then find my row number parameter I can double click on this and.