Help for a beginner please!!

[slewis66]

Hi,

Using Crystal v9. I have the follwing already created:-

GH1 JobRef
GH2 Transaction Type (this is based on a formula)
Revenue
Invoice 1 £100.00
Invoice 2 £200.00

Total Revenue £300.00

Materials
Invoice 9 £50.00
Invoice 8 £75.00

Total Materials £125.00

GH2 Net Profit £175.00

What I then want to do in GH1 is to replicate the sub-total for each grouping (eg. Revenue, Materials) so that the result can be used in a further calculation.

So:-
GF1
Total Revenue £300.00 Revenue Budget xx.xx (avalable field) Variance (actual - budgeted)
Total Materials £125.00 Materials Budget xx.xx (available field) Variance (actual - budget)

I currently have the following formula replicated for each grouping (revenue, materials etc), however it only returns a value for the last group that appears within the details, so if there is a value against both revenue and materials it will onlt return a result against materials :-

whileprintingrecords;
if({@Main Groups})= "Materials" then
sum ({@Cost}, {@Main Groups})
else 0

Thanks for any help!!

 

[lbass]

Try setting up your formulas like:

whileprintingrecords;
numbervar materials;

if({@Main Groups})= "Materials" then
materials := sum ({@Cost}, {@Main Groups})
else materials := materials;

This will allow the value to be carried forward for later use.

-LB

 

[slewis66]

Hi LB,

Thanks for such a prompt response.

I've tried the formula you suggested and it's certainly heading in the right direction. It's including a total against each grouping in GF1 for the actuals, however it is pulling in the figures from the previous GF1 each time, and only changing the GF1 actual figure for the last grouping which appears in the Details within the same GH1.

So, to use the example for my original post, the next GF1 would read exactly the same with only the Total actaul materials having changed. And so on throught the whole report.

Hope this makes some sort of sense!!

 

[lbass]

Sorry, forgot the reset. You would need to add a formula {@reset} to the Group Header 1:

whileprintingrecords;
numbervar materials := 0;

This assumes that "Revenue" and "Materials" are instances of Group 2. Or is transaction type something else, with "Revenue" and "Materials" being Group 3 fields, so that there can be multiple instances of "Materials" groups within Group 1?

-LB

 

[slewis66]

Hi again,

I have to admit you've somewhat lost me now - I'm a new to all this Crystal stuff.

What I have is

GH1 which is a job number based on an existing field

GH2 is created by a formula on a field called transaction type, which is as follows ( I left the other possibilites out of my original post to try and make things easier to explain. The formula is as follows:-

if {JOB_COST_TRANSACTION.TRANSACTION_TYPE} = 1 or {JOB_COST_TRANSACTION.TRANSACTION_TYPE} =2 or {JOB_COST_TRANSACTION.TRANSACTION_TYPE} = 4
or {JOB_COST_TRANSACTION.TRANSACTION_TYPE} = 5 then "Revenue" else
if {JOB_COST_TRANSACTION.TRANSACTION_TYPE} = 20 or {JOB_COST_TRANSACTION.TRANSACTION_TYPE} =21 or {JOB_COST_TRANSACTION.TRANSACTION_TYPE} = 22
then "Materials" else
if {JOB_COST_TRANSACTION.TRANSACTION_TYPE} = 60
then "Labour" else
if {JOB_COST_TRANSACTION.TRANSACTION_TYPE} = 100 or {JOB_COST_TRANSACTION.TRANSACTION_TYPE} = 101
then "Misc"

Then within Group 2 the details of the individual transactions are listed and totalled by Revenue, Materials etc.

What I then want to acheive in the GF2 is to repeat the totals for each transaction type (Revenue etc) against another column for the budget (an existing standard field) and then calculate the variance.

If this is the case do I then need to create a formula as in your second post {@reset} and just drop in somewhere in GH1?

Thanks again for your help!

Sarah

 

[lbass]

Yes, that should work. My original post was in response to your original approach, but I think it would be easier if you used the running total editor instead of variables.

Instead of doing the above, you could create a running total for each of the Group #2 instances that you want to use for calculations in Group #1 Footer (I assume that was a typo when in your last post you mentioned calculations in Group #2 Footer). Go to insert->field object->running total and for {#Materials}, choose {@cost}, sum, evaluate based on a formula:

{@transactiontype} = "Materials"

Reset on change of Group #1 (Job Ref).

Repeat for "Revenue", etc., only changing the evaluation formula. Place these running totals in the Group #1 Footer. You can use them in further calculations as in {@Variance}:

{#Materials}- {table.budget}

-LB

 

[slewis66]

Hi again,

Thanks for your reply.

I've tried putting in a running total (haven't used this before!) and have it set as follows:-
Field to summarize - {@cost}
Type of Summary - Sum
Evaluate on change of group - {@transaction type}
Reset on change of group - {@transaction type}

This almost but not quite works. It seems to only be picking up ther first line within each transaction type. For example:-
Within the materials group there are say 5 line items, but the running total is always only the first line item.

Line 1 £50 Running total £50 - correct
Line 2 £75 Running total £50 - wrong!
Line 3 £80 Running total £50 - wrong!

However on the positive - the running total does change on each change of transaction type and within each job ref. So we're definately on the right lines. But any further adive would be very gratefully received.

Sarah

 

[slewis66]

Hi LB,

Firstly, please ignore previous post - I've sorted it. I'm not entirely sure what I've done but whatever it was it worked!!

My only remaining problem (I think) is that if there are no line items for a particluar group (For example no materials costs for a particular job ref) the running total doesn't return a zero - just a blank. Then when I try and use the running totals in a further calculation or formula if there is a blank running total the result of the calculation/formula is also blank.

My current Actual Cost formula is very simple:-
{#Labout RT}+{#Materials RT}+{#Misc RT}
but of course only works when all of the 3 fields have a value.

Is there any way to get the running total to return a zero if there are no results thereby enabling the zero to be included for use elsewhere?

I'm sure I'm missing something very simple!!

Thanks in advance!!

 

[lbass]

Not sure what is in your {@cost} formula, but try changing it to allow for nulls, as in:

if isnull({table.amt}) then 0 else {table.amt}

You'll have to adjust this according to your current {@cost} formula. Then sum this formula in your running totals, instead of your original {@cost}. If you need help with this, please provide the contents of your current formula.

-LB

 

[slewis66]

Hi,

My original cost was a specific field (Total Value at Cost) but I've now replaced this with a formula field that reads:-

{@NetValue}
if isnull({JOB_COST_TRANSACTION.TOTAL_VALUE_AT_COST}) then 0 else {JOB_COST_TRANSACTION.TOTAL_VALUE_AT_COST}

I've also reset the Running Totals so that each one sums {@NetValue}. This works fine within GF2 and gives a total for each grouping here (revenue, materials etc). However when I then drop the running totals fields into GF1 (job ref) any running totals (eg materials) which don't appear against that particular jobref are blank, as are any subsequent calculations.

Hope this makes sense!

Sarah

 

[slewis66]

Hi,

Well you'll no doubt be pleased to know that I think I've cracked it. I changed the details in Report Options to convert NULL values to default and that seems to have done the trick.

Thanks so much for all your help.

At the risk of pushing my luck I have one final (hopefully) question - is it possible to add a summary of the running total field to the overall report footer?

Sarah

 

[lbass]

You would create another running total like the ones at the group levels, except for the reset, use "Reset Never."

-LB

 

[slewis66]

Finally got there!!

Thanks for all your help.