How to increase MYSQL functions character lengths without changing validating SQL in Jasper

Prerequisites::
Jasper Server 5.5,IReport
Database:: Foodmart

Scenario : I have two Tables for Table 1 i need to pass Input Control value column and for  Table2 i need to pass Input Control visible column

For Table 1 I have created a multiselect parameter(Customer Name) and passing the value column to query

1

For Table 2 I have created one more hidden cascading parameter(single select) based on above parameter and make it as mandatory(Customer Hidden)

2

Using hidden parameter we can pass as a single string to the Query using GROUP_CONTACT ,but here I have came across with a problem is maximum character length for GROUP_CONTACT is 1024 characters so I need to increase character length if we selected more than 1024 characters from Customer Name

First I have selected only 3 customers from Customer Name,so it showing 3 records

3

Now I am selecting all customers from Customer Name but Customer Hidden parameter is filtering data  upto “Adams Philp” customer only

4

To overcome the above issue we need to increase character length by placing SET SESSION group_concat_max_len = <number>; in Query this will work only when we will change validate.sql=false in jasper server and again we need to restart jasper sever

But without changing the validate.sql we can handle for that kindly follow this blog

Pass session value in Datasource URL  as follows
Here I am passing Session value as 4294967295 ,now it take length as 4294967295
                                       SET SESSION group_concat_max_len = 4294967295;

5

Now Data is filtering till Last Customer “Larkins John”

6

Thanks,
Satya Gopi,
BI Developer.

Leave a Reply