phone: (540)338-0194

´╗┐ ReCrystalize

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.