Data Source Connection Details Passing from Jasper Server to SubReport Dynamically

Data Source Connection Details Passing from Jasper Server to SubReport Dynamically

Here is how we get it working using a MYSQL connection for a subreport which removes the need to hard code the connection to the sub report’s data source on Jaspeserver

Prerequisites:: Ireport 5.6 ,Jasper server 5.6
DataBase : Foodmart,SugarCRM

Step 1 : Create a Main Report with Two Subreports ,one subreport pointing to Main Data Source i.e.,SugarCRM and for other SubReport foodmart details we need to pass Dynamically

Step 2 : Pass the main data source to the report unit as usual, and create the other data source on jasper server you want to use in the sub report

Step1

Step 3 : In the main report, create a parameter $P{DATA_SOURCE_PATH} where

Parameter Class : com.jaspersoft.jasperserver.api.metadata.jasperreports.domain.JdbcReportDataSource

Default Expression :
com.jaspersoft.jasperserver.api.engine.jasperreports.util.RepositoryUtil.getThreadRepositoryContext().getRepository().getResource(null,”/datasources/FOODMART”)

Step2

In Default Expression we need to pass the DataSource Path which we have created for SubReport

Step 3

Step 4 : Now we can call functions on this parameter and pass in the connection to your subreport as follows

Create a parameter that you will pass to the subreport named SUBREPORT_CONNECTION

Default Expression :

java.sql.DriverManager.getConnection($P{DATA_SOURCE_PATH}.getConnectionUrl(),$P{DATA_SOURCE_PATH}.getUsername(),$P{DATA_SOURCE_PATH}.getPassword())

Step 4

Step 5 : Pass SUBREPORT_CONNECTION parameter to SubReport

Step 5

Step 6 : Deployed in Server and Verify once

Output in Jasper Sever:

Table using SugarCRM Database
Step 6

Table using FoodMart Database
Step 7

 

Thanks,
Satya Gopi Parisa,
BI Developer.

XmlSlurper

XmlSlurper in Groovy

This class belongs to the  package groovy.util.XmlSlurper. The XmlSlurper is very useful in groovy to handle XML related operations

 

The constructor XmlSlurper() can be used to create a very loose (non-validating and namespace-aware) instance.

XmlSlurper parsers
The parse method has many flavors. The parse method returns a GPathResult object.

parse(File file) : XML file can be parsed directly
parse(InputSource input) : Parse the content of input source
parse(InputStream input) : Any binary input stream which refers to XML
parse(Reader in): Using a Reader instance
parse(String uri): URI eg: http://www.helicaltech.com/test.xml
parseText(String text): A helper method to parse the given text as XML

GPathResult

This is the key object which helps in navigating the xml object parsed by XmlSlurper

Some important method of the GPathResult
breadthFirst(): Provides an Iterator over all the nodes using a breadth-first traversal.
childNodes(): Returns an iterator over the child nodes
children(): Returns the children of this GPathResult as a GPathResult object.
declareNamespace(Map newNamespaceMapping): Adds the specified map of prefix to namespace mappings to this GPathResult.
depthFirst() :Provides an Iterator over all using depth-first traversal.
find(Closure closure) :Returns the first child matching the condition(s) specified in the passed closure.
name(): Returns the name of element
parent(): Returns as parent nodes
size() : Returns the size of this GPathResult.
text(): Returns the text of this GPathResult as a String.

 

nodetree

 

Display Text Values Diagonally in Jasper Reports

Display Text Values Diagonally in Japser Reports

Here we will discuss how to display the Text Values Diagonally in  Jasper Reports with a simple Example,
Prerequisites:: Ireport 5.6 ,Jasper server 5.6
DataBase : Foodmart
In this Example ,we will display the column headers diagonally by using a Table Component
Step 1:Create a sample report contains Table Component

step1

Step 2: Next Increase the Column Height and do the below changes

Step2

After the above changes the Report looks like

step3

Step 3:Assign the below property for the column headers :

step4

Step 4:Then Deploy the Report in Jasper Server,then the output will be

step5

We need to use overrides_custom.css to see Text field as Diagonally ,in overrides_custom.css file include the below code and make it as a default theme

.table_header  span
{
                                                 transform:rotate(-150deg);
                                       display:inline-block;
}

