How to increase MYSQL functions character lengths without changing validating SQL in Jasper

How to increase MYSQL functions character lengths without changing validating SQL in Jasper

Prerequisites::
Jasper Server 5.5,IReport
Database:: Foodmart

Scenario : I have two Tables for Table 1 i need to pass Input Control value column and for  Table2 i need to pass Input Control visible column

For Table 1 I have created a multiselect parameter(Customer Name) and passing the value column to query

1

For Table 2 I have created one more hidden cascading parameter(single select) based on above parameter and make it as mandatory(Customer Hidden)

2

Using hidden parameter we can pass as a single string to the Query using GROUP_CONTACT ,but here I have came across with a problem is maximum character length for GROUP_CONTACT is 1024 characters so I need to increase character length if we selected more than 1024 characters from Customer Name

First I have selected only 3 customers from Customer Name,so it showing 3 records

3

Now I am selecting all customers from Customer Name but Customer Hidden parameter is filtering data  upto “Adams Philp” customer only

4

To overcome the above issue we need to increase character length by placing SET SESSION group_concat_max_len = <number>; in Query this will work only when we will change validate.sql=false in jasper server and again we need to restart jasper sever

But without changing the validate.sql we can handle for that kindly follow this blog

Pass session value in Datasource URL  as follows
Here I am passing Session value as 4294967295 ,now it take length as 4294967295
                                       SET SESSION group_concat_max_len = 4294967295;

5

Now Data is filtering till Last Customer “Larkins John”

6

Thanks,
Satya Gopi,
BI Developer.

Python – Get data from CSV and create chart

Python – Get data from CSV and create chart


Hello everyone, this blog shows how we can import data from csv and plot as mesg grid chart in python libraries, through the help of Pycharm IDE (Community Version)


Prerequisite :



The following are the steps for creating mesh-grid in pycharm :
1. Installing NumPy,Pandas and Matplotlib libraries in Pycharm IDE :

  • 1. Create a scratch file in Pycharm.
  • 2. Click on “File” and goto settings.
  • 3. Goto “Project Interpreter” and click on ‘+’ symbol to add new libraries.
  • NumPy

2. Writing python code for importing csv data and creating meshgrid :

  • 1. Create a csv file containing these data “-5,5,0.01”.
  • 2. Use this python code in your scratch file .
  • import csv
    import numpy as np
    import matplotlib.pyplot as plt

    point = ""
    with open('C:/Users/Helical/Desktop/Blog/Python/MeshGrid.csv','r') as csvfile:
    meshreader = csv.reader(csvfile, delimiter=',')
    for row in meshreader:
    point = point + ', '.join(row)
    print(point)
    meshpoints = point.split(',')
    points = np.arange(float(meshpoints[0]),float(meshpoints[1]),float(meshpoints[2]))
    dx, dy = np.meshgrid(points,points)
    z = (np.sin(dx)+np.sin(dy))
    plt.imshow(z)
    plt.colorbar()
    plt.title('plot for sin(x)+sin(y)')
    plt.show()

  • If you are able to sucessfully run the code, then you will generate mesh grid in an new Pycharm window.
  • GraphMatplotLib

Loops in Pentaho Data Integration

Loops in PDI

 

If only there was a Loop Component in PDI *sigh*.

But we can achieve Looping Easily with the Help of few PDI Components. Let’s take a requirement of having to send mails. Now if the mails id and files to be sent where fixed we could just use a property file and send the mail id’s as a parameter.

but here mail ids will be coming in from a Table source and then mail id’s can never be the same.
So how did i approach this?

let’s say mail id’s are from Table: mail_address so now my aim here is to first add a row number to each record line and a Maxcount of all the records at each line, *Keep In Mind of the Maxcount*

and then pass it as a filter clause

where row_number = 1

now my assumption is the query should execute and return one row and then the column values like sender mail, receiver mail will be set into a variable and then passed to a Mail Transformation Component;

But how can we increase the value of the Where clause i.e how can we make

where row_number = 1 to

where row_number = 2

where row_number = 3 and so on.

 

So here is were Looping is required tp we take a variable and increment the value.

