Working with Crosstab Component In Pentaho Report Designer

This blog will teach reader about Working with Crosstab Component In Pentaho Report Designer

Crosstab is an experimental feature in Pentaho Report Designer . By default we can’t see crosstab component in PRD.

To enable this feature we need to do some modifications in Edit – > Preferences

1

In this Panel , go to “Other Settings” Tab and check the 2nd Option i.e, “Enable (unsupported) experimental features”.

Then Crosstab component will be automatically visible in PRD.

Data Source : JDBC : Sample Data Memory

Query :

2

If we will preview this query then we will get the data like below :

3

But In the above resultset it is very difficult to analyze the data .

So , its better visualization , I decided to create report with crosstab on this query .

Steps :

  1. Go to Master Report in Structure Tab
  2. On the right click of Master Report click on Add Crosstab Group

4

  1. Then you will find the below window

5

  1. Here you need to decide which field you want to see in row and which in column , accordingly set the fields you will get the output .

EXAMPLE :

6

Now Preview the report you will find something like below :

7

 

 

Rupam Bhardwaj

Helical IT Solutions

How to create reports using Pentaho Report Designer

How to create reports using Pentaho Report Designer

Pentaho Business Intelligence platform provides several tools to design and deploy reports, the easiest is the Pentaho BI web reporting wizard (see pentaho_reporting).

While this approach is useful for basic or self service reporting, for complex reports with charts, cross linking and scripting a tool like Pentaho Report Designer is more appropriate. Pentaho Report Designer is the reporting engine integrated in Pentaho Business Intelligence.

Other excellent Pentaho compliant reporting tools are Jasper Reports and Eclipse BIRT, however Pentaho Report Designer has the advantage of being more integrated and easily deploy-able.

Steps :

Connect a database in Pentaho Report Designer

Pentaho Report Designer is an open source reporting tool available at http://sourceforge.net/projects/pentaho/files/

Once unzipped the application can be run with report-designer.bat (report-designer.sh in unix). To create a new report press File->New and then Data->Add datasource->JDBC. Pentaho Report Designer connects with several data source providers, for example SQL databaseMondrian OLAP and Pentaho Data Integration (Kettle).

In this example we’ll use a common JDBC connection for Postgres databases. To add a new connection press the plus icon on the JDBC dialog.

JDBC Connection

On the new dialog set the data base connection credentials, in this example a pre-existent Postgres database named Foodmart, with the following parameters:

Connection name: SugarBI
Connection type: Postgres
Host name: <host>
Database name: <database name>
Port number: 5432
User name: root
Password: <your password>
Access: Native (JDBC)

img-2

Now press Test to check the connection and then press ok. In case of connection error check the connection parameters and whether the JDBC driver is present in /report-designer/lib/jdbc.

Now press the plus icon and fill the SQL query text and query name, like in the following picture.

img-3

To check if the query works press Preview button and then Ok to close the dialog.

In a real enterprise application a JNDI connection would be a better solution than a direct JDBC connection. A JNDI connection, which is explained at the end of this post, defines a JDBC connection alias and avoids the hassle to update all the reports in case of database parameters changes.

Build a report with Report Designer

After the Postgresql connection configuration the Data tab (on the right) should display the connection name with all its fields. Placing a field in the report is very straight forward and can be accomplished by dragging and dropping the field on the Details band.

img-4

 

To set a page title click on “Ab” button in the left toolbar and drop it on Page Header band and to set a column label simply drag the “Ab” button over the Report Header band.

A page number can be added by right clicking Functions (right Data tab) and selecting Add functions..->Common->Pages of Pages. The new Pages of Pages field can then be dragged on the Page Footer.

img-5

Pentaho Report Designer allows to test the final result at any time by pressing the play icon and choosing an output format, for example PDF or HTML.

img-6

How to publish a report in Pentaho

Once built the report can be run directly on Pentaho Business Intelligence suite. Pentaho Report Designer provides direct access to Pentaho‘s repository, however the access needs to be enabled first. To enable the repository access edit biserver\pentaho-solutions\system\publisher_config.xml and set the password in the publisher-password tag:

<publisher-config>
<publisher-password>your password here</publisher-password>
</publisher-config>

img-7

The default credentials are:

URL: http://localhost:8080/pentaho
User: joe
Password: password

Click Ok and if Pentaho server is running a form like this one should appear:

img-8

Press ok, the report should appear on the Pentaho repository browser of the Pentaho Business Intelligence suite:

img-6

 

 

 

 

 

Anonymous Authentication in Pentaho

