Adding ‘All’ in JasperReport input control query and passing 'All' as parameter

In this post you will see how to append ‘All’ keyword in JasperReport input control query and passing 'All' as parameter.

Now the question is where we can use this trick in report, I saw a question in forum where user wanted to append ‘All’ with other input control values so user can also select ‘All’ to select all the values of that input control instead of selecting manually  all the values.

In the case of multiselect parameter of Collection type (java.util.Collection)  you do not need to give "All" to select all the values in iReport as default value for that parameter by default it means all the values are selected.
But if the parameter is single select of String type then the query for input control will be: -

SELECT *

  FROM (SELECT 'All Country' SHIPCOUNTRY FROM orders

        UNION

        SELECT DISTINCT SHIPCOUNTRY FROM orders) b

ORDER BY SHIPCOUNTRY

iReport query will be : - 

SELECT SHIPCOUNTRY,SHIPCITY

FROM orders

WHERE ($P{p_shipcountry}='All' OR SHIPCOUNTRY=$P{p_shipcountry})

Where p_shipcountry is a parameter in iReport and it is single select of String (java.lang.String) type parameter.

Defining a variable in SQL SELECT statement in iReport

In this blog you will see how to define a variable in SQL SELECT statement and use that variable to do the calculations for other computed values.

Defining variables in MySQL or in other database is easy but defining variable in iReport is totally different.

I show you both way how to define variable in MySQL SELECT statement and in iReport.

Defining variable in MySQL: -

SET @tsum := 0;

SELECT SUM(@tsum := @tsum + ProfitTotal) as Cumulative,

       SUM(profit) as ProfitTotal

FROM table_name

Defining a variable in iReport : -

SELECT SUM(@tsum := @tsum + ProfitTotal) as Cumulative

FROM

    (SELECT  profitTotal, @tsum :=0

     FROM table_name ) a

As you can see first I have defined the variable in inner select statement then you can sum up in the outer select statement.

http://stackoverflow.com/questions/23731737/dynamic-variable-value-jasper-report/23742241#23742241

2. What is Hadoop ?

Apache Hadoop is a framework written in Java for running applications on large cluster built of commodity hardware.

The Hadoop framework transparently provides both reliability and data motion to sapplications. Hadoop implements a computational paradigm named MapReduce, where the application is divided into many small fragments of work, each of which can execute or re-execute on any node in the cluster. In addition, it provides a distributed file system that stores data on the compute nodes, providing very high aggregate bandwidth across the cluster.

All the modules in Hadoop are designed with a fundamental assumption that hardware failures (of individual machines or racks of machines) are common and thus should be automatically handled in software by the framework. Apache Hadoop's MapReduce and HDFS components originally derived respectively from Google's MapReduce and Google File System (GFS) papers.

Hadoop was created by Doug Cutting and Mike Cafarella in 2005. Cutting, who was working at Yahoo at the time, named it after his son's toy elephant. It was originally developed to support distribution for the Nutch search engine project.

1. What is Big Data ?

Big data is the term for a collection of data sets so large and complex that it becomes difficult to process using on-hand database management tools or traditional data processing applications. The challenges include capture, curation, storage, search, sharing, transfer, analysis, and visualization.

Every day, we create 2.5 quintillion bytes of data — so much that 90% of the data in the world today has been created in the last two years alone.
 According to IBM, 80% of data captured today is unstructured, from sensors used to gather climate information, posts to social media sites, digital pictures and videos, purchase transaction records, and cell phone GPS signals, to name a few. All of this unstructured data is Big Data.

When dealing with larger data sets, organizations face difficulties in being able to create, manipulate, and manage big data. Big data is particularly a problem in business analytics because standard tools and procedures are not designed to search and analyze massive datasets.

As per Gartner : "Big data is high volume, high velocity, and/or high variety information assets that require new forms of processing to enable enhanced decision making, insight discovery and process optimization.

Big data spans three dimensions: Volume, Velocity and Variety.

Volume (amount of data): Enterprises are awash with ever-growing data of all types, easily amassing terabytes—even petabytes—of information.

• Turn 12 terabytes of Tweets created each day into improved product sentiment analysis
• Convert 350 billion annual meter readings to better predict power consumption

Velocity (speed of data in and out): Sometimes 2 minutes are too late. For time-sensitive processes such as catching fraud, big data must be used as it streams into your enterprise in order to maximize its value.

• Scrutinize 5 million trade events created each day to identify potential fraud
• Analyse 500 million daily call detail records in real-time to predict customer churn faster

Variety (range of data types and sources): Big data is any type of data - structured and unstructured data such as text, sensor data, audio, video, click streams, log files and more. New insights are found when analyzing these data types together.

• Monitor 100’s of live video feeds from surveillance cameras to target points of interest
• Exploit the 80% data growth in images, video and documents to improve customer satisfaction

How to develop a Tabular Report using iReport

In the previous post we saw how to develop a simple bar chart with optional where clause now will see how to develop a tabular report for the same set of records so user can also see the data when he clicks on the chart. 
 

Create a report with three input controls, p_year (java.lang.Integer), p_shipcountry(java.util.Collection) and p_shipcity(java.util.Collection).

Where p_year is a single select input control to select year means user can select only one year at a time, p_shipcountry is a Collection type input control, which is multiselect cascading input control means user can select multiple ship countries for the selected year and p_shipcity is also a Collection type multiselect cascading input control.

Query for the report:-

SELECT  YEAR(ORDERDATE) year,

                 MONTHNAME(ORDERDATE) month,

                  CONCAT(MONTHNAME(ORDERDATE), RIGHT(year(ORDERDATE), 2)) monthyear,

                  COUNT(*) orders

FROM orders

WHERE (YEAR(ORDERDATE) = $P{p_year} OR $P{p_year} IS NULL)

               AND $X{IN,shipcountry,p_shipcountry}

               AND $X{IN, shipcity,p_shipcity}

GROUP BY YEAR(ORDERDATE), MONTH(ORDERDATE)

ORDER BY YEAR(ORDERDATE), MONTH(ORDERDATE)

For Tabular report, we have to create a sub dataset. To add sub dataset follow these steps : -

1 - Go to Windows and select Report Inspector.

2- Right click on the report name and click on “Add Dataset” and select option “Create an empty dataset” and then finish, edit the dataset and paste the above query and add all the parameters and click on the Read Fields button.

Once sub dataset is ready, go to Window, select Palette, and then drag Tabular Component to the Summary or Title Band (Do not use Detail band for any chart and Tabular components)

Select The Dataset name that you just created for the report

Move the field in the same order in which you want in the report.

Use alternated detail rows background option to get the alternate row color.

You can also select the “Add Column Footer” option if you want the total by column.

 Edit the sub datsource and pass all the parameters.

 

Click on the Preview button.

You can find the jrxml file here which i used for this post.
 Tabular report

Useful links:-

http://stackoverflow.com/questions/19401825/optional-where-clause-jasper-reports

http://stackoverflow.com/questions/11082379/jasperserver-null-values-for-input-controls?lq=1