Report table calculation with a specific row

Ignition
1

I am in need of some guidance.

I need to perform a calculation on a table that has 7 rows in the details rows based on the query.

I have a summary row and i need to calculate the difference between the last row in the details rows and the average row. I cant seem to find a way to specify the row number for use in the calculation.

Maybe i am just missing it but i cant seem to find it in the manual.

If anyone could help i would appreciate it.

2

What context is this in? (Also, Perspective or Vision or Reporting?) An expression binding? In expressions, you can use these syntax options for dataset access:

  • {path/to/dataset}[0,0]
  • {path/to/dataset}[0,'colName']

In the square brackets, you put the zero-based row offset, and either the zero-based column offset or the column name.

3

This is in reporting using the table.

I need to subtract the average/summary from the last row in the list.

Header Row
Data Row (Will always be 7 rows)
Summary Row

Need to subtract summary row data from last or 7th row data row.

Thank You

4

I could always make a separate tag that could do this and place it in the summary but figured i should be able to do it without creating a seperate tag.

5

Hmm. No idea. I'd use a script data source to generate this.

6

Okay. Thanks

7

You can access rows in a table manually; I believe with a subscript style access similar to our dataset access, or maybe @datakey.get(index)@, so, in theory, you could do this aggregation manually.

But I agree with Phil, I'd probably just do this in scripting. Much easier to see (and change) the logic down the road.

8

Thanks. I will test this.

9

That doesnt seem to work. Just shows N/A

How would you oerform this in script?

10

In an expression inside the summary row of a table:
image
Average without last: @(total.Column2 - static_data[count - 1].Column2) / (count - 1)@

Where static_data is the name of the table's dataset, and Column2 is the column you care about aggregating. The key is that from anywhere, you can retrieve any value from a dataset, you just need to 'fully qualify' your reference, e.g. @static_data[1].Column2@ to get the value of column 2 at row 1. More on that syntax here: http://www.reportmill.com/support/keychains.pdf

To do it in scripting, you could just generate a new data key that's based on your dataset. Doing the same as the other example could be something like this:

def updateData(data, sample):
	pds = system.dataset.toPyDataSet(data["static_data"])
	runningSum = 0
	for row in pds[:-1]:
		runningSum += row["Column2"]
	data['summaryKey'] = runningSum / (len(pds) - 1)

Or, golfed a little bit:

def updateData(data, sample):
	pds = system.dataset.toPyDataSet(data["static_data"])
	runningSum = sum(row["Column2"] for row in pds[:-1])
	data['summaryKey'] = runningSum / (len(pds) - 1)

The basic idea is the same, though - go through all the rows in the dataset except the last one, and calculate the average off that.