Archive for the ‘Uncategorized’ Category
Formatting the Style Properties
The grid has some unique formatting properties that don’t appear with other objects. These are called the Style properties. Right-click on the cross-tab object and select Cross-Tab Expert.
Figure 13-6. Selecting the Cross-Tab Expert menu item.
Click on the Style tab of the Cross-Tab Expert (the second tab) to choose from a list of more than a dozen predefined styles. As you click on each style, the right window shows a template of how your cross-tab object will be formatted. The Style tab makes formatting the cross-tab object easy.
Click on the Customize Style tab to make granular changes to the cross-tab’s style. If you made any custom formatting changes previously, you are prompted about whether you want to save that style. If you choose Yes, the new style will be reflected on the Customize Style tab. If you choose No, the Customize Style tab will reflect the formatting of the cross-tab object before you opened the Cross-Tab Expert.
Figure 13-7. The CustomizeStyle tab of the Format Cross-Tab expert.
The Customize Style tab has numerous properties that you can use to format the cross-tab grid to look exactly like you want. There is a Rows window and a Columns window and within each window are the names of the grouping fields. Click on the field you want to format. The properties and their descriptions are listed in the Group Options frame directly below those windows. Each of these properties only affects the group field that is currently selected. These style properties are listed in Table 13-2.
Table 13-2. Style formatting properties for cross-tab group options.
|Suppress Subtotal||When you have multiple groups for a row or column, the cross-tab grid shows a subtotal for the top-most groups. This suppresses that subtotal from printing.|
|Suppress Label||Suppresses the label for a suppressed subtotal field.|
|Alias for Formula||Changes the name that you use to reference the group in the conditional formatting formulas.|
|Background Color||Sets the background color for the cell.|
At the bottom of the dialog box is a frame titled Grid Options. The properties listed in this frame apply to the entire cross-tab. These grid options are listed in Table 13-3.
Table 13-3. Grid options for the cross-tab object.
|Show Cell Margins||By default, each group field has a margin surrounding it. Turning this off makes the edge of the group field flush with the grid lines.|
|Indent Row Labels||The row labels can be indented so that they are offset from the Total row. This makes it easier to notice the Total row and it makes your report appear more professional. When this is checked, you can specify the indentation in inches.|
|Repeat Row Labels||When there are too many columns to fit on a single page, they will span across to the next page. Turning this option on causes the row labels to be printed on the additional pages.|
|Keep Columns Together||Select this option (it is selected by default) to force columns that span multiple pages to stay intact. Unselecting this option could cause a column to be split in half.|
|Row/Column Totals on Top||Forces row totals to be at the top-most row and column totals will be left-most column. Otherwise they appear at the bottom and to the right.|
|Suppress Empty Rows/Columns||Don’t print rows/columns with no data.|
|Suppress Row/Column Grand Totals||Don’t print the grand-totals for rows and/or columns.|
The Format Grid Lines button is used to set the line styles for the grid. Clicking on this button brings up the Format Grid Lines dialog box shown in Figure 13-8. For each grid line in the cross-tab object, you can set the color, style, and width properties. You can also suppress a line by un-checking both Draw options. If you don’t want any grid lines to be shown then uncheck the Show Grid Lines option.
Figure 13-8. The Format Grid Lines dialog box.
One important point to be aware of when highlighting individual summary fields is that you can’t modify them using the Cross-Tab Expert dialog box. This dialog box applies changes to the cross-tab report as a whole. It doesn’t affect the data within the report. To do this, you have to move your cursor on top of the summary field and right-click. This opens the pop-up menu where you can select Format Field. See Figure 13-9 for an example.
Figure 13-9. Summary field pop-up menu.
Managing the Columns
One of the most interesting features of the cross-tab object is that it is dynamic. The number of columns changes according to the data being displayed. As an example, assume that you have a cross-tab report where the columns represent the historical sales figures per year. If a company has been in existence for five years, there will be five columns printed. Once a new year starts, a new column is automatically added to the cross-tab. Columns are created on an as needed basis without requiring any additional work on your part.
Dynamic columns are a blessing and a curse. The fact that you can even have dynamic columns is great. Being able to have the number of columns grow and shrink according to the data in a table is very powerful. You can’t do this with standard reports objects. The drawback to this feature is that you can’t control how many columns are created and you can’t insert additional columns. You are at the mercy of the data.
Having columns dynamically added to your report is a problem when you get more columns than you expected. For example, assume that your company has twenty divisions world-wide and you formatted the cell’s width so that there is just enough room on the page to represent each division. The report runs fine until six months later when your company acquires three new divisions. Now, your columns run off the edge and onto a new page. The number of pages printed has just doubled and everyone is complaining to you about it. You have to watch out for this behavior and correct it, if necessary.
Figure 13-10. Adding a heading to the cross-tab object.
For cross-tab objects that span across multiple pages, you can limit their size by placing them in a subreport. If you place a cross-tab object in a subreport, it will not print wider than the width of the subreport. By making the subreport object the width of a single page, the cross-tab object will only print on that page. This can be used to give the user a “preview” of the cross-tab report without spanning across multiple pages. If the user wants to see the entire cross-tab object, then they can click on it to open it in another tab.
Another problem with dynamic columns is that you can’t insert additional columns in the grid. This is a common problem with reports that use the month of the year as the column. When the report is run at the end of the year, there are twelve columns and this is what you would expect. But when the report is run in February, only two columns are printed: January and February. Some people want their reports to show all twelve months even if they haven’t occurred yet. Cross-tab reports won’t do this. A similar problem is a report that uses the weeks of the year as the column heading. Assume a company is a production plant and they want to see the volume of units produced every week. Occasionally, the plant builds up too much inventory and is shut down for a week. The report should show a zero balance for the week that was shutdown. But since there wasn’t any activity, no records exist for that week and a column won’t be printed. The cross-tab report can’t print a zero-filled column for that week because it doesn’t have any data to even know that the week exists.
There really isn’t an easy solution for this. The best advice is to write a SQL query that uses a creative Outer Join statement to generate zero value data for the missing records. Another option is to create a zero filled table with a record for each column. Use a SQL Outer Join statement to join it with the live data. Each situation is unique and presents a new challenge.
Calculated Members and Embedded Summaries
Throughout this chapter, we’ve seen that cross-tabs are very powerful because they give Crystal Reports the ability to create summarized data and display it in an easy to read grid format. But after telling Crystal Reports the data we want summarized, we had very little control over customizing that data. In other words, we tell Crystal Reports to take some data, calculate the summaries and then display the results. We can’t insert new rows or columns that handle special circumstances.
For example, not being able to customize a cross-tab object has always created problems in the area of financial reporting. When creating financial reports, cross-tab objects let you create rows and columns of complex calculations and summaries. But within a financial report you often need to insert special rows that calculate subtotals of individual rows or add summary data for specific columns. Cross-tab object don’t allow this.
Starting with Crystal Reports 2008, we have the power to create custom calculations and summaries within a crosstab object. This powerful new feature makes it easy to create custom rows and summaries in a cross-tab object. Let’s look at some examples where custom rows and summaries can be beneficial.
- For every two rows printed in a cross-tab, insert a new row that shows the percent difference for those two rows.
- If a row heading starts with the text, “Less:”, subtract it from the previous row and display the subtotal on a new row.
- You want every other row in the cross-tab to be blank so that it is easier to read and the reader has room to write notes
- A new column should be created which subtracts the first column from the third column.
Creating Calculated Members
A calculated member is a way to insert a new row or column anywhere within the existing rows and columns. It can be as simple as inserting a blank row into the cross-tab to make it more readable, or it can be as complex as inserting a new formula that couldn’t be calculated using the simple summary formulas that are provided with Crystal Reports by default. Let’s look at Figure 13-11. It shows an example cross-tab object with two examples. The first is a blank row and the second is summing two separate rows within the cross-tab object.
Figure 13-11. Samples of calculated member rows.
- A blank row is inserted after the Competition product type subtotal.
- A calculated member that totals the Gloves and Helmets subtotals.
Let’s walk through an example of creating a calculated member. As we go through the example, we will look at the different parts that make up a calculated member and how to use these parts to modify it.
Tutorial 13-3. Insert an empty row
This tutorial shows you one of the simplest examples of using the calculated member functionality. It inserts a blank row after a specific row in the cross-tab object.
- Open up any cross-tab object and view it in the Preview tab so that you can see the data.
- Pick a row where you would like to insert a blank row and right-click its label field. Select Calculated Member > Insert Row from the pop-up menu.
- Note: a dialog box will probably pop up that tells you a little about calculated members. Just click the OK button to close it.
- Look at the cross-tab object and you’ll see that a new row has been inserted after the one you clicked on. Unfortunately, instead of being an empty row it is actually a row of zeros in each summary value. We want the entire row to be empty, so let’s get rid of the zeros.
- Right-click on one of the zeros (the summary value) in the new row that was just created, and select the menu options Calculated Members > Edit Calculation Formula. This opens Formula Workshop.
- Look at the bottom right-corner of the Formula Workshop and you’ll see that the formula is simply a zero. Delete the zero and click the Save and Close button.
Note: You might attempt to enter an empty string to blank out the row value. But this will give you an error that the formula is expecting a number. Thus, you can’t enter an empty string here. Simply deleting the zero from the formula and not typing anything else is sufficient to allow the formula to be saved without generating an error message and printing an empty row.
The row should now be empty and the zeros have disappeared. If you have more than one summary function in your cross-tab object, you’ll need to delete the calculation formula for each summary.Note: After changing the calculation formula to be blank, you can no longer right-click on it and select the Edit Calculation Formula menu option. It disappears for empty rows. Thus, if you want to modify the calculation formula again, you’ll have to do so via the expert dialog which we’ll discuss later in this section.
When Crystal Reports creates a calculated member, it needs to know where you want the new row to be placed in the cross-tab object. This is called the Insertion formula. To build this formula, you create a set of conditions that will only return true when that specific row is printing. When Crystal Reports sees that the formula returns true, it inserts the new calculated member after the current row. For example, if the row header is a unique value then it is ideal for identifying the specific row. Let’s look at the insertion formula for the blank row that we created in the previous tutorial.
Right-click on the header label for the blank row that was just created and select the menu options Calculated Member > Edit Insertion Formula. This opens the Formula Workshop dialog box with the insertion formula in it.
Here is the formula that was created for my report. Your formula will be similar to mine with the exception that the specific field name will be different.
GetRowGroupIndexOf(CurrentRowIndex) = 1 and GridRowColumnValue(“Customer.Customer Name”) = “Aruba Sport”
This formula tests for two conditions: that the current cell is part of the correct group level and that the value of the row header is “Aruba Sport”. The first condition uses the formula GetRowGroupIndex() to determine the current row’s group level. In this example, there is only one group so the test checks that the group level is 1. Of course, since there is only one group level, you could also leave this test out and get the same result.
The second test checks the value of the current row header cell to make sure it is on the correct row. It does this by looking at the header cell’s value and checking that it is equal to “Aruba Sport”. If both conditions are true, the blank row is inserted after the row.
Calculations with Two Separate Rows
As mentioned in this section’s introduction, calculated members let you perform calculations on two or more rows within a cross-tab. Crystal Reports has pre-defined menu options that make it easy to create the formulas which add, subtract, multiply and divide two rows. You just need to tell it which rows to use and which arithmetic function to perform on them.
To create a calculated member using one of the predefined functions, the first step is to right-click on the first value you want to use in the formula and select the Calculated Member option from the context menu. For example, if you want to create a formula which is “Row A – Row B” then the first value would be from Row A.
After telling Crystal Reports which row is the first value in the formula, right-click on the row that is the second value and select the Calculated Member menu option again. This time you specify whether you want to add, subtract, multiple or divide it by the first row. After doing this, a new row is automatically created below the second row.
It’s also important to know that the new row that is created is located directly below the second value chosen (i.e. Row B in our example). You can always change this later if necessary, but it makes it easier if you think about this in advance when choosing which row will be first and second.
The easiest way to see how this works is with a quick tutorial!
Tutorial 13-4. Summing Two Rows Together
In this tutorial, we will walk through the steps of summing together two rows in a cross-tab object. I will use the sample cross-tab report that comes with Crystal Reports to illustrate how to do it.
The Crystal Reports sample report summarizes sales for each bike product and groups the products by category. Let’s sum the total sales for bike gloves and helmets into one row.
- Open the Crystal Reports sample report called CrossTab.rpt.
- Right-click on the row header “Gloves” and select the menu options Calculated Member > Select “Gloves” as first value.
- Right-click on the row header “Helmets” and select the menu options Calculated Member > Sum of “Gloves” and “Helmets”.
As you can see in Figure 13-2, when you selected “Helmets” as the second value, there were more menu options you could have chosen from.
Figure 13-12. Pre-defined summary options.
Notice the other options available to you when you click the “Helmets” row. The first option lets you select “Helmets” as the first option (in case you previously made a mistake and want Helmets to be the first value).
The second option, Insert Row, inserts an empty row after the current row (as discussed earlier). The third option, Insert Child, is only available for subtotal rows and it inserts a row above the current row. Thus, it effectively inserts a row that is a child of the current subtotal row and places it above the subtotal row. This lets you create a calculated member that always appears as the last row before the sub-total is printed.
The third group of menu options let you choose from one of the four built-in formulas for calculated members (add, subtract, multiply, and divide).
Creating Complex Calculated Members
It’s great that Crystal Reports makes it easy to perform basic calculations on two rows in a cross-tab object, but there are times when you need to create formulas that are more complex. For example, you could create calculations based on your company’s business processes, or summarize data between three or more rows. Let’s look at the requirements for creating more complex formulas.
Creating the Insertion Formula
The first requirement is to define where the calculated member should appear in the cross-tab object. This is done in the Insertion Formula.
As we learned earlier, the insertion formula tells Crystal Reports where to “insert the row” in the cross-tab object. Each time a row is printed, the insertion formula is calculated and if it returns True, then a row is created for the calculated member.
In most cases, you want your formula to return True for a specific row in the cross-tab object so that the calculated member is only printed once and not duplicated. The most common way of determining which row is currently being printed is by getting the row or column header value. If it is the correct value then insert the new row. The formula GridRowColumnValue() returns the value of the current row or column being printed.
The following formula returns True when the product type is “Competition” and the product name is “Endorphin” and inserts a row afterward.
GridRowColumnValue(“Product_Type.Product Type Name”) = “Competition” and GridRowColumnValue(“Product.Product Name”) = “Endorphin”
If your cross-tab object has multiple groups and a row header could be repeated, you need to be careful that the value you are testing for isn’t in both groups and causes too many rows to be inserted. In an employee report that groups each person by their supervisor, a person could be listed as both an employee and as someone’s supervisor.
To ensure you have the correct group level, use the GetRowGroupIndexOf(CurrentRowIndex) or GetColumnGroupIndexOf(CurrentColumnIndex). This returns 0 for the outermost group, 1 for the first sub-group, and so on. Turn back to Tutorial 13-3 and at the end is the formula created by Crystal Reports for inserting a blank row after the “Aruba Sport”row. It uses both of these formulas for testing the group level and the value of the current row.
Tutorial 13-5. Print a blank row after every subtotal.
Let’s look at an example that doesn’t rely upon the value of a specific row header. This tutorial prints a blank row after each sub-total. We do this by letting Crystal Reports create a generic blank row and then we modify its formulas.
- Open the Crystal Reports sample report “CrossTab.rpt” and view it in the Preview tab.
- Right-click on the row header value “Competition” and select the menu options Calculated Member > Insert Row. This inserts a blank row after the Competition sub-total row.
Right-click on the row header value for the blank row that you just created and select the menu options Calculated Member > Edit Insertion Formula. This opens the Formula Workshop Editor and displays the insertion formula:
GetRowGroupIndexOf(CurrentRowIndex) = 1 and GridRowColumnValue(“Product_Type.Product Type Name”) = “Competition”
Just like the formula we looked at before, this formula first checks the group level index and the value of the row header. In this example, it only prints a new row when the row header value is “Competition”, but we want it to print after every sub-total for the group. To do this we will delete the GridRowColumnValue() function. The new formula should be this:
GetRowGroupIndexOf(CurrentRowIndex) = 1
This formula inserts a new calculated member row every time a row is printed where the group index has a value of 1. This effectively prints a new row every time a sub-total is printed for this group.
- Save the new formula and preview the report. There will be a blank row printed after every sub-total row. Unfortunately, once again it prints a zero for every column. Let’s clean that up as well.
- Right-click on one of the zeros in the new row and select the menu options Calculated Member > Edit Calculation Formula. This opens the Formula Workshop Editor.
- Delete the “0″ from the formula and leave the formula blank.
- Save and close the formula.
Figure 13-13. Blank rows printed after every sub-total.
There are many more functions that you can use to determine which row or column is currently being printed. These formulas give you a lot of options for creating more complex insertion formulas. We’ll look at these functions in more detail later in this chapter.
Creating the Calculation Formula
Now that we’ve had a chance to create some calculated member rows and modify the insertion formula, let’s look at how the summary values are calculated for each row. This is called the calculation formula.
The essence of the calculation formula is that it takes a value from one row and performs a calculation with another row. We use the GridValueAt() function to do this. It returns the value of any cell in the cross-tab object.
The GridValueAt() function is passed a row number, column number, and a summary index number. The way this function is typically used is that as the cross-tab object prints each cell in a row, you pass it the current row and cell number to get the current value being printed. For example, you could change the cell’s font color to red if its value is greater than $100,000.
A calculated member uses the GridValueAt() function slightly different because it typically gets the value of a specific row within the cross-tab object and performs a calculation with another cell in the cross-tab. It will still use the current column being printed, but the current row may or may not be used. Let’s look at an example to see how this works.
In Tutorial 13-b we used a calculated member to add the values of the Gloves row with the Helmets row. Here is the formula that Crystal Reports created to do this:
GridValueAt(GetRowPathIndexOf(“Gloves”), CurrentColumnIndex, CurrentSummaryIndex) + GridValueAt(GetRowPathIndexOf(“Helmets”), CurrentColumnIndex, CurrentSummaryIndex)
This formula can be broken down into two parts. The first half gets the current value from the Gloves row and the second part gets the current value from the Helmets row. It then adds these two values together and displays them on the new row. Let’s look at the formula in more detail.
The first half of the formula uses the GridValueAt() function to get the current cell value. But rather than using the current row, it uses the GetRowPathIndexOf() function to get the index of the row that has “Gloves” in its row header. The rest of the function is passed the current column index and current summary index values because we still want the data in the current column to be totaled.
The second half of the formula is almost identical to the first except that it gets the value from the Helmets row.
The key point to remember is that the function GetRowPathIndexOf() is used to get the value of the current column from any row in the cross-tab object. Pass it the value in the group row header, (e.g. “Gloves” or “Helmets”), and it returns the row number that can be used in the GetValueAt() function.
Of course, your own formulas can be as complex as you want to make them. You are not limited to doing basic arithmetic or only using the values from two rows in your formulas. You can use any business logic that you like as well as include as many rows as necessary.
Customizing with the Calculated Member Expert
After creating a calculated member and getting the formulas working, you have a few options for customizing it. For example, you can change the header text, or set whether it is inserted either before or after the current row being printed. Customizing a calculated member is done using the Cross-Tabs Calculated Member Expert dialog box.
Figure 13-14. Cross-Tab Calculated Members Expert dialog box.
To open this dialog box, you can view the cross-tab object in either the Design tab or Preview tab. Right-click on the top left corner of the cross-tab object and select the menu options Advanced Calculations > Calculated Member.
The dialog box is broken down into four sections. The top left corner lists the calculated members in the cross-tab. When customizing a calculated member, first select it from this list so that it is highlighted.
In the section below the dialog box are five buttons that let you modify the calculated members. They are as follows:
- New: Creates a new calculated member and adds it to the list. You have to use the remaining buttons to set its properties.
- Remove: Remove a calculated member from the list.
- Edit Insertion Formula: As discussed in detail earlier, the insertion formula determines where the calculated member’s row will be inserted into the cross-tab object.
- Edit Row Value Formula: Edit the group value of a calculated member. You need to assign a string to the group value if you want to reference the calculated member in another formula.
- Edit Header Formula: This sets the string printed on the cross-tab for the row’s header value. If this is left blank, and the row value has a formula assigned to it, the row value formula is displayed on the cross-tab.
The top right corner of the dialog box lets you set the properties of the calculated member.
- Description: The description is displayed in the top left corner of the dialog box. As you can see in the figure, the default description is meaningless and it is helpful to enter a description that clearly identifies its purpose.
- Type: You can have the calculated member inserted as a new row or column.
- Insert Evaluation: Sets whether the calculated member row is inserted either before or after the current row that triggered it.
- Group Level: Lets you specify the calculated member’s group level.
The bottom right corner of the dialog box is a list of the summary fields that the cross-tab calculates. These are called value formulas in the dialog box. Because a calculated member has a value displayed for every summary field in the cross-tab object, you need to create a formula that calculates the value that should be displayed for each summary field.
Note: This formula has two names in Crystal Reports. In the dialog box it is referred to as Edit Value Formula, but the pop-up menus call it a Calculation Formula. Recall that earlier in the chapter we discussed that you edit this formula by right-clicking on the cell and selecting Calculated Members > Edit Calculation Formula. Personally, I always call it the calculation formula because it calculates the value displayed in each cell.
To change the edit value formula (i.e. the calculation formula), select one of the summary fields and click the Edit Value Formula button to enter the calculation formula.
When you create a cross-tab object, you first specify the row and column groups, and then you add fields to be summarized within the cross-tab. The summary calculations appear within each column and for every row. But there is a limitation with summary fields because the calculations are based on the raw data within the report. You can’t perform calculations on the summarized values as they appear in the cross-tab object. For example, you can’t subtract the first summary field from the second summary field.
Embedded summaries get around this deficiency by letting you perform calculations between different summaries in the cross-tab.