Page-based discussion of SQL Server Reporting Service

Previously, when using Reporting Service, we did not know its specific paging rules. I only know that when I think that each page of a report is too long, I want to extend the report body so that the report can display more data on each page. Of course, the results are unsatisfactory, because you will find that after you lengthen the report subject, the report is still paged in the original position. The root cause is that the report's paging position is not the length and width of the report subject, in the report Properties window, the width and height under the paper title are displayed.

 

1. First find out how to adjust the height and width of the page paper

Before proceeding to the following explanation, we need to understand two concepts: InteractiveSize (Interactive page length and width) and PageSize (page length and width), which are two attributes of page size control in SSRS, these two attributes can be viewed after selecting a report in The SSRS Properties window (open the Properties window and click any location in the blank area except the report body, header, and footer on the designer, select the report in the Properties window). Why does SSRS have two attributes to control the length and width of the page?

InteractiveSize and PageSize attributes in the Properties window

 

This is because the paging mechanism varies depending on the report presentation format and page size. As you know, SSRS reports can be exported in many formats: Excel, Word, PDF, CSV, HTML, and XML. The paging mechanism of these file formats is different, for example, the paging mechanism of PDF, EXCEL, and HTML is different, and XML cannot be paged at all. Therefore, depending on the report presentation format, the results displayed after pagination are naturally different.

 

Here, the width and height set in InteractiveSize control the paging width of EXCEL and HTML, while the width and height set in PageSize control the paging width of PDF. This means that, depending on the report presentation format, only one of InteractiveSize and PageSize plays a role in specifying the page length and width. In SqlServer2008, when you set the width and height under the paper title in the report Properties window, the InteractiveSize and PageSize will also change to the set value. However, when you set the width and height under a large title in the report Properties window in SqlServer2005, you will find that only the PageSize attribute has changed to the set value, and the value of the InteractiveSize attribute has not changed, you need to manually modify it in the SSRS attribute window.

Width and height options under the paper title in the report Properties window

 

2. Now let's look at the page

So what is the relationship between the page size and the page size?

 

If the height of the current report subject area plus the height of the header and footer (provided that the report has a header and footer) is greater than the height of the page paper, then, the SSRS inserts a vertical paging character in the report subject area that is just at the height of the page.

Here I will illustrate this situation in detail. Assume that the report header and footer are now 10 CM, and the report subject has a Table. If the Table contains 100 rows of data, if 100 rows of data are displayed and the Height of the report subject is 100CM (1 CM per row), set the Height of InteractiveSize and PageSize to 40 CM, that is, the height of the paper on each page is 40 CM. As a single page cannot accommodate all the data when the Table is displayed, when the Table displays 20th rows of data, since the header and footer are 20 cm + the report subject is 20 CM = 40 CM, the height of the first page reaches 40 CM of the paper, therefore, the SSRS inserts a vertical paging character after the second row of data in the Table (after the paging operator is inserted, the report will immediately pagination, therefore, the position of the page break is the end of the old page of the report subject and the beginning of the new page. The report content after the page break is displayed on the new page ), the remaining 80 rows of Table data can only be displayed on the page after the first page. Similarly, on the second page, when When the data is displayed from 21 rows to 40th rows, because the second page also reaches 40CM page height, SSRS inserts a vertical paging character under 40th rows, the remaining 60 rows of data are only displayed on the page after the second page. Similarly, to display a complete Table, five pages are required.

 

Similarly, if you use a matrix, we all know that the matrix will display data in the vertical and horizontal directions. If your matrix displays data in the horizontal direction, as a result, the width of the report body is greater than the width of the page. Then, SSRS inserts a horizontal paging character into the columns of the matrix, and the remaining column data of the matrix is only displayed on the new page.

 

