December 29, 2012 at 22:01Microsoft Reporting Services Tips for Beginners. Part 2
We continue to consider useful tips for beginners when creating reports in Microsoft Reporting Services.
Start can be found here: Microsoft Reporting Services Tips for Beginners.
Interested, please, under the cat…
Quite often there is a need to place several controls in 1 cell of the table. If you simply drag the control into the cell, then it completely fills it. The control called Rectangle helps to get out of this situation.
Rectangle is an analogue of the Panel control used in WinForms, it is a kind of platform for placing controls. When placed in a cell, the Rectangle fills the entire cell of the table by itself, creating a “platform” for placing the remaining controls.
When creating reports, try to declare the language of the report (Report Properties - Language).
This is very useful if you are working with a foreign client whose regional settings are different from yours. If you do not know what settings your client uses, then it is possible to specify Expression with the following contents:
This way you will avoid any irregularities in the formatting of numerical values, dates, times, currencies, etc. that the client expects.
Sometimes it becomes necessary to “insert” the value of a field from the database into the static text of a report placed, for example, in texbox. In other words, you need to add lines of text to 1 line. For this, the operand "&" is provided in SSRS.
Suppose in texbox we have static text
upd: As the respected pokryshkin suggested in a comment , a more correct way to concatenate strings is to use placeholder.
Using it is simple: in texbox, you need to position the cursor in the place where you intend to insert the value from the database field. Right-click the context menu and select "Create placeholder ..."
Set the Label value (this value will be visible in the text, if you do not specify a value for the Lable field, then the field name from the database will be used) and Value (in our case, this field from the database)
The use of placeholder provides additional benefits:
1) You can see the text that is under control (in the method that I described, the user sees only the text
2) Placeholder allows you to format the field (set the data format, change the font, color, alignment, etc.) without changing the formatting body text.
The difference "on the face":
SSRS provides several options for formatting data. The easiest way to format this is to specify the format (Format property) in the control properties.
This method has 2 drawbacks:
1) the control contains, in addition to the data that is supposed to be formatted, additional text.
2) when exporting a report to xml It is possible to
solve this problem by calling special methods. Consider an example:
Textbox contains the following text:
If you do not format, then, provided that the value of Fields! UserBalance.Value is 1005.35, it can be displayed to the user as 1005.350000000000. To avoid this incident, you must use the Format or FormatNumber method .
Usage example:
When using Format, you must use the formatting mask; when using FormaNumber, it is enough to specify the number of decimal places.
Note: Format method is also applicable for formatting date, time, money, etc.
SSRS allows you to export the report in different formats, one of them is CML. Each control of the report has 3 properties that are responsible for the behavior when exporting to xml.
Let's take a closer
look : DataElementName - responsible for the name of the element in the xml, by default the value is not satisfied. If this element is not performed, then the name in xml will correspond to the name of the control.
DataElementOutput - the property is responsible for whether the element will be exported to xml or not. Values that can be set:
Auto (default value) - the element will be exported if it matters, if null, then it is not exported.
Output - the element is always exported.
Nooutput- the element is not exported in the
DataElementStyle - is responsible for the style of the element in the HTML (will it be an element or attribute)
Values that can be used:
Auto (default value) - depending on the control, it will be exported as an element (node) or attribute (attribute) . In fact, only texbox is exported by default as an attribute, the rest of the elements as nodes.
Attribute - the value will be represented as an
Element attribute - the value will be represented as an element (node)
An important trick: the Hidden property does not apply to export to HTML. Therefore, if you intend to export an element that is displayed or not based on the condition, then this condition must be transferred to the Expression control. Example: textbox contains text that is shown only when the field condition = 1. Then Expression will look like this:
That's it for today, to be continued.
Start can be found here: Microsoft Reporting Services Tips for Beginners.
Interested, please, under the cat…
0) How to place several controls in a table cell?
Quite often there is a need to place several controls in 1 cell of the table. If you simply drag the control into the cell, then it completely fills it. The control called Rectangle helps to get out of this situation.
Rectangle is an analogue of the Panel control used in WinForms, it is a kind of platform for placing controls. When placed in a cell, the Rectangle fills the entire cell of the table by itself, creating a “platform” for placing the remaining controls.
1) Set the default report language
When creating reports, try to declare the language of the report (Report Properties - Language).
This is very useful if you are working with a foreign client whose regional settings are different from yours. If you do not know what settings your client uses, then it is possible to specify Expression with the following contents:
=User!Language
This way you will avoid any irregularities in the formatting of numerical values, dates, times, currencies, etc. that the client expects.
2) String concatenation
Sometimes it becomes necessary to “insert” the value of a field from the database into the static text of a report placed, for example, in texbox. In other words, you need to add lines of text to 1 line. For this, the operand "&" is provided in SSRS.
Suppose in texbox we have static text
«Здравствуйте, ув. Иван Иванович…».
=” Здравствуйте, ув. “ & Fields!fio.Value & ” …”
upd: As the respected pokryshkin suggested in a comment , a more correct way to concatenate strings is to use placeholder.
Using it is simple: in texbox, you need to position the cursor in the place where you intend to insert the value from the database field. Right-click the context menu and select "Create placeholder ..."
Set the Label value (this value will be visible in the text, if you do not specify a value for the Lable field, then the field name from the database will be used) and Value (in our case, this field from the database)
The use of placeholder provides additional benefits:
1) You can see the text that is under control (in the method that I described, the user sees only the text
<> , which introduces the person who sees the report code for the first time in confusion and spends his time searching for the desired Expression.) 2) Placeholder allows you to format the field (set the data format, change the font, color, alignment, etc.) without changing the formatting body text.
The difference "on the face":
3) Methods for formatting numeric data
SSRS provides several options for formatting data. The easiest way to format this is to specify the format (Format property) in the control properties.
This method has 2 drawbacks:
1) the control contains, in addition to the data that is supposed to be formatted, additional text.
2) when exporting a report to xml It is possible to
solve this problem by calling special methods. Consider an example:
Textbox contains the following text:
“Уважаемый клиент, остаток средств на вашем счету составляет: ” & Fields!UserBalance.Value & “ руб.”
If you do not format, then, provided that the value of Fields! UserBalance.Value is 1005.35, it can be displayed to the user as 1005.350000000000. To avoid this incident, you must use the Format or FormatNumber method .
Usage example:
“Уважаемый клиент, остаток средств на вашем счету составляет ” & Format(Fields!UserBalance.Value,”#,##0.00”) & “ руб.”
“Уважаемый клиент, остаток средств на вашем счету составляет ” & FormaNumber(Fields!UserBalance.Value,2) & “ руб.”
When using Format, you must use the formatting mask; when using FormaNumber, it is enough to specify the number of decimal places.
Note: Format method is also applicable for formatting date, time, money, etc.
4) Export report in hml format field settings
SSRS allows you to export the report in different formats, one of them is CML. Each control of the report has 3 properties that are responsible for the behavior when exporting to xml.
Let's take a closer
look : DataElementName - responsible for the name of the element in the xml, by default the value is not satisfied. If this element is not performed, then the name in xml will correspond to the name of the control.
DataElementOutput - the property is responsible for whether the element will be exported to xml or not. Values that can be set:
Auto (default value) - the element will be exported if it matters, if null, then it is not exported.
Output - the element is always exported.
Nooutput- the element is not exported in the
DataElementStyle - is responsible for the style of the element in the HTML (will it be an element or attribute)
Values that can be used:
Auto (default value) - depending on the control, it will be exported as an element (node) or attribute (attribute) . In fact, only texbox is exported by default as an attribute, the rest of the elements as nodes.
Attribute - the value will be represented as an
Element attribute - the value will be represented as an element (node)
An important trick: the Hidden property does not apply to export to HTML. Therefore, if you intend to export an element that is displayed or not based on the condition, then this condition must be transferred to the Expression control. Example: textbox contains text that is shown only when the field condition = 1. Then Expression will look like this:
=IIF(Fields!Condition.Value = 1, “Some text”,””)
That's it for today, to be continued.
