REPORT SCHEDULING

You can schedule, set the output format, attachment, timing and notification with the use of below functionality

Before apply this step you have to follow the below instruction to set SMTP and parameter in two files.

Gmail Account Setting in Jasper
Setting mail account for Subscription of scheduled Jobs in JasperServer.

Prerequisites -
1. You should have working copy of JasperServer community/pro
2. You should have uploaded a report to the server
3. Shut down the jasperserver so that no sort of errors come in case of Editing/Saving documents.

After following the above prerequisites now we head on to edit two files as follows.
Both these files will be found at the location 
<directory of Jasperserver on Disk>/apache-tomcat/webapps/jasperserver/WEB-INF/

1. js.quartz.properties
   - Edit the file with the Text Editor
    -Change the portion of the document as 
    (Please select your own server details I have used a gmail account )
report.scheduler.mail.sender.host=smtp.gmail.com
report.scheduler.mail.sender.username=test@gmail.com
report.scheduler.mail.sender.password=password
report.scheduler.mail.sender.from= test@gmail.com
report.scheduler.mail.sender.protocol= smtp
report.scheduler.mail.sender.port= 587
- Make the changes and save the file.

2.   applicationContext-report-scheduling.xml
  -  Locate the bean reportSchedulerMailSender
  -  Locate the property javaMailProperties
  -  Do the changes as below - This will enable java to interact the smtp/startls authentication.      
         <props>
                <prop key="mail.smtp.auth">true</prop>
    <prop key="mail.smtp.starttls.enable">true</prop>
         </props>
  - Now save the document.
  
Now the report subscription is ready for run, just start the Jasperserver and you are good to go.

Tags: 

Jasperserver.

Use Variable or With clause in Jasper Server.

With or Variable not working in Jasper Server.

I am getting error in subsequent report. if I use “common table expression” in MY/MS sql query in Jasperserver.

for example:
declare @var integer
or
with a as ( select…)

Solution for this problem:

Change the below parameter in /jasperserver-pro/WEB-INF/classes/esapi/security-config.properties  to generate the report.

security.validation.sql.on=true
and 
Go to // WEB-INF/classes/esapi/validation.properties

Find property:
Validator.ValidSQL=(?is)^\\s*(select)\\s+[^;]+;?\\s*$
And change in to:
Validator.ValidSQL=(?is)^\\s*(select|with|declare)\\s+[^;]+;?\\s*$

If you want to use other sql constructions (in start of query string), just add to regular expressions your sql command.

Tags: 

Mysql

Mssql

Jasperserver.

Java Function - Fetch Last 4 Characters From String

Java Function V/S Jasper Expression.

Fetch Last 4 Characters From String
$V{VARIABLE_NAME}.substring(Math.max($V{VARIABLE_NAME}.length() - 4, 0))

tags: 

Java Functions.

Java Expression

String Manipulation

Enable No Data Band

No data band use to display content/warning/error while main query not return a single row.

Follow the below step to use the"No Data" band of jasper report.

Step 1 : Right click on "No Data" band and select "Add Band" If it is not visible, More detail follow the section 1 of above image.

Step 2: select report (as mention in section 1 of above image) and right click as well select property option. Set "When No Data" as "No Data Section" property (detail display in section 2 of above image) 

Step 3 : Now run the report with no data query i.e. select 'Yes' where 1=2 so this query not return a single row so the result will display as mention in no Data section.

Tags: 

No Data Section.

When no data found display alert message.

Dynamic Columns Cross Tab Report.

Multi Columns Cross Tab Report.

You can generate the multiline cross tab report as par below output by providing raw data (There is 8 rows are the input and below table is the output of the cross tab report.)

Tags: 

Jasper Report
Multi Columns Report
Dynamic Columns

Cross Tab Report

Jasper with Mongodb

Jasper with mongodb step by step. 

[ Mongodb not support the two tables/collection join but you can display two tables data in report by
Follow the below step one by one.]

Install MONGODB & Start MONGODB Server / Client

Download mongodb installation (mongodb-win32-x86_64-2.4.4.zip) and put in c:\ drive.

Open command prompt (cmd) and follow below steps.

Execute the below command to rename the folder name.

C:\>move C:\mongodb-win32-* C:\mongodb
C:\mongodb-win32-i386-2.0.6
1 file(s) moved.

Create mongodb default database directory
C:\>md data
C:\>md data\db

To Start the server execute below command.
C:\>cd mongodb\bin
C:\mongodb\bin>mongod

Open another command prompt (cmd) and start mongodb client.
C:\>cd mongodb\bin
C:\mongodb\bin>mongo

For more help of installation : http://docs.mongodb.org/manual/tutorial/install-mo...

Create Mongodb sample tables to understand in detail

Execute below all command in mongodb client  C:\mongodb\bin>mongo

Display all database command : show dbs
Create new database command :use test
db.createCollection("employee");
db.employee.insert(
{
_id: 1,
name: { first: 'John', last: 'Backus' },
city: 'New York',
department:1
}
);
db.employee.insert(
{
_id: 2,
name: { first: 'Merry', last: 'Desuja' },
city: 'London',
department:2
}
);
db.employee.insert(
{
_id: 4,
name: { first: 'Roman', last: 'Methyu' },
city: 'London',
department:1
}
);
db.employee.insert(
{
_id: 5,
name: { first: 'Roman', last: 'Roman' },
city: 'London',
department:2
}
);
db.createCollection("department");
db.department.insert(
{
_id: 1,
department: 'Manager'
}
);
db.department.insert(
{
_id: 2,
department: 'Accountant'
}
);

Create Mongodb Connection with Jasper

Create new connection and select MongoDB Connection.

Provide MongoURL and press test and save button.