resultop0
1. Take a Set Variable at the Job Level [Initialize Loop] and then set a variable loop and assign the value to your initial value as shown below: In My case loop value = 1

resultop

 

Now next take a Transformation to get the variables and set the variables as shown below:

resultop1Now in my scenario I have a Modified JS because few conversions were required hence after that set the variable.

Next We need Transformation where we pass our variable of loop into our Query
as shown below

resultop3

 

SELECT * FROM (
SELECT *,ROW_NUMBER() OVER (Order by createfolderpath) as row_num,COUNT(dummy)           OVER (partition by dummy) as maxcount FROM (SELECT * FROM
(
SELECT DISTINCT replace(createfolderpath ,’/’,’\’) as createfolderpath,1 as dummy
FROM reportpaths
)base
)base2
Cross JOIN [TEST_SLSDMBKP].[dbo].[job_mailing_address]
WHERE
scheduled_flag = ‘Y’
)base3
WHERE
row_num = ${loop}
ORDER BY row_num,receivers_email,createfolderpath

Now here in my table input, 1 row will be the output so hence I pass the required columns into the Modified JS and then Set them as Variables.

resultop5

Next at our Job Level we use a JavaScript Component to increment the loop value to 2 as shown above.

Next at our Job Level we add a new transformation which does our requirement of Sending Mails as shown below:

resultop6

And we pass all the required variables into the Mail Component as shown below:resultop8

RECEVIERSEMAIL and all are values from our DB we Set as Variables.

After that we go to our JOB Level and we use a Simple Evaluation Component

To stop the Loop after a particular condition

resultop10

Now ${MAXCNT} is the  Maxcount of all the records at each line from our Query we sent as variable

Now once loop number is 6 and Maxcount is  5 then it stops the loop and ends else

It continues and goes to the next Component which is Wait Component which in a previous blog it was specified the importance of a WAIT Component.

So that’s it we went through looping in PDI.

Hence if we have any other approach, please feel free to drop it in the comment below

Thanks
Sohail Izebhijie

How to install NTP Client on Ubuntu 16.04

How to install NTP Client on Ubuntu 16.04

To install NTP Client on Ubuntu 16.04 follow below steps :

Note :To install NTP Client it have some dependency ,we need to install libopts25 package before installing NTP Client.

1.Download or use below attached libopts25 package(.deb) which supports Ubuntu 16.04
 
libopts25_5.18.10-4_amd64
 

2.Keep package file at specific location and execute below command to install ibopts25 package
 
sudo dpkg -i libopts25_5.18.10-4_amd64.deb
 
The package will automatically get installed.

> You can check whether package is installed or not using below command :
 
dpkg –get-selections | grep libopts25
 
3.Next download or use below attached ntpdate(ntp client) .deb package to install NTP Client.
 
ntpdate_4.2.8p4+dfsg-3ubuntu5.3_amd64

 
sudo dpkg -i ntpdate_4.2.8p4+dfsg-3ubuntu5.3_amd64.deb
 
The package will automatically get installed.
> You can check whether package is installed or not using below command :
 
dpkg –get-selections | grep ntpdate
 
4. Now we need to configure the NTP client to which NTP server NTP Client can access , goto server at location (/etc/default/) where we installed NTP client.
 
5.Open the file “ntpdate” present at location “/etc/default/” and add the ntp server which you are using mentioned below :
 
Server ntp-server-host-name
 
For Example : Server 172.20.1.200
 
6.Save file and check whether you are able to connect to NTP Server or not using below command :
 
sudo ntpdate ntp-server-name
 
For Example : sudo ntpdate 172.20.1.200

 
To Install NTP-Server on Ubuntu Server.
 
1.Download NTP-server from internet
 
ntp_4.2.8p4+dfsg-3ubuntu5.3_amd64.deb
 
2.Upload the downloaded file on ubuntu server.
 
3.Execute the below command to install the downloaded package
 
sudo dpkg -i ntp_4.2.8p4+dfsg-3ubuntu5.3_amd64.deb
 
4.Once the installation will complete, we can check the NTP daemon is running or not by using below command
 
