In this article by Steven Renders, author of the book Microsoft Dynamics NAV 2015 Professional Reporting, we will see how you can format report items and use placeholders, when you design the layout of a report in RDLC.
As you will noticed, when you create a new report layout, by default, amounts or quantities in the report are not formatted in the way we are used to in Dynamics NAV. This is because the dataset that is generated by Dynamics NAV contains the numerical values without formatting. It sends a separate field with a format code that can be used in the format properties of a textbox in the layout.
(For more resources related to this topic, see here.)
Formatting report items
Numerical fields have a Format property. This Format property is populated by Dynamics NAV and contains, at runtime, an RDL format code that you can use in the Format property of a textbox in Visual Studio.
To get started with formatting, perform the following steps:
- When you right-click on a textbox, a menu appears, in which you can select the properties of the textbox, as shown in the following screenshot:
- In the Textbox Properties window, go to Number and then select Custom. Click on the Fx button to open Expression Designer and type an expression. The result of the expression will be the value of the property. In this case, our expression should fetch the value from the format field from the Quantity field. The expression will be:
=Fields!Quantity_ItemLedgerEntryFormat.Value This means that the format of the textbox is fetched from the dataset field: Quantity_Item. - Instead of using Expression Designer, you can also just type this expression directly into the Formatcode textbox or in the Format property in the properties window of the textbox, as shown in the following screenshot:
Reporting Services and RDLC use .NET Framework formatting strings for the Format property of a textbox. The following is a list of possible format strings:
C: Currency
D: Decimal
E: Scientific
F: Fixed point
G: General
N: Number
P: Percentage
R: Round trip
X: Hexadecimal - After the format string, you can provide a number representing the amount of digits that have to be shown to the right of the decimal point.
For example:
F2 means a fixed point with 2 digits: 1.234,00 or 1,234.00
F0 means a fixed point with no digits: 1.234 or 1,234
The thousand and comma separators (.and,) that are applied, and the currency symbol, depend on the Language property of the report.
More information about .NET Framework formatting strings can be found here:
Custom Numeric Format Strings: http://msdn.microsoft.com/en-us/library/0c899ak8.aspx.
Standard Date and Time Format Strings: http://msdn.microsoft.com/en-us/library/az4se3k1.aspx.
As an alternative, you can use custom format strings to define the format value. This is actually how Dynamics NAV populates the Format fields in the dataset. The syntax is:
#,##0.00You can use this to define the precision of a numeric field. The following image provides an example:
Why does the Format property sometimes have no effect?
To apply formatting to a textbox, the textbox must contain an expression, for example, =Fields!LineTotal.Value or =1000.
When the text in the textbox does not begin with the = sign, then the text is interpreted as a string and formatting does not apply.
You can also set the format in the report dataset designer, instead of in the layout. You can do this by using the Format function. You can do this directly in the dataset in the SourceExpression of any field, or you can do it in the data item triggers, for example the OnAfterGetRecord() trigger. But, if you use an expression in the SourceExpression, you lose the option to use the IncludeCaption property.
A good example of a textbox format property is available here: http://thinkaboutit.be/2015/06/how-do-i-implement-blankzero-or-replacezero-in-a-report.
Using placeholders
If you select a textbox and right-click on it, you open the textbox properties. But, inside the textbox, there’s the placeholder. A placeholder is the text, or expression, that becomes the information displayed in the textbox at runtime. And the placeholder also has a set of properties that you can set. So you can consider a placeholder as an entity inside a textbox, with its own set of properties, which are, by default, inherited from its parent, the textbox.
The following screenshot shows that, when you right-click on the text in a textbox, you can then select its placeholder properties:
A textbox can contain one or more placeholders. By using multiple placeholders in one textbox, you can display multiple fields in one textbox, and give them different properties.
In the following example, I will add a header to the report, and in the header, I will display the company information.
To add a header (and/or footer) to a report, go to the Report menu and select:
- Add Page Header
- Add Page Footer
The following screenshot shows an example of this:
A report can contain a maximum of one header and one footer.
As an alternative you can right-click anywhere in the body of the report, in the empty space to the left or right of the body, and add a page header or footer.
The page header and page footer are always shown on every page, except if you decide not to show it for the first and/or last page by using the properties:
- PrintOnFirstPage
- PrintOnLastPage
Dynamically hiding a page header/footer
A page header and footer cannot be hidden dynamically. A workaround would be to put a rectangle in the page header and/or footer and use the Hidden property of the rectangle to show or hide the content of the header/footer dynamically. You need to be aware that, even when you hide the content of the page header/footer, the report viewer will preserve the space. This means that the header/footer is still displayed, but will be empty.
A page header or footer cannot contain a data region. The only controls you can add to a page header or footer are:
- Textbox
- Line
- Rectangle
- Image
So, in the page header, I will add a textbox with a placeholder, as in the following screenshot:
To do this, add a textbox in the page header. Then, drag a field from the dataset into the textbox. Then, add one or more spaces and drag another field into the same textbox. You will notice the two fields can be selected inside the textbox and, when they are, they become gray. If you right-click on the placeholder, you can see its properties. This is how you can see that it is a placeholder.
It is interesting that the mark-up type for a placeholder can be changed to HTML. This means that, if the placeholder contains HTML, it will be recognized by the report viewer and rendered, as it would be by a browser. The HTML tags that are recognized are the following:
- <A href>
- <FONT>
- <H{n}>, <DIV>, <SPAN>,<P>,
- <DIV>, <LI>, <HN>
- <B>, <I>, <U>, <S>
- <OL>, <UL>, <LI>
If you use these HTML tags in a badly organized way then they will be interpreted as text and rendered as such.
The possibility of using HTML in placeholders creates an opportunity for Dynamics NAV developers. What you can do, for example, is generate the HTML tags in C/AL code and send them to the dataset. By using this approach, you can format text and manage it dynamically via C/AL. You could even use a special setup table in which you let users decide how certain fields should be formatted.
In our example report, I will format the company e-mail address in two ways. First, I will use the placeholder expression to underline the text:
Then, I will go to the C/AL code and create a function that will format the e-mail address using a mailto hyperlink:
When you run the report, the result is this:
The e-mail address is underlined and there is also a hyperlink and, when you click on it, your e-mail client opens. As you can see, the formatting in the placeholder and the formatting in the C/AL code are combined.
Use a code unit or buffer table
In this example I used a custom function in the report (FormatAsMailto). In real life, it is better to create these types of functions in a separate code unit, or buffer table, so you can reuse them in other reports.
Important properties – CanGrow and CanShrink
A textbox has many properties, as you can see in the following screenshot.
If you right-click a textbox and select the textbox properties, they will open in a separate popup window. In this window, some of the textbox properties are available and they are divided into categories. To see all of the textbox properties you can use the properties window, which is usually on the right in Visual Studio. Here you can sort the properties or group them using the buttons on top:
The first button groups the properties. The second button sorts the properties and the third button opens the properties popup window.
I am not going to discuss all of the properties, but I would like to draw your attention to CanGrow and CanShrink. These two properties can be set to True or False. If you set CanGrow to True then the height of the textbox will increase if the text, at runtime, is bigger than the width of the textbox. With CanShrink, the height of the textbox may shrink.
I do not recommend these properties, except when really necessary. When a textbox grows, the height increases and it pushes the content down below. This makes it difficult to predict if the content of the report will still fit on the page. Also, the effects of CanGrow and CanShrink are different if you run the report in Preview and export it to PDF, Word, Excel, or if you print the report.
Example – create an item dashboard report
In this example, I am going to create an item dashboard report. Actually, I will create a first version of the dashboard and enhance it.
The result of the report looks like the following screenshot:
What we need to do is to show the inventory of a list of items by location. The report also includes totals and subtotals of the inventory by location, by item and a grand total. To start, you define a dataset, as follows:
In this dataset, I will start with the item table and, per item, fetch the item ledger entries. The inventory is the sum of the quantities of the item in the item ledger entry table. I have also included a filter, using the PrintOnlyIfDetail property of the item data item. This means that, if an item does not have any ledger entries, it will not be shown in the report. Also, I’m using the item ledger entry table to get the location code and quantity fields. In the report layout, I will create a group and calculate the inventory via an aggregate function.
In real life, there might be many items and ledger entries, so this approach is not the best one. It would be better to use a buffer table or query object, and calculate the inventory and filter in the dataset, instead of in the layout. At this point, my objective is to demonstrate how you can use a Matrix-Tablix to create a layout that has a dynamic number of rows and columns.
- Once you have defined the dataset, open the layout and add a matrix control to the report body. In the data cell, use the Quantity field, on the row, use the Item No and, on the column, use the Location Code. This will create the following matrix and groups:
- Next, modify the expression of the textbox that contains the item number, to the following expression:
=Fields!Description_Item.Value & " (" & Fields!No_Item.Value & ")"This will display the item description and, between brackets, the item number.
- Next, change the sorting of the group by item number to sort on the description:
- Next, add totals for the two groups:
This will add an extra column and row to the matrix.
- Select the Quantity and then select the Sum as an aggregate. Then, select the four textboxes and, in the properties, apply the formatting for the quantity field:
- Next, you can use different background colors for the textboxes in the total rows and resize the description column, to resemble the layout in the preceding screenshot. If you save and run the report, you have now created an item dashboard.
Notice how easy it is to use the matrix control to create a dashboard. At runtime the number of columns depends on the number of locations. The matrix has a dynamic number of columns. There is no detail level, because the ledger entries are grouped on row and on column level.
Colors and background colors
When using colors in a report, pay attention to how the report is printed. Not all printers are color printers, so you need to make sure that your visualization has an effect. That’s why I have used gray colors in this example.
Colors are sometimes also used by developers as a trick to see at runtime, where which textbox is displayed and to test report rendering in different formats. If you do this, remember to remove the colors at the end of the development phase of your report.
Summary
Textboxes have a lot of properties and contain placeholders, so we can format information in many ways, including using HTML, which can be managed from C/AL, for example using a layout setup table.
It’s important to understand how you can formatting report items in Dynamics NAV, so you can create a consistent look and feel in your reports as it’s done inside the Dynamics NAV application.
Resources for Article:
Further resources on this subject:
- Standard Functionality[article]
- Understanding and Creating Simple SSRS Reports[article]
- Understanding master data [article]