mongodb://127.0.0.1:27017/test
Host Name : 127.0.0.1 (you can get the host Name number from mongodb server content).
Database Name : test 

Create Jasper Report

Step 1 : Report Main Query : { 'collectionName' : 'employee'}

Step 2 : Create Dataset : Employee
Create New Parameter : P_EMPLOYEE_ID
Employee Dataset Query :
{
'collectionName' : 'employee',
findQuery : {
$where : "this._id == $P!{P_EMPLOYEE_ID} "}
}

Step 3 :
Create Dataset : Department
Create New Parameter : P_DEPARTMENT_ID
Department Dataset Query :
{
'collectionName' : 'department',
findQuery : {
$where : "this._id == $P!{P_DEPARTMENT_ID} "}
}

Step 4:
Take two list control one for display employee data and second for display department data.

Output

To quick understanding use the below jasper report and run it

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="report2" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
<property name="ireport.zoom" value="1.5"/>
<property name="ireport.x" value="0"/>
<property name="ireport.y" value="0"/>
<subDataset name="Employee">
<parameter name="P_EMPLOYEE_ID" class="java.lang.String"/>
<queryString language="MongoDbQuery">
<![CDATA[{
    'collectionName' : 'employee',
     findQuery : {
    $where : "this._id == $P!{P_EMPLOYEE_ID} "}
}]]>
</queryString>
<field name="_id" class="java.lang.Integer"/>
<field name="city" class="java.lang.String"/>
<field name="department" class="java.lang.Double"/>
<field name="name.first" class="java.lang.String"/>
<field name="name.last" class="java.lang.String"/>
</subDataset>
<subDataset name="Department">
<parameter name="P_DEPARTMENT_ID" class="java.lang.Double"/>
<queryString language="MongoDbQuery">
<![CDATA[{
    'collectionName' : 'department',
     findQuery : {
    $where : "this._id == $P!{P_DEPARTMENT_ID} "}
}]]>
</queryString>
<field name="_id" class="java.lang.Double"/>
<field name="department" class="java.lang.String"/>
</subDataset>
<subDataset name="dataset1"/>
<queryString language="MongoDbQuery">
<![CDATA[{ 'collectionName' : 'employee'}]]>
</queryString>
<field name="_id" class="java.lang.Double"/>
<field name="city" class="java.lang.String"/>
<field name="department" class="java.lang.Double"/>
<field name="name.first" class="java.lang.String"/>
<field name="name.last" class="java.lang.String"/>
<background>
<band splitType="Stretch"/>
</background>
<detail>
<band height="26" splitType="Stretch">
<componentElement>
<reportElement x="0" y="0" width="299" height="26"/>
<jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Vertical">
<datasetRun subDataset="Employee">
<datasetParameter name="P_EMPLOYEE_ID">
<datasetParameterExpression><![CDATA[$F{_id}]]></datasetParameterExpression>
</datasetParameter>
<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
</datasetRun>
<jr:listContents height="26" width="299">
<textField>
<reportElement x="0" y="0" width="100" height="26"/>
<textElement verticalAlignment="Middle" lineSpacing="Single"/>
<textFieldExpression class="java.lang.Double"><![CDATA[$F{_id}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="100" y="0" width="100" height="26"/>
<textElement verticalAlignment="Middle" lineSpacing="Single"/>
<textFieldExpression class="java.lang.String"><![CDATA[$F{city}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="200" y="0" width="87" height="26"/>
<textElement verticalAlignment="Middle" lineSpacing="Single"/>
<textFieldExpression class="java.lang.String"><![CDATA[$F{name.first}]]></textFieldExpression>
</textField>
</jr:listContents>
</jr:list>
</componentElement>
<componentElement>
<reportElement x="299" y="0" width="195" height="26"/>
<jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Vertical">
<datasetRun subDataset="Department">
<datasetParameter name="P_DEPARTMENT_ID">
<datasetParameterExpression><![CDATA[$F{department}]]></datasetParameterExpression>
</datasetParameter>
<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
</datasetRun>
<jr:listContents height="26" width="195">
<textField>
<reportElement x="0" y="0" width="102" height="26"/>
<textElement verticalAlignment="Middle" lineSpacing="Single"/>
<textFieldExpression class="java.lang.String"><![CDATA[$F{department}]]></textFieldExpression>
</textField>
</jr:listContents>
</jr:list>
</componentElement>
</band>
</detail>
<summary>
<band height="42" splitType="Stretch"/>
</summary>
</jasperReport>

Jasper Server - Customize Theme

Jasper Server - Change Theme with company logo.

You can customize your existing Jasper Server Theme and login page as per your requirement.
Please follow the below steps for change Theme and customization.

1: Click on the Themes directory of the Jasper server where you can see the list of existing themes.

2: Right click on the theme (For example : defaults) which you want to change and press Download the theme option.

3: Give the name to the downloaded theme and save it in your computer.

4: Right click on the Theme folder in Jasper server and select Upload a Theme option.

5: Browse the same compress file (.zip) which you have downloaded with different name.
How to modify the company logo/theme.

1. To upload a new logo click on your theme which you have just uploaded and select images sub directory,
here you can find all the images which are currently being used by theme.

2: Search for the logo.png image in images and edit the image, and upload your company logo but logo should be the same in dimension of JasperSoft logo.
(Logo dimension should be 141* 28 in pixels)

3: Just right click on the new theme and select Set as Active Theme option.
Now you can see new company logo in place of the JasperSoft logo.

4: You can also change the main login page image, click on the your theme which you have just uploaded and select images
sub directory, here you can find all the images which are currently being used by theme, search for the
login_welcome_ce_bkgd.jpg and edit the image and upload your image (image size 669*283 in pixels)

Tags: 

Jasper Server

Customize Theme

Change company logo

Customize Jasper Server Log-in page