Summarising/ manipulating formula values 1

[Kevinski]

Hello

I am trying to emulate an Excel pivot/ spreadsheet in Crystal V10 and am getting tied in knots...

I have a crosstab which summarises hours spent by groups and individuals (rows) across a range of activities (columns)

I have a formula @CallType for the columns which states

if {CCS_JN_Service_Desk_TM.Call Type} in ['Internal','Procurement']
then 'Not Charged'
else {CCS_JN_Service_Desk_TM.Call Type}

This allows the user to see how many hrs someone spent on chargeable Vs non-chargeable work. This works fine.

Next I am creating another crosstab report (to be used as a sub-report) for each group looking purely at chargeable work. So I have a selection which says @CallType <> 'Not Charged' to bring back the correct data set for the crosstab. This works fine.

But in the original spreadsheet at this point there is a summary below the pivot which consists of 4 lines:

1. Average Rate $130 (purely a text object - works fine)
2. Total Est Revenue (Grand Total of hrs from subreport crosstab *130. works fine)
3. Total Billable Revenue (here's my problem - the hours for @CallType 'Contract', whilst being chargeable, need to be deducted from the grand total to arrive at:
4. Total Transfer (Total Est Revenue - Total Billable Revenue). the object of the exercise being that management can see that yes, while Contract work is generally chargeable it is covered by monthly contract fees and so it should be seperated out from all other chargeable work and an accounting transfer made.

As stated 3. above is giving me the problem. I have tried a formula which states

if {@CallType} <> 'Contract'
then sum ({@Billable})*130

but this ignores the distinction between call types and just returns the same value as in 2. above.

I hope you can follow this - I'm even confused writing it!

 

[lbass]

A little hard to follow, but for your formula I think you need something like the following {@noncontract} placed at the detail level (and probably suppressed):

if {@CallType} <> 'Contract'
then {table.amt}

Then a second formula would be:

sum({@noncontract})*130

Also, if you are trying to use a figure from a subreport in a calculation in the main report, you must set it up as a shared variable. Couldn't tell whether this might be part of the problem.

-LB

 

[Kevinski]

lbass - I don't know who or where you are but in my book you are some kind of guru. Works perfectly thank you!