Return Values from Sub Dataset to Main Dataset in Jasper Reports

Return Values from Sub Dataset to Main Dataset in Jasper Reports

Sometime it is required to return values from sub dataset to main report dataset but due to bug in the jasper studio we are unable to it. Please refer this bug link
http://community.jaspersoft.com/jaspersoft-studio/issues/4746

Steps to return values from sub dataset to main report dataset

1) Create two variable one in subreport dataset (lets say SubDataset_Var) and one in main report dataset (lets say MainDataset_Var).

2) Suppose this sub dataset is used by table component. Click on table component and goto properties window (dataset tab) it will show you below screen

returnvalue

3) Click on return values button it will open one pop up window , see the below image

return_values

In above image, you can see instead of MainDataset_Var it is showing SubDataset_Var in “To Variable”. So now in “From Variable” just put the MainDataset_Var name. then click ok.

It will add follow line in JRXML file

<returnValue fromVariable="MainDataset_Var" toVariable="SubDataset_Var" incrementerFactoryClass=""/>

4) Open source of JRXML and replace the fromVariable value to toVariable value and vice versa and remove the incrementerFactoryClass=””.

<returnValue fromVariable="SubDataset_Var" toVariable="MainDataset_Var"> 

Now you can use MainDataset_Var in your main report.

Note : Change the Evoluation Time of component in which you are going to use main dataset variable.

Beginners Guide to Understanding Cross Join

Beginners Guide to Understanding Cross Join

In my previous Beginners Guide we talked about Types of joins i.e.

Inner Join, Left and Right Join but today we will be talking about cross join which happens to be more interesting to me than any other joins and happens to be helpful in real time for me.

What is a CROSS JOIN?

A CROSS JOIN is a JOIN operation that produces the Cartesian product of two tables.

Why CROSS JOIN?

Let’s say we have two table employee table “emp1” and department table “dept” now lets see how many records do we have in the emp1 table:

emp1
and also how many records do we have in the dept table:

dept

and for the emp1 table you want to “associate” the location column from the
dept table to all the records in the emp1 table so we use CROSS JOIN

but really why ?
In realtime Sometimes you might want to associate dates with a particular table and its records this happens to be helpful

Now we can do that with the Keyword CROSS JOIN as shown below.
cross

And there you go hope you will have a better understanding now.

-Sohail I.E

Beginner’s Guide to Crosstab in Business Intelligence Using Jaspersoft IReport

What is Cross Tab?

A Table is just of 2 Dimension but at times we do need our table to be represented in a 3 dimensional manner or in a Dynamic Manner.

Why?

Take for example a table with 2 data set i.e. in the row we have countries and in the column we have months of the year

Now at times our data set will have an extra data which needs to be represented in the table where then can we keep these column or even row?

So here Cross tab Helps in those situation to make the report more meaningful

Now we can have Rows as “Country” and a sub row “State” grouped by the country.

To get a better understanding I’ll be taking an Example to represent my data in-form of a cross-tab.

From the following

Tables :

  • sales_fact_1997 sf7
  • customer
  • time_by_day

Fields like country, state, month, store_sales and etc if required,
should be represented in Cross Tab.

 

Open Jasper-soft I Report

-> Click on File->New->Report->Select Any Template of your choice

->Remove the Bands you don’t require

in My case i just need the Title and Summary Band so i wont remove them

Connect to the required Tool/Database where your Query will be retrieved from

After connection

Enter your query by clicking on ->

Crosstab_blog

and then

Crosstab_blog_6

After entering your query select Read Fields and Click OK.

->Go to the palette section and Drag and Drop the cross tab to which ever band you want
(In my case “Summary” Band)

Now select Main Report Data-set and Click Next

Now In Your Row Group 1 select what do you want as the Group i.e. the 1st Dimension (in My Case Country)

Now in Row Group 2 select what do you want as the Group i.e. the 3rd Dimension which is a Sub-Group of the First Group (in My Case State_province)

As shown below:

Crosstab_blog_1

Then Click Next

Now in Your Column Group 1 Select what should be your Column Group which is your 2nd Dimension (month of year)

As shown below:

Crosstab_blog_2

Then click Next

Now in measure what will you like to calculate in your table? So you input in the Measure Field (in my case Store Sales)

And what function will you like to perform (in my case SUM of store sales)

As shown below

Crosstab_blog_5

Then click Next.

The Next Dialogue Box is of your Choice

Then click Finish

For any formatting select Cross-tab

Crosstab_blog_4

As shown above

And then perform your formatting techniques.

 

final output-> Crosstab_blog_3

By Sohail Izebhijie