This blog will be talking about anonymous authentication in Pentaho. You can bypass the built-in security on the BA Server by giving all permissions to anonymous users. An “anonymousUser” is any user, either existing or newly created, that you specify as an all-permissions, no-login user, and to whom you grant the Anonymous role. The procedure below will grant full BA Server access to the Anonymous role and never require a login.

1. Stop the BA Server.
2. Open the /pentaho/server/biserver-ee/pentaho-solutions/system/applicationContext-spring-security.xml file and ensure that a default anonymous role is defined. Match your bean definition and property value to the example below.

<bean id=”anonymousProcessingFilter” class=”org.springframework.security.providers.anonymous.AnonymousProcessingFilter”>

<!– omitted –>

   <property name=”userAttribute” value=”anonymousUser,Anonymous” />

</bean>


 

3. Find these two beans in the same file .
o filterSecurityInterceptor
o filterInvocationInterceptorForWS
Locate the objectDefinitionSource properties inside the beans and match the contents to this code example.

<bean id=”filterInvocationInterceptor” class=”org.springframework.security.intercept.web.FilterSecurityInterceptor”>
    <property name=”authenticationManager”>
        <ref local=”authenticationManager” />
    </property>
    <property name=”accessDecisionManager”>
        <ref local=”httpRequestAccessDecisionManager” />
    </property>
    <property name=”objectDefinitionSource”>
        <value>
            <![CDATA[ CONVERT_URL_TO_LOWERCASE_BEFORE_COMPARISON
\A/.*\Z=Anonymous,Authenticated ]]> </value>
    </property>
</bean>

 

4. Save the file, then open pentaho.xml in the same directory.
5. Find the anonymous-authentication lines of the pentaho-system section, and define the anonymous user and role.

<pentaho-system>
<!– omitted –>
    <anonymous-authentication>
        <anonymous-user>anonymousUser</anonymous-user>
        <anonymous-role>Anonymous</anonymous-role>
    </anonymous-authentication> <!– omitted –>
</pentaho-system>

6. Open the repository.spring.properties file in the same directory.

a) Find the singleTenantAdminUserName and replace the value with the anonymousUser name.
b) Find the singleTenantAdminAuthorityName and replace the value with Anonymous.
c) Save the file.

Open the pentahoObjects.spring.xml file.
Find all references to the bean id=”Mondrian-UserRoleMapper” and make sure that the only one that is uncommented (active) is this one:

<bean id=”Mondrian-UserRoleMapper”
        name=”Mondrian-SampleUserSession-UserRoleMapper”
        class=”org.pentaho.platform.plugin.action.mondrian.mapper.
                            MondrianUserSessionUserRoleListMapper”
        scope=”singleton”>
    <property name=”sessionProperty” value=”MondrianUserRoles” /> </bean>

Save pentahoObjects.spring.xml and close the file.
Restart BA Server.
Enter http://localhost:8080/pentaho in browser address field. You will find that the pentaho home page opens without requiring login.

Archana Verma
Helical IT Solutions

D3 Bubble Chart Integration with Jaspersoft

In this blog we will be discussing about D3 Bubble Chart Integration with Jaspersoft using HTML method of integration.

All the reports are develop using ireport 5.5 professional and jasper server 5.5

As html component of jasper server does not load any scripts in the html component, we loaded the script in one of the decorator page(jsp page). The page is located at the location:

C:\Jaspersoft\jasperreports-server-5.5\apache-tomcat\webapps\jasperserver-pro\WEB-INF\decorators\decorator.jsp

In the page we included the scripts which we want to load. We added the following code in the jsp page at line no 46:

<script type="text/javascript" language="JavaScript"
src="${pageContext.request.contextPath}/scripts/d3.v3.min.js"></script>

The script to be added should be kept at location:

C:\Jaspersoft\jasperreports-server-5.5\apache-tomcat\webapps\jasperserver-pro\scripts

Bubble Chart

Bubble Charts Integration with Jaspersoft

Bubble Charts Integration with Jaspersoft

    Bubble Chart:-


For this chart we need to include one more js script file in the decorator page as described in the start of the document.
The js file is sankey.js and can be downloaded from

http://dimplejs.org/dist/dimple.v1.1.5.min.js

Sample Code is shown below:

var svg = dimple.newSvg("#chartContainer", 1090, 500);
var data = [
{
"Date":"1/1/2011",
"Analyst":"Cidalina Rivera",
"Category":"Application",
"Total Minutes":2114200,
"Minutes Per Item":1100,
"% SLA":80
},
{
"Date":"1/1/2011",
"Analyst":"Kiran Parvathala",
"Category":"Application",
"Total Minutes":391800,
"Minutes Per Item":1959,
"% SLA":21
},
{
"Date":"1/1/2011",
"Analyst":"KrishnaReddy Mavuru",
"Category":"Application",
"Total Minutes":1056125,
"Minutes Per Item":1207,
"% SLA":26
},
{
"Date":"1/1/2011",
"Analyst":"Narasimha Dara",
"Category":"Application",
"Total Minutes":386740,
"Minutes Per Item":610,
"% SLA":30
},
{
"Date":"1/1/2011",
"Analyst":"AslamJavid Shaik",
"Category":"Application",
"Total Minutes":1573856,
"Minutes Per Item":1096,
"% SLA":94
},
{
"Date":"1/1/2011",
"Analyst":"Harini Vemulapalli",
"Category":"Application",
"Total Minutes":2846340,
"Minutes Per Item":1890,
"% SLA":100
},
{
"Date":"1/1/2011",
"Analyst":"William Nelson",
"Category":"Application",
"Total Minutes":1205502,
"Minutes Per Item":662,
"% SLA":40
},
{
"Date":"1/1/2011",
"Analyst":"Janaki Govindarajan",
"Category":"Batch Job",
"Total Minutes":72684,
"Minutes Per Item":673,
"% SLA":79
},
{
"Date":"1/1/2011",
"Analyst":"Chiranjeevi Krishna Karne",
"Category":"Education",
"Total Minutes":542348,
"Minutes Per Item":3307,
"% SLA":52
},
{
"Date":"1/1/2011",
"Analyst":"Masaru Hirata",
"Category":"Fileshare",
"Total Minutes":129090,
"Minutes Per Item":662,
"% SLA":12
},
{
"Date":"1/1/2011",
"Analyst":"Naveen Kodali",
"Category":"Phone",
"Total Minutes":75076,
"Minutes Per Item":548,
"% SLA":84
},
{
"Date":"1/1/2011",
"Analyst":"JoshyPeter Joseph",
"Category":"Phone",
"Total Minutes":574860,
"Minutes Per Item":2948,
"% SLA":27
},
{
"Date":"1/1/2011",
"Analyst":"Maheshwar Malkapuram",
"Category":"Phone",
"Total Minutes":19845,
"Minutes Per Item":105,
"% SLA":92
},
{
"Date":"1/1/2011",
"Analyst":"Sunil Bhalerao",
"Category":"Procurement",
"Total Minutes":353000,
"Minutes Per Item":1765,
"% SLA":20
},
{
"Date":"1/1/2011",
"Analyst":"Tina Chan-Browne",
"Category":"Report",
"Total Minutes":51121,
"Minutes Per Item":469,
"% SLA":95
},
{
"Date":"1/1/2011",
"Analyst":"Sirajuddin Mohammad",
"Category":"Report",
"Total Minutes":323363,
"Minutes Per Item":1693,
"% SLA":47
},
{
"Date":"1/1/2011",
"Analyst":"Nishanth Nadam",
"Category":"Enterprise",
"Total Minutes":620000,
"Minutes Per Item":10000,
"% SLA":22
},
{
"Date":"1/1/2011",
"Analyst":"Santoshkumar Shinde",
"Category":"Enterprise",
"Total Minutes":715000,
"Minutes Per Item":11000,
"% SLA":90
},
{
"Date":"1/1/2011",
"Analyst":"Keith Moller",
"Category":"Enterprise",
"Total Minutes":948000,
"Minutes Per Item":12000,
"% SLA":100
},
{
"Date":"1/1/2011",
"Analyst":"AshokKumar Sangeetham",
"Category":"Server",
"Total Minutes":1166550,
"Minutes Per Item":7070,
"% SLA":88
}
];
data = dimple.filterData(data, "Date", "1/1/2011");
var myChart = new dimple.chart(svg, data);
myChart.setBounds(400, 60, 500, 330);
myChart.addMeasureAxis("x", "Minutes Per Item");
myChart.addMeasureAxis("y", "Total Minutes");
myChart.addMeasureAxis("z", "% SLA");
myChart.addSeries(["Analyst","Category"], dimple.plot.bubble);
myChart.addLegend(600, 10, 360, 30, "right");
myChart.draw();

    Integration with JasperServer:


The data which we use for developing the calendar view can be fetched from any database. The data fetched from database is stored in a variable and is then accessed in the html component using the same variable. Applying this of process makes the report dynamic instead of static. Few parameters can also be added in the report which can be used in query and/or html component.
Generally for these type of charts we pass a variable which contains required data containing date, hour and a value associated with that particular date and hour. The string is created according to JSON format, so that when accessed in script tag, can be easily converted to JSON object using eval function.
Any variable/parameter can be accessed as shown below:
” var arr =”+$V{variable1}+” ”
Parameter in query:
Select * from table_name
where date between $P{parameter1} and $P{parameter2}

The steps on how to integrate it with jasperserver was discussed in my previous blog(D3 Integrating with Jasperserver).

D3 Sankey Chart Integration with Jaspersoft

In this blog we will be discussing about D3 Sankey Chart Integration with Jaspersoft using HTML method of integration.

All the reports are develop using ireport 5.5 professional and jasper server 5.5

As html component of jasper server does not load any scripts in the html component, we loaded the script in one of the decorator page(jsp page). The page is located at the location:

C:\Jaspersoft\jasperreports-server-5.5\apache-tomcat\webapps\jasperserver-pro\WEB-INF\decorators\decorator.jsp

In the page we included the scripts which we want to load. We added the following code in the jsp page at line no 46:

<script type="text/javascript" language="JavaScript"
src="${pageContext.request.contextPath}/scripts/d3.v3.min.js"></script>

The script to be added should be kept at location:

C:\Jaspersoft\jasperreports-server-5.5\apache-tomcat\webapps\jasperserver-pro\scripts

Sankey Chart

Sankey D3 Jaspersoft

Sankey D3 Jaspersoft

For this chart we need to include one more js script file in the decorator page as described in the start of the document.
The js file is sankey.js and can be downloaded from
https://github.com/d3/d3-plugins/blob/master/sankey/sankey.js

Sankey diagrams visualize the magnitude of flow between nodes in a network. Sankey diagrams are a specific type of flow diagram, in which the width of the arrows is shown proportionally to the flow quantity. They are typically used to visualize energy or material or cost transfers between processes.
The diagram will contain two quantities, 1) All the nodes. 2) Which nodes are connected and with what value.

The json object should be like : http://bost.ocks.org/mike/sankey/energy.json

    Integration with JasperServer:

 
The data which we use for developing the calendar view can be fetched from any database. The data fetched from database is stored in a variable and is then accessed in the html component using the same variable. Applying this of process makes the report dynamic instead of static. Few parameters can also be added in the report which can be used in query and/or html component.
Generally for these type of charts we pass a variable which contains required data containing list of quantity1(employees or people or students, etc.) and a value associated with different type of skills. The string is created according to JSON format, so that when accessed in script tag, can be easily converted to JSON object using eval function.

Any variable/parameter can be accessed as shown below:
" var arr ="+$V{variable1}+" "

Parameter in query:
Select * from table_name
where date between $P{parameter1} and $P{parameter2}

The sample code of static chart is shown below at:

 

#chart {
height: 500px;
}

.node rect {
cursor: move;
fill-opacity: .9;
shape-rendering: crispEdges;
}

.node text {
pointer-events: none;
text-shadow: 0 1px 0 #fff;
}

.link {
fill: none;
stroke: #000;
stroke-opacity: .2;
}

.link:hover {
stroke-opacity: .5;
}

var margin = {top: 1, right: 1, bottom: 6, left: 1},
width = 960 – margin.left – margin.right,
height = 500 – margin.top – margin.bottom;

var formatNumber = d3.format(“,.0f”),
format = function(d) { return formatNumber(d) + ” TWh”; },
color = d3.scale.category20();

