ORGANIZATION ADMIN PERMISSION IN LIFERAY

In liferay, Administrator of any Organization is able to delete, update or change user details of any other organization which is not secure to organizations. User Details of any other organization should not display to any other organization admin.

To avoid the above scenario, it is required to change permissions and role for organizations admin.

Role Keywords

Administrator:   Administrator can create organization, assign or add users to organization, update or remove user, organization. It means Poweruser has all the authority of liferay portal.

Organization Administrator: Organisaton Administrator is able to see organization pages, he can edit organization page, but he is not able to edit his private and public pages.

 

How can we change Role of organization admin

  1. Login as Admin (Administrator role).
  2. Go to Control Panel.
  3. Select User and Organization menu from portal panel of left side to window.

Liferay user and role management

 

4. Select Organization in which you want to change the role of admin.
E.g. Selecting New York Organization

Liferay user and role managment

Liferay user and role managment

We can see there are 2 user in New York Organization, NYC (Admin) and NY1USER (organization User). Here NYC is Administrator for this organization.

5.Click Actions (NYC in this example) -> Edit

liferay user and role management

liferay user and role management

6. Select Roles tab from User Information under Admin menu [NYC Admin] which is a right side of window

liferay 4

7. We can see Current role of this user in Regular Roles. (Current is Administrator)

liferay 5

8. Remove Administrator from Regular Role and assign New Role from “+Select” option. When we click on Select Hyperlink, pop-up window appears. From this window, we can see 2 Roles- Administrator and Power User. [It is not that only above mentioned roles can be seen, there are chances of seeing other regular roles too]

liferay 6

9. Select Power User.

 

10. Now, from Organization Roles view. Click on “Select” Hyperlink and select organizations role from pop-up window.

liferay 7

11. Select Organization Administrator.

liferay 8

Now, we can see we have two roles for Organization Administrator, Power User and Organization Administrator

12. Click Save.

Now, when same organization admin logins into his/her account user can’t get access to any other organization details from control panel.

 

Before applying changes to organization administrator role.

liferay 9

After applying changes to organization administrator role.

liferay 10

He is not able to see any organization except his organization.

Finally we have secured Organization’s details from any other organization’s admin.

Learn Scheduling using Quartz

Why do we need scheduling?
Scheduling is needed if you want to automate the repetition of a task at specific intervals or        particular date. You could of course manually watch the time and execute your task, albeit an inefficient task. If anyone wants to do particular task after every 3 seconds, then who wants to watch the computer every 3 seconds just to hit the Enter key? No one.

History of the Quartz Framework

Quartz was created by James House, who envisioned the first conceptual pieces of the framework in 1998. These included the concept of a queue of jobs and a pool of threads to process the jobs, although probably in an unrecognizable form by most of today’s Quartz users.

 

QUARTZ:-

Before you can use the scheduler, it needs to be instantiated. To do this, you use a SchedulerFactory. Some users of Quartz may keep an instance of a factory in a JNDI store, others may find it just as easy (or easier) to instantiate and use a factory instance directly (such as in the example below).

Once a scheduler is instantiated, it can be started, placed in stand-by mode, and shutdown. Note that once a scheduler is shutdown, it cannot be restarted without being re-instantiated. Triggers do not fire (jobs do not execute) until the scheduler has been started, nor while it is in the paused state.

Here’s a quick snippet of code, that instantiates and starts a scheduler, and schedules a job for execution:

SchedulerFactory schedFact = new org.quartz.impl.StdSchedulerFactory();

 

Scheduler sched = schedFact.getScheduler();

 

sched.start();

 

 

 

 

// define the job and tie it to our HelloJob class

JobDetail job = newJob(HelloJob.class)

.withIdentity(“myJob”, “group1”)

.build();

 

 

 

// Trigger the job to run now, and then every 40 seconds

Trigger trigger = newTrigger()

.withIdentity(“myTrigger”, “group1”)

.startNow()

.withSchedule(simpleSchedule()

.withIntervalInSeconds(40)

.repeatForever())

