I've added a dynamic prompt to a CRXII report, which works fine.
However, I then want to add correlated fields from the record matching the prompt-option they chose, and so far, I'm stumped as to how to go about this.
To clarify, I have the following table:
CREATE TABLE c_crx_units (
crx_param varchar(45) NOT NULL,
crx_unit varchar(1024) DEFAULT NULL,
crx_address varchar(1024) DEFAULT NULL,
PRIMARY KEY (crx_param)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
And my dynamic prompt will allow them to select a crx_param value.
Now, in the report, I want to add the field, @crx_address, using the value from the row that matches the chosen crx_param value.
Can anyone point me in the right direction?
(and to clarify one thing - the data from c_crx_units is independent of my main data-source)
One option is to include the c_crx_units table in the report (no need to actually join it to the main report tables) and use the parameter to filter it to the selected value. You then have direct access to the address column.
In other words, apply the parameter to both the main report data as well as to the parameter table.