Dates in Jasper iReport – Expression for default values

This post teach you how to play with default date parameters & calculations on dates in jasper iReport.
In this tutorial you learn how to make use of  “SimpleDateFormat” class and java.util.Calendar class.

I) Let us start with Report Start(Start Date) and Report End(Report End) parameters.

In some reports you need to give default parameters in your start date and end date.
Eg:
Start Date = Previous month 1st day
End Date= Previous month last day
Qn :
 How to write default expression for this ?
Ans:
NOTE: 
$P{cal}  is a parameter used to reduce the expression complexity.
$P{cal}=java.util.Calendar.getInstance()
Start Date: ( java.util.Date)
(
$P{cal}.add(java.util.Calendar.MONTH, -1) ||
$P{cal}.set(java.util.Calendar.DAY_OF_MONTH, 1)
)
? null : $P{cal}.getTime()
 End Date(java.util.Date)
(
$P{cal}.set(java.util.Calendar.DAY_OF_MONTH, $P{cal}.getActualMaximum(java.util.Calendar.DAY_OF_MONTH))
)
? null : $P{cal}.getTime()
Sample output(i.e., default values in the date place holders would be)
Star Date :  2013-10-01
End Date : 2013-10-31

II) To get the individuals of a date using SimpleDateFormat class.

From Report Start  Date($P{ReportStart})
StartYYYY
Integer.parseInt(new SimpleDateFormat(“yyyy”).format($P{ReportStart}))
StartMM
Integer.parseInt(new SimpleDateFormat(“MM”).format($P{ReportStart}))
StartDD
Integer.parseInt(new SimpleDateFormat(“dd”).format($P{ReportStart})) 
Similarly you can extract the parts of the date for ReportEnd parameter($P{ReportEnd} )

III) To get the current parts of the date from Calendar class in iReport

CurrentYear:
$P{cal}.get(Calendar.Year)
CurrentMonth:
$P{cal}.get(Calendar.MONTH)+1
CurrentDayOfMonth:
$P{cal}.get(Calendar.DAY_OF_MONTH)

start_date and end_date parameters – giving default values – iReport

Hi Guys,

This post let’s you know how to use date parameters in SQL query using between operator and using  multi select parameter.

Example Query:

SELECT
column1, colum2
FROM
XYZ_table
WHERE
$X{[BETWEEN], date_field_name_from_table, start_date, end_date}

In this way you can use between operator for start_date and end_date parameters with multi select.
Note that multiselect generally selects the input controls at a time.

Default values for start_date and end_date:
Problem statement:
Find the problem statement in this post.
http://community.jaspersoft.com/questions/819583/default-values-date-parameters

The problem statement which I faced is :
If today is 2013-10-07 then startdate is : 2013-09-16 enddate is : 2013-09-31
if today is : 2013-10-23 then startdate is : 2013-10-01 enddate is: 2013-10-15
The same logic should applicable in january month as well(i.e, for instance if today is : 2013-01-13 then startdate: 2012-12-16 enddate:2012-12-31)


Apart from the solution that given in the community, also find below solution

start_date Default Value Expression:

Syntax followed : ternary expression:  condition?True:False

(
$P{cal}.get(java.util.Calendar.DAY_OF_MONTH)>15 ?
$P{cal}.set(java.util.Calendar.DAY_OF_MONTH, 1) :
($P{cal}.add(java.util.Calendar.MONTH, -1) ||
$P{cal}.set(java.util.Calendar.DAY_OF_MONTH, 16))
)
? null : $P{cal}.getTime()

end_date Default Value Expression:

(
$P{cal}.get(java.util.Calendar.DAY_OF_MONTH) == 1 ?
$P{cal}.set(java.util.Calendar.DAY_OF_MONTH, 15):
$P{cal}.set(java.util.Calendar.DAY_OF_MONTH, $P{cal}.getActualMaximum(java.util.Calendar.DAY_OF_MONTH))
)
? null : $P{cal}.getTime()


In the above two start_date and end_date parameters you can find $P{cal} – which is a parameter created to get the calender dates (in simple words java calender instance)

You must create this($P{cal}) parameter and have to give default value expression before you use this parameter in start_date and end_date parameters.

Default Value Expression for $P{cal} parameters is : java.util.Calendar.getInstance()

NOTE:
for all the input controls Parameters classs is : java.util.Calendar

Sadakar
(Learning never exhausts the mind)

Internationalization of jasper reports – Hello world example

Link

Hi guys.. small work out but very use full..

Generally customers want to see data in their own language.

This example is developed using iReport 5.1.0 pro, Jasper server pro and foodmart db with postgresql.

Sources/References :

Example : Converting English to Spanish

1) From Jasper Community

 https://www.jaspersoft.com/jasperserver-and-ireport-internationalization#int_reports