.build();

 

// Tell quartz to schedule the job using our trigger

sched.scheduleJob(job, trigger);

 

 

By Prashansa Kumari

Helical IT Solutions

Adding new chart in Helical Dashboard Insight (HDI)

Adding new chart in Helical Dashboard Insight (HDI)

1) Adding the Pie Chart in HDI:-
HDI use D3 (Data-Driven Documents) library. D3 allows you to bind arbitrary data to a Document Object Model (DOM), and then apply data-driven transformations to the document. For example, you can use D3 to generate an HTML table from an array of numbers.
For adding the Pie chart in the HDI following steps should be followed:-
1) EFW file:- EFW contain the Title, author, description, Template name, visibility of the Dashboard.

2) EFWD File: – EFWD file contain the Data Source Connection Properties such as connection id and connection type.It also contain Url of the connection to the database, User name and Password to the Database

The DataSource Details used in our demo is shown as below:-


<DataSources>
        <Connection id="1" type="sql.jdbc">
           <Driver>com.mysql.jdbc.Driver</Driver>
           <Url>jdbc:mysql://192.168.2.9:3306/sampledata</Url>
            <User>devuser</User>
            <Pass>devuser</Pass>
        </Connection>
    </DataSources>

Data Map contains Map id and connection and connection Type. Map id is same as that specified in the EFWVF. Query for the Data Map and the Parameter to be used is specified in the Tags and Parameter in the Tags.


<DataMap id="2" connection="1" type="sql" >
       <Name>Query for pie chart component - Order Status</Name>
		<Query>
			<![CDATA[
					select STATUS, count(ORDERNUMBER) as totalorders 
					from ORDERS
					where STATUS in (${order_status})
					group by STATUS;  
			]]>
                </Query>

       <Parameters>
          <Parameter name="order_status" type="Collection" default="'Shipped'"/>
       </Parameters>
</DataMap>

3)EFWVF File :-
In EFWVF file we first set the chart id the chart we set the chart properties. For Pie Chart we set the chart Properties between the tags. The properties such as Chart name, Chart type, Chart Data Source. In chart section we specify the chart script.

In Chart Script we set the below variables to customize the Pie chart
Setting Up the Chart


var placeHolder = "#chart_1";
var chartHeader = "Orders By status";
var width = 300,
height = 300;

The query returns 2 columns – ‘STATUS’ and ‘totalorders’.

As in the JasperReport we set Catagary Expression, Value Expression, Tool Tip for the pie chart,same way the Below variables are set accordingly.


var category = "d.data.STATUS";
var values="d.totalorders";
var tooltip = "\"Total orders with status \"+ d.data.STATUS+\" are \"+d.data.totalorders";
var legendValues = "d.STATUS";

You may change the below script directly for further customization


function angle(d)
{
var a = (d.startAngle + d.endAngle) * 90 / Math.PI - 90;
return a > 90 ? a - 180 : a;
}
$(placeHolder).addClass('panel').addClass('panel-primary');
var radius = Math.min(width, height) / 2;
var color = d3.scale.ordinal().range(["#1f77b4", "#ff7f0e", "#2ca02c", "#d62728", "#9467bd", "#8c564b", "#e377c2", "#7f7f7f", "#bcbd22", "#17becf"]);
var arc = d3.svg.arc().outerRadius(radius - 10).innerRadius(0);
var pie = d3.layout.pie().sort(null).value(function(d) { return eval(values); });
var heading = d3.select(placeHolder).append("h3").attr("class", "panel-heading").style("margin", 0 ).style("clear", "none").text(chartHeader);
Creating The SVG Element
var svg = d3.select(placeHolder).append("div").attr("class", "panel-body").append("svg")
.attr("width", width)
.attr("height", height)
.append("g")
.attr("transform", "translate(" + width / 2 + "," + height / 2 + ")");

Drawing The Pie