ps -aef | grep ntpd
 
Hope it helps 🙂

 
Thanks ,
Sayali | Helical IT Solutions

Tabbed Dashboard using C3 library

Tab Pane in Dashboard containing charts using C3 library.

In this blog I’ll discuss how we can create tab menu in the dashboards  if one need html reports in the web pages. On click of these tab menus the report will change in the body section of the page.

Overview : For creating different charts you can use http://c3js.org/ as its free and create any chart as per your requirement. Here in this example I have used Bar chart and pie chart in my dashboard. And the individual charts will load on click of the individual tab buttons.

1                                                                                                                                                  Fig 1.1

In the picture shown above, ‘marks’ and ‘diversity’ are the two tab buttons and our objective is to load the different charts on click of each of the tab buttons.

Steps:
1. Create an html page.
2. In the body of the page use the code as written below:

2

3. In the script tag of the page use following code:

34. Used the C3 library to create the bar chart and pie chart. Similarly you can use any chart as per requirement.

5

4

Bind the chart to render  on different div ids.
5. Final output will be something like shown in fig 1.1.
Refer the attached folder : Tab dashboard

Recursive With Query in Postgres

Recursive with Queries in Postgres

Sometimes we might have a requirement for certain hierarchy relation in data.
This can be done with the help of Recursive WITH Queries(Recursive CTE) in Postgres.

Consider the example –
Location is stored in table location_hierarchy.Query output will have all the children of ‘India’ along with its depth relative to “India”. The result should look somewhat similar to this:

Sample Data:

location data

In such cases, Self JOIN query will fail since the depth here is arbitrary and may change in future.

For this, we use a special query called WITH RECURSIVE. It is also called as PostgreSQL hierarchical query since it is usually used to query on hierarchical data, like the one which we are discussing. The WITH RECURSIVE actually is an extension of WITH query which is referred to as Common Table Expressions(CTE) in PostgreSQL. WITH query can be seen as forming a temporary table(s) which has a scope for a single query or as a named sub-query.

Query :
WITH RECURSIVE children AS (
SELECT child, 1 AS depth ---|Non
FROM locatio_hierarchy --|Recursive
WHERE parent = 'India' ---|Part

UNION ALL

SELECT a.child, depth+1 ---|Recursive
FROM locatio_hierarchy a --|Part
JOIN children b ON(a.parent = b.child) ---|
)
SELECT * FROM children

Output:

Query Output

Regards
Prasad Kshirsagar

 

 

Export Jasper report into Specific formats using Visualize.js in JasperSoft

Export Jasper report into Specific formats using Visualize.js in JasperSoft

Pre-requisites: Used Notepad++ to create html file and jasper server enterprise 6.3 version.

In jasper server for a particular jasper report we have specific options to export the report. When user wants to embed the specific jasper report in their application and if they have requirements to add export options, Then please follow below steps to create the same i.e export the jasper report in different formats.

In this bolg, I will explain how to export the Jasper report into different formats like pdf,excel using visualize.js and html file.

Step 1: Create a HTML file and add dropdown button for export options

<ul class="dropdown-menu">
<li><button id="pdf" class="export" value="pdf">PDF</button></li>
<li><button id="xls" class="export" value="xls">Excel</button></li>
<li><button id="csv" class="export" value="csv">CSV</button></li>
</ul>

I have created a dropdown list contains three buttons. In this case i have used pdf, export and csv options

Step2: Create a div container to render the report

<div id="container"></div>

Step3: Add visualize.js for the jasper server instance in html file and add all required css and js files.

<script src="http://code.jquery.com/jquery-2.1.0.js"></script>
<script src="http://localhost:8083/jasperserver-pro/client/visualize.js"></script>

Step4: Create visualize object, authenticate using user credentials and render the particular report in container div.
In this case i have sample report in jasper server public repository