var svg = d3.select(“#chart”).append(“svg”)
.attr(“width”, width + margin.left + margin.right)
.attr(“height”, height + margin.top + margin.bottom)
.append(“g”)
.attr(“transform”, “translate(” + margin.left + “,” + margin.top + “)”);

var sankey = d3.sankey()
.nodeWidth(15)
.nodePadding(10)
.size([width, height]);

var path = sankey.link();
var energy = {“nodes”:[ {“name”:”Energy”}, {“name”:”Industrial Processes”}, {“name”:”Electricity and heat”}, {“name”:”Industry”}, {“name”:”Land Use Change”}, {“name”:”Agriculture”}, {“name”:”Waste”} ], “links”:[ {“source”:0,”target”:1,”value”:12}, {“source”:1,”target”:2,”value”:20}, {“source”:2,”target”:3,”value”:30}, {“source”:4,”target”:1,”value”:8}, {“source”:4,”target”:2,”value”:10}, {“source”:4,”target”:5,”value”:5}, {“source”:5,”target”:6,”value”:5} ]};

sankey
.nodes(energy.nodes)
.links(energy.links)
.layout(32);

var link = svg.append(“g”).selectAll(“.link”)
.data(energy.links)
.enter().append(“path”)
.attr(“class”, “link”)
.attr(“d”, path)
.style(“stroke-width”, function(d) { return Math.max(1, d.dy); })
.sort(function(a, b) { return b.dy – a.dy; });

link.append(“title”)
.text(function(d) { return d.source.name + ” → ” + d.target.name + “\n” + format(d.value); });

var node = svg.append(“g”).selectAll(“.node”)
.data(energy.nodes)
.enter().append(“g”)
.attr(“class”, “node”)
.attr(“transform”, function(d) { return “translate(” + d.x + “,” + d.y + “)”; })
.call(d3.behavior.drag()
.origin(function(d) { return d; })
.on(“dragstart”, function() { this.parentNode.appendChild(this); })
.on(“drag”, dragmove));

node.append(“rect”)
.attr(“height”, function(d) { return d.dy; })
.attr(“width”, sankey.nodeWidth())
.style(“fill”, function(d) { return d.color = color(d.name.replace(/ .*/, “”)); })
.style(“stroke”, function(d) { return d3.rgb(d.color).darker(2); })
.append(“title”)
.text(function(d) { return d.name + “\n” + format(d.value); });

node.append(“text”)
.attr(“x”, -6)
.attr(“y”, function(d) { return d.dy / 2; })
.attr(“dy”, “.35em”)
.attr(“text-anchor”, “end”)
.attr(“transform”, null)
.text(function(d) { return d.name; })
.filter(function(d) { return d.x < width / 2; })
.attr(“x”, 6 + sankey.nodeWidth())
.attr(“text-anchor”, “start”);

function dragmove(d) {
d3.select(this).attr(“transform”, “translate(” + d.x + “,” + (d.y = Math.max(0, Math.min(height – d.dy, d3.event.y))) + “)”);
sankey.relayout();
link.attr(“d”, path);
}

 

The steps on how to integrate it with jasperserver was discussed in my previous blog(D3 Integrating with Jasperserver).

Avi Dawra
Helical IT Solutions

Creating Cascading Parameters with Pentaho Report Designer 5.0.1 Stable

Here we will be talking about creating cascading parameters with Pentaho Report Designer 5.0.1 Stable.

Cascade parameters provide a way of managing large amounts of data in reports. You can define a set of related parameters so that the list of values for one parameter depends on the value chosen in another parameter.

PRD cascading parameter

Step-1. Build the Parameter Queries :

I will need to build two parameter queries, the first will need to display a distinct list of countries and the second will need to display a distinct list of cities which belong to the country.

 

To create a new query click on the Add a new query icon (add a new query) and enter a SQL statement which will retrieve a distinct list of countries – make sure you don’t forget to name your query (in this example my country parameter query is named countryList):

PRD cascading parameter 2

Here is the above query in a format which you can copy and paste:

SELECT
 DISTINCT `customers`.`COUNTRY`
FROM
 `customers`

The next step is to preview the query – as you can see from the screen shot below the countryList parameter query is retrieving a distinct list of countries:

PRD cascading parameter 3

Now we will need to add the second parameter which will display a distinct list of cities based on the value of the country parameter. Close the preview window and click on the icon (add a new query) and enter a SQL statement which will retrieve a distinct list of cities – make sure you don’t forget to name your query (in this example my city parameter query is named cityList):

PRD cascading parameter 4

Here is the above query in a format which you can copy and paste:

SELECT
 DISTINCT `customers`.`CITY`

FROM
 `customers`

Preview this query. At the moment the above query is only retrieving a distinct list of every city. I need to make sure that this query retrieves a list of cities based on the country parameter

PRD cascading parameter 5

For this query to only show a list of cities based on the value of the country parameter I will need to add the country parameter name to the WHERE clause of the cityList query. As I have not yet created the country or city parameters I will need to make note of the country parameter name I will be specifying in the cityList query – I have chosen to call the country parameter sCountryName. The new cityList query now looks like this:

PRD cascading parameter 6

Here is the above query in a format which you can copy and paste:

SELECT
 DISTINCT `customers`.`CITY`
FROM
 `customers`
WHERE
 `customers`.`COUNTRY` = ${sCountryName}

I have now created both parameter queries – I will need to revisit this area later on to modify the report query customerList but for now click on the OK button to close the JDBC Data Source window.

 

Step -2. Create the Parameters

I will need to create two parameters:

  1. A country drop down parameter which is named sCountryName (step 1)
  2. A city drop down parameter which will be named sCityName

Creating the sCountryName Parameter

To create a new parameter make sure you have the Data tab active, right click on the Parameters menu item and select the Add Parameter… option:

PRD cascading parameter 7

An Add Parameter… window will pop up. Before you make any changes to the options expand the connection folder on the left and then select the query which will populate the parameter, in this example it is the countryList query. Below is a screen shot of all the sCountryName parameter options completed:

PRD cascading parameter 8

 

The options above are fairly self explanatory however here are descriptions of the most important options:

  • Name: This is the name we specified in step 1, this must match the value of this parameter we put in the cityList WHERE clause
  • Type: The type of this parameter is a drop down
  • Query: The query that will populate this parameter is the countryList query which was created in step 1
  • Value and Display Name: I have set these both to the COUNTRY field as the value is the same as the output I would like displayed in the drop down parameter
  • Value Type: The COUNTRY field is a string
  • Mandatory: I have checked this option as a user must select a country before running the report – this also ensures that the city parameter will be populated

Click the OK button to save the sCountryName parameter.

Creating the sCityName Parameter

The second parameter I will need to create is for the city drop down. There isn’t much different from this parameter and the sCountryName parameter so I can copy and paste this parameter and then change some options.

To copy the parameter right click on the sCountryName parameter under the Data tab and select the Copy option (alternatively you can use the CTRL+C shortcut):

PRD cascading parameter 9

To paste the parameter right click on the Parameters… item and select the Paste option (alternatively you can use the CTRL+V shortcut):

PRD cascading parameter 10

This will create an identical copy of the sCountryName parameter.

PRD cascading parameter 11

 

Double click on the sCountryName parameter which is located at the bottom of the Parameters list, this will open the Edit Parameter… window. Before you make any changes to the options expand the connection folder on the left and then select the query which will populate this parameter, in this example it is the cityList query. Below is a screen shot of all the sCityName parameter options completed:

PRD cascading parameter 12

The options above are fairly self explanatory however here are descriptions of the most important options:

  • Name: I have decided to name this parameter sCityName
  • Type: The type of this parameter is a drop down
  • Query: The query that will populate this parameter is the cityList query which was created in step 1
  • Value and Display Name: I have set these both to the CITY field as the value is the same as the output I would like displayed in the drop down parameter
  • Value Type: The CITY field is a string
  • Mandatory: I have checked this option as a user must select a city before running the report

Step-3. Modify the Report Query

Now that the parameters and parameter queries have been created we are now ready to modify the report query (customerList) to use the new parameters. To modify the report query expand the data source connections under the Data tab and double click on the report query, customerList:

PRD cascading parameter 13

This will open the JDBC Data Source window and automatically highlight the customerList query:

PRD cascading parameter 14

To make the customerList query use the two new parameters created in step 2 you will need to add in two new conditions to the WHERE clause like:

PRD cascading parameter 15

 

Here is the above query in a format which you can copy and paste from below:

SELECT
 `customers`.`CUSTOMERNUMBER`,
 `customers`.`CUSTOMERNAME`,
 `customers`.`CITY`,
 `customers`.`COUNTRY`

FROM
 `customers`
WHERE
 `customers`.`COUNTRY` = ${sCountryName}
AND
 `customers`.`CITY`    = ${sCityName}

Now the report query will use the values of the sCountryName and sCityName parameters in its WHERE clause. Click on the OK button to close the JDBC Data Source window.

Step -4. Preview the Report

The last step is to preview the report, to do this you can click on the (preview icon) found in the top left hand corner or alternatively click on View > Preview menu item.

PRD cascading parameter 16

By default nothing will be displayed in the report and none of the drop down parameters will be populated (if you would like a country to be set when you first run your report set a value for the Default Value option in step 2 for the sCountryName parameter).

To check if the cascading parameters are working pick a country from the first drop down for example Australia:

PRD cascading parameter 17

 

The city drop down parameter should automatically populate with the cities which belong to Australia (even though I’m not sure if you would classify Glen Waverly as a city):

PRD cascading parameter 18

After selecting a city i.e. Melbourne click on the UPDATE button and now the report is filtered to customers who are located in the country Australia and the city Melbourne:

PRD cascading parameter 19

 

Thanks

Rohit Verma

Helical IT Solutions

D3 DayHour Charts integration with Jaspersoft

This blog talks about D3 DayHour Charts integration with Jaspersoft

All the reports are develop using ireport 5.5 professional and jasper server 5.5

As html component of jasper server does not load any scripts in the html component, we loaded the script in one of the decorator page (jsp page). The page is located at the location:

C:\Jaspersoft\jasperreports-server-5.5\apache-tomcat\webapps\jasperserver-pro\WEB-INF\decorators\decorator.jsp

In the page we included the scripts which we want to load. We added the following code in the jsp page at line no 46:

<script type=”text/javascript” language=”JavaScript” src=”${pageContext.request.contextPath}/scripts/d3.v3.min.js”></script>

The script to be added should be kept at location:

C:\Jaspersoft\jasperreports-server-5.5\apache-tomcat\webapps\jasperserver-pro\scripts

Meaning of DayHour Chart:-

This chart represents functioning of any particular thing during different hour of different days. These graph can be used to view variations in different situations.

DayHour Jaspersoft Integration

Integration with JasperServer:

The data which we use for developing the calendar view can be fetched from any database. The data fetched from database is stored in a variable and is then accessed in the html component using the same variable. Applying this of process makes the report dynamic instead of static. Few parameters can also be added in the report which can be used in query and/or html component.

Generally for these type of charts we pass a variable which contains required data containing date, hour and a value associated with that particular date and hour. The string is created according to JSON format, so that when accessed in script tag, can be easily converted to JSON object using eval function.

Any variable/parameter can be accessed as shown below:

“<script> var arr =”+$V{variable1}+” </script>”

Parameter in query:

Select * from table_name

where date between $P{parameter1}  and $P{parameter2}

 

The sample code of static chart can be found at:

http://bl.ocks.org/tjdecke/5558084

The steps on how to integrate it with jasperserver was discussed in my previous blog(D3 Integrating with Jasperserver).

 

 

Avi Dawra

Helical IT Solutions

D3 Skill Charts integration with Jaspersoft

This blog talks about D3 Skill Charts integration with Jaspersoft

All the reports are develop using ireport 5.5 professional and jasper server 5.5

As html component of jasper server does not load any scripts in the html component, we loaded the script in one of the decorator page (jsp page). The page is located at the location:

C:\Jaspersoft\jasperreports-server-5.5\apache-tomcat\webapps\jasperserver-pro\WEB-INF\decorators\decorator.jsp

In the page we included the scripts which we want to load. We added the following code in the jsp page at line no 46:

<script type=”text/javascript” language=”JavaScript” src=”${pageContext.request.contextPath}/scripts/d3.v3.min.js”></script>

The script to be added should be kept at location:

C:\Jaspersoft\jasperreports-server-5.5\apache-tomcat\webapps\jasperserver-pro\scripts

Meaning of Skill Chart:-

Skill chart represents the mapping of an entity to some other entity on a particular parameter. For example it can show mapping of employees to various technologies they know and the level of their understanding.

Integration with JasperServer:

The data which we use for developing the calendar view can be fetched from any database. The data fetched from database is stored in a variable and is then accessed in the html component using the same variable. Applying this of process makes the report dynamic instead of static. Few parameters can also be added in the report which can be used in query and/or html component.

SkillChart Jaspersoft Integration

Generally for these type of charts we pass a variable which contains required data containing list of quantity1(employees or people or students, etc.) and a value associated with different type of skills. The string is created according to JSON format, so that when accessed in script tag, can be easily converted to JSON object using eval function.

Integration with JasperServer:

Any variable/parameter can be accessed as shown below:

“<script> var arr =”+$V{variable1}+” </script>”

Parameter in query:

Select * from table_name

where date between $P{parameter1}  and $P{parameter2}

 

The sample code of static chart can be found at:

http://bl.ocks.org/derplau/8642419

The steps on how to integrate it with jasperserver was discussed in my previous blog(D3 Integrating with Jasperserver).

 

Avi Dawra

Helical IT Solutions

Jaspersoft Report Scheduler

This blog will talk about how to use Jaspersoft Report Scheduler, its configuration etc.

1.       Report Scheduler  :  It is an automated and iterative platform to email reports to anyone in PDF,CSV,Excel,….etc format.

 

2.       To  send email , it should be connected to outbound email server,  so for this it should be configured accordingly

a.       Go  to this  path : C:\Jaspersoft\jasperreports-server-5.5\apache-tomcat\webapps\jasperserver-pro\WEB-INF\js.quartz.properties

 

You will find the js.quartz.properties file like below screenshot:

Jaspersoft Report Scheduler 1

b.Change the yellow mark values to your required input

c.Restart the Jasper Server

 

3. Now Go to Report Wizard and scheduled your required job or report :

 

Below mentioned are the steps to be followed for doing the same:

Jaspersoft Report Scheduler 2

b. After this go to “create schedule “ tab , you will find like this:

Jaspersoft Report Scheduler 3

Here you can set how frequently you want to schedule this report and for what timezone

c. Then go for the “output options” tab in this window only[in the 2nd right] Here you have to specify in what format you want to send the report

Jaspersoft Report Scheduler 4d. Then go for “notification” in this window. To whom all we want to send the reports, the same is to be specified in this window

Jaspersoft Report Scheduler 5

Now You have successfully scheduled the reports…!!!

4. If we want to stop the scheduler, then go to this location

C:\Jaspersoft\jasperreports-server-5.5\apache-tomcat\webapps\jasperserver-pro\WEB-INF\applicationContext-report-scheduling.xml

Jaspersoft Report Scheduler 6

Remove the init-method=”start”

Restart the Jasperserver

Thanks

Rupam

Helical IT Solutions

Pentaho BI and Ctools – Introduction

Pentaho Overview:
It offer a suite of open source Business Intelligence (BI) product called Pentaho. Business Analytics provide, Data integration, OLAP Service, Reporting, Dash boarding, Data Mining, ETL Capabilities and BI platform. It is web application like Jaspersoft. It uses Tomcat server.

Pentaho BI Platform:
Pentaho BI Platform supports Pentaho end to end business intelligence capabilities and provide central access to your business information, with back end security, integration, scheduling, auditing and more. Pentaho BI Platform has been designed in such a way that it can be scaled to meet the needs of any size organization.

OLAP:-
OLAP Stands For Online Analytical Processing , OLAP is an Approach to answering a multi-dimensional analytical query swiftly.

Dash Boarding:-
Pentaho Dashboards give business users the critical information they need to understand and improve organizational performance. Provides immediate insight into individual, departmental, or enterprise performance by delivering key metrics in an attractive and intuitive visual interface

ETL (Extract Transform Load):-
ETL System are Commonly used to integrate data from multiple application, typically developed and supported by different vendors or hosted on separate computer Hardware.

Data integration:
Data integration involves combining data residing in different sources and providing users with a unified view of these data.

There are many tools present inside Pentaho, the same have been elaborated below,
Pentaho Tools -> Pentaho is having many tools like.

1) PRD – Pentaho Report Designer (Reporting)
Transform all your data into meaningful information tailored to your audience with Reporting, a suite of Open Source tools that allows you to create pixel-perfect reports of your data in PDF, Excel, HTML, Text, Rich-Text-File, XML and CSV. These computer generated reports easily refine data from various sources into a human readable form.

