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.

 

[cpitc]

Thanks luckily I sussed that out. I now another problem because I think it is currency now

In the sum of wread field I ahve the followin under the Display String

whileprintrecords;
currecnyvar actual;
numbervar budget;
totext (actual-budget,2)

The result is showing the number like a currency e.g 4 650,00 kr

I need to drop the KR however when I look to format the field the Number Tab is not there so I cannot drop the currency symbol. Alll the figures are correct though.

Any ideas please.

Thanks

 

[lbass]

Try changing it to:

whileprintrecords;
currencyvar actual;
numbervar budget;
totext (tonumber(actual)-budget,2)

-LB

 

[cpitc]

Is it possible to make the Wread field so that it is seprated by a , for 1000 and decimal is .

In the other fields I can foramt the field and change in the number tab. In the Wread field I dont have the number tab option. So it would read, for exampl 10,000.00

 

[cpitc]

The field for max @periodbudget is not calculating correctly in the Total. It should come to 3,412,358
but it actually coming out on the report to 355,712.

All budgets added are
case 1 : 213686 //budget for period 1
case 2 : 280921 //budget for period 2
case 3 : 345661 //budget for period 3
case 4 : 350233 //budget for period 4
case 5 : 355712 //budget for period 5
case 6 : 340140 //budget for period 6
case 7 : 326672 //budget for period 7
case 8 : 274820 //budget for period 8
case 9 : 268390 //budget for period 9
case 10 : 253163 //budget for period 10
case 11 : 235685 //budget for period 11

Any ideas why it could be calcualting incorrectly please.

Thanks

 

[lbass]

whileprintrecords;
currencyvar actual;
numbervar budget;
totext (tonumber(actual)-budget,2)

The above should already be resulting in two decimals with commas.

When you use a maximum as a summary in a crosstab, the total field will ALSO be a maximum, NOT a sum of the maximums.

Since you are hardcoding the budget field anyway, why not just change the display for the total to reflect actual budget total, using display string again?

-LB

 

[cpitc]

Sorry I dont fully understand what you mean, I assumed it was because of the maximums, how do I change the display string to show the correct budget total.

The total field is already using Supress with
whileprintingrecords;
numbervar budget := currentfieldvalue;
false

Not sure what I should put in the Display String

Thanks

 

[lbass]

Please explain what you are expecting to see in the total cell.

-LB

 

[cpitc]

I ma expeting to see the SUM of all the Budget figures

case 1 : 213686 //budget for period 1
case 2 : 280921 //budget for period 2
case 3 : 345661 //budget for period 3
case 4 : 350233 //budget for period 4
case 5 : 355712 //budget for period 5
case 6 : 340140 //budget for period 6
case 7 : 326672 //budget for period 7
case 8 : 274820 //budget for period 8
case 9 : 268390 //budget for period 9
case 10 : 253163 //budget for period 10
case 11 : 235685 //budget for period 11

If I use the SUM option in the Cross Tab expert, I get strange figures and not the ones that should come up, so we used Max to make it display the figures corrrectly, but of course this now makes the budget total a MAX.

So I am expecting a SUM of Case 1 to Case 12.

Thanks

 

[lbass]

All I'm saying is that you have hard coded the budgets for the periods, so why not hard code the sum of them? Select the total field->format field->display string and add:

"3,412,358"

Except I don't think you are looking for the sum of the budgets, but you haven't told me what you want to see there. Maybe the sum of (actual - budget)?

-LB

 

[cpitc]

Hi

I see what you mean by just hard coding the amount. I would prefer it if we could make it so it automatically adds them up if it is possible. The totals figures presently look like this

Total

MAx @periodbudget 355,712.00 (this should be 3,412,358.00)

Sum of Actual 4,104,166.95
Sum or Wread 3,748,454.95 (actual - budget)

So in the Sum of wread it should be 691808.95

Thanks

 

[cpitc]

If it is not possible really to do this automatically what do I put in the display string please. Will the SUm of Wread then still work to make the right calculation.

Thanks for your help

 

[lbass]

Tell me how the crosstab is set up--rows and columns (by field name).

-LB

 

[cpitc]

Hi This is how it is set up

Jan Feb Mar etc... Total
Actual sum of ft.faktradsumma (Currency)

Budget Max of @periodbudget

Difference sum of Wread

In Actual we have in Format field we have Supress with this code
whileprintingrecords;
currencyvar actual := currentfieldvalue;
false

In Budget we have under Surpress we have the code

whileprintingrecords;
currencyvar budget := currentfieldvalue;
false


In the difference row we have Display string
whileprintingrecords;
currencyvar actual;
numbervar budget;
totext(tonumber(actual)-budget,2)//2 for two decimal places


Thats how it is set presently.

Thanks

 

[lbass]

I meant what fields you are using as rows and columns in the crosstab expert. Are there no row fields?

-LB

 

[cpitc]

Did you manage to solve this situation at all. If we can just hard code the Max of @periodbudget Total figure that would be ok. If this is ok can you suggest the code and where i need to imput it.

Many thanks

 

[lbass]

What happens if you change the summary to SUM? Depending upon your data, this might work okay. Make sure that the formulas you entered into the formatting areas are still there after you make this change.

-LB

 

[cpitc]

Hi

this is the before and after from chanign summary from MAx to Sum

BEFORE
Jan Feb Mar

Actual 199,088.64 285,571.00 434,061.39
Budget 213,686.00 280,921.00 345,661.00
Diifrence -14 597,36 4 650.00 88 400.39


After

Jan Feb Mar

Actual 199,088.64 285,571.00 434,061.39
Budget 269,244,360.00 492,173,592.00 924,643,175.00
Diifrence 199 088,64 285 571,00 434 061,39

 

[lbass]

Well, you didn't follow my instructions about ensuring the formulas were still in place, but anyway I guess you need to use the maximum, since the budget figures changed. So now you need to answer the question from my post on 13 Jan 10 16:37.

-LB

 

[cpitc]

The columns are the Periods

Jan Feb Mar etc....... which is ft.period

In the Rows part of the Cross Tab Expert I ahve nothing in the Rows.

In the Summary Fields I have

sum of ft.faktradsumma (Currency)
Max of @periodbudget
sum of Wread

Thanks

 

[cpitc]

Hope that was the information you required. Do you think this is possible or should I try another way to do this.

The problem is the that the Budget field is not in the databse anywhere, so quite rightly we have tried to ahrd code it, its just the total field holding it back. jst wondering if you can link an excel sheet with the database table in anyway.

Id prefer to use the report we have done so far because it is so close now.

Thanks