"If I do a layout based on Table 2 - I correctly get subtotals but all values are shown (ie for previous dates)" - if you simply didn't see the detailed listings of the dates and their individual values, would that be what you want?
If so, all you have to do is keep the data you have in the sub-summary section, that you say displays correctly, and remove the fields from the body section, and remove the body section.
"I would also like to produce this report as at a date in the past, showing the most recent values related to the report date"
If you search for all Table 2 records up to the date you are interested in and sort and display the report as above, will that not give you what you want?
Thanks but these wold not work.
I require only a single line per asset code in table 1, showing only a single value for the most recent date (from table 2), and all these should be subtotalled per the market field in table 1.
"If I do a layout based on Table 1 - I get only the latest value of the asset but the subtotal (in the subsummary part) shows only the value of the last record reported ie not all total of the values of the all the assets in that market."
If it is only this bit that is wrong: "[it] shows only the value of the last record reported" then you seem to be placing a regular field in the sub-summary section. That is what will display if you do that. If you create a Summary Field in Table 1 that is the total of the field you want to summarise, and put that in the sub-summary section, it will show the total.
The field in the sub-summary section is a summary field. It only produces the correct total when running the report off table 2, but I then include all the records from table 2 in the report which is incorrect. The tutorial assumes that the report should include all the records in table 2, not as in my requirements, only the records showing the latest value of each asset. If I could find an algorithm to pick the records from table 2 which were the latest valuations (as per a input date ie the max date le an input variable). Perhaps I could produce the report but I have been unable to get the algorithm correct and also need to understand how to include a variable (a date) in the report selection process
Sorry - I get confused about which part works correctly for you in each of the scenarios. ("It only produces the correct total when running the report off table 2, but I then include all the records from table 2 in the report which is incorrect")
You have a Header Table with Asset Code and Market fields in it, related to a Detail table with Asset Code and Value in it, with a date of the valuation of that asset code?
If you sort the relationship connected via Asset code by Date Descending, then you can create two calculation fields in the Header Table:
DateLatest = Table2::Date
ValueLatest = Table2::Value
and a 3rd field, a summary field:
SumValueLatest = Summary of ValueLatest
Find in Table 1 those Assets whose DateLatest is in the date range you want, put their details in the body part, sort by market, etc, and put the summary fields in the sub-summary section. You will only get the sum of the latest values.
Thanks - tis may solve the problem of producing a report showing the most recent value for each aseet. If i would like to run a report showing the values as at a date in the past - for example last month or last year how would I achieve this?
Actually, the summary fields compute values based on all records in the current found set or a sub group as controlled by sorting and the use of a Sub Summary Part. That "found set" could be all the records in the table or just those you've pulled up by performing a find or other manipulation of the found set of records.
That's the key for this last question. If you can perform a find that specifies a date range, your report will then be based on those records instead of on all the records.
I have looked at the find in the layout - can i exclude records based on the comparison of 2 fields in the layout - eg 2 dates?
It's possible. Give an example of exactly what you want and I can show you how to do it manually and/or in a script.
I would modify the relationship between the tables. Create in the Parent Table1 a global field gDateInHistory. Set the relationship that was used above to include the requirement AND Table1::gDateinHistory >= Table2::Date. Leave it sorted the same way.
Set the gDateInHistory on the Table1 layout, pick a date and I think it's worth a try.
I have created the gDateinHistory field and modified relationship. Looks promising. How do I assign the gDateinHstory field the same date value without updating the field manually for every record in Table1. I have not used global fields - do these have a different definition?
Yes - when you create the field go to options -> storage tab -> select use global value.
Thanks then I assume I create a layout containing only the global field?