1. 

   Deepak, Member

   Hi,

   I am trying to use a multi-select parameter for my report. But the concatenated query result does not pass through the main query.
   Step1:
   The parameter query lists all document types using check box for filter type,
   SELECT 0,'All' as ord2, '','1' as ord1
   union
   select tm2.id,tm2.TAXVALDESC as ord2,'','2' as ord1 from sipo_db.dnrf_values dv
   LEFT JOIN sipo_db.dnrf_prefixes dp ON dp.dnrf_value_id=dv.id
   LEFT JOIN sipo_db.tm_m_ibm_taxdtls tm1 ON tm1.id=dv.business_unit
   LEFT JOIN sipo_db.tm_m_ibm_taxdtls tm2 ON tm2.id=dp.document_id
   where dv.business_unit=609 and tm2.id <> ''
   GROUP BY tm2.TAXVALDESC
   order by ord1,ord2;

   Step 2:
   Below is the main procedure which executes when report is called. The selected document types are passed
   CREATE PROCEDURE sp_DBR_DEMAND_SYS_33_v1_11(getdoctype_test int)
   BEGIN
   create temporary table data_tmp(id int);
   set @vSQL = concat('INSERT INTO ','data_tmp', ' VALUES(\'', REPLACE(getdoctype_test, ',', '\'),(\''), '\')');
   prepare stmt from @vSQL;
   execute stmt;
   deallocate prepare stmt;

   The result of the temp table i am passing into my where condition as below,
   WHERE dv.business_unit=609 and dp.document_id in (select id from data_tmp);

   When i try to print the result using select id from data_tmp; i get 628,612, not sure why the report still appears blank.

   Thanks
2. 

   myDBR Team, Key Master

   Hi,
   You might want to change the parameter "getdoctype_test int" to be "getdoctype_test text" as the multiple values are passed to original report as a comma separated list, not as a single integer.

   --
   myDBR Team
3. 

   Deepak, Member

   628,612 are the ones which is expected to pass in the where condition, if i directly place 628,612 the query works fine.
4. 

   myDBR Team, Key Master

   The selected id's (in your case 628 and 612) are passed as comma separated text ("628,612") into the actual report, which in your case is the sp_DBR_DEMAND_SYS_33_v1_11.

   Now that you pass a string "628,612" to sp_DBR_DEMAND_SYS_33_v1_11-procedure's parameter which defined as int (getdoctype_test int), MySQL will convert the string into integer resulting an integer value of 628. Your report treats the parameter as string (using REPLACE etc).

   Take a look at the Multiselect example report how to complete what you are doing.

   --
   myDBR Team
5. 

   Deepak, Member

   That worked..

   Thank You

Reply

You must log in to post.