Interpolate Points in a cross-tab

[nigelstewart2]

I am looking to interpolate points in a cross-tab as in the example below.

Data Table

Column a Column b

1 2

1 4

2 6

2 8

Cross-tab in report

Column a Column b

1 3

2 7

Column b is an average.

How would I insert a value in Column a of the cross-tab of 1.5 (that does not exist in the data) and then have the column b value be 5 (midpoint of the averages of values 1 & 2)?

[former_member260594 · Accepted solution]

If you can output the interpolation using the average in a main report then you could roll those values up into a string running total with a character separator, pass the string to a subreport, parse the values out in the subreport and create a crosstab off of those values.

Here is a link to a white paper called charting on print time formulas that uses the above logic but to create a chart, (just create a crosstab instead of chart).

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/40e7130d-aa1e-2b10-ce9f-eee2a54f...

[nigelstewart2 · Accepted solution]

Thanks for the answer Graham but I donu2019t think this solves my problem. It is not really about putting the results into a cross-tab, itu2019s doing the actual interpolation which is giving me grief. The results do not necessarily have to be in a x-tab, they could be displayed as a summary report. I suspect that this will need multiple passes in order to generate the interpolation results. So just re-capping, the results will look like:

1.0__3

1.5__5 (this record of 1.5 will not be in the db u2013 both 1.5 and 5 will be calculated)

2.0__7

Regards,

Nigel

[former_member260594 · Accepted solution]

Sorry about that Nigel,

Try using the next function in a report to generate the interpolation. For example;

If Not OnlastRecord then

( + next() ) / 2

this will average out the current value with the next value except on the last record where there is no next value.

I added a details B section to display the formula and you would need a separate formula for each interpolation.

[nigelstewart2 · Accepted solution]

Thanks Graham, that worked well and solved my problem, very much appreciated.

Thanks,

Nigel.

[nigelstewart2]

Sorry, the tables did not paste very well, the data is as follows:

Column a

1

1

2

2

Column b

2

4

6

8

[Former Member]

Hi

Create a formula field to calculate the average.

Use this formula field in "Show values" in your cross tab.

Hope this helps!!

Regards

Sourashree

[nigelstewart2]

Thanks for your response Sourashree, however that will not solve the problem, I need to insert a vaule in a cross tab that does not exist in the data.