Cross Tab and clauclate from non database field 1

[cpitc]

I have created a cross tab from our database which shows the sales invoice figure per period (Column). This is the actual sales.

We have figures for Planned Budget but our databse does not store this. Is there a way we can manually enter these in a text object for instance and use them to calculate the difference between the Actual and the Planned. I tried adding a text object into the cross tab but it would not accept it. Any ideas please.

 

[Turkbear]

Hi,
maybe you could create formulas that resolve to the numbers you want and use those in the XTab..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[cpitc]

Thanks for your reply, I am fairly new to forumulas,how could this be done because the way I see the cross tab they are all the same name Field: Sum of table1.actual amount (number)but the columns relate to the different period table1.period. Any ideas please?

 

[lbass]

You can create a formula {@budgetperperiod} like this and use it as a second summary:

select {table1.period}
case 1 : 12345 //budget for period 1
case 2 : 23445 //budget for period 2, etc.

-LB

 

[cpitc]

LB dont understand what you mean

case 1 : 12345 //budget for period 1

I am new to formulas. I have grouped the Periods and put sub total on each period and it is called Sum of Table1. actual amount (number)

How would I put this in a formula as you advise.

Thanks

 

[cpitc]

LB

Sorry I got confused with another thread on my last reply.

Could you explain in more detail what you mean

case 1 : 12345 //budget for period 1

Thanks

 

[lbass]

Enter the following in a new formula field {@periodbudg}:

select {table1.period}
case 1 : 12345 //budget for period 1
case 2 : 23445 //budget for period 2, etc.

The 12345 is the budget for period 1. Add cases up to the total number of periods. Then use this as a summary field in your crosstab. Use sum or maximum as your summary.

-LB

 

[cpitc]

Not sure we are on the right tracks here. I made a formula and then inserted it below the actual figures, it then gives me the Budget amount below.

select {table1.period}
case 1 : 1000 //budget for period 1
case 2 : 1000 //budget for period 2

P1 P2
Actual 1000 1,010,000
Budget 3000 2000

Sorry I am fairly new to cross tabs and formulas how do I use it as a Summary field.

Thanks

 

[cpitc]

I made some progress myformual now looks like this


case 1 : 1000 //budget for period 1
case 2 : 1000 //budget for period 2
case 3 : 1000 //budget for period 3 etc...

I had to use maximum to get the

P1 P2
Actual 100 100
Budget 1000 1000 etc.....

This is good but I now want to take the budget away from the actual. I have entered this formula and I am getting strange results

sum ({Table1.actual amount})-{@periodbudget}

I get

P1 P2
Actual 100 100
Budget 1000 1000
Diff 400 400 etc....

Which obviously is not correct.

Any ideas please

Thanks

 

[cpitc]

One more note if I use sum instead of average it makes all the budgets change to 2000 and does not keep them at 1000.

I thin kperhaps because I am using maximum the Diff formula does not work.

Thanks

 

[lbass]

What version of CR are you using?

-LB

 

[cpitc]

I am using Crystal Reports X1

 

[lbass]

Create a formula like this:

whilereadingrecords;
0

Add this as your third summary. This is a "holder" field.

Then select your actual summary->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar actual := currentfieldvalue;
false

Then select the budget summary-right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar budget := currentfieldvalue;
false

Then select the {@0} summary->right click->format field->DISPLAY STRING->x+2 and enter:

whileprintingrecords;
numbervar actual;
numbervar budget;
totext(actual-budget,2)//2 for two decimal places

-LB

 

[cpitc]

Thanks as always

Dont quite understand this bit

Add this as your third summary. This is a "holder" field.

How and where do I add this ?

Also the

Create a formula like this:

whilereadingrecords;
0

Should this have a speific name

Thanks

 

[lbass]

Just call the formula "0" or whatever you like. After creating the formula, go into the crosstab expert and add it as your third summary field (below the others). You are using this field not for its actual value, but so that you can display the result of the difference between the first two summaries.

-LB

 

[cpitc]

Ok did all that and still getting strange conflicts

In the cross tab I have

Columns : Period

Rows : 1. Actual amount which as the
whileprintingrecords;
numbervar actual := currentfieldvalue;
false

inserted in the correct place

2. Budget which as the
whileprintingrecords;
numbervar budget := currentfieldvalue;
false

inserted in the correct place

3. Diff which as the formula
sum ({Table1.actual amount})-{@periodbudget}

and whileprintingrecords;
numbervar actual;
numbervar budget;
totext(actual-budget,2)//2 for two decimal places

inserted in the correct place

4. Wread which as the formual

whilereadingrecords;
0


I now get rows like this

Actual 100
Budget 2000
Diff -1900
Wread -1900

I understand we only need the Wread field for the difference. That works except for one thing. The budget is set to 1000 and not 2000 (this is for all periods but show 2000 for all periods)

case 1 : 1000 //budget for period 1
case 2 : 1000 //budget for period 2

it should appear as 1000 and not 2000. Any ideas why this is.

Thanks

 

[lbass]

I don't think you have implemented this properly. You haven't mentioned that you have any row fields, and the formulas I gave you are not for rows. Please reread my post. You should be going into preview mode and selecting the relevant summary and then entering these formulas in formatting formula areas for summary fields.

-LB

 

[cpitc]

Ok I have retraced every step and made some adjustments as you suggested. The report is now up and working. Many thanks for your replies and patience.

 

[cpitc]

Hi

When inserting this into the live report I am getting it requireds a Number field. I ahve just checked and the field is a currency field. Do you know how I can change it to a Number field within the code or within Crystal. Thanks

whileprintingrecords;
numbervar actual := currentfieldvalue;
false

 

[lbass]

whileprintingrecords;
currencyvar actual := currentfieldvalue;
false

You will need to change numbervar to currencyvar wherever you reference the "actual" variable.

-LB