var g = svg.selectAll(".arc")
.data(pie(data))
.enter().append("g")
.attr("class", "arc");
g.append("path")
.attr("d", arc)
.style("fill", function(d) { return color(eval(category)); });
g.append("text")
.attr("transform", function(d) {
d.outerRadius = radius -10;
d.innerRadius = (radius -10)/2;
return "translate(" + arc.centroid(d) + ")rotate(" + angle(d) + ")"; })
.attr("dy", ".35em")
.style("text-anchor", "middle")
.style("fill", "White")
.text(function(d) { return eval(category); });

Drawing The Lable and Title


g.append("title")
.text(function(d){ return eval(tooltip)});
var legend = d3.select(placeHolder).select('.panel-body').append("svg")
.attr("class", "legend")
.attr("width", 75 * 2)
.attr("height", 75 * 2)
.selectAll("g")
.data(data)
.enter().append("g")
.attr("transform", function(d, i) { return "translate(50," + (i * 20) + ")"; }); legend.append("rect")
.attr("width", 18)
.attr("height", 18)
.style("fill", function(d){return color(eval(legendValues))});
legend.append("text")
.attr("x", 24)
.attr("y", 9)
.attr("dy", ".35em")
.text (function(d) { return eval(legendValues); });
]]>

4) HTML File:-
HTML file name should be the same that specified in the EFW file under the Template Section.
In HTML File On Top we specify links of the external resources such as :-


<script src="getExternalResource.html?path=UI_Testing/dimple.js"></script>

Below it we create the Divisions for proper alignments and the Position where the Pie Chart should be placed.


<div class="row">
    <div class="col-sm-5">
       <div id="supportChartObj4"></div>
    </div>
    <div class="col-sm-5">
       <div id="supportChartObj5"></div>
    </div>
</div>

Below the Division section,we have Script section we specify the parameters and the chart.

Parameter Creation:-


var select =
{
name: "select",
type: "select",
options:{
multiple:true,
value : 'STATUS',
display : 'STATUS'
},
parameters: ["order_status"],
htmlElementId: "#supportChartObj1",
executeAtStart: true,
map:1,
iframe:true
};

Chart Creation:-


var chart = {
name: "chart",
type:"chart",
listeners:["order_status"],
requestParameters :{
order_status :"order_status"
},
vf : {
id: "1",
file: "Test.efwvf"
},
htmlElementId : "#supportChartObj4",
executeAtStart: true
};

And all the parameters and chart specified in HTML file are passed to the dashboard.init.

This Way Pie chart is Created.

pie

2)Adding the Pie Chart in HDI:-

1)EFW file:- EFW contain the Title, author, description, Template name, visibility of the Dashboard.

2)EFWD File: – EFWD file contain the Data Source Connection Properties such as connection id and connection type.It also contain Url of the connection to the database, User name and Password to the Database

The DataSource Details used in our demo is shown as below:-


<DataSources>
        <Connection id="1" type="sql.jdbc">
           <Driver>com.mysql.jdbc.Driver</Driver>
           <Url>jdbc:mysql://192.168.2.9:3306/sampledata</Url>
            <User>devuser</User>
            <Pass>devuser</Pass>
        </Connection>
    </DataSources>

Data Map contains Map id and connection and connection Type. Map id is same as that specified in the EFWVF. Query for the Data Map and the Parameter to be used is specified in the Tags and Parameter in the Tags.
Ex:-


<DataMap id="2" connection="1" type="sql" >
       <Name>Query for pie chart component - Order Status</Name>
		<Query>
			<![CDATA[
					select STATUS, count(ORDERNUMBER) as totalorders 
					from ORDERS
					where STATUS in (${order_status})
					group by STATUS;  
			]]>
                </Query>

       <Parameters>
          <Parameter name="order_status" type="Collection" default="'Shipped'"/>
       </Parameters>
</DataMap>

3)EFWVF File :-
In EFWVF file we first set the chart id the chart we set the chart properties. For Pie Chart we set the chart Properties between the tags. The properties such as Chart name, Chart type, Chart Data Source. In chart section we specify the chart script.