In addition, you can also see that setting the height and width of the report subject is not too great, if you set the height of the report subject to only 10 CM in SSRS design mode, if there is a Table on the report topic, this Table has 100 rows of data (or assume that each row has 1 CM rows), so to show that the 100CM data report subject will be extended, far greater than the height of this 10CM, although it is of little use, setting the length and width of the report subject will also affect the report page, which will be discussed later.

 

3. A pagination character inserted manually

Some controls in SSRS support the insertion of pagination characters before and after the controls. For example, a Table supports the insertion of pagination characters before and after the controls, now, we assume that we select "add a paging character after the component" on a Table, and then manually add a paging character after the Table, after the Table shows all the data in the vertical direction, it will be immediately paged. This creates an interesting phenomenon. For example, if this report uses three pages to display Table data, each page displays 20 rows of data (1 CM rows in each row). If this Table contains 43 rows of data, on the third page, you will find that the Table is paged immediately after the last three rows of data are displayed, but the third page only contains three rows of data, that is, the report subject on the third page is only 3CM High, the page height does not reach 20CM, so the third page looks very short, which is caused by manually inserting a page break on the control.

 

4. The impact of report subject length and width on paging

As mentioned above, setting the length and width of a report subject will also affect the report page. Here is an example to illustrate: If you set the page height of a report to 40 CM, now, if the number of headers and footers on each page of the report is 20 CM, the report subject is set to 60 CM, and the report subject has a Table, which accounts for 2 CM in design mode, the following figure shows an example:

Design Mode report layout example diagram (this diagram uses the written height and the actual height as an example to see if there is an error)

 

You can see that there is a blank position of 58CM on the report subject. In addition, since none of the 58CM controls are supported, if the Table has 60 rows of data (1 CM rows per row), the report displays Table data on three pages, but after the Table is displayed, because there is still a blank area of 58CM in the design mode of SSRS under the Table, what will SSRS do?

After testing on SQL Server2005, we found that the remaining 58CM blank areas are all displayed on the last page, even if the overall height of the last page is 78CM greater than the height of the paper setting is 40 CM, this blank area will not be paged.

After testing on SQL Server 58CM, we found that the remaining 20CM blank areas are displayed on three pages. Because the report subject height of on each page is 20 + 20 + 18 = 58, the height of 58CM requires three pages to be displayed, but it is interesting that although there are three blank pages at the end, the height of the report subject is 0 CM for each of the three pages at run time, because SSRS found that there were no controls on the report subject on the last three pages, it was optimized to compress the report subject to 0CM and only display the header and footer, do not believe that you can put a TextBox on the top 58CM of the last 20CM area. As there is a TextBox on the first page of the last 3 pages of the report, the height of the TextBox is the report body on this page, however, the report subjects on the last two pages are compressed to 0 CM.

 

5. Paging problems when using HTML and Excel formats

As mentioned above, the attribute InteractiveSize (Interactive page length and width) is used to control the size of HTML and Excel pages, this is because reports in HTML and EXCEL formats can use some interactive display functions. For example, a group of data rows on a Table is hidden first, click a text box in the parent group and then display the data rows in this group. This is an interactive effect exclusive to HTML and EXCEL formats. This brings about a problem. This is also mentioned in MSDN. Interactive special effects will change the length and width of the page. For example, a group of data GroupII has 50 rows, however, when the GroupII group is hidden, its parent group GroupI may only display three rows on the Table. At the beginning, since GoupII was in the hidden state, only three rows of GroupI data were displayed on one page, however, if we show all the GroupII data after expanding the GoupI, the Table will become 53 rows of data, so the bad thing will happen, SSRS does not pagate the height that is displayed by using the interactive special effect, which means that if the paper size of the original report page requires that only 20 rows of data be displayed on each page, however, these 50 rows of data are produced by interactive effects. Even if the limit of 20 rows is exceeded, SSRS still does not paging the Table with 53 rows of data, all 53 rows of data on the Table are displayed on one page. Imagine what would happen if 1000 rows of data are generated after a group in the Table is expanded, all the 1000 rows of data are displayed on one page, and your page is estimated to have crashed .....