This document is to help users to overcome jasper reports complexities using PLPGSQL (Postgres Stored Procedure function). Will look into the complexity with input controls of type multi-select query and how to cascade them.
Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.
Creating a stored procedure function in plpsql, as we are dealing with multi-select query we need to create a stored procedure function with Array , there is also function VARDIC available in plpgsql but using Array is much easier.
Complexity 1:
Example: How to create a stored procedure function for type multi-select query input control.
CREATE OR REPLACE FUNCTION multiple_m1(text[])
RETURNS TABLE(comp_id integer, comp_name text, comp_sort text) AS
$$
BEGIN
IF ‘Khosla’ = ANY($1) THEN
RETURN QUERY SELECT id::int as comp_id, ltrim(company.compname)::text as compname, ltrim(company.compname)::text as sort
FROM company where deleteflag = ‘0’ and active = ‘1’
and id in (270,394,376,396,403);
ELSEIF ” = ANY($1) THEN
RETURN QUERY (SELECT id::int as comp_id,ltrim(company.compname)::text as compname, ltrim(company.compname)::text as sort
FROM company where deleteflag = ‘0’ and active = ‘1’
UNION SELECT -1::int AS id, ‘All’::text AS compname, ‘ ‘ as sort) order by sort;
ELSE
RETURN QUERY SELECT id::int as comp_id,ltrim(company.compname)::text as compname, ltrim(company.compname)::text as sort
FROM company where deleteflag = ‘0’ and active = ‘1’
and (compname like ANY($1) or ” like ANY($1));
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql
How to call function in Input Control?
Select * from company_multiple_m1 (‘{“”}’);
{“”} = NULL.
Create another Input Control of type Multi-Select Query. Here is another I/P control where comp_id column value of “multiple_m1” (Input control) is the input of my 2nd I/P Control. So My 2nd I/P control runs based on the comp_id value of 1st I/P control. This is nothing but cascading.
CREATE OR REPLACE FUNCTION mulitple_m2(integer, integer[])
RETURNS TABLE(id integer, reference integer, job_title text, status text) AS
$$
BEGIN
IF -1 = $1 THEN
RETURN QUERY select jobs.id::int, jobs.reference::int, jobs.job_title::text, ltrim(substring(jobs.status,3,char_length(jobs.status)))::text as status
FROM jobs ,company c
where jobs.”DeleteFlag” = ‘0’
and c.id= jobs.id and c.DeleteFlag = ‘0’ and c.active = ‘1’
and (jobs.id = ANY($2) OR ‘-1’= ANY($2))
order by jobs.job_title;
ELSEIF 1 = $1 THEN
RETURN QUERY select jobs.id::int, jobs.reference::int, jobs.job_title::text, ltrim(substring(jobs.status,3,char_length(jobs.status)))::text as status
from jobs ,company c
where jobs.”DeleteFlag” = ‘0’ and jobs.status = ‘1: Open Req’
and c.id= jobs.id and c.DeleteFlag= ‘0’ and c.active = ‘1’
and (jobs.id = ANY($2) OR ‘-1’ = ANY($2))
order by jobs.job_title;
ELSE
RETURN QUERY select jobs.id::int, jobs.reference::int, jobs.job_title::text, ltrim(substring(jobs.status,3,char_length(jobs.status)))::text as status
from jobs ,company c
where jobs.”DeleteFlag” = ‘0’ and jobs.status = ‘0: Inactive’
and c.id= jobs.id and c.DeleteFlag= ‘0’ and c.active = ‘1’
and (jobs.id = ANY($2) OR ‘-1’= ANY($2))
order by jobs.job_title;
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql
Cascading value of Parameter 1 into Parameter 2?
SELECT * FROM mulitple_m2($1,$2);
SELECT * FROM mulitple_m2($1,ARRAY[$P!{mulitple_m1}]::integer[]);
Complexity 2:
If let’s say your Input Control off type Multi-Select Query value is String and if this value is passed on your SQL Query. Your SQL Query does not works with $X{IN,}
$X{IN,column_name,parameter} does not works with plpgsql. It throws Error Message saying that Column “Column_name” does not exist.
Hence $X {} does not work in Jasper server when dealing with Stored Procedure.
Another work around is to pass $P{} in your SQL instead of $X{} and it throws an Error saying that “parameter type not supported in query : parameter_name class java.util.Collection”. Hence $P can be used.
Another work around is to pass $P{} with exclamation as $P!{parameter} in your SQL instead of $X{} and it throws an Error saying “org.postgresql.util.PSQLException: syntax error at or near “[” ”
What is that Error? Where is that Syntax Error?
Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.
The reason behind this is your jasperserver passing String Values like [AJ, alexandra, Amanda, Amy, andrea, Angie, API]. Your SQL statement does not accept string values without single quotes. Thus in order to add single quotes to the parameter values, we need to perform at java end and then to pass those values on SQLquery.
How to perform it?
1. Add another parameter on the report.
2. In expression field using Join function of Java, add single quotes along with Array and pass this parameter into SQL query.
EX:
“ARRAY[‘”+$P{parameter _m1}.join(‘\’,\”)+”‘]”
2) 3. Know your SQL query looks like
SELECT * FROM Nofsubmitted_report($P!{parameter _2});
Complexity 3:
If let’s say your Input Control off type Multi-Select Query value is Integer and How we pass this Integer array (collection) to call plpgsql function.
As we know $X{} and $P{} does not works in Jasper Server when dealing with stored procedure function, we have also seen what error throws when we use it.
The solution i found is to pass value with in Array like
SELECT * FROM function_name(ARRAY[$P!{parameter_m1}]);
For any confusion, please get in touch with us at Helical IT Solutions
Best Open Source Business Intelligence Software Helical Insight is Here