After making the Theme as  Default Theme,re-run the Report ,Output :

step6

Thanks,
Satya Gopi Parisa,
BI Developer.

Top 10 functions in _ underscore

Here is list of interesting functions available in underscorejs

 

  • reject: This function returns the values in list without the elements that matches the condition.

 

  • contains: This function returns true if the value is present in the list.

 

  • max: This function returns the maximum value in list.

 

  • shuffle: This functions helps us to shuffled copy of the list,

 

  • toArray: This function creates a real Array from the list.

 

  • first: This function returns the first element of an array.

 

  • last: This function returns the last element of an array

 

  • pairs: This function convert an object into a list of [key, value] pairs.

 

  • clone: This function create a shallow-copied clone.

 

  • matcher: This function helps in matching the key/value properties present in attrs.

Calling-jasper-report-from-java-application

Calling-jasper-report-from-java-application

Considering reader has prior idea of creation of report in jasper reports. I am going to describe how we can run jasper reports from java application.First create report, and publish the report on jasperserver.
Report name: report.jrxml. In the java code add all the jar files required to build the project from jasper installed directory.

lib-java

In java class write the following piece of code:
package local;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Locale;
import java.util.Map;

import net.sf.jasperreports.engine.JRDataSource;
import net.sf.jasperreports.engine.JREmptyDataSource;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JRParameter;
import net.sf.jasperreports.engine.JasperCompileManager;
import net.sf.jasperreports.engine.JasperExportManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;

public class JasperReportFillI18 {

public static void main(String[] args) throws JRException, InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {

String sourceFile = args[0]; //jrxml file path
String locale = args[1];//language
String outputDirectory = args[2];//output file path directory
String outputFileName = args[3];// output file path directory with  file name
JasperReport jp = JasperCompileManager.compileReport(sourceFile);
// calling exportReportToPdf method
//exportReportToPDF(jp, outputDirectory, outputFileName, locale);
File outDir = new File(outputDirectory);
outDir.mkdirs();
JasperPrint jasperPrint = null;
Connection con = null;
// Export to PDF.

try {
//database connection
Class.forName(“com.mysql.jdbc.Driver”).newInstance();
Class.forName(“org.postgresql.Driver”).newInstance();
Connection datasource = DriverManager.getConnection(
“jdbc:postgresql://localhost:5433/foodmart”, “postgres”,
“postgres”);

HashMap parameterMap = new HashMap();
//Preparing parameters
parameterMap.put(JRParameter.REPORT_LOCALE, new  Locale(locale));
//parameterMap.put(“year”, 1961);
//parameterMap.put(“DataFile”, “CustomTableModel.java”);
jasperPrint = JasperFillManager.fillReport(jp, parameterMap,
datasource);

//for empty datasource
 //jasperPrint = JasperFillManager.fillReport(jp, parameterMap, new JREmptyDataSource());
JasperExportManager.exportReportToPdfFile(jasperPrint,
outputFileName);

} catch (JRException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println(“Job has finished::!!”);
}
}
Configure the run time arguments in run configurations consider the following example:
“E:/Ireport Reports/local_test.jrxml””en_US””E:/Ireport Reports””E:/Ireport Reports/test.pdf”
Execute the java application to see the final output in pdf format in the path provided in arguments.

Thanks
Nisha Sahu
BI Developer

Table of contents in Jasper Community 6.3

Table of contents in Jasper Community 6.3

Here I’ll discuss how to meet requirement when in jaspersoft you have been asked to create index. Index as it is available in many books, exactly in same way. That is, in index there will be list of contents in the report along with page number. Not many knows that jasper has this feature available : report book, which one can used to publish report containing table of contents. Thus, proceed as follows :

How to create report and add it to the report book:

  1. Click to open the selection window.
    Jasperstudio->jasper Report->next->Blank A4 ->next
  1. Select the root folder->give suitable file name jrxml-> next
  2. In the Data Source window, select foodmart DB – Database JDBC Connection, and enter the following query:

SELECT * FROM customer ORDER BY state_province limit 200

  • Add the required data fields in the subreport from the list.
  • State_province
  • Occupation
  • Education
  • Phone1
  • Lname
  • Customer_id

Add the state_province field in the group by field.
Compile the Report.

Add report  to the report book:

  1. Click to open the selection window.
    Jasperstudio->jasper Report-> report books->abstract book-> next->report book-> query->finish.
  1. Drag Content_Page.jasperfrom the Project Explorer into the Content group.
  2. In the Properties view of Content_Page.jasper, -> data->click Edit Parameters.
  3. In the Report Part Parameters window, click Add to open the Parameter Configuration dialog.
  4. In the Parameter Name field, enter REPORT_CONNECTION.
  5. Click to open the Expression Editor.
  6. In the first column, select Parameters.
  7. From the center column, double-click REPORT_CONNECTION parameter connectionto add it to the editor field, and click Finish. The expression appears in the Parameter Configuration field.
  8. Click OK and confirm the parameter has been added to the Part Parameters list, then click Finish.

Similar way add as many content page in the report.
Preview the report to see the report book.
Also, we can add number of subreports in the main report and add that report in content page. In this case while previewing report we ll be able to see the subreport name and the respective page number in which it got rendered.

For further queries and help please comment.

Refer:http://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v60/report-books

Thanks,
Nisha Sahu
BI Developer

Show Alert Window based on lnput Control Validation in Jasper Server

Show Alert Window based on lnput Control Validation in Jasper Server

Requirement: Create a Pop Up alert box based on Input Control Validation in Jasper Server.

In order to achieve above scenario,please follow the below steps:

  • Created one report in jasper studio having two input controls having integer type as RangeA and RangeB.
  • Set default value to both input control as RangeA=50 and RangeB=10.
  • Created one jsp page as “AlertWindow.jsp” having javascript code for alert box based on below condition like RangeB value should never be greater than RangeA value if it is greater then show alert box.

JavaScript Code

var count ; // To set seconds
var RangeA; // To set RangeA input control value
var RangeB; // To set RangeB input control value
/* Function to setTimeout for applying javascript after report load*/

var counter=setTimeout(function() {

jQuery(“#RangeA input”).addClass(“RangeA”); // Add class “RangeA” to RangeA input(Taken id from firebug)
jQuery(“#RangeB input”).addClass(“RangeB”); // Add class “RangeB” to RangeB input(Taken id from firebug)

/* Get value of input text using class*/
RangeA=jQuery(‘.RangeA’).val();
RangeB=jQuery(‘.RangeB’).val()

console.log(“RangeA”+RangeA+”–RangeB”+RangeB); //console log value to check values

function changeValue(cl) {
/* OnChange event function on RangeB input to show alert window when RangeA value less than RangeB and ViceVersa*/
jQuery(“.”+cl).on(“change”,function(){

if (jQuery(‘.RangeB’).val()>jQuery(‘.RangeA’).val()) //If condition when RangeA value less than RangeB
{

alert(“RangeA percentage should be higher than RangeB percentage value”); //Alert box message
document.getElementById(“apply”).disabled = true;

}
else
{
document.getElementById(“apply”).disabled = false;
}
}); //End of OnChangeevent function
}
changeValue(“RangeA”);
changeValue(“RangeB”);

}, 10000);

  • Placed “AlertWindow.jsp” at location “/WEB-INF/jsp/modules/inputControls/”.
  • Deployed report on jasper server by assigning Min and Max value range to in input controls as
    RangeA : 50 to 90
    RangeB : 10 to 90
  • After addition of input controls to report on jasper server , you can see the Optional JSP Location section under “Controls & Resources” , add the “AlertWindow.jsp” path shown below :

AlertWindow

  • Submit the report and open the report , where you can the report with two input controls.
  • Now try to change the RangeB value any value which is greater the RangeA value and should be less than 90 according to range.
  • When to set value of RangeB greater than RangeA because of OnChange event you can see the Alert Box showing message as below which will be the expected output:

Popup

 

Thanks,
Satya Gopi,
BI Developer

Adjust alignment of label to right and fix label truncating issue in stacked bar chart

Adjust alignment of label to right and fix label truncating issue in stacked bar chart

Softwares:

Jasper Server 5.6.0
IReport 5.6.0

Now we will see how to work on Lables Alignment and fix truncating in Stacked Bar Chart

First create a sample report which includes Stacked Bar Chart

Report Output in Jasper Server as follows

OriginalOutputt

Expected Output in Jasper Server as follows

ExpectedOutput

In order to achieve Expected Output,write custom code in java and include below lines of code:

For Label Alignment include below line

 plot.setDomainAxisLocation(AxisLocation.BOTTOM_OR_RIGHT);

For avoid truncating Labels include below line

  categoryAxis.setMaximumCategoryLabelWidthRatio(3.0F);

Create jar file of the java code and add it in chart customiser property of chart in the format: ‘packagename.classname’ as follows:-
Refer the screenshot:

ChartCustomzeer

Publish the Report,and add the Jar for the Report which had published
Right Click on the Report –>Edit

EditJaar

Control Resources –>Add Resource–>Upload Jar

UploadJar

Give a valid name and then Submit

Execute the Report and output as follows

ExpectedResult

Thanks,
Satya Gopi,
BI Developer

Dynamic Query Implementation in Jasper CVC

Dynamic Query implementation in stacked bar chart using high chart library in jasper CVC

In this blog, I’ll discuss how dynamically data returned from query can be used in stacked bar chart. Here, I have created stacked bar chart using highcharts library through jasper CVC component in jasper studio.  I am writing this blog, considering that whosoever is trying this has previous knowledge about jasper CVC component integration using highchart library.

Initially create one blank jasper report using CVC component in jasper studio. Create two .js files: build.js,  stackedbarchart.js. Use the following codes in their respective files:

Code in Build.js:

({

baseUrl: '',

paths: {

'highcharts': 'highcharts'

},

name: "stackedbarchart",

out: "stackedbarchart.min.js"

})

Sample query used for report:

select  ‘Silent Alert’ as  alert_type ,’1′ as number, ‘John’ as MonthYear , 100 as count

union

select  ‘Silent Alert’ as  alert_type ,’1′ as number, ‘Jane’ as MonthYear , 150 as count

union

select  ‘Silent Alert’ as  alert_type ,’1′ as number, ‘Joe’ as MonthYear , 50 as count

union

select  ‘No Motion Alert’ as  alert_type ,’2′ as number, ‘John’ as MonthYear , 200 as count

union

select  ‘No Motion Alert’ as  alert_type ,’2′ as number, ‘Jane’ as MonthYear , 250 as count

union

select  ‘No Motion Alert’ as  alert_type ,’2′ as number, ‘Joe’ as MonthYear , 300 as count

union

select  ‘Missed Check-in Alert’ as  alert_type , ‘3’ as number,’John’ as MonthYear , 150 as count

union

select  ‘Missed Check-in Alert’ as  alert_type , ‘3’ as number,’Jane’ as MonthYear , 180 as count

union

select  ‘Missed Check-in Alert’ as  alert_type , ‘3’ as number,’Joe’ as MonthYear , 120 as count

Set the fields name in the properties of CVC component for the query as shown in the images below:

1b



















2b











3b


















Code in stackedbarchart.js:

//function to compare series name value with data coming from query.

function passobjalert(objs, key) {

var aa = objs.filter(function(v) {

return v.alerttype === key;

}).map(function(val) {

return val.count;

});

return aa;

};

var silentalert = passobjalert(WholeObj, "Silent Alert");

var motionalert = passobjalert(WholeObj, "No Motion Alert");

var missedcheck = passobjalert(WholeObj, "Missed Check-in Alert");

silentalertObj.name = WholeObj[0].alerttype;

silentalertObj.data = silentalert;

motionalertObj.name = WholeObj[3].alerttype;

motionalertObj.data = motionalert;

missedcheckObj.name = WholeObj[6].alerttype;

missedcheckObj.data = missedcheck;

console.log("obj: ", silentalertObj);

console.log("obj: ", motionalertObj);

console.log("obj: ", missedcheckObj);

 

Highcharts.chart(instanceData.id, {

chart: {

renderTo: instanceData.id,

type: 'bar'

},

xAxis: {categories: MonthUnique

},

yAxis: {

min: 0,

title: {

text: 'Count'

},

stackLabels: {

enabled: true,

style: {

fontWeight: 'bold',

color: (Highcharts.theme && Highcharts.theme.textColor) || 'gray'

}

}

},

title: {

text: ''

},

plotOptions: {

bar: {

stacking: 'normal',

dataLabels: {

enabled: true,

color: (Highcharts.theme && Highcharts.theme.dataLabelsColor) || 'white'

}

}

},

series: [silentalertObj,motionalertObj,missedcheckObj]

});

};

return result1;

});

