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.