Making a Simple Interactive Map in HDI (Helical Dashboard Insights)

 

Creating Interactive and Zoomable Map in HDI (Helical Dashboard Insight)

 
The Goal of this blog is how to make responsive, interactive and zoomable Map in HDI (Helical Dashboard Insights):

For creating the Map in HDI, we are using D3.js , a javascript library.

The Data to use :

A special geospatial file called a Topojson. Here we are going to use a file that is comprised of all US counties. If u go to this link and copy into text file and save it as “us.json” (or anything .json) .

Since we have a county map of USA, we will need some data that is broken down by county. The us.json file we are using only has counties drawn. For this tutorial we are using data from query in json format.

Whatever data you have, make sure that there is a column that associates the information to a naming or id standard that is also present in your map/topojson.

Integrating Map in HDI:

To start integration of Map we have to change four files in HDI.

1) EFW file: EFW contain the Title, author, description, Template name, visibility of the Dashboard.

2) HTML File:HTML file name should be the same that specified in the EFW file under the Template Section.

In HTML File On Top we specify links of the external link and CSS properties.
Here we are using the ‘topojson.js’ external Library and it specified in the HDI as below:

<script src="getExternalResource.html?=Map/topojson.js"></script>

And CSS used to create Map is as follows:

.states {
  fill: none;
  stroke: #fff;
  stroke-linejoin: round;
}
body {
 font-family: Arial, sans-serif;
}
.city {
 font: 10px sans-serif;
 font-weight: bold;
}
.legend {
 font-size: 12px;
}
div.tooltip {
 position: absolute;
 text-align: left;
 width: 150px;
 height: 25px;
 padding: 2px;
 font-size: 10px;
 background: #FFFFE0;
 border: 1px;
 border-radius: 8px;
 pointer-events: none;
}

We have to declare one Map component in “Map.html” file and in this component we need to provide the link of the file where the Map chart property is defined.

3) EFWD File: EFWD file contain the Data Source Connection Properties such as connection id and connection type.It also contain Url of the connection to the database, User name and Password to the Database.

The DataSource Details used in our demo is shown as below:-

<DataSources>
        <Connection id="1" type="sql.jdbc">
           <Driver>com.mysql.jdbc.Driver</Driver>
           <Url>jdbc:mysql://192.168.2.9:3306/sampledata</Url>
            <User>devuser</User>
            <Pass>devuser</Pass>
        </Connection>
    </DataSources>

Data Map contains Map id and connection and connection Type. Map id is same as that specified in the EFWVF.

Query for the Data Map and the Parameter to be used is specified in the Tags and Parameter in the Tags.

<DataMap id="2" connection="1" type="sql" >
       <Name>Query for Tooltip </Name>
		<Query>
			<![CDATA[
					SELECT  cl.ID as id,cl.Name as name,sum(fact.votes) as votes
					FROM
					Voting_Summary as fact,region as r,contest as ct,county_list as cl
					where
					fact.region_id=r.region_id and
					fact.contest_id=ct.contest_id and
                   		        cl.Name = r.county	
			]]>
              </Query>
</DataMap>

4)EFWVF File :-

In EFWVF file we first set the chart id the chart we set the chart properties. For Map, we set the Map Properties between the tag. The properties such as Chart name, Chart type, Chart Data Source.

“Path” refers to lines drawn as instructed by our topojson file (us.json). Notice that .legend and .tooltip refer to objects we’ll designate with our javascript, but we can still set what they’ll look like here in the CSS.

You’ll see a lot of “var=”, which is setting up our variables for the code. Note that the first of the variables affect what values map to what colors. See that changing up these variables is an easy way to change the appearance of this map (as well as the CSS).

Colors are coded by RGB HEX value . There are multiple ways to scale colors, but this is the one we’ll go with here.

In Script we set the Map as Below :

Setting the Map Size, Position. And translation.

var width = 960,
 height = 500,
centered; 

Setting up the view:

var projection = d3.geo.albersUsa()
    .scale(1070)   // If scale is specified, this sets the projection’s scale factor to the specified value.
    .translate([width / 2, height / 2]);

Defing Map and Legend Color :

var color_domain = [5000,10000, 15000, 20000, 25000, 30000, 35000, 40000, 45000, 50000, 55000, 60000]
 var ext_color_domain = [0, 5000,10000, 15000, 20000, 25000, 30000, 35000, 40000, 45000, 50000, 55000, 60000]
 var legend_labels = ["< 5000","10000", "15000+", "15000+", "20000+", "25000+", "30000+", "35000+", "40000+", "45000+", "50000+", "55000+", "60000+"]
 var color = d3.scale.threshold()
 .domain(color_domain)
 .range(["#CCE0FF","#B2D1FF","#99C2FF","#80B2FF","#66A3FF","#4D94FF","#3385FF","#1975FF","#005CE6","#0052CC","#0047B2","#003D99","#003380","#002966"]);