After successful build of the component, run the report in HTML format. The final output will be as shown in the following screenshot:

4b











Hope it helps!

Thanks,

Nisha Sahu

BI Developer

 

Custom Hyperlink Handler in Jasper Reports Server

Use custom hyperlink handler with a Dashboard in JasperReports Server

Used jasper server version : 6.3.0

Problem Statement: Add hyperlink control for particular dashlet which will open drill-down in same dashboard page instead of opening drill-down report in new page.

Expected Output :

Expected output should be in one dashboard as shown below when we click on one dashlet hyperlink should open drill down report in same panel.

 

Step 1 : Click on any part of pie chart(HTML5) ie.department .
Lets assume if we click on department_id = 18 shown below :

 
Picture3

Step 2 : After click on department_id 18 we can see the difference it will reflect to drill down report which shows you the information related to department_id 18 and in input control automatically department_id as 18 will be selected shown below :

 
Picture4
 

If you select/click on another department_id should reflect in drill down report , you can try it.

 
Solution :

As mentioned above in expected output we created one dashboard having two dashlets.In it instead of passing hyperlink url we set constant value in hyperlink type as shown below :

 
Picture5
 

Or you can set it in source file(.jrxml) mentioned below :

 

  • Create a custom hyperlink type in your main report. Here’s an example for an HTML5 chart. In this case we named it “UpdateDashboardParams”(in source code):
  • Create a new Dashboard and put all your reports on canvas shown below:
  •  
    Picture6
     

  • Go to “Show Parameter Mapping dialog” in the Dashboard and manually create a filter (will create a section named “Manually Created Filters”) with the same name as it is produced by your custom hyperlink. In this example it is “Gender”. Then wire it to corresponding parameters in your “child” reports:
  •  
    Picture7
    *Property Name*
     
    Picture8
    *Show parameter mapping dialog*
     

  • Save the Dashboard.
  • Create a custom handler for your new hyperlink type, save it as dashboardHyperlinkHandler.js (in this case it is dashboardHyperlinkHandlerUpdateDashboardParams.js) should be same as constant value (Step 1) given for hyperlinkType otherwise it will not work.
  • Put dashboardHyperlinkHandlerUpdateDashboardParams.js to both /scripts and /optimized-scripts folders of your server deployment (.js file attached below):
  •  
    dashboardHyperlinkHandlerUpdateDashboardParams.js

  • Restart jasper server and see hyperlink in dashboard.
  • Open your dashboard in Dashboard Viewer and try hyperlinks.

 
Here I attached the same above mentioned example using HTML5 chart, you can refer :
 
Custom dashboard hyperlink using HTML5 chart Example
 
Custom_dashboard_hyperlink_HTML5_charts
 
Custom dashboard hyperlink using charts-pro Example
 

Here , we used charts-pro pie chart in dashboard for custom hyperlink , the functionality is same just little bit of chart data configuration is different in charts-pro.

 

Following are the steps to use custom hyperlink in charts-pro :

 

  • In jasper studio create new report.
  • From left side components-pro palette add charts-pro component and select pie chart.
  • Set series , categories and value expression according to you requirement under chart data.
  • Under Item Hyperlink set the constant value and its type which we previously used in HTML5 charts shown below :
  •  
    Picture9

  • Set hyperlink parameters under “Hyperlink Parameters” Section.
  • Now add your dashlets in dashboard :
  •  
    Picture10
     

  • Follow above Step 3 to set filters for drill down report shown below :
  •  

  • Save dashboard and follow above step 5 and step 6.
  • Restart jasper server and see hyperlink in dashboard.
  •  

    You can see the dashboard like this :

     
    Picture11
     

    Please find the attachment of above example(custom hyperlink dashboard using charts-pro)

     
    Custom_dashboard_hyperlink_charts_pro
     
    Reference:

    http://community.jaspersoft.com/wiki/how-use-custom-hyperlink-handler-dashboard-jasperreports-server
     

    Thanks,
    Sayali | Helical IT Solutions