2) From google translator

http://translate.google.co.in/#auto/pt/BRAZILIAN%20STATE%20X%20SIGN%20UPS

 

google_translator 3) Locale Codes 

 http://download1.parallels.com/Plesk/Plesk8.2/Doc/plesk-8.2-win-l10n-guide/39382.htm

4) http://www.tutorialspoint.com/jasper_reports/jasper_Internationalization.htm

Steps:

1) File->New ->Save report  to your fav directory.

2) Write a simple query and drag a field to title band( Not necessary to do but iReport requires some query and field otherwise it’ll show empty report).

3)Take Text field(Not static text) and write the following.

$R{localization.text1}

NOTE: $R is the special syntax used for internationalization of text in iReport.

4) Right click on report Name -> Select properties -> Give Resource bundle Name

Ex: localizationdemo

This is resource bundle name , do not give extension for this.

in_report

 

5) Publish your report to Jasper Server and in resources section add the bundle files.

You need to add two files..

One file contains general English text(USA) another consists internationalized text

In this example second file having spanish(B’z you are converting English to spanish)

 Right click on Resources -> Add -> Resource Bundle  id ,Name and browse for the files

You must add two files.

In this example:

File 1 ID & Name : localizationdemo.properties  (You can give any of your name , extension is not mandatory). Write this text in file

localization.text1=hello world

File2 ID & Name : localizationdemo_es.properties and add the file from your local machine. Write this text in file

localization.text1=hola mundo

 in_repository

In jasper server Change locale to es-Spanish

login_page_es_SpanishOUTPUT

output Meet us @

http://helicaltech.com/

 


 

 

Tech Tips in Penaho Server – publishing password setting for server – Disable the folder names in Browser panel in the Server

Hi guys..

The below tips are very useful in Pentaho Server for BI developers.
1) publishing password setting for server
You need to publish the reports or schema(s)[developed in PSW] once you done with developing. For this you need set the publishing password for Pentaho server.
Go to this location 
<location of Pentaho>biserver-ce-4.8.0-stable\biserver-ce\pentaho-solutions\system\publisher-config.xml and edit the “publisher-config.xml”  file.

Give your publish password b/w  these tags  <publisher-password> </publisher-password>

Example:
<publisher-password>password</publisher-password>    ( password can be any name that you wish to give)

Snapshot is a worth more than text. find the image below.

Save the file. stop the server and start the server.

2) Disable the folder names in Browser panel of Pentaho Server
* What ever the folder you create in Browser panel of pentaho server, it directly stores   under pentaho-soulutions
i.,e in this location
<Pentaho location>Pentaho\biserver-ce-4.8.0-stable\biserver-ce\pentaho-solutions
* You can disable all of your folders that comes with installation & the later created ones  which you don’t want to appear in the browser panel.
*   Inside every folder(pentaho default or the folders created by you) you can find “index.xml” file. You need to edit this file. You need to change visable tag as “false”.
Find the sanpshot.

You need not restart the server. Just you need to refresh the browser panel in the server.
You can observer the invisibility of the folder.

Sadakar Pochampalli.

 

 

Pentaho Community Dashboard (CDE) Basics – Creating a Simple Dashboard

Pentaho Community Dashboard Editor (CDE)
CTools Website – Community Tools

http://www.webdetails.pt/ctools.html

What is CDE ?
* CDE is one of the plugins for Pentaho BI Server, contributed and maintained by     Pentaho Partner webdetails.
* We create dashboards using this tool.
* Community Dashboard Editor (CDE) was born to simplify the creation, edition and      rendering processes of the CTools Dashboards.
* CDE is a very powerful and complete tool, combining front end with data sources and custom components in a seamless way.

Where you can get the plug-in in Pentaho Community Server ?
How to download the plug-in & what are the dependencies?

You can find the symbol “Pentaho Market place” on the menu bar..
Click on it you can see the Pentaho Marketplace editor as shown in below figure.
Find Community Dashboard Editor and Click on install.
As I have already installed it is showing as Up to Date.
Its generally a good practice to keep on updating the plugins with the latest version available in the market.
NOTE:
You must install the dependent plug-ins before you start working with CDE.

The dependencies for CDE are:
CDF(Community Dashboard Framework)
CDA(Community Data Access)
You must restart your server to take the effect of installation.

Where you can find the sample examples after installing?
* In the left side panel you can find plugin-samples folder.
* Expand it and then click on CDE
* In the Files you can find sample example CDE Sample Dashboard.
* Right Click on CDE_Sample Dashboard àThen click on Edit.
CDE has 3 major components
They are.
* Layout
* Componets
* Data Sources.CDE has developed based on MVC-2 architecture of Advanced Java Technologies.
* Layout – View
* Components – Controller
* Data sources – Model.
Find the images below to understanding the basic of CDE.

