Programming Microsoft Dynamics NAV 2009 — Save 50%
Using this Microsoft Dynamics NAV book and eBook - develop and maintain high performance applications to meet changing business needs with improved agility and enhanced flexibility.
Make the report changes
Having reviewed the VS RD in some detail, let's begin the task of making the changes we defined earlier.
First, we will add the ICAN Campaign column to each data line. In the VS RD showing the original VS RD layout for Report 50002, right-click on the top line of the last data column. Ignore the three very small columns at the right end of the data line. These are Hidden Fields which we will explain later. You will see the following:
Then choose the option to Insert Column to the Right. That will (surprise!) insert another column—to the right. Now expand the Data Sources, so that you can access the fields we added earlier to the Classic Sections Designer. Those fields are Gift_Ledger__ICAN_Campaign_ (data) and Gift_Ledger_ICAN__Campaign_Caption (header).
Grab, drag-and-drop the data field into the new column, third row (table detail row). Grab, drag-and-drop the caption field into the new column, top row (table header row). Save, save, compile, and test. Depending on your data, the new column on your report may look similar to the following:
If so, then you need to change the properties of the data field so that the full data contents can be displayed. Return to the VS RD, highlight the field containing Gift_Ledger__ICAN_Campaign_. Confirm that the property Layout | CanGrow is False, rather than True. This will keep the data field from wrapping to the next line when it exceeds the width of the available space. Instead, it will be truncated when it exceeds the available space (obviously, you may prefer different behavior in different situations). The other change required in this case is to revise the Size | Width property to a larger number. After those changes are made, the next test gives the following results for a sample of the ICAN Campaign column of data.
Our second change is to add a Gift count to each total line. As all we need to do in this case is to count the detail lines printed, we should check if we can use a function which is built into the VS RD. We can count the lines if we can count the instances of one of the data elements, such as the Donor ID. Let's go exploring to see if we can find the tools and the technique to accomplish our goal.
We will start our exploration by investigating how a report transformation generated total has been created. Right click on the textbox containing the Estimated Value total (shown in the following screenshot):
Then click on the Properties option. As shown in the following screenshot, the Value field contains =Sum(Fields!Gift_Ledger__Estimated_Value_.Value), which is not listed in the Data Sources window. But, as Gift_Ledger__Estimated_Value_.Value is one of the items in the Data Sources window, it seems reasonable to assume that =Sum is a VS RD function and that we might find a Count function if we keep looking.
As we've determined that the Value field contains an expression, let's go back to the text box, right-click again, and choose Expression…. That will open the following form, where we can see the tools that are used to construct an expression like the one that sums the total gift amounts.
Our task now is to find a count function (or equivalent). We can continue our exploring, but let's move to a blank instance of the Edit Expression form, so we don't accidentally change the existing, working expression. Close the Edit Expression form for the Gift_Ledger_Estimated_Value and open an Edit Expression form for the Textbox in the same row, but in the column containing the Donor ID field (that is, the next column to the right in our example).
With that empty Edit Expression form open, if we can find a count function, perhaps we can build an expression to give us our desired result. Using the very practical "peek and poke" method (that is, clicking on various fields to see what we can find), we uncover the following.
There's the Count function we were hoping to find (along with quite a few other functions we can use in future situations). Double-click on the Count function or click on the Paste button, and the Count function will be pasted into the workspace above. Looking at the expression for the Estimated Value total, we can guess that now we want to identify what to count, in this case the Donor ID instance. If we click on the left pane, Fields (DataSet_Result), then we will see a list of all of the available data fields. Included in those is Gift_Ledger__Donor_ID, as shown in the following screenshot. The wavy line under the Count function in the workspace simply indicates that we don't have a complete operable expression defined yet.
Double-click on the field Gift_Ledger__Donor_ID (or highlight and click on Paste). The field name will appear in the Edit Expression workspace after the Count function. You will likely still see the wavy underline, indicating you're still not quite done. If you look at the expression for the Sum, you will see that it begins with an equals sign (=) and the field name is enclosed in parentheses. Let's make those additional changes. The final expression should look like the following:
Once you have that, you can close the Edit Expression form. You may notice at this point that the font for the new Count expression is different from (for example, larger) the other fields. If you want all of the fonts to be the same, obviously, you will need to change the font property of the new field to match the old fields. Once that is done, exit and save the VS RD layout changes, save and compile the report, and test it. You should now see a count of the gifts for each donor on the group total lines, looking something like the following:
If this were to be a production report to be given to a client, it is likely that we would work on the layout some more, perhaps more clearly identifying the gift count total. But we certainly have achieved our original goal of obtaining and printing the count.
Some interactive report capabilities
Our third change is to add some interactive capabilities to our report. We want the report to display in either a summary or detail format, and we want to allow sorting the detail into different sequences. Return again to the VS RD layout, focused on Report 50002.
Let's allow the users to sort on the gift amount or on the campaign. And, to provide full flexibility, let's also allow sorting on the gift date. Right-click on the heading for the column on which we wish to control the sort, and display the properties form—let's start with gift amount. Click on the Interactive Sort tab. Click on the option Add an interactive sort action to this textbox. Then choose the field on which the sort should occur. In this case, that's the =Fields!Gift_Ledger__Estimated Value_.Value:
Click on OK, and then perform a similar set of actions on the layout table column for the ICAN Campaign and Date fields. Once you've done that, exit VS RD, saving the changes, then save, compile and test the changed report design. You should see a report, the top of which looks something like the following. There will be pairs of up/down arrowheads that can be used for sort control. The last one of them used will show just the option not invoked (that is, only an up or a down arrowhead, not both).
The work required to implement our other interactive capability, Expand/Collapse Detail, is very similar to what we just did for the interactive sorting. Once again return to the VS RD for the layout of Report 50002. This time click on one of the layout table fields, so that the Table Element icons are visible. Then right click on the Table Detail icon.
Choose Properties, this time you will not get a Properties form, but will use the VS Property window (which defaults to the lower right panel of the layout screen, but is movable anywhere within the VS RD frame). Find the Visibility property group, expand it so that you can see the Toggle Item property. Click on the selection caret at the right end of the property space and choose the fi eld with which you would like to control the Expand/Collapse option. The Gift_Ledger_DateCaption field is a good choice, because it is the topmost left field, so the Expand/Collapse option will be quite visible to the user there. After you exit, save, compile, and run the report. You will see the Expand/Collapse icon in the top left of the report, as shown in the following screenshot, where the detail data is expanded (that is visible):
In the next screenshot, the detail data is collapsed (that is, hidden):
As you can see, the Expand/Collapse icon will be either plus or minus, depending on whether the related data is currently visible (plus) or hidden (minus). You should also experiment with using this feature to make columns on a report be visible or hidden, at the user's option.
Page Header fields
Fields that are displayed in the Page Header or Page Footer sections of the VS RD cannot contain variable data. As a result, a couple of different approaches are used to be able to display "normal" report headings that do contain variable data. One approach is the use of Hidden Fields. In the following screenshot at the point of the cursor arrow, three Hidden Fields are present, which were all created as part of the automatic transformation process of Create Layout Suggestion. These fields are essentially similar to other VS RD data fields, except they are set to not be visible, and are only used to manage data rather than display it directly.
Hidden Fields that are created by the Report Transformation process are set to red by default (and by convention). Hidden Fields that are created in the C/SIDE RD to hold special data or logic should be set to yellow (see the Help titled "How to: Add and Identify Hidden Fields"). This color coding makes the hidden fields' purpose easier to identify for software maintenance.
eBook Price: $41.99
Book Price: $69.99
Another approach takes advantage of the ReportItems collection. This is particularly useful for Page Header and Page Footer. The ReportItems collection contains all of the elements that are in the report Body. You can put a field on the report Body, make it invisible, then reference it from the header or footer via the ReportItems collection. In the layout generated by the Create Layout Suggestion transformation, this is the way the Report Caption, Company Name and Page Number information is displayed.
In this screenshot, the leftmost of these fields handles the Company Name, the second handles the Report Caption, the third handles the Page No. The fields defined in the Page Header then refer to the Hidden Field textboxes, thus overcoming the limitation on displaying variable data from Data Sources.
The second method of handling header information is to include it as an independent textbox within the body section. The standard reports often use this technique, as shown in the next screenshot from Report 108, Customer - Order Detail. There are two such textboxes in this report, both designed to display the filters that have been applied to a particular instance of the report execution:
A Request Page is a page that is executed at the beginning of a report. Its presence or absence is under developer control. A Request Page looks similar to the following screenshot based on one of the standard system reports, the Customer – Order Detail report, Report 108.
There are three FastTabs in this page. The Customer and Sales Order Line FastTab are tied to the data tables associated with this report. These FastTabs allow the user to define both data filters and Flow Filters to control the report processing. The Options FastTab exists, because the software developer decided to allow some additional user options for this report.
As our example report only has one table, the default Request Page for it has only one FastTab. As we have not defisned any of the processing options that would require user input before the report is generated, we have no Options FastTab (see following screenshot).
Our final enhancement of our Gifts by Donor report will be to add the option to allow the user to choose whether or not the report detail will include Gifts with a Category of SERVICES.
There is some very simple C/AL programming needed to support providing the user with this option:
- Open Report 50002 in the Classic RD.
- Access the C/AL Globals form from View | C/AL Globals.
- Add a new variable to the C/AL Globals as shown in the following screenshot.
- Exit the C/AL Globals form. Then click on the Gift Ledger DataItem and press F9. That will take us into the C/AL Editor. Add a line of C/AL code which will skip all Gift Ledger entries where the Category is 'SERVICES', unless the PrintServices variable is set to TRUE (see the following screenshot):
Finally, we need to design a Request Page that will display our Options FastTab along with the FastTab for filters on the Gift Ledger entries. The second line is a Type Group line, SubType Group, with a Caption of Options. The third line is Type Field with a SourceExpr equal to Global Variable (PrintServices) that we defined a moment ago. The Caption for this third line is the screen label we want for the variable, so that the user will understand the option available to them. If there were additional variables to be entered by the user, there would need to be additional field lines similar to the one in this page design. The page design is shown in the following screenshot:
When this is all done, the modified report, complete with the new Request Page design, should be saved and compiled, then Run for a test. Your result should look similar to the following screenshot. When you run the report with the Print Gifts of Services? option checked, the SERVICES entries should be printed. When the option is not checked, those entries should not be printed.
One of the report properties we reviewed earlier was ProcessingOnly. If that property is set to Yes, then the report object will not output a report, but will simply do the processing you program it to do. The beauty of this capability is that you can use the built-in processing loop of the NAV report object along with its sorting and filtering capabilities to create a variety of data updating routines with a minimum of programming. Use of report objects also gives you access to the Request Page to allow user input and guidance for the run. You could create the same functionality using codeunit objects and programming all of the loops, the filtering, the user-interface Request Page, and so on yourself. But with a Processing-Only Report, NAV takes the load off you.
When running a Processing-Only object, at the beginning you see very little difference as a user. You see that there is no visible output at the end of processing, of course. However, at the beginning, the Processing-Only Request Page looks very much as it would for a printing report, except that the Print and Preview choices are not available. Everything else looks the same.
Creating a report from scratch
Even when you're going to create a report that cannot be done with the Report Wizard, it's still a good idea to use the Report Wizard as a starting point, if feasible. Choose the primary table in terms of data flow and layout, then rough out the report using the Wizard. Once that is done, begin modifying the report using the Designer, in order to add parent and child data items, additional total levels, and so on.
If your final target report is to be an Invoice format, for example, you could begin the report development by using the Wizard to lay out the Invoice Detail line. You would then use the Designer to create the Invoice Header sections and the appropriate Footer sections.
A good designer defines the goal before starting development. To work the other way around is known as the "Ready, Fire, Aim" approach. Obviously, the first step is to define the need the report is to satisfy, and what data should be displayed in order to meet that need.
A wide variety of design decisions must be made, including factors such as the reporting sequence (that is the key to be used), what subtotals and totals should be calculated, what format will be easiest to use, and so on. The totaling requirements are often the determinant of what key will be appropriate.
A part of the design and development process is testing. Quite often, when a report is developed, there isn't a lot of meaningful test data available. Frequently, therefore, another task is to design and create quality test data. Testing should be done incrementally as individual changes are made. If too many changes are made without an intermediate test, it may be difficult to identify the source of problems. It is a good idea to compare the generated result to your original layout and identify what remains to be done. It's also always useful at this point to also compare the printout of the generated report to the sections, to see what controls were used for each of the displayed fields. From that information, you can begin to plan how you want to manually change the sections to make the final report look like your original design layout.
A key point is to realize that even though the Wizards are of limited capability, they still can be used to make the work a lot easier. This is especially true for relatively straightforward reports. There will certainly be occasions when using the Report Wizard is simply not useful. In that case, you will begin with a totally blank slate. There will also be cases where you start with the Wizard's output and strip out some of what the Wizard puts in. The reason for using the Wizard is not the high quality of what it generates (it's adequate, but not very elegant), but for the time it saves you.
Creative report plagiarism
When you want to create a new report of a type that you haven't done recently (or at all), it's a good idea to find another report that is similar in an important way and study it. At the minimum, you will learn how the NAV developers solved a data flow or totaling or filtering challenge. In the best case, you will find a model that you can follow closely, respectfully plagiarizing a working solution, thus saving yourself much time and effort.
Often, it is useful to look at two or three of the standard NAV reports for similar functions to see how they are constructed. There is no sense in re-inventing the wheel (recreating the design for a report of a particular type) when someone else has not only invented a version of it already, but provided you with the plans and given you the ability to examine the C/AL code and the complete structure of the existing report object.
When it comes to modifying a system such as NAV, plagiarism is a very effective research and design tool. In the case of reports, your search for a model may be based on any of the several key elements. You might be looking for a particular data flow approach and find that the NAV developers used the Integer table for some Data Items (as many reports do).
You may need a way to provide some creative filtering similar to what is done in an area of the standard product. You might want to provide user options to print either detail or a couple of different levels of totaling, with a layout that looks good no matter which choice the user makes. You might be dealing with all three of these design needs in the same report. In such a case, it is likely that you are using multiple NAV reports as your models, one for this feature, another for that feature, and so forth.
If you have a complicated, application-specific report to create, you may not be able to directly model your report on a model that already exists. But, often you can still find ideas in standard reports that you can apply to your new design. You will almost always be better off using a model rather than inventing a totally new approach.
If your design concept is too big a leap from what has been done previously, you will find the tools available have built-in constraints, which make it difficult to achieve your goal. In other words, generally you should build on the obvious strengths of C/AL. Creating entirely new approaches may be very satisfying (when it works), but too often it doesn't work well and costs a lot.
In this article, we focused on the structural and layout aspects of NAV Report objects. We studied the primary structural components, data and format, along with the Request Page. We also experimented with some of the tools and modestly expanded our ICAN application.
If you have read this article you may be interested to view :
- Report components in NAV 2009: Part 1
- Report components in NAV 2009: Part 2
- A Short Tour through NAV 2009: Part 1
- A Short Tour through NAV 2009: Part 2
- A Short Tour through NAV 2009: Part 3
- NAV 2009: Reports
eBook Price: $41.99
Book Price: $69.99