How to use IN/NOT IN Using Parameter

Example

SELECT * FROM EMPLOYEE WHERE EMP_NAME IN($P{P_STUDENT_NAME})
'SANDIP','VIJAY' <-- Not Work As Considering As A Single Value.

SELECT * FROM EMPLOYEE WHERE EMP_NAME IN ($P!{P_STUDENT_NAME})
'SANDIP','VIJAY' <-- Work As Per Expected

SELECT * FROM ORDERS WHERE $X{IN,EMP_NAME,P_STUDENT_NAME} 
Work As Per Expected But Need To Pass Array

$P!{P_STUDENT_NAME} : This will not be treated as a SQL parameter. 
JasperReports will consider this parameter value will be a part to sql statement.
So you can also pass the query part from parameter.

Example: How to make order by as a parameterised.
select * from employee ORDER BY $P!{P_STUDENT_NAME}

Pass Parameter Value as : EMP_NAME DESC 
Note : Without " or ' sign as there is no quate in query.

JasperServer: How to generate/export report using url

http://<host>:<port>/<context>/flow.html?_flowId=viewReportFlow&reportUnit=/supermart/Details/XXX&employee=123&output=pdf

&output=pdf : Add this parameter in url to get a pdf file.
&output=xls  : Add this parameter in url to get a xls file.

Tags: 

Export PDF
Export Excel

Jasperserver.

Custom JQUERY in Jasper Server

You can add your custom JQUERY code with below syntax.
i.e. jQuery('#back').hide();

Follow the below steps to test JQUERY code. After implement below steps you can hide back button with JQUERY custom code.

Step 1: Open \\apache-tomcat\webapps\jasperserver\scripts\commons.main.js

Step 2: Add JQUERY code in below function.      

           domReady(function(){
 //Add the below jquery to hide back button
 jQuery('#back').hide();
            });

Step 3: You can see that back button is not display.

Below is result.

Tags: 

JQUERY
Customization

Jasperserver.

How to use JSON File with Table Component.

 Please Refer Attached JSON File and understand that below is my structure.

You can download the JSON Viewer Freeware Software from below link.
http://tomeko.net/software/JSONedit/index.php?lang=en

Below image display that how we can display JSON data in Table component.

Step 1 : Create connection with JSON File.

Step 2 : Create one data source and define Json query language path.

Step 3: Take Table Component and set parameter.

SAMPLE JSON FILE: Two.json

{"company" : "abc",
 "dbinformation" : {
      "company_detail" : "the description",
      "company_id" : 1538,
      "division" : {
         "product" : [
            {
               "proddetail" : {
                  "description" : "abc mobile",
                  "id" : 1,
                  "type" : "mobile"
               },
               "amount" : 6.0,
               "rate" : 4000
            },
            {
               "proddetail" : {
                  "description" : "xyz tablet",
                  "id" : 2,
                  "type" : "tablet"
               },
  "amount" : 2.0,
               "rate" : 5000
            }
         ],
         "product_supply_detail" : [
            {
               "address" : {
                  "description" : "Pune",
                  "pincode" : 38001
               },
            "productid" : 1,
     "quantity"  : 3
            },
            {
               "address" : {
                  "description" : "Ahmedabad",
                  "pincode" : 38001
               },
            "productid" :1,
     "quantity" : 4
            }
         ]}
   },
   "date" : 15022015
}

Tags: 

JSON
iReport

Jasper Studio
JSPN With Table Component

Display $ sign before numeric amount field.

Use Pattern as : $#,##0.00

Tags: 

iReport
Jasper Studio

Display Column Total in Table Control - With the use of Variable

    Example :

    You can use the Table Control Footer Group Band and add variable in this Footer group band
    To achieve below output.

    Check mark the below highlighted check box to enable group band.
   

   Below is the report output.

Tags: 

iReport
Jasper Studio

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.