In Chart Script we set the below variables to customize the Pie chart

Setting Up the Chart


var placeHolder = "#chart_1";
var chartHeader = "Orders By status";
var margin = {top: 20, right: 30, bottom: 30, left: 70},
width = 500 - margin.left - margin.right,
height = 400 - margin.top - margin.bottom;

The query returns 2 columns – ‘STATUS’ and ‘totalorders’.

As in the JasperReport we set Catagary Expression, Value Expression, Tool Tip for the pie chart,same way the Below variables are set accordingly.


var category = "d.STATUS";
var values="d.totalorders";
var tooltip = "\"Total orders with Status \"+ d.STATUS+\" are\"+d.totalorders";

You may change the below script directly for further customization


$(placeHolder).addClass('panel').addClass('panel-primary');
var x = d3.scale.ordinal().rangeRoundBands([0, width], .1);
var y = d3.scale.linear().range([height, 0]);
var xAxis = d3.svg.axis().scale(x).orient("bottom");
var yAxis = d3.svg.axis().scale(y).orient("left");
var heading = d3.select(placeHolder).append("h3").attr("class", "panel
heading").style("margin", 0 ).style("clear", "none").text(chartHeader);
var chart = d3.select(placeHolder).append("div").attr("class", "panel-body")
.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 + ")");

Drawing The Bar Chart


x.domain(data.map(function(d) { return eval(category); }));
y.domain([0, d3.max(data, function(d) { return eval(values); })]);
        chart.append("g")
        .attr("class", "x axis")
	.attr("transform", "translate(0," + height + ")")
	.call(xAxis);

	chart.append("g")
	.attr("class", "y axis")
	.call(yAxis);

	chart.selectAll(".bar")
	.data(data)
	.enter().append("rect")
	.attr("class", "bar")
	.attr("x", function(d) { return x(eval(category)); })
	.attr("y", function(d) { return y(eval(values)); })
	.attr("height", function(d) { return height - y(eval(values)); })
	.attr("width", x.rangeBand());
	
        chart.selectAll(".bar")
	.append("title")
	.text(function(d){ return eval(tooltip)});

4) HTML File:-
HTML file name should be the same that specified in the EFW file under the Template Section.
In HTML File On Top we specify links of the external resources such as :-


<script src="getExternalResource.html?path=UI_Testing/dimple.js"></script>

Below it we create the Divisions for proper alignments and the Position where the Pie Chart should be placed.


<div class="row">
    <div class="col-sm-5">
       <div id="supportChartObj4"></div>
    </div>
    <div class="col-sm-5">
       <div id="supportChartObj5"></div>
    </div>
</div>

Below the Division section,we have Script section we specify the parameters and the chart.

Parameter Creation:-


var select =
{
name: "select",
type: "select",
options:{
multiple:true,
value : 'STATUS',
display : 'STATUS'
},
parameters: ["order_status"],
htmlElementId: "#supportChartObj1",
executeAtStart: true,
map:1,
iframe:true
};

Chart Creation:-


var chart = {
name: "chart",
type:"chart",
listeners:["order_status"],
requestParameters :{
order_status :"order_status"
},
vf : {
id: "2",
file: "Test.efwvf"
},
htmlElementId : "#supportChartObj4",
executeAtStart: true
};

And all the parameters and chart specified in HTML file are passed to the dashboard.init.

This Way Bar chart is Created.

Bar Chart

Steps to migrate oracle with pentaho

Step 1:-

Run script as DB admin.

Script is available in location:- biserver-ce\data\oracle10g.

Modify configuration file:-

  1. applicationContext-spring-security-hibernate.properties.

Location:-

pentaho-solutions\system\applicationContext-spring-security-hibernate.properties.

original code:-

jdbc.driver=org.hsqldb.jdbcDriver

jdbc.url=jdbc:hsqldb:hsql://localhost:9001/hibernate

jdbc.username=hibuser

jdbc.password=password

hibernate.dialect=org.hibernate.dialect.HSQLDialect

Modified code:-