* Scroll down the dashboard. Find “About” on the left side and find the version number of CDE installed.

* Close the version window and Just click on Preview. I’ll let you know how to work out with all the components in CDE with a good working example.
* Now, just click on Preview. You can find the output of the dashboard as shown in below figures.

* Scroll down.

* Read the points that were in boxes. You will get the basic idea of CDE dash boarding.
Example:
Aim : To design a simple chart(on dashboard)
Environment:
* Pentaho BI Server community 4.8 stable version.
* CDA,CDF & CDE installed in the server from Market place.
Database: SampleData (It comes along with pentaho installation.. for this artical I’m not connecting to any external database.)
* This example was based upon CCC-Version1. The current Example which I’m going to explain is based upon CCC2-Version2.
* There are a lot of changes made from CCC-V1 to CCC-V2.
* Make sure your environment of CDE supports CCC-V2 for the example.

Step 1: Creating New CDE Dashboard
There are two ways to create a new CDE dashboard.
i) From the menu bar
ii) By clicking CDE icon
 Save your dashboard
 Click on Save -> Save your dashboard in your fav folder with your fav name.
 

After saving your dashboard  “Refresh your Repository” and then you can find the file as shown below figure.

Step 2:  Working with Layout, Componets and Data sources.
CDE is mainly works on scripting such as css, javascript.
Click on + sign.. (We are going to add Cascading Style Sheet code(css) code to our dashboard).
Resource type : css
External file
Resource type : css

Resource file : click on   ^  that is highlighted using arrow in the below image.

   * Give Styles2(do not give Styles2.css i.e, only give name but not extension).
   * It automatically cmes with .css extension and with $ and flower braces.
   * Save the css file externally to a folder as shown in below figure.
   * To see the Styles2.css appearing in the folder your file must contain some css code.
   * You must refresh your Repository to see the name of the file(Styles2.css) in the folder.
Giving Title to Dashboard:
*  CDE layout mainly works on Rows and Columns.
* Click on Add row symbol as shown in below figure.
 *  Give name of the Row and give back ground colour as shown in below figure.

* And give Corners: Round( it is below the back ground , not shown in the image-it is not visible here in the image)

 *  Now add Column(find ||) symbol on Layout Structure and click on it.

* Give all the necessary properties as shown in the below figure.
  Name: title
 HTML : <h1> Sales OverView YDT <h1> ( as show in figure at arrow symbol click on button.. You will be prompted to a new window there you have to write this HTML Code)
Font Size: 10
 See the preview:
The preview consists of only the title of the dashboard with its layout.
It is further going to be modified.

 Add your .css code here…

Css code
Body{
                background-color:#fffdf1;
}
h1{
                font-size:24px;
                color:#fff;
                margin-left:10px;
                margin-top:10px;
}
 Now see the preview.

By the time you get some idea on it.

Do as follows, I’m reducing the images now as you might get some idea..
Add another row -> Column->Html  as shown in figure.
For html give Name as title and write HTML code <h3>Sales Performance</h3>  in HTML editor
Add the following code to Styles.css to editor
h3{
    font-size:18px;
                font-weight:bold;
                color:#b68c58;
                margin-left:10px;
                margin-top:10px;
                margin-bottom:8px;
}
.salesTitle{
    color:#666;
                height:2px;
    margin-top:10px;
                margin-left:10px;
                margin-bottom:2px;
                width:939px;
}
Find the images for doing the above and see the preview again
 Add another column as shown in below figure

Give the following properties:

Name: sales_chart
Span size: 16
Now working with Data Sources and Query part:
  Click on Data Sources

