Formula within a Cross Tab

[geoffreyg]

I have a cross tab in the report footer of a report. It is setup as follows:
Row: Customer Name
Summarized Field 1: Count of Estimates (how many estimated sent to the customer)
Summarized Field 2: Count of number of orders (how many order were actually created for the client).

Question:
I would like to add a third field: % Closed. This would simply be (Summarized Field 2/Summarized Field 1).

The problem is that the "Count of Summarized Field 1" and "Count of Summarized Field 2" are not showing up in the "New Formula" window.

How do I create this new field?

Thanks!

 

[rhinok]

This is a fairly common question. Unfortunately, the answer is that you cannot calculate a percentage total as part of a standard cross-tab. The only way to accomplish this is to create a "custom" cross-tab. That is, you must build your report using formulas in a "cross-tab-like" format.

Based on the fields you've listed:

1) In the Detail section you will have three fields ("Customer Name", "Estimates" and "Orders&quot.
2) Group by "Customer Name"
3) Insert a Count Subtotal for the "Estimates" and "Orders" fields each and move the newly created Subtotal fields from the Group Footer to the Group Header.
4) Create a new formula called @Percentage and place it in the Group Header.
{@Percentage}

//calculates the percentage of Estimates actually Ordered
(count({TableName.Estimates},TableName.CustomerName})/count({TableName.Orders},TableName.CustomerName})) *100

5) Suppress your Detail Data and Group Footer - Now your report mimics a cross-tab and you can calculate a percentage.

*Note - Since you wanted your cross-tab in the report footer, you may need to create this custom cross-tab as a new report and import it into your main report as a sub-report.

 

[rhinok]

This is a fairly common question. Unfortunately, the answer is that you cannot calculate a percentage total as part of a standard cross-tab. The only way to accomplish this is to create a "custom" cross-tab. That is, you must build your report using formulas in a "cross-tab-like" format.

Based on the fields you've listed:

1) In the Detail section you will have three fields ("Customer Name", "Estimates" and "Orders&quot.
2) Group by "Customer Name"
3) Insert a Count Subtotal for the "Estimates" and "Orders" fields each and move the newly created Subtotal fields from the Group Footer to the Group Header.
4) Create a new formula called @Percentage and place it in the Group Header.
{@Percentage}

//calculates the percentage of Estimates actually Ordered
(count({TableName.Estimates},TableName.CustomerName})/count({TableName.Orders},TableName.CustomerName})) *100

5) Suppress your Detail Data and Group Footer - Now your report mimics a cross-tab and you can calculate a percentage.

*Note - Since you wanted your cross-tab in the report footer, you may need to create this custom cross-tab as a new report and import it into your main report as a sub-report.

 

[rhinok]

sorry for the double post, I received an error screen that said it didn't go through...