The follow portion of code creates a new geographic path generator


var path = d3.geo.path()
    .projection(projection);

The next block of code sets our svg window;

var svg = d3.select("#chart_4").append("svg")
.attr("viewBox", "0 0 " + width + " " + height)
 .style("margin", "10px auto");
var div = d3.select("#chart_4").append("div")
 .attr("class", "tooltip")
 .style("opacity", 0);
svg.append("rect")
    .attr("class", "background")
    .attr("viewBox", "0 0 " + width + " " + height)
    .on("click", clicked);

Since our data file contains the json data returned from query and this data is used to map the tooltip.

 var pairIdWithId = {};
 var pairNameWithId = {};
var pairVotesWithId = {};
 
data.forEach(function(d) {
 pairIdWithId[d.id] = +d.id;
 pairNameWithId[d.id] = d.name;
 pairVotesWithId[d.id] = d.votes; 
 });

here d.id ,d.name and d.votes refer to the column headers of our query.And now we’ll select the svg objects we’ve created but not specified, and map our data onto them:

var g = svg.append("g");
 g.append("g")
 .attr("class", "county")
 .selectAll("path")
 .data(topojson.feature(data1, data1.objects.counties).features)
 .enter().append("path")
 .attr("d", path)
 .on("click", clicked)
 .style ( "fill" , function (d) {
 return color (pairIdWithId[d.id]);
 })
.style("opacity", 0.8)

This will draw each county as an object, each with its own values. Notice that we’ve named this class of object “county”.

If we wanted to change the style of the counties in CSS up at the top, we could just refer to .county and make changes Also the “.data” line associates information from our us.json file with the county objects.

Also important is that “color” refers to the function set above in the code. “Color” expects a number as input, but instead of a specific number, we’re going to give it our container filled with pairs of ID numbers and rate values, and use [d.id] to make sure that we read in a value for each id number.The rest is what happens when the mouse glances over the county:


.on("mouseover", function(d) {
 d3.select(this).transition().duration(300).style("opacity", 1);
 div.transition().duration(300)
 .style("opacity", 1)
 div.text(pairNameWithId[d.id] + " : " + pairVotesWithId[d.id])
 .style("left", (d3.event.pageX) + "px")
 .style("top", (d3.event.pageY -30) + "px");
 })
 .on("mouseout", function() {
 d3.select(this)
 .transition().duration(300)
 .style("opacity", 0.8);
 div.transition().duration(300)
 .style("opacity", 0);
 })

If you want to change what each label is, make sure to adjust the variable “legend_labels.”


var legend = svg.selectAll("g.legend")
 .data(ext_color_domain)
 .enter().append("g")
 .attr("class", "legend");
 
var ls_w = 20, ls_h = 20;
legend.append("rect")
 .attr("x", 20)
 .attr("y", function(d, i){ return height - (i*ls_h) - 2*ls_h;})
 .attr("width", ls_w)
 .attr("height", ls_h)
 .style("fill", function(d, i) { return color(d); })
 .style("opacity", 0.8);
 
legend.append("text")
 .attr("x", 50)
 .attr("y", function(d, i){ return height - (i*ls_h) - ls_h - 4;})
 .text(function(d, i){ return legend_labels[i]; });

Function that gives Zoom Functionality to the map


function clicked(d) {
  var x, y, k;
  if (d && centered !== d) {
    var centroid = path.centroid(d);
    x = centroid[0];
    y = centroid[1];
    k = 4;
    centered = d;
  } else {
    x = width / 2;
    y = height / 2;
    k = 1;
    centered = null;
  }

  g.selectAll("path")
      .classed("active", centered && function(d) { return d === centered; });

  g.transition()
      .duration(750)
      .attr("transform", "translate(" + width / 2 + "," + height / 2 + ")scale(" + k + ")translate(" + -x + "," + -y + ")")
      .style("stroke-width", 1.5 / k + "px");
}

By following these we are able to see the output which is as follows:
D3 Map

-By
Nitin Uttarwar
Helical It Solution

Adding images to html in Jasper Reports

In my last blog post I discussed about Generating jasper reports in different formats using json file as a data source.You can find my last post here. In this blog article I will discuss about exporting the jasperPrint object in different formats like pdf, html, csv, xls and docx using the newer API of jasper reports.

