Tutorial 13-6. Different Formulas for Each Row

As I was finishing this chapter, someone posted a question on the forum that is perfect for this chapter. Rather than explain it, I’ll show the question here:

Hi, I was asked to display this data in a cross-tab and I’m wondering if this is possible: We have regions, services and clients. The clients are classified according to different services they take. Depending on this service, I have to create totals in a different way and show those totals in the crosstab. The crosstab should look like this:

For the S1 row, I have to count the number of different clients that take service S1. For the S2 row I have to count the $ amount those clients pay. For the S3 row I have to take 2 dates (d1 and d2) and show the difference.

I know I’m showing different kinds of values in each row (and the general total will have no meaning) but I was asked to show them all together in the same table because they are related in some way and they need to see them together.

Could I do something like this?

This is a great example of a very unique requirement for a cross-tab report. Let’s build it ourselves and see how it’s done. Of course, since we don’t have her exact data available, we need to tweak the sample a bit. But, we’ll worry about that when we come to it.

Before getting into building the report, let’s look at the primary focus of how to create the report. The difficulty will be in creating a function that returns three different values within the cross-tab output.

The first requirement is that if the service is S1, the summary should count how many customers are in that service. The cross-tab object’s summary function is going to be Sum().To count how many customers there are for a service, we want to increment the total by 1 for each customer. Thus, for service S1, we are simply going to return the value 1 from the function. The formula would look like this:

If {Customer.Service} = “S1” Then
1;

The second requirement is that if the service is S2, we want to get a total of all the order amounts. This is easy because all we have to do is return the order and the cross-tab will summarize it. The modification to the formula to account for this is:

If {Customer. Service } = “S1” Then
1
Else If {Customer. Service } = “S2” Then
{Orders.Order Amount};

The last requirement is that if the service is S3, we want to calculate the difference between two dates. The formula would be something like this:

If {Customer. Service } = “S1” Then
1
Else If {Customer. Service } = “S2” Then
{Orders.Order Amount}
Else
{Orders.Ship Date}-{Orders.Order Date};

Let’s take this function and create a report with it so that we can see it in action. We can’t create the exact same report that was mentioned in the forum’s post, but we can make something similar using the Xtreme.mdb database. Let’s create the report based on the Customer table and the Orders table. This lets us group the customers by country, summarize the total order amounts, and do a simple date calculation.

1. Create a new report using the Standard Report wizard. Note: Don’t use the Cross-Tab Wizard report type because we need to create a new function prior to creating the cross-tab object.
2. For the data source, choose the Xtreme.mdb database and select the tables Customer and Orders.
3. Link the two tables together using the Customer ID field.
4. Don’t select any data fields to display on the report. Just click the Finish button to skip it and go to the report designer.
5. This report doesn’t have any data relating to customer services, so we will group customers by country. And since the example only needed three services, we will limit our report to just three countries.
4. Click the menu options Report > Select Expert.
5. Choose the field Country from the Customer table and click the OK button.
6. In the Select Expert, from the drop-down list choose “Is One Of”.
7. For the field values, choose the countries Canada, England, and USA.
8. Click the OK button to save your changes.
9. Before building the cross-tab object, we first have to create the formula that will do our magic. Insert a new formula field and call it CrossTab Summary.
10. Since we are going to group on Country, we will report the Service field in the previous formula with Country.

If {Customer.Country} = "USA" Then
1
Else If {Customer.Country} = "England" Then
{Orders.Order Amount}
Else
{Orders.Ship Date}-{Orders.Order Date};

10. Click the Save and Close button to check that you typed everything in correctly and save the changes.
11. Right-click on the report and select Insert Cross-Tab. Place the cross-tab object in the Report Header section.
12. Right-click on the cross-tab report and select Cross-Tab Expert.
13. When the dialog box opens, insert the following fields into the appropriate area. Move the Customer.Country field to the Rows category. Move the Orders.Order Date to the Columns category.
14. We need to use the function we created, so move the function CrossTab Summary to the Summarized Fields category.

Lastly, we want to modify how the Order Date is grouped. We don’t want to have a separate column in the cross-tab for every order date. Instead, let’s just have a column for each year.

15. Click on the Group Options button under the Columns category.
16. Change the selection “for each day” to “for each year”. Click the OK button to close the Group Option dialog box.

Before we close the Cross-Tab expert, let’s turn off the row and column totals.

17. Click on the Customize Style tab. Click on the checkboxes Suppress Row Grand Totals and Suppress Column Grand Totals.
18. Click the OK button to save and close the Cross-Tab expert dialog box.

Now that we are back in design mode, let’s change the formatting of the summary function because the default is currency and we don’t want to print dollar signs.

19. Right-click on the summary field in the bottom right-hand corner and select Format Field. Uncheck the option Display Currency Symbol.
20. Click the OK button to save the formatting changes.
21. Preview the report and your cross-tab should look similar to the following:

The Canada row calculates the sum of date differences. The England subtotals calculate the sum of the order amounts. The USA subtotals calculate the total number of days it took to ship ordesr for that year. By basing the crosstab summary field on a formula, you can customize what is calculated within each cross-tab cell.

Tutorial 13-6 shows how powerful the cross-tab object is for summarizing data. But, the caveat is that even though you can use formulas to customize cross-tab results, the summary function has to be the same for each cell. You can’t use different summary functions for different results. For example, if you choose the Sum() function, then the results of the formula will be added together for each cell. If you select the Avg() summary function, then the cross-tab cells will show the average of all the formula results. It is not possible to have certain cells perform the Sum() function and others perform the Avg() function.