Date Field Format Customisation in Adhoc View on Japser UI

Date Field Format Customisation in Adhoc View on Japser UI

Often while working with Adhoc Reports on jasper we get limited option for the availble date format in jasper. There are number of ways to fix this but one I am going to discuss here is by adding date format options in the backend of Jasperserver.

How we can add the different date formats in the Jasper UI itself is what I am going to describe in this blog. Here we are going to add the date format for “06/24/2016 11:15:25 a.m”.

1.First navigate to the path:
apache-tomcat\webapps\jasperserver-pro\WEB-INF\bundles\adhoc_masks.properties
where jasperserver is installed.
2. Open the file adhoc_mask.properties.
3. Edit the file content which appear somewhat like this:-

blog 1

4. Add “ADH_100_MASK_timestamp_5 = MM/dd/yyyy HH:mm:ss a” at required location.
5. In similar fashion we can customise the UI for the required date formats.
6. After making the changes save the file and restart the server. As new changes get reflected only after restarting the server.
7. Once the server gets restarted in the jasperserver you can notice the new option for date format by clicking on the date field in adhoc view, which we have just added in the backend file.

blog 2

Similarly we can edit the formatting of integer and decimal. Adding the format options in this way gives you more generalised approach to handle data formatting related tasks in jasper adhoc.

Thanks
Nisha Sahu

How to Replace Various Unwanted Characters during Reporting Using Jasper Studio

How to Replace Various Unwanted Characters during Reporting Using Jasper Studio

Reporting in Business Intelligence is the representation of Data in a more meaningful manner
so at times we need to follow some few formats to make our data to the End-User or Clients more meaningful to their Business

Now in some cases
characters like

commas (,), asterisk (*) and periods (.) are not permitted
and permitted characters can be Ampersand (&) , hyphens (-)

so what do we do here?

since jasper has various text functions one in particular that can be used here will be

the replaceAll() method

so this can be done on the particular columns you want make the required changes.

Now Let’s go to the field column you want and edit the Expression.

In my case:
I would be editing the First Name Column to the required format
so now we use the replaceAll() method

My requirement is to remove all commas and replace it with a [space]
so a simple

$F{firstname}.replaceAll(“,”,” “).trim()

can be used
Note: if you are wondering and new to Jasper why i used trim(), I used a trim() here because at times a name can be in the form

Helical,IT Solutions,

Now with the $F{firstname}.replaceAll(“,”,” “) it gives us the result Helical IT Solutions [space] this hereby create an extra space at the End so
a trim() method is used to remove unwanted spaces at the End.

OK now you have an idea on how to replace unwanted characters with the required characters
Now go ahead and replace * with –

and . with [space]

Did you get and Error?

Indeed yes you will get an Error if you
wrote your expression as

¬†$F{firstname}.replaceAll(“,”,” “).replaceAll(“*”,” “).replaceAll(“.”,” “).trim()

and if you didn’t well skip this part ūüėČ

ok now if “.” is kept it actually treats it as a . and not as “.” the character same goes with the “*”

so the appropiate expression would be

$F{firstname}.replaceAll(“,”,” “).replaceAll(“[*]”,” “).replaceAll(“[.]”,” “).trim()

 

well there are various way also to express the . as character and [.] is one of them
and now you are good to go

– Izebhijie Sohail Ehizogie

Note: Quotations are in the form of quotation open and quotation close

USING PARMETERS IN iREPORT

USING PARMETERS IN iREPORT

 

Most basic knowledge any new learner would have of word parameter -‚Äú parameter is a limit or boundary which defines the scope of a particular process or activity ‚ÄĚ. And technically we can define it as – ‚ÄúA numerical or other measurable factor forming one of a set that defines a system or sets the conditions of its operation. ‚ÄĚ Or call it ‚Äúfilter‚ÄĚ in simple words.

But in jasper-reports they represent the best way communication channel between the report engine and the execution environment (which is your application). After designing the report and checking its preview, what designer will provide as solution to the user for his desired criteria for report generation at run time, the answer is parameter. So, here are basic steps using which you can add parameters to your report to enhance its reporting experience for user. Add parameter to the report from report inspector, and manage its addition and removal from the report .

