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.

 

[lbass]

Change the formula in the INNER CELLS for max of budget to:

whileprintingrecords;
numbervar sumbud := sumbud + currentfieldvalue;
numbervar budget := currentfieldvalue;
false

In the total cell only, select the max of budget->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar sumbud;
false

In the same screen for max of budget->DISPLAY STRING->x+2 and enter:
whileprintingrecords;
numbervar sumbud;
totext(sumbud,2)//2 for two decimals

Then select the total cell for wread ->Format field->display string->x+2 and enter (it might already have this formula)->:

whileprintingrecords;
currencyvar actual;
numbervar sumbud;
totext(tonumber(actual)-sumbud,2)//2 for two decimal places

-LB

 

[cpitc]

Fantastic that worked, many thanks for that and your patience. I dont want to push my luck but I have a couple of other questions, I think these are more simplistic thankfully.

I am now adding more rows below and will have

Budget (working)
Actual (working)
Difference (working
Difference as a %

Budgt GM (gross margin)
Actual GM
Difference
Difference as a %

Two questions

1. How do I display the Difference as a %
2. The gross margin fields I assume I can use the fourmulas as the working ones but with different names
3. Is there a way to put a space between the rows to split the Difference % and the Busget GM

Maybe it should be another sub report and add it in for the gross margin.

Many thanks for your help and I cannot say how thankful i am for your knowledge. Thanks

 

[cpitc]

Just an update

I have managed to get the number 2 of the above working adding the the gross margins and giving different variable names.

I still need help on 1 and 3 if possible

Thanks

 

[cpitc]

I dont suppose anyone as any more input on the below at all please.

For the first I think maybe I could copy the Diff Wread and set them in a report to show as a % perhaps.
The number 3 I cannot find a way and not sure if it is even possible

1. How do I display the Difference as a %
3. Is there a way to put a space between the rows to split the Difference % and the Busget GM

Thanks

 

[lbass]

Yes, my patience has been tried. You should lay out all requirements up front, instead of stringing additional requests.

1- You can just add another holder field and use a display formula:

whileprintingrecords;
currencyvar actual;
numbervar sumbud;
totext((tonumber(actual)-sumbud)%sumbud,2)+"%"

3- Add another summary and suppress the label and all cell contents.

-LB

 

[cpitc]

yes I am very sorry about it , it is my first Cross tab and the further I got into it I relaised ther was alot more to it than i relaised, I will know in the future and try and plan my requirements up front.

3. Added in the row and surprsed it and it works very well thanks.

1. Added your code in and examined the spreadsheet figures come from and relaised that the for some reason the sales figures divide the actual / planned for the % and in the Gross Margin it divides Actual GM / Actual Sales as a %.
So I need to find out what the requirement is and sorry I may be back, but ill ahve a go before.

Many thanks for your help and I will scope things better in the future and think ahead.