dbc.driver=oracle.jdbc.OracleDriver

jdbc.url=jdbc:oracle:thin:@localhost:1521/sysdba

jdbc.username=hibuser

jdbc.password=password

hibernate.dialect=org.hibernate.dialect.Oracle10gDialect

  1. hibernate-settings.xml

Location:- pentaho-solutions\system\hibernate\hibernate-settings.xml.

Original code

<config-file>system/hibernate/hsql.hibernate.cfg.xml</config-file>

Modified code:-

<config-file>system/hibernate/oracle10g.hibernate.cfg.xml</config-file>

oracle10g.hibernate.cfg.xml:-

Location:-

pentaho-solutions\system\hibernate\ oracle10g.hibernate.cfg.xml

Do not need to change any code in this file.. Just need to check everything is perfect or not

 

   <!– Oracle 10g Configuration –>

<property name=”connection.driver_class”>oracle.jdbc.driver.OracleDriver</property>

<property name=”connection.url”>jdbc:oracle:thin:@localhost:1521/sysdba

</property>

<property name=”dialect”>org.hibernate.dialect.Oracle10gDialect</property>

<property name=”connection.username”>hibuser</property>

<property name=”connection.password”>password</property>

<property name=”connection.pool_size”>10</property>

<property name=”show_sql”>false</property>

<property name=”hibernate.jdbc.use_streams_for_binary”>true</property>

<!– replaces DefinitionVersionManager –>

<property name=”hibernate.hbm2ddl.auto”>update</property>

<!– load resource from classpath –>

<mapping resource=”hibernate/oracle10g.hbm.xml” />

 

  1. quartz.properties:-

Location:-

pentaho-solutions\system\quartz\quartz.properties

Original Code

org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate

Modified Code:-

org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.oracle.OracleDelegate

4.context.xml:-

Location:-

tomcat\webapps\pentaho\META-INF\context.xml

Original Code

<Resource name=”jdbc/Hibernate” auth=”Container” type=”javax.sql.DataSource”

factory=”org.apache.commons.dbcp.BasicDataSourceFactory” maxActive=”20″ maxIdle=”5″

maxWait=”10000″ username=”hibuser” password=”password”

driverClassName=”org.hsqldb.jdbcDriver” url=”jdbc:hsqldb:hsql://localhost/hibernate

validationQuery=”select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES” />

 

<Resource name=”jdbc/Quartz” auth=”Container” type=”javax.sql.DataSource”

factory=”org.apache.commons.dbcp.BasicDataSourceFactory” maxActive=”20″ maxIdle=”5″

maxWait=”10000″ username=”pentaho_user” password=”password”

driverClassName=”org.hsqldb.jdbcDriver” url=”jdbc:hsqldb:hsql://localhost/quartz

validationQuery=”select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES”/>

 

Modified Code:-

<Resource validationQuery=”select 1 from dual”

url=” jdbc:oracle:thin:@localhost:1521/sysdba

driverClassName=”oracle.jdbc.OracleDriver” password=”password”

username=”hibuser” maxWait=”10000″ maxIdle=”5″ maxActive=”20″

factory=”org.apache.commons.dbcp.BasicDataSourceFactory”

type=”javax.sql.DataSource” auth=”Container” name=”jdbc/Hibernate”/>

                       

<Resource validationQuery=”select 1 from dual”

url=” jdbc:oracle:thin:@localhost:1521/sysdba

driverClassName=”oracle.jdbc.OracleDriver” password=”password”

username=”quartz” maxWait=”10000″ maxIdle=”5″ maxActive=”20″

factory=”org.apache.commons.dbcp.BasicDataSourceFactory”

type=”javax.sql.DataSource” auth=”Container” name=”jdbc/Quartz”/>

6. repository.xml

Location of the file: pentaho-solutions\system\jackrabbit\repository.xml.

Comment this code means (<! – – every thing here – -> )

Active means: Remove comment

i)                    FileSystem part

Comment this code

