Manual Cross-Tabs (cross-tabs that allow formulas):
There is currently no easy way to write formulas that refer to the cells of a standard cross-tab. Starting with v9 you can do "Percentage of Total" calculations but there are no simple options for calculations based on the rows or columns of a Cross-Tab.Workaround #1:
If the formula you need involves adding or subtracting cross-tab cells,
you may be able to write a formula in the report and use it as an additional
summarized field. For example, if you have fields called revenue and
expenses in your report, and you summarize both in a cross-tab. You could
create a formula field called net that was revenue minus expenses.
Summarizing this formula in the cross-tab would be the same as taking the
difference between the cross-tab cells. Of course, this doesn't fit all
situations.
Workaround #2
You can create a manual
cross-tab. To do this you group your report on the field you want as your
row field. Hide the details and group header to get a summary report.
This creates the rows for your manual cross-tab. Now, create a conditional
formula for each column of your desired cross-tab. Add subtotals and grand
totals for each formula (see the instructions for conditional totals using
If-Then-Else). If you know how to use summary fields in formulas, you can
now calculate a percentage of total for each group of the report, and also add
formulas that do math between the columns. The downside is that the
columns are hard coded.
Workaround #3
A weighted average works
in some situations. See the article link below. Read more in my July 2004 newsletter.
Workaround #4
Some calculations can be
done by hiding variables in the formatting formulas of a crosstab.
Read more in my July 2004 newsletter.