Exporting the jasperPrint object which consists of images into html format is a tedious task with the jasper’s deprecated API found all over the internet. I have tried using the JRHtmlExporter class that consists of mostly deprecated methods and using it I couldn’t get the images in the jrxml in the html format.

So, I wanted to write a blog post to help my fellow programmers to illustrate how it can be done with the new API of jasper reports. HtmlExporter class is part of new API and using it one can export the report to html format.

To do this first we need to place the jasperPrint object in the http session using the following code.

request.getSession().setAttribute(ImageServlet.DEFAULT_JASPER_PRINT_SESSION_ATTRIBUTE, jasperPrint);

After placing the jasperPrint object in the session, we need to set the image handler for images in the report using the code,

exporterOutput.setImageHandler(new WebHtmlResourceHandler("image?image={0}"));

The images are served by ImageServlet which should be mapped to ‘/image’ in the web.xml. Here is the configuration that should be added to web.xml file.

 <servlet>
    <servlet-name>ImageServlet</servlet-name>
    <servlet-class>net.sf.jasperreports.j2ee.servlets.ImageServlet</servlet-class> </servlet>
 <servlet-mapping>
     <servlet-name>ImageServlet</servlet-name>
     <url-pattern>/image</url-pattern>
 </servlet-mapping>

The JasperCompileManager.compileReport(jrxmlSource) method compiles and generates a jasperReport object which is used to generate jasperPrint object using the JasperFillManager.fillReport(jasperReport, parameters, dataSource) method. In the following example the dataSource is populated using a string which is in json format. I am exporting the generated documents to the response. So, accordingly I have set content-type, content-disposition headers appropriately, which I have not shown in the code. The headers are set for all formats except for the type html as htmls are to be displayed in the browser along with images.

You can refer my previous blog post for maven dependencies. I have used commons-io dependency in addition to the previous dependencies.

		
        private static final Logger logger = LoggerFactory.getLogger(YOURCLASS.class);
	JRDataSource dataSource = getDataSource(jsonData);//pass jsonData to populate the dataSource
	JasperReport jasperReport = null;
	JasperPrint jasperPrint = null;
	//String type = Any of the types mentioned above
	//jrxmlSource is the the jrxml generated using the iReport
	
	Map<String, Object> parameters = new HashMap<String, Object>();
	//Add any parameters that are referenced in the jrxml to this map

	try {
		jasperReport = JasperCompileManager.compileReport(jRXMLSource);
		jasperPrint = JasperFillManager.fillReport(jasperReport, parameters, dataSource);
	} catch (JRException ex) {
		ex.printStackTrace();
	}

	if ("pdf".equals(type)) {
		JRPdfExporter exporter = new JRPdfExporter();
		try {
			exporter.setExporterInput(new SimpleExporterInput(jasperPrint));
                        exporter.setExporterOutput(new SimpleOutputStreamExporterOutput(response.getOutputStream()));
			exporter.exportReport();
		} catch (IOException e) {
			logger.error("IOException occured", e);
			e.printStackTrace();
		} catch (JRException e) {
			logger.error("JRException while exporting for pdf format", e);
			e.printStackTrace();
		}

	} else if ("xls".equals(type)) {

		JRXlsExporter exporter = new JRXlsExporter();
		try {
			exporter.setExporterInput(new SimpleExporterInput(jasperPrint));
   		        exporter.setExporterOutput(new SimpleOutputStreamExporterOutput(response.getOutputStream()));
			SimpleXlsReportConfiguration configuration = new SimpleXlsReportConfiguration();
			configuration.setOnePagePerSheet(true);
			exporter.setConfiguration(configuration);
			exporter.exportReport();
		} catch (JRException e) {
			logger.error("JRException while exporting for xls format", e);
			e.printStackTrace();
		} catch (IOException e) {
			logger.error("IOException occured", e);
			e.printStackTrace();
		}

	} else if ("csv".equals(type)) {
		JRCsvExporter exporter = new JRCsvExporter();
		try {
			exporter.setExporterInput(new SimpleExporterInput(jasperPrint));
			exporter.setExporterOutput(new SimpleWriterExporterOutput(response.getOutputStream()));
			exporter.exportReport();
		} catch (IOException e) {
			logger.error("IOException occured", e);
			e.printStackTrace();
		} catch (JRException e) {
			logger.error("JRException while exporting report csv format", e);
			e.printStackTrace();
		}
	} else if ("html".equals(type)) {
		request.getSession().setAttribute(ImageServlet.DEFAULT_JASPER_PRINT_SESSION_ATTRIBUTE,jasperPrint);

		HtmlExporter exporterHTML = new HtmlExporter();
		SimpleExporterInput exporterInput = new SimpleExporterInput(jasperPrint);
		exporterHTML.setExporterInput(exporterInput);

		SimpleHtmlExporterOutput exporterOutput;
		try {
			exporterOutput = new SimpleHtmlExporterOutput(response.getOutputStream());
			exporterOutput.setImageHandler(new WebHtmlResourceHandler("image?image={0}"));
			exporterHTML.setExporterOutput(exporterOutput);
			
		        SimpleHtmlReportConfiguration reportExportConfiguration = new SimpleHtmlReportConfiguration();
			reportExportConfiguration.setWhitePageBackground(false);
			reportExportConfiguration.setRemoveEmptySpaceBetweenRows(true);
			exporterHTML.setConfiguration(reportExportConfiguration);
			exporterHTML.exportReport();
		} catch (IOException e) {
			logger.error("IOException occured", e);
			e.printStackTrace();
		} catch (JRException e) {
			logger.error("JRException while exporting for html format", e);
			e.printStackTrace();
		}
	} else if ("docx".equals(type)) {
		JRDocxExporter exporter = new JRDocxExporter();

		try {
			exporter.setExporterInput(new SimpleExporterInput(jasperPrint));
		        exporter.setExporterOutput(new SimpleOutputStreamExporterOutput(response.getOutputStream()));
			exporter.exportReport();
		} catch (IOException e) {
			logger.error("IOException occured", e);
			e.printStackTrace();
		} catch (JRException e) {
			logger.error("JRException while exporting for docx format", e);
			e.printStackTrace();
		}
	}


	public JRDataSource getDataSource(String jsonData) {
		logger.info("jsonData = " + jsonData);
		JRDataSource dataSource = null;

		if ("null".equals(jsonData) || jsonData == null || "".equals(jsonData)) {
			logger.info("jsonData parameter value is null. Creating JREmptyDataSource");
			dataSource = new JREmptyDataSource();
			return dataSource;
		}

		InputStream jsonInputStream = null;
		try {
			// Convert the jsonData string to inputStream
			jsonInputStream = IOUtils.toInputStream(jsonData, "UTF-8");
			// selectExpression is based on the jsonData that your string contains
			dataSource = new JsonDataSource(jsonInputStream, "data");
		} catch (IOException ex) {
			logger.error("Couldn't covert string into inputStream", ex);
			ex.printStackTrace();
		} catch (JRException e) {
			logger.error("Couldn't create JsonDataSource", e);
			e.printStackTrace();
		}

		if (dataSource == null) {
			dataSource = new JREmptyDataSource();
			logger.info("dataSource is null. Request parameter jsondData is null");
		}

		return dataSource;
	}

