Performing cross-tab calculations

[gav12345]

Hi,

We're trying to find out whether Crystal (Version 10) is able to perform some calculations for us within a cross-tab object.The database is Oracle 11g.

We will have around 80 calculations that could be performed, in the following format:


Column Name Calculation Operands

Column 1 Column 3 x 100 / Column 5 Column 3, Column 5
Column 2 Column 4/12 x Column 1 Column 4, Column 1


Essentially, for a given cell in the crosstab we would look at it's column name, which would let us know which calculation should be performed. We would then look at the values from two other column names for that row, and calculate the value in the first column using the examples in the above table.

We would likely store all calculations in a single function which we'd keep in the repository. For example:

If TABLENAME.FIELDNAME = 'Column1' then formula = 'Column3 x 100 / Column5'
else if TABLENAME.FIELDNAME = 'Column2' then formula = 'Column 4/12 x Column 1'

From past experience with cross-tabs, I have found that it can be a little difficult to perform calculations on cross-tab cells, based on the values of other cells in the cross-tab. Using the above example, can anyone suggest a way that we can do this? For example, could we perform these calculations before the cross-tab is actually rendered?

Thanks, Gavin

 

[Madawc]

Crosstabs can show the sums of a value calculated at detail-line level, e.g. unit cost times volume. But Crystal can't produce summary or crosstab values based on data from different detail-lines. So unless there's soem trick new to me, a crosstab can't do extra calculations on its own columns.

Your best solution is probably a mock-crosstab. A 'Mock Crosstab' is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then. Crystal should have included an example along with the Crosstabs.

You can save a little time by doing a paste to a dummy report, changing the name and then pasting back. In Crystal 11.5, you can also duplicate formula fields using the Field Explorer. Probably not possible in version 10.

PS
Another possibility is to export the crosstab to Excel, and then add the calculations to Excel. Probably not what you're after.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

It might be simpler to use a manual crosstab, depending upon your crosstab layout. Please identify your current row, column and summary fields.

If you are using an inserted crosstab, in order to use fields in a calculation, they have to be present in the cell, though not necessarily displayed. You can then use formula areas for field formatting as holders for variables that will do the calculations.

-LB

 

[gav12345]

Thank you Madawc and lbass.

Because of the complexity of the cross-tab, a manual cross-tab isn't really viable.

We've made a little progress where we build up a string prior to the cross-tab, and then using that for calculations within each cell of the cross-tab, but I will update with more details when we know we have a solution.

Thanks, Gavin

 

[lbass]

I would be glad to help if you provide more information as I mentioned in my last post.

-LB

 

[gav12345]

Hi lbass - Sorry things are a little crazy here just now, it's just been decided (for non-crosstab reasons) that we'll take the calculations back to the database instead of doing this in Crystal.

I appreciate your help. Gavin