How to implement multi value parameter in spagoBi server

Question

I made a birt report in spagoBI studio with multi_value parameter it works fine in studio. But when i upload it to server it's execution give a blank page. pls someone help me.

Answer 1

Has been a couple of years since the queston was asked but I thought I would post my experience with this issue as it may help others out there.

The problem is due to the fact that each value in the parameter string is being wrapped with single quotes so no where condition is met in your report's sql where statement.

So if you are using Postgresql see: https://www.spagoworld.org/jforum/posts/list/382.page. However if like me you are using MySQL then that where all the fun and games begin because MySQL does not have a ready to use regxp_split_to_table function! What worked for me was to use temporary tables and a stored procedure to return the report dataset. I then called the procedure in the report's queryString.

So the following were the steps I took:

1. create a function to split out each parameter from the multivalue String and remove the single quotes:

   CREATE DEFINER=root@localhost FUNCTION SPLIT_STR(
   x VARCHAR(255),
   delim VARCHAR(12),
   pos INT
   ) RETURNS varchar(255) CHARSET utf8
   RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
   LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
   delim, '')

2. Create a stored procedure to return the parameters as a resultset that can be matched via your sql's where in ($P{parameter}) statement. Thet trick here is to take the split and cleaned up parameter and insert it into a temporary table that can then be queried in the subsequent select statement that returns the dataset. My stored procedure looks like:

   CREATE DEFINER=root@localhost PROCEDURE create_temp_breweries(fullstr varchar(255), startDate date, endDate date, outlet_Type varchar(255))
   BEGIN
   DECLARE a INT Default 0 ;
   DECLARE b INT Default 0 ;
   DECLARE str VARCHAR(255);
   DECLARE outletStr VARCHAR(255);
   drop temporary table if exists temp_table1;
   create temporary table temp_breweries(col1 varchar(255));
   drop temporary table if exists temp_table2;
   create temporary table temp_outletTypes(col2 varchar(255));
   loop1: LOOP
   SET a=a+1;
   SET str= REPLACE(SPLIT_STR(fullstr,",",a),'\'', '');
   IF str='' THEN
   LEAVE loop1;
   END IF;
   #Do Inserts into temp table here with str going into the row
   insert into temp_table1 values (str);

   END LOOP loop1;

   loop2: LOOP
   SET b=b+1;
   SET outletStr= REPLACE(SPLIT_STR(outlet_Type,",",b),'\'', '');
   IF outletStr='' THEN
   LEAVE loop2;
   END IF;
   #Do Inserts into temp table here with outletStr going into the row
   insert into temp_table2 values (outletStr);
   # For testing: insert into mytest (brewery) values (outletStr);
   END LOOP loop2;

   SELECT [fields]
   FROM
   [tables]
   WHERE
   BINARY field IN (SELECT * FROM temp_table1)
   AND DATE BETWEEN startDate AND endDate AND
   BINARY field2 IN (SELECT * FROM temp_table2);

   END

Got this from: MySQL Split Comma Separated String Into Temp Table.

I am sure there must be a better or easier way but this worked like a charm!