Hope the above code helps to resolve the issue of getting the images in html format. The images can be placed in WEB-INF/classes directory if the directory is not mentioned in the jrxml. If the directory is mentioned then the path should supplied as a parameter which should be kept inside parameters map.

Wish you happy coding!!

Rajasekhar
Helical IT Solutions

Generating Jasper reports in different formats using a JSON file as a Data Source

This article helps the reader how to fill a report with data from a JSON data source ( Generating Jasper reports in different formats using a JSON file as a Data Source ). JSON stands for Java Script Object Notation. Suppose that we have a file in the following format. JSON File The above file consists of a JSON array. The above JSON array consists of five objects of type students separated by commas. The array notation uses []. The JRXML file consists of the following data to generate the report regarding the student details. This JRXML is generated using iReport 5.6.0.

Fields

The JRXML detail tag consists of the following tags. TextFields

Using the above JSON file data we can fill the JRXML and generate reports in different formats like PDF, HTML, XLS, CSV etc. The Jasper’s API converts the .jrxml into .jasper file and out of the .jasper file a jasperprint object can be made, which can be converted into various formats for printing purposes. Using Jasper’s API requires the following jars in your class path. Add the following dependencies to your pom.xml so that maven will automatically manage the dependencies for you. Dependencies

Dependencies-2

Java code to generate the different reports.

Code

The above code consists of an interface JRDataSource, which is implemented by JsonDataSource. The JsonDataSource’s constructor takes a file that consists of the data. For the sake of readability I have used another method which takes 4 arguments – one, the type of file to be generated; two, the JRXML file; three, the data source; four the destination of the file where the file has to be saved.

Code 2

The above printToFile method takes the data from the data source and fills it in the JRXML and generated the .jasper file which is actually the jasper print object. The jasper print object is used to generate different types of files based on the fileType argument. The above piece of code also demonstrates the use of the new API related to JRXlsExporter and JRCsvExporter, which are actually used to generate excel and csv files respectively.

Rajasekhar

Helical IT Solutions