Hi,
I have an issue with connected parameters that could be an error on my part or a bug.
I have a report that requires two input parameters. For example the report gives the details of documents in a certain time period for each language selected.
The first parameter is a list of available time periods generated by simple sql query in the parameters field. This is rendered as a radio button list and is a required field. It returns a time period code.
The second parameter lists all the available languages in that time period (the connection). This is rendered as a list of check boxes. This is a required field. The user is allowed to select multiple items. The second parameter calls a procedure. The procedure takes the time period code and turns it into dates which in turn generates all the available languages.
BEGIN
DECLARE StartDate date; DECLARE EndDate date;
CASE WHEN inPeriod='1' THEN set StartDate=(DATE_SUB(NOW(), INTERVAL 1 WEEK)), EndDate=now(); WHEN inPeriod='2' THEN set StartDate=(DATE_SUB(NOW(), INTERVAL 4 WEEK)), EndDate=now(); WHEN inPeriod='3' THEN set StartDate=date_format(NOW() - INTERVAL 3 MONTH, '%Y-%m-01'), EndDate=last_day(NOW() - INTERVAL 1 MONTH); WHEN inPeriod='4' THEN set StartDate=date_format(NOW() - INTERVAL 12 MONTH, '%Y-%m-01'), EndDate=last_day(NOW() - INTERVAL 1 MONTH); WHEN inPeriod='99' THEN set StartDate=DATE_SUB(NOW(), INTERVAL 5 YEAR), EndDate=DATE_ADD(NOW(),INTERVAL 5 YEAR); END CASE;
select '0','All' as ord2,'','1' as ord1 union select distinct(fd.language_id),(select tm.taxvaldesc from sipo_db.tm_m__taxdtls tm where tm.id=fd.language_id) as ord2,'','2' as ord1 from sipo_db.frf_documents fd JOIN sipo_db.cm_translation_objects cto on cto.doc_number=fd.documentid JOIN sipo_db.dnrf_prefixes as dp on dp.id=fd.dnrf_id JOIN sipo_db.dnrf_values as dv on dv.id=dp.dnrf_value_id JOIN sipo_db.cm_translation_state_names as ctsn on ctsn.id=cto.translation_state where (fd.significant='1' or fd.future_sig='1') and date(cto.start_language_date) > StartDate and date(cto.start_language_date) < EndDate order by ord1,ord2; END
The report takes the coma separated fields generated by the multi-select check boxes and loads them into a temporary table. The temporary table is processed to pass the correct parameters into the report.
BEGIN
/* Our IDs are integers, so well prepare a temporary table for those */ DROP TEMPORARY TABLE if exists data_tmp; create temporary table data_tmp( id int );
-- build a query SET @table_name = 'data_tmp'; SET @inputstring = inLanguageID; SET @query = CONCAT('INSERT INTO ', @table_name, ' VALUES(\'', REPLACE(@inputstring, ',', '\'),(\''), '\')');
-- run the query PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SELECT ........ REPORT
END
All works fine at this point. The correct check box lists are generated dynamically when the time period is selected. The temporary table holds all the correct information. When you select the report and execute it everything is fine.
The problem occurs when you change the parameters of the report using the default cog icon. In other words not re-executing the report but modifying the existing parameters. If you just change the languages (parameter 2) you get an error.
Could not execute the report. There was an error in the report.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1028'')' at line 1
The 1028 refers to the first item being loaded into the temporary table
If you change the time period (parameter 1) which clears all previous language selections it works fine.