<FileSystem class=”org.apache.jackrabbit.core.fs.local.LocalFileSystem”>

     <param name=”path” value=”${rep.home}/repository”/>

</FileSystem>

Active this code:-

<FileSystem class=”org.apache.jackrabbit.core.fs.db.OracleFileSystem”>

   <param name=”url” value=”jdbc:oracle:thin:@localhost:1521“/>

   <param name=”user” value=”jcr_user”/>

   <param name=”password” value=”password”/>

   <param name=”schemaObjectPrefix” value=”fs_repos_”/>

   <param name=”tablespace” value=”pentaho_tablespace”/>

</FileSystem>

ii)                  DataStore part

Comment this code

<DataStore class=”org.apache.jackrabbit.core.data.FileDataStore”/>

Active this code:-

<DataStore class=”org.apache.jackrabbit.core.data.db.DbDataStore”>

<param name=”url” value=”jdbc:oracle:thin:@localhost:1521/sysdba”/>

<param name=”driver” value=”oracle.jdbc.OracleDriver”/>

<param name=”user” value=”jcr_user”/>

<param name=”password” value=”password”/>

<param name=”databaseType” value=”oracle”/>

<param name=”minRecordLength” value=”1024″/>

<param name=”maxConnections” value=”3″/>

<param name=”copyWhenReading” value=”true”/>

<param name=”tablePrefix” value=””/>

<param name=”schemaObjectPrefix” value=”ds_repos_”/>

</DataStore>

iii)                Security part in the FileSystem Workspace part

Comment this code:-

<FileSystem class=”org.apache.jackrabbit.core.fs.local.LocalFileSystem”>

<param name=”path” value=”${wsp.home}”/>

</FileSystem>

Active this code:-

<FileSystem class=”org.apache.jackrabbit.core.fs.db.OracleFileSystem”>

<param name=”url” value=”jdbc:oracle:[email protected]:1521/sysdba”/>

<param name=”user” value=”jcr_user”/>

<param name=”password” value=”password”/>

<param name=”schemaObjectPrefix” value=”fs_ws_”/>

<param name=”tablespace” value=”pentaho_tablespace”/>

</FileSystem>

iv)       PersistenceManager part

Comment this code:-

<PersistenceManager class=”org.apache.jackrabbit.core.persistence.pool.H2PersistenceManager”>

<param name=”url” value=”jdbc:h2:${wsp.home}/db”/>

<param name=”schemaObjectPrefix” value=”${wsp.name}_”/>

</PersistenceManager>

Active This Code:-

<PersistenceManager class=”org.apache.jackrabbit.core.persistence.bundle.OraclePersistenceManager”>

<param name=”url” value=”jdbc:oracle:thin:@localhost:1521/sysdba”/>

<param name=”driver” value=”oracle.jdbc.OracleDriver”/>

<param name=”user” value=”jcr_user”/>

<param name=”password” value=”password”/>

<param name=”schema” value=”oracle”/>

<param name=”schemaObjectPrefix” value=”${wsp.name}_pm_ws_”/>

<param name=”tablespace” value=”pentaho_tablespace”/>

</PersistenceManager>

v)       FileSystem Versioning part

Comment This Code:-

<FileSystem class=”org.apache.jackrabbit.core.fs.local.LocalFileSystem”>

<param name=”path” value=”${rep.home}/version” />

</FileSystem>

Active This Code:-

<PersistenceManager class=”org.apache.jackrabbit.core.persistence.bundle.OraclePersistenceManager”>

<param name=”url” value=”jdbc:oracle:thin:@localhost:1521/sysdba”/>

<param name=”driver” value=”oracle.jdbc.OracleDriver”/>

<param name=”user” value=”jcr_user”/>

<param name=”password” value=”password”/>

<param name=”schema” value=”oracle”/>

<param name=”schemaObjectPrefix” value=”pm_ver_”/>

<param name=”tablespace” value=”pentaho_tablespace”/>

</PersistenceManager>

 

 

Stopping HSQL db start up

In web.xml file