·         Find SQL Queries in Left panel.
·         Click on sql over sqlJndi
·         In left panel give Properties
o   Name : get_sales_performance
o   Access Level : public (default)
o   Jndi: SampleData(We are working with pre defined database that comes along with pentaho software to develop this dashboard)
o   Query:
(
    SELECT
        ‘Profit’ as CATEGORY,
        ‘Measure’ as SERIES,
        SUM(ORDERFACT.TOTALPRICE-ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE
    FROM
        ORDERFACT
    INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
    WHERE
        YEAR_ID = 2004
)
UNION ALL
(
    SELECT
        ‘Profit’ as CATEGORY,
        ‘Target’ as SERIES,
        SUM(ORDERFACT.TOTALPRICE-ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE
    FROM
        ORDERFACT
    INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
    WHERE
        YEAR_ID = 2003
)
UNION ALL
(
    SELECT
        ‘Cost’ as CATEGORY,
        ‘Measure’ as SERIES,
        SUM(ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE
    FROM
        ORDERFACT
    INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
    WHERE
        YEAR_ID = 2004
)
UNION ALL
(
    SELECT
        ‘Cost’ as CATEGORY,
        ‘Target’ as SERIES,
        SUM(ORDERFACT.QUANTITYORDERED*PRODUCTS.BUYPRICE) AS MEASURE
    FROM
        ORDERFACT
    INNER JOIN PRODUCTS ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
    WHERE
        YEAR_ID = 2003
)
UNION ALL
(
    SELECT
        ‘Revenue’ as CATEGORY,
        ‘Measure’ as SERIES,
        SUM(ORDERFACT.TOTALPRICE) AS MEASURE
    FROM
        ORDERFACT
    WHERE
        YEAR_ID = 2004
)
UNION ALL
(
    SELECT
        ‘Revenue’ as CATEGORY,
        ‘Target’ as SERIES,
        SUM(ORDERFACT.TOTALPRICE) AS MEASURE
    FROM
        ORDERFACT
    WHERE
        YEAR_ID = 2003
)
Working with Component
·         Click on Components
·         On the left panel click on Charts
·         In properties give the following
o   Name: sales_chart
o   Datasource: get_sales_performance
o   Width: 610
o   Height:140
o   HtmlObject : sales_chart
o   SeriesInRows : False
·         I will show you how to work with Advanced Properties in the upcoming posts on
CDE.
Now save your dashboard and Preview
The preview will look like as follows
That’s it.
This artical is for developing simple dashboard.
In my next article I’m going to make this dashboard dynamic with parameters and will make the dashboard better look and feel.
Thanks,

Installing Jasper server with MySQL Database

This blog will help you understand how to install Jasper Server with MySQL database

Pre requisites
1. Install the Oracle/Sun Java JDK 1.6 or 1.7. Create and set the JAVA_HOME system
environment variable. i.e., Make sure Java path are set for your user profile or system
profile
Please follow the link to get the software.
http://www.oracle.com/technetwork/java/javase/downloads/java-se-jre-7-download-
432155.html

2. Jasperreports-server-cp-5.0.0-bin-zip
a. Download Jasperreports-server-cp-5.0.0-bin-zip from the below mentioned location.
http://sourceforge.net/projects/jasperserver/files/JasperServer/JasperReports%20S
erver%205.0.0/jasperreports-server-cp-5.0.0-bin.zip/download

3. MySQL database.
a. Please follow this link to get the software.
http://dev.mysql.com/downloads/mysql/5.0.html
Note: We can use any other database as well. For demo purpose we are using mySQL.

4. Apache Tomcat Server
a. Please follow this link to get the software.
http://tomcat.apache.org/download-60.cgi

Follow below mentioned steps to install Jasper server CE 5.0.0 with mySQL database.

Step 1:- Extract all files from jasperreports-server-cp-5.0.0-bin.zip. Choose a destination, such as C:\Jaspersoft on Windows. The directory, jasperreports-server-cp-5.0.0-bin, appears in the file location you choose.

Step 2 :- Copy the <database>_master.properties file for your database from sample_conf and paste it to buildomatic:
Copy from — <js-install>/buildomatic/sample_conf/
Paste to — <js-install>/buildomatic
For example, copy mysql_master.properties to <js-install>/buildomatic.

Step 3:- Rename the file you copied to default_master.properties.

Step 4:- Edit the default_master.properties file to add the settings for your database and application server.

Below mentioned is a sample property values MySql database.

MySQL appServerType=tomcat6 [tomcat7, tomcat5, jboss, jboss7, glassfish2,
glassfish3, skipAppServerCheck*]
appServerDir=c:\\Program Files\\Apache Software Foundation\\Tomcat 6†

dbUsername=root
dbPassword=password
dbHost=localhost

Set the following in the file
webAppNameCE = jasperserver

***NOTE:
Place mysql-connector-java-5.1.10.jar driver in the following location
D:\install SW\jasperreports-server-cp-5.0.0-bin\buildomatic\conf_source\db\mysql\jdbc

Step 6:-
Run the js-install scripts.
a. Start your database server.
b. Stop your application server.
c. Open Command Prompt as Administrator on Windows.
d. Run the appropriate commands:

js-install-ce.bat : Installs JasperReports Server, sample data,
and sample databases (foodmart and sugarcrm)

js-install-ce.bat minimal : Installs JasperReports Server, but does not
install sample data and sample databases

NOTE
If you installed the optional sample databases, complete the installation by executing this command: js-ant deploy-webapp-ce from buildomatic location in command prompt.

For example in the following location:
C:\Program Files\Apache Software Foundation\Tomcat 6.0\webapps

Step 7:-
Run the tomcat server and type the jasper server url.
For example: http://localhost:2222/jasperserver/login.html