How to use Google maps in Jaspersoft Studio

How to Use Google Maps in Jaspersoft Studio

The report in this example is developed on Jaspersoft Studio version: 6.3.1 final and deployed on Jaspersoft server version: 6.3.0 Enterprise edition.

Step 1: Create a report in jasper studio. In this example report is called GoogleMapsTest.

Screenshot (193)

1- Delete all the bands except Title and summary band. Use the query “Select 1 as one” in main data-set query.

Step 2: Adding the jasper map component to report.

Screenshot (173)

1- Add the text box to Title band and insert any relevant text into it.

2- Drag the Map component from Palette on to the Summary band of the report.

3- Add a data-set named “Markers” to the report, and use this query in it.

Query:

select ‘NorthCharleston’ as CityName,’-80.02842′ as Longitude,’32.92896′ as Latitude
union
select ‘GREENVILLE’ as CityName ,’-82.33626515′ as Longitude ,’34.79360263′ as Latitude
union
select ‘ZIBO’ as CityName,’117.8351787′ as Longitude,’37.17124911′ as Latitude
union
select ‘Mumbai’ as CityName, ‘72.9227767’ as Longitude, ‘19.10662726’ as Latitude

Step 3: Add markers to the map.

Screenshot (175)      Screenshot (176)

1- Click on the Map and select the Markers tab from the Properties section.

2- Add the created data-set to the marker by selecting the Data-set tab.

Step 4:Add markers properties.

Screenshot (177)

1- For adding marker properties, click the add button on right side, and insert the following details.

i.) Longitude & Latitude : For adding Longitude & Latitude, click the the properties icon on the right hand corner of the input text field. Check the “Use Expression” box and provide the latitude and longitude fields in their appropriate input boxes.

Screenshot (179)

ii.) Adding Mouse-over(Information appearing when hovered on marker) : For mouse-over information, insert data into the “Title” box in the window.

Screenshot (181)

iii.) Adding Pop-up window content(Content shown after clicking the marker) : For adding pop up window content, use the “Info Window”  text boxes.

Screenshot (183)

Step 5: Deploying the report in jaspersoft server

Publish the report on jasper server.

Screenshot (192)

………. and Voila..!!!!!!!!!!!

Thank You

Ravi Bhatta

 

 

Creating a custom template with Jaspersoft Studio

Creating a custom template with Jaspersoft Studio

Create the Structure

Creating template is nothing more than a standard report where some elements have a precise and fixed name that is used by the Jaspersoft Studio engine to understand where to place every element.

The First thing is to create new report from File -> New -> Jasper Report. We can start with blank report or selecting one of the other templates as a starting point.

Now Design the template as par your requirement .Creating the template is same as that of generating the report.

I have created like this

Report Template

Now switch to the Preview mode and look if the result is what you want and if it is allright you can continue with the export operation.

Export the Template

To start the export wizard select click File -> Export as Report Template.

In the first dialog you will see all the resources that will be exported. Between these resources you can fine the template and other files used by it, in our example you will see the one image. In this step you will need also to select the destination folder, where the template and all his resources will be placed.

Template Export

When you have finished press Next.

Here you can define the categories where your template will be visible inside the New Report Wizard.  You can select any number of categories or your own category. To define a custom category you need only to type its name inside the text area on the bottom and press the Add button. Then your category will be added to Selected Categories list. Then your category will be added to Selected Categories list.

At this point you need to select the correct type of report, for example if it is a Tabular Report or a standard one.
Template Type and Categories

At this point you can see two different steps:

  • The first one, and the one that should appear follwoing this tutorial, is a congratulations step. It means that the template you designed is valid for the report type you have selected in the previous step.
  • The second one is a list of error messages if the template you designed is not valid for the selected type because of some design errors. For example for a template with Tabular type is expected a table in the summary band. If this table is not found it’s probably a design error. In this step all the design errors found are listed so you can look to what is wrong and fix it.

5(30)
 

OR

 
6(22)

 

In both the cases we can export the template and all resources by clicking the finish button.

In second case may your template should not work while generating the report.

Add the Templates location to Studio

  1. Now you have to put your custom template for studio.
  2. Navigate to Windows ->Preferences. A new Preferences window open.
  3. From the Left panel expand the category Jaspersoft Studio and select Template Locations.
  4. Click on New and browse the folder where you have saved template jrxml file.

Refer the screenshot below:
5(14)

Test the new template

  1. Open Jaspersoft studio.
  2. Navigate to File > New > Jasper Report.

A new Report wizard will open.
Template

  1. Select the template that you have added. Then Report will open as shown below:

Report Template
 
-By
Nitin Uttarwar
Helical It Solution

Overcome Jasper Reports Complexities using PLPGSQL

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.

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?

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.

Jasper Reports using PLPGSQL

    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