1Fig 1.1

Right click on the ‚Äúparameter 1‚ÄĚ as shown in Fig 1.1, and rename the parameter as per your convenience. Now, pay attention to its properties to modify it .

2Fig 1.2

In properties you will notice fields as shown in Fig 1.2 , after naming it and defining its class in ‚Äúparameter class‚ÄĚ which is basically its data type, you need to provide ‚Äúdefault value expression ‚ÄĚ which gets executed only when value of parameter is not provided by the user at run time. Check box of ‚Äúuse as prompt‚ÄĚ for pop up to get value from user , which will appear as shown in Fig 1.3.

3

Fig: 1.3

Next question is how parameter gets executed in SQL query of report?

 

Parameters can be used in SQL queries to filter records in a where condition or to add/replace pieces of raw SQL or even to pass the entire SQL string to execute.

In the first case the parameters are used as standard SQL parameters, in example:
SELECT * FROM DEPARTMENT WHERE DEPARTMENT_ID = $P{parameter1}
Syntax of parameter in query : $P{parameter1}
In above example the entered  value get evaluated to display the details of row containing values of DEPARTMENT_ID entered by user through parameter. Result will get displayed as Fig: 1.4
In this user will be solely responsible for the correct execution of report as only entered value will be used in the execution of query.

4Fig 1.4

Thanks,

Nisha Sahu

 

 

Restrict Menu items by Role in Jasper Server

Restrict Menu items by Role in Jasper Server

We can use role-based customizations to control access to menus. The example here shows how to control access to menu items.

In this example scenario, we wish to hide Create Report, Dashboard, Domain & Data Source from roles other then ROLE_ADMINISTRATOR and ROLE_HRIS. So, these menu items would only be accessible to ROLE_ADMINISTRATOR and ROLE_HRIS.

Steps:

  • Edit the file <js-webapp>/WEB-INF/actionModel-navigation.xml. The actionModel for Create > Ad Hoc View is near the end of the file.
 <context name="main_create_mutton" test="isProVersion">
  <condition test="!banUserRole">
    <condition test="!isMainFeaturesDisabled">
        <selectAction labelKey="NAV_005_CREATE">
        
        
            <condition test="isAvailableProFeature" testArgs="AHD">
                <option labelKey="NAV_051_ADHOC_REPORT" action="primaryNavModule.navigationOption"
                        actionArgs="designer"/> 
                        
                <condition test="checkAuthenticationRoles" testArgs="ROLE_ADMINISTRATOR,ROLE_HRIS|organization_1">
                <option labelKey="NAV_057_REPORT" action="JRS.CreateReport.selectADV"
                        actionArgs="createReport"/>
                </condition>
            
            </condition>
            
            <condition test="isSupportedDevice">
                <condition test="isAvailableProFeature" testArgs="DB">
                <condition test="checkAuthenticationRoles" testArgs="ROLE_ADMINISTRATOR,ROLE_HRIS|organization_1">
                    <option labelKey="NAV_050_DASHBOARD" action="primaryNavModule.navigationOption"
                            actionArgs="dashboard"/>
                </condition>
                </condition>
                
                <condition test="isAvailableProFeature" testArgs="AHD">
                    <condition test="checkAuthenticationRoles" testArgs="ROLE_ADMINISTRATOR,ROLE_HRIS|organization_1">
                        <option labelKey="NAV_056_DOMAIN" action="primaryNavModule.navigationOption"
                                actionArgs="domain"/>
                        <option labelKey="NAV_058_DATA_SOURCE" action="primaryNavModule.navigationOption"
                                actionArgs="dataSource"/>
                    </condition>
                </condition>
                
            </condition>
        </selectAction>
    </condition>
  </condition>
</context>


  • Save the file after making changes.
  • Restart Jasper server.
  • Login as user with role other than ROLE_ADMINISTRATOR and ROLE_HRIS. You can see that the menu item to Create Report, Dashboard, Domain & Data Source is removed, and user can only see Create Ad-hoc view as menu item. Log out and log back in as admin or HRIS user. All menu items are visible to administrators and HRIS user.

Note:

In commercial editions, you must specify the role’s organization ID when restricting access to roles defined in an organization. There are three ways to specify a role in the commercial edition:

  • ORG_ROLE|orgID ‚Äď Explicitly specify a role belonging to an organization
  • ORG_ROLE|* ‚Äď Match the role name in any organization in the user‚Äôs scope (parent organizations and root).
  • SYSTEM_ROLE ‚Äď Explicitly specify a role defined at the root or system level, such as ROLE_ADMINISTRATOR.

How to restrict Multiselect input control values in jaspersoft :

How to restrict Multiselect input control values in Jaspersoft 

This blog will teach the reader , how to restrict multiselect input control values in jasper without hampering the performance .

DB : Postgres (foodmart)

Table : customer

Reporting tool : iReport

Server : Jasperserver 5.6

I/P Parameter : country

Requirement : Get All the informations from customer table where country name is either USA or Mexico or Canada

Condition :

  1. you can not select more than 2 value in ‚Äúcountry‚ÄĚ input parameter
  2. if more than 2 values are selected in ‚Äúcountry‚ÄĚ input parameter then the main report query should also not get executed. This will unnecessarily hamper the performance.

Solution :

  1. create multiselect input control named as ‚Äúcountry‚ÄĚ

select distinct country from customer

  1. create a sub report which will display all the informations from customer table on selection of ‚Äúcountry‚ÄĚ parameter

and give its print when expression as :

$P{country}.size() <= 2

  1. create a text box in the header , write a message init as :

‚ÄúYou can not select more than 2 values in country parameter‚ÄĚ

And give its print when expression as :

$P{country}.size() > 2

  1. in the sub report , query will be :

select * from customer

where $X{IN,country,country}

limit 20

  1. publish the report on jasper server.

O/P

# 1 : when 2 values selected in country parameter

lenlt2#2 : when more than 2 values selected in country parameter

lengt3

Thanks,

Rupam Bhardwaj

Remove Repeated Column header, Page Header, Page Footer in CSV Export (JASPER)

In CSV export, we want only column header once and data should be printed. We do not want page header, page footer and repeated columns header. So in order to achieve this do the following.

    1. Remove Page Header:
 
<property name="net.sf.jasperreports.export.csv.exclude.origin.band.(uniqueValue)" value="pageHeader"/>
    1. Remove Page Footer:

<property name="net.sf.jasperreports.export.csv.exclude.origin.band.(uniqueValue)" value="pageFooter"/>
    1. Remove Title Band:

<property name="net.sf.jasperreports.export.csv.exclude.origin.band.(uniqueValue)"value="title"/>
    1. Remove Repeated Column Header:

<property name="net.sf.jasperreports.export.xlsx.exclude.origin.keep.first.band.(sameValue)" value="columnHeader"/>
<property name="net.sf.jasperreports.export.xlsx.exclude.origin.keep.first.report.(sameValue)" value="*"/>

Some times we are using sub report to display page header and page footer. Do the following to remove the it in CSV Export.

    1. Remove Sub Report:

<property name="net.sf.jasperreports.export.xlsx.exclude.origin.report.(sameValue)" value="Actual Report Name not the sub report name"/>
<property name="net.sf.jasperreports.export.xlsx.exclude.origin.band.(sameValue)" value="detail"/>

This is not working for crosstab.

Thanks & Regards,
Fahad Anjum

Creating a custom template with Jaspersoft Studio

Creating a custom template with Jaspersoft Studio

Create the Structure

Creating template is nothing more than a standard report where some elements have a precise and fixed name that is used by the Jaspersoft Studio engine to understand where to place every element.

The First thing is to create new report from File -> New -> Jasper Report. We can start with blank report or selecting one of the other templates as a starting point.

Now Design the template as par your requirement .Creating the template is same as that of generating the report.

I have created like this

Report Template

Now switch to the Preview mode and look if the result is what you want and if it is allright you can continue with the export operation.

Export the Template

To start the export wizard select click File -> Export as Report Template.

In the first dialog you will see all the resources that will be exported. Between these resources you can fine the template and other files used by it, in our example you will see the one image. In this step you will need also to select the destination folder, where the template and all his resources will be placed.