2) PSW – Pentaho Schema Workbench or Mondrian Schema Workbench
The Mondrian Schema Workbench is a designer interface that allows you to create and test Mondrian OLAP cube schemas visually. The Mondrian engine processes MDX requests with the ROLAP (Relational OLAP) schemas. These schema files are XML metadata models that are created in a specific structure used by the Mondrian engine. These XML models can be considered cube-like structures which utilize existing FACT and DIMENSION tables found in your RDBMS. It does not require that an actual physical cube is built or maintained; only that the metadata model is created.

3) PME – Pentaho Meta Editor
Pentaho Metadata Editor (PME) is a tool that allows you to build Pentaho metadata domains and relational data models. A Pentaho Metadata Model maps the physical structure of your database into a logical business model.

4) PDI – Pentaho Data Integration
Pentaho data integration prepares and blends data to create a complete picture of your business that drives actionable insights.

In Server -> There are many tools inside server like.
1) C-Tools ->CDA,CDF,CDE
C-Tools-> Used to create Dashboard.
CDA : Community Dashboard Data Access
CDA – Community Data Access is a community project that allows to fetch data in various formats from Pentaho BI platform. It will be accessed by simple url calls and supports the following datasources:
1)      SQL
2)      MDX
3)      Metadata
4)      Kettle

CDF : Community Dashboard Framework
Community Dashboard Framework (CDF) is a project that allows you to create friendly, powerful and fully featured Dashboards on top of the Pentaho Business Intelligence server.
CDE :Community Dashboard Editor
CDE and the technology underneath it (CDF, CDA and CCC) allows the development and deployment of Pentaho Dashboards in a fast and effective way.

2) Saiku Analytics:-  Explore Data,Visualize  etc

 

Please get in touch with us for any Pentaho related queries, Helical IT Solutions