Microsoft Reporting Services Tips for Beginners

Foreword

Historically, when I came to a new workplace, I was assigned to create several reports using Microsoft Reporting Services. Before my arrival, the company where I work to this day used the Sybase DataWindow for building reports. But at the time of my arrival, it was decided to switch to Reporting Services, so I had the honor to walk this thorny path and fill more than one lump. In the process of creating reports, a lot of useful tips have been accumulated for beginners;

0) Try to use tables where possible.

The use of tables allows you to avoid a large number of problems associated with the formatting of data in the report.
Consider an example using texboxes:

This method will work correctly until the data exceeds the length of the texboxa. As soon as the length is greater, the textbox will change its height, which will either overlap the rest of the controls on the bottom, or the rest will be shifted down.

When using a table this does not happen, all data will be held together and if you meet a long text, the data will be moved correctly without overlaps and unexpected shifts.

Note: the table is a container for other controls. Each default cell is a texbox.

1) Do not forget to set the size of the report

This will prevent problems when exporting a report, for example in PDF format. If you do not specify the size, then there is a high probability of getting two or more into the same page.
In most cases, reports are printed on A4 paper, so I’ll give an example of how this might look like:

1) Right click on the report and select “Report properties
2) Select the position (Orientation) depending on the report
3) Choose a paper size (Paper size) equal to A4
4) Set the fields (In my case it is 1 cm)
5) Click "OK"
6) The last important touch: Set the page width for the body of the report. In my case, this is 19cm, because width A4 is 21cm, and the indentation is 1 cm to the left and right. We get a body size of 21-2 = 19. If you use the position of the Landscape page (29.7 cm) then the body size will be 27.7 cm. This moment is very important If the body is at least 1 mm longer, then when exporting to PDF, we get 2 sheets instead of 1.
')

2) Use the null test and type casting for arithmetic operations

If an arithmetic formula is set in the table cell, add a null check (IsNothing Method) and use a cast.
In the case of floating-point numbers, convert to double (CDbl method)
Example of use: Suppose that we have a field in the table that consists of the sum of several fields (This example may not be the best, because this amount can be obtained at the SQL level, but for clarity, it will work). We have a field A (in the database type INT) and a field B (and in the database type Numeric (28,12)). Then the Expression for the calculated field will look like this:

=CDBl(IIF(IsNothing(Fields!A.Value),0, Fields!A.Value)) + CDBl(IIF(IsNothing(Fields!B.Value),0, Fields!B.Value)) 

Actually what happens: we check the value for null if the value is null, we take 0, otherwise we use the value of the field. We make conversion to type double (CDBl) and we co-ordinate. Casting to the double type is not necessary but desirable, because in the case of large numbers there is a high probability that in the place of the result to get #Error

3) Repeating the cap of a complex table on each page

For simple tables, everything is simple, set the “Repeat header columns on each page” flag in the table property and the trick is done. In complex tables (consisting of several groups) this method will not work. To do this, open the "Advanced Mode"

And choosing the appropriate lines to set the property "RepeatOnNewPage" to True

4) Pagination when exporting to Excel

Very often when exporting data to an Excel spreadsheet, there is a need to split the data into sheets. There may be several reasons, for example, when using old versions of Excel (version 2003 or lower), there are restrictions on the maximum number of rows on 1 sheet (65,536 rows) or you need to display grouped data on each page. To do this, we come to the aid group.
Consider an example of exporting a dataset that exceeds 65,536 rows.
1) Create a group
2) In the group property, press the button to enter the Expression

and enter the condition:

 =CInt(Ceilng(RowNumber(Nothing)/65000)) 

3) In the group properties on the Page Breaks tab, set the flags as shown in the figure.

5) color the lines

Often it is necessary to make a selection in some other color of some data (for example, to make the coloring ala "zebra") This is done by writing the condition (Expression) for the BackgroundColor property

 = IIF(RowNumber(Nothing) Mod 2, "White", "Gainsboro") 

If it is necessary to select for example negative values ​​in red, this can be done by the following condition in the “Color” property of the cell.

 =IIF(ReportItems!YourCell.Value < 0, "Red", "Black") 

6) Page break

Sometimes you need to insert a page break after a certain block, it is very easy to make it. It is necessary to place an object of the type “Rectangle” in the place where it is necessary.
In the Rectangle properties set PageBreak-BreakLocation = Between. There is also a useful property “ResetPageNumber” used in case you need to reset the page numbering.


That's all for now, a new batch of tips in the next issue.