Template Export

When you have finished press Next.

Here you can define the categories where your template will be visible inside the New Report Wizard.  You can select any number of categories or your own category. To define a custom category you need only to type its name inside the text area on the bottom and press the Add button. Then your category will be added to Selected Categories list. Then your category will be added to Selected Categories list.

At this point you need to select the correct type of report, for example if it is a Tabular Report or a standard one.
Template Type and Categories

At this point you can see two different steps:

  • The first one, and the one that should appear follwoing this tutorial, is a congratulations step. It means that the template you designed is valid for the report type you have selected in the previous step.
  • The second one is a list of error messages if the template you designed is not valid for the selected type because of some design errors. For example for a template with Tabular type is expected a table in the summary band. If this table is not found it’s probably a design error. In this step all the design errors found are listed so you can look to what is wrong and fix it.

5(30)
 

OR

 
6(22)

 

In both the cases we can export the template and all resources by clicking the finish button.

In second case may your template should not work while generating the report.

Add the Templates location to Studio

  1. Now you have to put your custom template for studio.
  2. Navigate to Windows ->Preferences. A new Preferences window open.
  3. From the Left panel expand the category Jaspersoft Studio and select Template Locations.
  4. Click on New and browse the folder where you have saved template jrxml file.

Refer the screenshot below:
5(14)

Test the new template

  1. Open Jaspersoft studio.
  2. Navigate to File > New > Jasper Report.

A new Report wizard will open.
Template

  1. Select the template that you have added. Then Report will open as shown below:

Report Template
 
-By
Nitin Uttarwar
Helical It Solution

Best Practices when designing & using iReport /Jaspersoft

This blog talks about the best practices which should be followed when creating reports using iReport or Jasper studio, deploying the same on Jaspersoft server, nomenclature to be used etc.

 

1) Report Margins:

When you develop reports for dashboards, it is advisable to keep all the margins with 0 pixels.

By default margins will be
Left margin         20
Right margin       20
Top margin         20
Bottom margin    20

Change the values to 0

Left margin         0
Right margin       0
Top margin         0
Bottom margin  0

Why?
Because, when set to 0 report panels are well fit when designing the dashboards.

 

2) Bands to keep the components

Do not keep table component, cross tab component in Detail band. Keep all the components either in Title band or in Summary Band as per the requirement. It is advisable to create custom bands to keep the different charts if you need to develop a report with multiple charts.

Why it is not recommended to keep the components in Detail band?

Details band falls into loop till the end of the row/data for fields hence if you keep any other component it will fall in a loop and will give you unexpected behaviour of iReport with bad output.

3) Parameter Naming conventions

It is advisable to give good naming conventions for parameters. For example parameter name could be param_paramName or p_paramName

Eg : 1)  p_startDate 2) p_endDate

Other Naming conventions
The same thing you can apply when you create input controls /Data source Names/Custom band names/Data Set names in iReport & Jasper Repository respectively.

Why ?

Easy to differentiate the variables, parameters and group names etc

4) Remove the other bands which you are not going to use in iReports

5) Variables and Parameter usage in iReport

Make use of internal parameters for the report and for the summation of columns recommended to use the variables.

6) Jasper Project Folder Structure

Project Name

archive (take a back up of jrxmls if you are going to update/modify them in this folder with a version number)

      Resources
     Input Controls ( All your parameter names for the project/various reports)
      Data sources(This folder is useful when you have multiple databases to use in your project)
      Files(Keep all your data source files here, for eg : Excel, CSV, XML and etc)
      JRXML’s (Whatever the JRXMLS you are creating you can keep all of them in this folder)
      Sub Reports(keep all you sub reports in this folder and refer from here where ever you want)
      Images(Keep all your images in this folder- for easy understanding)
      Reports(Keep all your reports in this folder)
      Dashboards(Save all your dashboards here)
      Temp (for temporary files)
      Test (Do experiment at the time of development of report in this folder)

Note that if your project is having lot many reports according to some sections/departments, it is advisable to sub divide the Reports folder with other folders.
For example:
Reports
    A.Department-1
         1.Report Name
         2.Report Name
    B.Department-2
        1.Report Name
         2.Report Name

 NOTE:

When you upload a report to JRXML it is recommended to write the description of the report. By seeing it every one can easily understand the purpose of the report/visualization.

7) Export / Import Utility

Command line utility to import/export/update folders/reports from the jasper server is given below.

Importing
js-import ‚Äďinput-zip(space) <Filename>
Ex: js-import ‚Äďinput-zip(space)‚ÄĚE:Work Space\Unified\Unified Reports\<file name>‚ÄĚ

Updating
js-import ‚Äďinput-zip(space)‚ÄĚE:Work Space\Unified\Unified Reports\<file name>‚ÄĚ ‚Äďupdate

Exporting
js-export <location of the folder in jasper server> ‚Äďoutput-zip <location of exporting folder>/<exporting_filename.zip>

8) Bands

Title band:

·         Every report must have some name, give the name of the report in this band.

·         Blue colour back ground with white colour font is preferable to give the titles.

·         Logos of the company are recommended to be placed left side of the band in title band under the title of the report.

Page Header:

·         Page header consists of the page numbers and date type of information. It is recommended to give page header information for long reports with heavy text involved in the reports.

Column Header:

·         This band is used for giving column headers for the fields. You can change the font style, size, give the borders, back ground colours and etc.

Detail band:

·         Detail band is used to display the output of the report using fields fetched by the query.

·         You need to drag and drop required fields to create the report to Detail band format them accordingly.

·         Detail band falls into for loop so we should keep only fields in this band rather than keeping any other component like table , cross tab, chart components.

Column footer:

 ·         This band is used to find the total, max, min of the columns from the details band.

·         You need to create variable for this and drag that variables under the column where you want see the sum, max or min

Page footer:

·         Page footer is used to place the page numbers, confidential type of text for the company etc.

Summary:

·         Summary of the report will be placed in the summary band.

·         Generally we keep the chart component, table component, cross tab component to summarize the report.

9)   9) Why should we keep input controls and data sources in resource folder?

  

Input controls in repository:

Create all your input controls in resource folder because every time for each report you need not to create the same input controls. You just need to link the existing input control from the repository folder.

Data sources in repository:
It is considered as a best practice to create data source connections in a folder called resources  and use this data source for the reports. It’ll reduce the report development time. You need to not create import database connections from iReport once you create this connection in the repository.

For any Jaspersoft, ireport, jasper studio, jasperserver or Open source DWBI requirement, please get in touch : nikhilesh@helicaltech.com, www.helicaltech.com

HOW TO CREATE LOCK FILE IN C

 

File locking provides a very simple yet incredibly useful mechanism for coordinating file accesses. Before I begin to lay out the details, let me fill you in on some file locking secrets:-

There are two types of locking mechanisms: mandatory and advisory.

– Mandatory systems will actually prevent read() and write() to file.

– There are two types of (advisory!) locks: read locks and write locks (also referred to as shared locks and exclusive locks, respectively.)

 

Here I am demonstrating READ-LOCK …

In  my  case , I want  to reduce CPU usage which  takes very  high CPU usage % in case of accessing Sqlite3  by  two  application simultaneously.

To solve  this problem  I  applied  lock  file  concept  to synchronizes  applications ,

I applied it for three different applications.

 

Steps followed while creating lock file:-

 

РFinalize  File Name (xyz.lock)

РFinalize  lock file  location

РBefore running  application , check lock file is  present or not

if(getlock(lockfile)== 1) :: Lock file is not present

РIf lock file is not present  then create  lock file

int lockstatus = setlock(lockfile);

РAfter  completion  of job  delete lock file

 

Removelock (lockfile)

РIf  lock file is  present  then sleep  for  sometime then again check for lock file

– While creating lock file, handle unexpected application closing mechanism to avoid trouble when next time application needs to be run.

 

Note: Extension of lock file is (abc.lock)

Note: Remove lock file when application is forcefully terminating (ctrl + C)

 

(Example of lock file in c using semaphores : –¬† )

Reference: http://www.c-program-example.com/2012/04/c-program-to-lock-file-using-semaphores.html