I have a report where I would like to use a SQL query to do a single column lookup from a table that is not in the original query defined for the report.
I have an id column in my report query. Using that column data, I would like to perform a second SQL query within the report that returns column data. It would be something like this:
SELECT description FROM other_table WHERE id = $F('desc_id')
Ideally, this could go into an Expression text field where the 'description' from this lookup would display in that field. I do not need this query to run for every row in the page body, only once within a Group Header.
I can build a more complex main report query for this information, but it makes the query much slower. The query has to retrieve this information for each record to be displayed in the page body, not just the few records where column criteria for the Group Header changes.
A subreport looks like it wants to display the results of a secondary query in a tabular format, whereas I just need a single column's data.
Thanks for the help,
Rocky Scofield