The data source passed to a report has a Serial property, I need to write a field in this format for every details section: [Serial] from [Top serial]
I wrote this formula for the top serial:
Maximum({VW_Sizes.Serial})
but it gets the current serial, so instead of 1 of 2, 2 of 2
it gets 1 of 1, 2 of 2.
The problem you are running into is that when each iteration of the details section prints to the report, it only knows the Max value for SERIAL for the rows that have already printed to the report.
I prefer to use a SQL Expression Field to get around this issue. This allows you to use a SQL query to retrieve the maximum value of SERIAL for the grouped data before all of the rows are printed to the report.
Something like this usually works for me.
(
SELECT MAX("ORD_DETAIL"."ORD_DET_SEQNO")
FROM ORD_DETAIL
WHERE "ORD_DETAIL"."ORDERS_ID" = "ORDERS"."ID"
)
In my example, I have two tables, ORDERS and ORD_DETAIL. ORD_DETAIL.ORD_DET_SEQNO contains the sequence numbers of order detail rows. My data is grouped on ORDERS.ID to iterate through each Order and the following formula field would print an output for each detail line that indicates its sequence out of the maximum value of all sequence numbers for that order.
ToText({ORD_DETAIL.ORD_DET_SEQNO}) + " of " + ToText({%Max});
In this formula, %Max is the name of the SQL Expression Field in the example above.
If you have no point in your data where the SERIAL number resets, then your SQL Expression Field would look like this.
(
SELECT MAX(Serial)
FROM VW_Sizes
)
If you need a reset at certain points, simply add a WHERE clause that references the table.column that is used to group a set of SERIAL values.