Passing Multi Select Values from Jasper to PostgresSQL Function

Prerequisites:
JasperServer Studio 6.3,Postgress
DataBase:Foodmart

In most of the scenario’s we will think, how we can pass a Multi-select value from Jasper to a SQL function, here is the solution for that

Step 1: Created a Sample Report with a Multi-Select Parameter



Step 2: Create a SQL function to convert the Multi-Select Parameter to rows

CREATE OR REPLACE FUNCTION public.get_4(
brandname text[])

RETURNS TABLE(bname text, count integer) AS
$BODY$
BEGIN 
RETURN QUERY

select product.brand_name ::text,count(distinct product_name)::integer from product where brand_name in (SELECT regexp_split_to_table(array_to_string(brandname,','),E','))
 group by product.brand_name order by brand_name
;

END;
$BODY$
LANGUAGE plpgsql VOLATILE

Step 3: Pass the multi-select parameter to SQL Function

select * from get_4(ARRAY $P!{BrandName})

Step 4: Output as follows after the MultiSelect to Query

In case if you have any queries please get us at support@helicaltech.com

Thank You
Satya Gopi
Helical IT Solutions Pvt Ltd

Free Open Source BI Platform

• AI-Powered Analytics
• Embedded Analytics
• Single Sign-On (SSO)
• Multi-Tenancy
• Paginated Canned Reporting
• Interactive Dashboard
• White Labeling

Download Free