Comment or delete this code (Commenting is preferable)

<!– [BEGIN HSQLDB DATABASES] –>

<context-param>

<param-name>hsqldb-databases</param-name>

<param-value>[email protected]/../data/hsqldb/sampledata,[email protected]/../data/hsqldb/hibernate,[email protected]/../data/hsqldb/quartz</param-value>

</context-param>

<!– [END HSQLDB DATABASES] –>

 

Also comment this code

<!– [BEGIN HSQLDB STARTER] –>

<listener>

<listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener</listener-class>

</listener>

<!– [END HSQLDB STARTER] –>

You have done with integrating pentaho 5.0.1 CE with Oracle

Now login to the Pentaho server .

URL:  http://localhost:8080/pentaho

Username/Password : Admin/password

Using MDX Query in Pentaho CDE

This blog will talk about Using MDX Query in Pentaho CDE

In Layout Section:

Design the layout of your Dashboard, from scratch or from a template – while defining the layout you can apply styles and add HTML elements as text or images.

Pentaho CDE MDX Query

In Component Section:

In side Component layout we are using different types of chart and table or other things. According to component we will provide Data source name and Html Object and some other properties you can also use if you needed.

Pentaho CDE MDX Query

In Data source Panel:

In side data source panel we are we are using driver like jdbc or jndl according to requirement. In My example I am using Jndl data source and Mondrian Schema like Sample data and query like:

Select NON EMPTY {[Measures].[Actual]} ON COLUMNS,

NON EMPTY Crossjoin(Hierarchize(Union(Union(Crossjoin({[Region].[All Regions]},
{[Department].[All Departments]}), Crossjoin({[Region].[All Regions]},
[Department].[All Departments].Children)), Crossjoin([Region].[All Regions].Children,
{[Department].[All Departments]}))), {[Positions].[All Positions]}) ON ROWS

from [Quadrant Analysis]

Note: Below Image you can see the Process.

Pentaho CDE MDX Query

Preview Panel:

After Applying all those thing what I mentioned in above information the output will show like these. This output is same as OLAP creation output in MDX query. You can compare both output.

Pentaho CDE MDX Query

Pentaho CDE MDX Query

Note: When you are start working in MDX Query in Pentaho CDE. You have to first know how to use OLAP Kettle -MDX OLAP. After that you can use that MDX query inside Pentaho CDE (Pentaho Community Dashboard).

Nikhilesh

Helical IT Solutions

Reading multiple properties files in spring 3 web MVC

    Reading multiple properties files in spring 3 web MVC

Spring allows us to externalize string literals in its context configuration files into external properties files, in order to separate application specific settings from framework specific configuration. Spring will read all the properties files declared by PropertyPlaceholderConfigurer bean to resolve the placeholders at application’s start up time.

Simple Example

Declare a PropertyPlaceholderConfigurer bean in spring’s application context as follows:
That tells spring to load the properties file name project.properties in the class path. An exception will be thrown if spring could not find specified property file.
The project.properties file should contain the name and value pair for example
Name = xyz
Filepath = C:/Test/Setting.xml
Host = localhost
User = testuser

By default spring look for the properties files in the application directory. So if we specify

Then it will find project.properties file under WEB-INF directory of the application in case of spring MVC application.
We can use prefix classpath: to tell spring load properties file in the application’s classpath. For example:

In the case of spring application the project.properties file should be present in WEB/INF/classes directory or in source directory src in eclipse IDE.

Use the prefix file:/// or file: to load property file from file system.

Spring context load the project.properties file from D:/Applicatin/Config else throws the exception if file is not present in the specified path.
Loading multiple properties files
Spring allows us to specify multiple properties files for the PropertyPlaceholderConfigurer as follows

classpath:/processor.properties
classpath:/datasource.properties

</property

We can add as many as properties file in list as shown above. Spring context will load all the properties files specified in list as shown above at the application start up time.

Loading Properties files values in spring controller
Suppose you have properties file as follows
Name = xyz
Filepath = C:/Test/Setting.xml
Host = localhost
User = testuser