visualize({
auth: {
name: "username",
password: "password"
}
}, function (v) {

//render report from provided resource
report = v.report({
container : "#container",
resource: "/public/Samples/Reports/ProfitDetailReport",
error: function(err){
alert(err.message);
}
});

Step5: Create export function and add the below code
//exporitng report into specific format

$(".export").click(function () {
var formatType = this.id;
report.export({
//export options here
outputFormat: formatType,
}, function (link) {
var url = link.href ? link.href : link;
window.location.href = url;
}, function (error) {
console.log(error);
});
});

Explanation:
$(“.export”) –> I have created a class name export for all three export options. So whenever user clicked on eithe of these three buttons this function will call.
var formatType = this.id –> Each of the export options has a unique id and this will return the particular id when the user clicked on export options
for example if user clicked on “excel” button, id will return ‘xls’ value
Create a report.export object and the specific outputFormat
Step6: Run the html and try to export the report using different options

Regards
Vdugula Ramu
BI Developer

 

Get historical Twitter data using Twitter4j libraries in java

Get historical Twitter data using Twitter4j libraries in java

Pre-requistes :
1. Valid twitter account
2. Some knowledge of core Java.
3. Any IDE for running Java programs and knowledge of using them. (Eclipse or InteliJ)
This blog covers the following steps for getting twitter historical data :
1. Create app in twitter.
2. Download Twitter4j libraries (Jar files).
3. Write a program in Java to get historical twitter data using particular query string.

Steps are as follows :

    • Step 1: Creating App in twitter :
      1. Visit the twitter developer’s site. (https://dev.twitter.com/web/sign-in)
      2. Sign-in with twitter account.
      3. Go to “My Applications” area and click on “Create a new application”.
      4. Fill in the application details. You can ignore the “Callback URL” field but you have to provide your website URL.
      5. After you have filled the form and submitted it, you will be directed to a page which has option to generate tokens.
      6. Click on “Create Your Access Tokens” button and choose your preferences.
      7. Make a note of/copy the following values :
      i.Consumer Key
      ii.Consumer Secret
      iii.OAuth Access Token
      iv.OAuth Access Token Secret

Step 2: Downloading “Twitter4J” libraries :
1. Download twitter4j libraries from this site http://twitter4j.org/archive/twitter4j-4.0.4.zip
2. Extract the downloaded zip file in your PC.

Step 3: Write Java program for fetching historical data from twitter using Twitter4J libraries.

1. Create a project in your IDE, and include all the jar files you have in the twitter4J zip file.

2. Java Code  :

Main Class : TwitterMain

package com.helical.twitter;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.HashMap;

import twitter4j.Twitter;

public class TwitterMain {
	private static String AccessToken = "8xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxo";
	private static String AccessSecret = "sxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxC";
	private static String ConsumerKey = "Uxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
	private static String ConsumerSecret = "rxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";

	private static HashMap authTokenMap = new HashMap();

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		authTokenMap.put("AccessToken", AccessToken);
		authTokenMap.put("AccessSecret", AccessSecret);
		authTokenMap.put("ConsumerKey", ConsumerKey);
		authTokenMap.put("ConsumerSecret", ConsumerSecret);

		TwitterAuth twitterAuth = new TwitterAuth();
		TwitterData twitterData = new TwitterData();
		Twitter authObject = null;
		
		String topic = args[0];
		ArrayList tweets = new ArrayList();
		try {
			authObject = twitterAuth.authenticate(authTokenMap);
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println(e.getMessage());
		}
		try {
			tweets = twitterData.getTwitterData(authObject, topic);
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println(e.getMessage());
		}
		System.out.println("Tweets related to " + topic + " : " + tweets);

	}

}

Authentication class : TwitterAuth

package com.helical.twitter;

import java.util.HashMap;

import twitter4j.Twitter;
import twitter4j.TwitterFactory;
import twitter4j.conf.ConfigurationBuilder;

public class TwitterAuth {
	public static Twitter authenticate(HashMap<String, String> authTokenMap) {

		ConfigurationBuilder cb = new ConfigurationBuilder();
		cb.setDebugEnabled(true);
		cb.setJSONStoreEnabled(true);
		cb.setOAuthConsumerKey(authTokenMap.get("ConsumerKey"));
		cb.setOAuthConsumerSecret(authTokenMap.get("ConsumerSecret"));
		cb.setOAuthAccessToken(authTokenMap.get("AccessToken"));
		cb.setOAuthAccessTokenSecret(authTokenMap.get("AccessSecret"));
		cb.setHttpConnectionTimeout(100000);

		Twitter twitter = new TwitterFactory(cb.build()).getInstance();
		return twitter;

	}

}

Fetch twitter data class : TwitterData

package com.helical.twitter;

import java.util.ArrayList;
import java.util.List;

import twitter4j.Query;
import twitter4j.QueryResult;
import twitter4j.Status;
import twitter4j.Twitter;

public class TwitterData {

	public static ArrayList getTwitterData(Twitter authObject, String topic) {
		System.out.println("Inside tweet data method: "+authObject + " , "+topic);
		ArrayList tweetList = new ArrayList();
		try {
			Query query = new Query(topic);
			QueryResult result = null;
			do {
				result = authObject.search(query);
				List tweets = result.getTweets();
				for (Status tweet : tweets) {
					tweetList.add(tweet.getText());
				}
			} while (result.hasNext() == true);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("Failed to search tweets: " + e.getMessage());
		}
		System.out.println(tweetList);
		return tweetList;
	}
}

3. The program takes the search topic in the form of command line argument.After providing command line argument, this code will use twitter4J methods to fetch data related to the topic entered, and give output in JSON format.

Feel free to play with the code and please comment if you find any issues with the code.
So, that’s it.. go knock your self off…!!!!

Jasper server upgrade steps from CP 5.x to EE/Pro 6.x

Jasper server upgrade steps from CP 5.x to EE/Pro 6.x

Here we will see how to upgrade Jasper Server Community 5.6 to Jasper Server Enterprise 6.2

Prerequisites::
Jasper Server 5.6 Community,Java 1.8,Jasper Server 6.2 binary.zip,Linux Environment,PostgreSQL

Step 1:
First Take Backup of Jasper server repository folder of Jasper Server 5.6
1

It should be present inside <Tomcat_Home>/webapps
Backup of Jasper server DB in PostgreSQL
2

Step 2:

  • Unpack zip file (jasperreports-server-6.0-bin.zip)
  • Copy postgresql_master.properties from
    <js-install-cp>/buildomatic/sample_conf/postgresql_master.properties  and paste to <js-install-cp>/buildomatic
  • Rename the file to default_master.properties
  • Edit default_master.properties
    appServerType = tomcat7
    appServerDir = /opt/jasperreports-server-cp-5.6.0/apache-tomcat //here we should provide tomcat instalation path
    dbHost=localhost
    dbUsername=postgres
    dbPassword=postgres
    dbPort=5433
    webAppNamePro = jasperserver-pro // if we are upgrading to Enterprise version

Step 3:
Take backup of whole repository from Jasper Sever(js-cp-export.zip)3

Alternative through command prompt
1.Navigate to the buildomatic directory
                                    cd <js-install-cp>/buildomatic
2.Run buildomatic with the export target
                                   ./js-ant export-everything-ce -DexportFile=js-cp-export.zip

Step 4:
 Stop Application server (Tomcat)
Start Database server (Postgres)

Step 5:
 Run the following commands
Change path to <js-install-cp>/buildomatic

        jsant dropjsdb  // it will delete your jasperserver db
        jsant createjsdb  //recreate your jasperserver db
        jsant initjsdbpro  // initialize the database.
        jsant importminimalpro //Adds superuser, Themes, and default tenant structure
        js-ant import-upgrade -DimportFile=<path>/js-cp-export.zip -DimportArgs=”–include-server-settings” //The -DimportFile argument should point to the js-cp-export.zip file you created earlier.
       jsant importsampledataupgradepro // This step is optional.Loads the 6.4 commercial sample data.
       jsant deploywebappcptopro // Delete the CP war file, and deploy the commercial (pro) war file.

Step 6:
 Set up the JasperReports Server License.
Copy the <js-install-cp>/jasperserver.license file to the /root

References –
https://community.jaspersoft.com/documentation/tibco-jasperreports-server-upgrade-guide/v60/upgrading-community-project

Thanks,
Satya Gopi Parisa,
BI Developer.

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.