We can use the variable read from properties files in spring controller by declaring @Component annotation in controller class for example:
@Controller
@Component
public class Controller{
@Value(“${Name}”)
String Name;
@Value(“${Filepath }”)
String Filepath;
@Value(“${Host }”)
String Host;
@Value(“${User }”)
String User;
}

The value of properties file automatic assign to declared variable by spring framework we can use these variables inside the application.

Muqtar Ahmed
Helical IT Solutions

Define JNDI for Pentaho BA server 5.0

Define JNDI for Pentaho BA server 5.0

Note: For illustration I’m showing Oracle 11g configuration. Replace the Resource name, username, password, driverClassName, and url parameters, or any relevant connection settings.

Add Driver

Place appropriate driver for Oracle 11g which is ojdbc6-11.2.0.2.jar to this directory for the BA Server: /pentaho/server/biserver-ee/tomcat/lib/.

Note: There should be only one driver for one database in this directory. Ensure that there are no other versions of the same vendor’s driver in this directory. If there are, back up the old driver files and remove them to avoid version conflicts.

Specify JNDI connections

  1. Stop server & BA server.
  2. Edit the /tomcat/webapps/pentaho/WEB-INF/web.xml file.
  3. At the end of the <web-app> element, in the same part of the file where you see <!– insert additional resource-refs –>, add this XML snippet.
  4. myDatasource
    jdbc/ myDatasource
    javax.sql.DataSource
    Container

  5. Save & Close web.xml
  6. Open & modify this file /tomcat/webapps/pentaho/META-INF/context.xml.
  7. Add this XML snippet anywhere in context.xml
  8. Save & Close context.xml
  9. Open simple jndi
  10. Open & Modify this file biserver-ce/pentaho-solutions/system/simple-jndi/jdbc.properties.
  11. Add these lines
  12. myDatasource /type=javax.sql.DataSource
    myDatasource /driver= oracle.jdbc.OracleDriver
    myDatasource /url= jdbc:oracle:thin:@//localhost:1521/sysdba
    myDatasource /user=dbuser
    myDatasource /password=password

  13. Save & Close jdbc.properties.
  14. Delete the pentaho.xml filed located in the /tomcat/conf/catalina/directory.
  15. Start tomcat & BA server.

Generating Jasper reports in different formats using a JSON file as a Data Source

This article helps the reader how to fill a report with data from a JSON data source ( Generating Jasper reports in different formats using a JSON file as a Data Source ). JSON stands for Java Script Object Notation. Suppose that we have a file in the following format. JSON File The above file consists of a JSON array. The above JSON array consists of five objects of type students separated by commas. The array notation uses []. The JRXML file consists of the following data to generate the report regarding the student details. This JRXML is generated using iReport 5.6.0.

Fields

The JRXML detail tag consists of the following tags. TextFields

Using the above JSON file data we can fill the JRXML and generate reports in different formats like PDF, HTML, XLS, CSV etc. The Jasper’s API converts the .jrxml into .jasper file and out of the .jasper file a jasperprint object can be made, which can be converted into various formats for printing purposes. Using Jasper’s API requires the following jars in your class path. Add the following dependencies to your pom.xml so that maven will automatically manage the dependencies for you. Dependencies

Dependencies-2

Java code to generate the different reports.

Code

The above code consists of an interface JRDataSource, which is implemented by JsonDataSource. The JsonDataSource’s constructor takes a file that consists of the data. For the sake of readability I have used another method which takes 4 arguments – one, the type of file to be generated; two, the JRXML file; three, the data source; four the destination of the file where the file has to be saved.

Code 2

The above printToFile method takes the data from the data source and fills it in the JRXML and generated the .jasper file which is actually the jasper print object. The jasper print object is used to generate different types of files based on the fileType argument. The above piece of code also demonstrates the use of the new API related to JRXlsExporter and JRCsvExporter, which are actually used to generate excel and csv files respectively.

Rajasekhar

Helical IT Solutions

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