Hi ..! This blog I have written to help you in creating dashboard report in Pentaho.
Things we will learn out of this blog -
_______________________________________
A. How to install pentaho
B. How to run pentaho bi-server (8080)
C. How to work with pentaho(regarding DB)
D. How to start coding
E. How to use different component -
F. Parameters - custom and simple
G. Table component
H. Chart component - Line chart, pie -chart , bar chart
I. Writing query for different chart
K. Drill Down : Linking one chart to other
a) in same dashboards
b) in different dashboards
L. Button component
M. Text Component
N. Putting images
O. Select component
P. SelectMulti component
Q. Fetching values from session
R. how to check the value of parameter by displaying it on screen
For xaction creation for Pentaho using Pentaho Designer Studio(PDS), click http://ranjan-xaction.blogspot.in/
First, we need to install Pentaho software.
A. How to install pentaho -
B. How to run pentaho bi-server (8080) -
The first thing after installation is DB connection and CDE. How to do DB connection is also mentioned below.
The CDE can be added with the means given there.For that first open Pentaho.
To open it, you need to open the folder where you have installed Pentaho. Go to Pentaho/bi-server, you will find two .sh files named as -
start-pentaho.sh
stop-pentaho.sh
Double click start-pentaho.sh ,then click 'run' in the appearing window. In order to stop pentaho, double click another one. Check whether pentaho server started or not by typing this url in browser -
http://localhost:8080/pentaho/
C. How to work with pentaho(regarding DB) -
Before you start using pentaho, you need to create user, you have to run admin console. For this, you need to open the Pentaho folder. Go to Pentaho/admin console, you will find these .sh file there -
start-pac.sh
stop-pac.sh
Double click start-pac.sh ,then click 'run' in the appearing window. In order to stop it, double click another
one. To check whether pentaho admin console started or not, type this url in browser -
http://localhost:8099/
A page will open which ask for username/password, by default username is 'admin' and password is 'password'.
As the web page get opens, you will find 'User' tab. In that tab click '+' to add new user and choose your password.
Next to the user tab is DB connection tab. Click on tab.
In the left frame of web page, it displays the existing connection which is established. Click '+' on the top of the left frame to add new connection. Enter details of DB as for example -
Test the connection by clicking 'Test' below -
After you get finish with creating user in admin console(localhost:8099/), you can login to Pentaho bi-server with that newly created username/password.
[Login Figure]
After login, the next page shows the workspace to create your dashboard for charts and reports. Here the journey begins for report creation.
In left frame, folders of pentaho workspace is shown. We can create your own folder by right clicking in the blank area of left frame. In order to create folder within another folder, right click on that folder within which you want to create new folder. Choose 'New Folder'.
(figure)
D. How to start coding -
--------- A Simple Dashboard ---------
Your workspace on page creating a dashboard is divided into three basic section -
1. LayOut
2. Component
3. DataSource
After completing these three, you view the result by clicking the fourth one 'Preview' in top-right.
1. Layout - Layout section is used to design the output screen in form of row and column. Chart,table,Label,etc all are set in row/column with the help of 'htmlObject' property in component section and 'Name' property of row/column of Layout section.
2. Components - This section is used to set the component which you want to use like Charts, table, etc. We need to set its properties which is defined later below.
3. DataSource - It is used to relate components like chart,table,etc with the query which fetches data from the database.
To preview something, design a layout only and click 'Preview'.
In layout section, click '=' button to add row. It is the first row of your dashboard, you can futher divide it in
two or more columns by selecting row which is to be divided and click '||' button.
For each column, you will find different properties in right side.
Instead of adding any column within row,yu can write HTML coding by clicking '<>' for HTML. Click right of 'HTML' property and write your code.
Now 'save' it. Choose your folder and name the file meaningful. Whatever you will write in 'Description' will appear in the below-left frame of pentaho page.
Click 'Preview' to see the result.
2. Custom Parameter
The usage of parameter is explained here. Suppose you need to display a record of student whose roll no. is '01', then
we have to execute query like - Select name,class,address,dob from Student_table where roll='01';
So here we will save roll in simple parameter which we can name as - 'paramRoll' or we can get its value from other part of code,
using Custom parameter. With custom parameter we can write this code in its js property -
paramRoll = Dashboards.getQueryParamter("roll");
where roll is the variable which is used to store value of roll no. in some part of code(like on click of table/chart, we can set paramter value, how its done is explained ahead, see 'paramNew' example) .
Here, we are using that value.
After this,in DataSource side, we will mention 'paramRoll' in the property 'Parameters' that we are going to use the parameter in query.
The query would be written in 'Query' property as :-
Select name,class,address,dob from Student_table where roll='${paramRoll}';
Hence, here parameter is declared in component, but is used in DataSource query.
G. Table component -
This is used to display data in form of table. Sometimes, charts contains very less value like 2.5,0.5 for which the bar
is not seen properly if there are large value also existing there. Let me tell you one scenario, suppose there is a bart chart for average marks of different class, where x-axis representing as 'Class' and y-axis as 'Avg Marks'.One can get the detail marks of all students in a class by clicking on the corresponding bar. The application will move to next chart , where marks of each student could be viewed for that class in form of another bar chart. Suppose some exams is not conducted for any class , and its average goes down to 2, while other have 80,90 i.e. big values. In this situation we sometimes may not view bar representing lowest average marks, and hence will be difficult to click too to move to the another chart representing all student marks detail for that class.
Frankly speaking, chart is not the thing to view the exact value, its just for comparison and analysing without going into depth.
If you need to view the exact value , then go for 'Table Component' which displays data in form of table.
The process of using it is simple, just create a row/column for table, give its name, define width and height.
Now, in component section create Table component , give the htmlObject value which you just given as name in 'LayOut' section.
Declare parameter if required for the query,
add that parameter as 'Listener' (its property) if parameter is used.
Go to DataSource section, create jdbc/jndi query, mention parameters if it is there, otherwise move to query property , write the query. Mention parameters if required in the query -
See the Preview.
We can set any new parameter 'paramNew' on clicking any field of any row of the displayed table. It can be used to show other chart/tables
in the query as parameter.The code is simple -
Dashboards.fireChange("paramNew",a.tableData[a.rowIdx][0]);
See a.tableData[a.rowIdx][0] , we used 2nd index as [0] for sending first field of the clicked row, similarily [1] for second one.
Instead of using a.tableData[a.rowIdx][0] , you can also use a.series but it will always send first field of the row clicked by the user, no matters, which field the user clicks. It will check the row only and will send first field value in form of 'paramNew' parameter.
H. Chart component - Line chart, pie -chart , bar chart -
Bar Charts/Line Charts - The process of creating charts is same as above in 'Layout' and 'Component'. Just create the row, give the same name in Component section's 'htmlObject' property of bar chart/pie chart. Declare parameters. If its waiting for any parameter to come then, mention that in 'Listener' and parameter property of chart.
The things are different in DataSource query part only, other things are same in DataSource, as listing the 'parameters' used in property
'Parameter'.
In the query, we can list the values in three parts only for Bar chart :-
1. series (s)
2. category (c)
3. value (v)
popularly called s,c,v. Example : suppose we want to list average student marks class wise with year, then we have to write query like -
SELECT CLASS_NAME,YEARS,AVG(TOTAL_MARKS)
FROM
DB_NAME.STUDENT
GROUP BY(CLASS_NAME,YEARS)
ORDER BY(CLASS_NAME,YEARS)
As you can see,here CLASS_NAME is at the place of 's', YEARS at place of 'c' and AVG(TOTAL_MARKS) as value.
We can list three things at a time to generate bar chart.
Write the query in 'Query' property like below in Sql Wizard -
Suppose, on clicking any CLASS_NAME(bar in the chart), we need to view all student marks detail for that class, then pass 'YEARS' as parameters. Write this js code in Onclick property of this Bar-Chart -
function sendYear(s,c,v) {
Dashboards.fireChange("paramYEARS",c); //since, YEARS is at the place of 'category'.
}
and write this query in DataSource and to appropriate parameter works -
SELECT CLASS_NAME,STUDENT_ID,TOTAL_MARKS
FROM
DB_NAME.STUDENT
WHERE
YEARS='${paramYEARS}'
GROUP BY(CLASS_NAME,STUDENT_ID)
ORDER BY(CLASS_NAME,STUDENT_ID)
But for Pie charts, we have two things only- c,v
1. category (c)
2. value (v)
Suppose on clicking on bar represing any student's total marks, we moving to a screen where pie-chart is display. In this pie-chart, subject wise marks is shown for that particular student, then in this case, the query can be -
SELECT SUBJECT_NAME,SUBJECT_MARKS
FROM DB_NAME.STUDENT
WHERE
YEARS='${paramYEARS}' AND
CLASS_NAME='${paramCLASSNAME}' AND
STUDENT_ID='${paramSTUDENTID}'
where we have to take years,class name and student id as parameter. The way of setting parameter studentId is like this -
Write the following js code on OnClick property of Bar Chart
function sendStudentId(s,c,v){
Dashboards.fireChange("paramSTUDENTID",c);
}
Why 'c' here, as I mentioned above we can have only 3 things in bar chart s,c and v. See the position of STUDENT_ID in the query of second Bar chart,
SELECT CLASS_NAME,STUDENT_ID,TOTAL_MARKS
(s) (c) (v)
Its in 2nd place.
So,if we need to send CLASS_NAME , then we can write this code in OnClick property of the 2nd bar chart which will send this parameter to the pie-chart.
function sendClassName(s,c,v){
Dashboards.fireChange("paramCLASSNAME",s);
}
Pie Chart should have this parameter in 'Listener' property.
All the parameters should be declared in Component section with the qualified name.
I. Writing query for different chart
K. Drill Down : Linking one chart to other
a) in same dashboards
b) in different dashboards
Charts can be made in same dashboard or different dashboards and can be linked togather. How they are linked..?
The answer is - they are linked with parameter. Yes, parameter is send from one to the other chart. So, we need to learn how to send parameter and receive parameters if charts are in same or different dashboard.
Same Dashboard-
Dashboard.fireChange() is enough in case of same dashboard to send parameter from 1st chart written in Onclick property of chart.
To recieve parameter for 2nd chart, create custom parameter and write this js code to receive parameter value.
function getStudentId(){
paramStudentId = Dashboards.getQueryParameter("studentId");
}
where paramStudentId is the name of custom parameter you have to create. Now in Listener property of chart, mention this parameter and go through the datasource works for query.
Different Dashboard -
If chart of 1st dashboard is sending parameter to a chart of 2nd dashboard, then it can be send as -
function sendParameter(s,c,v){
windows.location=url+'&studentId='+c; we can send more parameters by appending similarily as - '&variable_name='+ s or c or v
}
to send all three we can write -
window.location= url +'¶mClassName='+s+'¶mStudentId='+c+'¶mMarks='+v ;
Here url is the url of dashboard which will recieve this parameter in its custom parameter, how to get this is mentioned below.
Use same variable name in quote to recieve these parameters like -
paramStudentId = Dashboards.getQueryParameter("studentId");
if only one parameter is to be recieved. If three paramters is to recieve then -
paramClassName = Dashboards.getQueryParameter("paramClassName");
paramStudentId = Dashboards.getQueryParameter("studentId");
paramMarks = Dashboards.getQueryParameter("paramMarks");
The name of left side variable can be different but the name in double quote should be same as windows.location statement in Dashboard which is sending these paramter.
How to get this url..?
You can easily get this url , select your dashboard in left, click the first button, it will get open in form of output in a new tab. double click this tab, it will open this in different tab of browser from where you can copy the url , it will be like this -
http://localhost:8080/pentaho/content/pentaho-cdf-dd/Render?solution=RanjanResearch&path=&file=StudentMarksDashboard.wcdf
so ,the code would be like this -
function sendParameter(s,c,v){
var url='http://localhost:8080/pentaho/content/pentaho-cdf-dd/Render?solution=RanjanResearch&path=&file=StudentMarksDashboard.wcdf';
windows.location=url+'&studentId='+c; we can send more parameters by appending similarily as - '&variable_name='+ s or c or v
}
suppose if you are sending parameters from Table component, i.e. the row value on which the user clicked then write this code in OnClick property of Table component in dashboard which is sending parameter -
function sendStudentId(a){
window.location='http://localhost:8080/pentaho/content/pentaho-cdf-dd/Render?
solution=RanjanResearch&path=&file=StudentMarksDashboard.wcdf&studentId='+a.series+'&stdName='+a.tableData[a.rowIdx][1];
}
here in the query written to generate table has the first field : studentId and 2nd field as : Student Name. Here we are sending both.
a. series - it will send first column data.
a.tableData[a.rowIdx][1] - it will send second column data.
Parameter values will be received in the same way as in case of 'Same Dashboard'
paramClassName = Dashboards.getQueryParameter("paramClassName"); - in javascript property of 'paramClassName' custom parameter
paramStudentId = Dashboards.getQueryParameter("studentId"); - in javascript property of 'paramStudentId' custom parameter
paramMarks = Dashboards.getQueryParameter("paramMarks"); - in javascript property of 'paramMarks' custom parameter
in dashboard which one is receiving these.
L. Button component - This component is used to add button on screen. In ordet to use it, select "Others" in left side in "Components" section page. In it's 'Expression' property, JS code can be written to perform certain action.
M. Text Component - Its used to add Textboxes.
The function written in its property could be like this -
function func(){
return "<h>PopUp</h><p>Hello!! We can write html also</p>"
}
N. Putting Images -
You can use images in Dashboards. by simply adding row/column for image in Layout section. In layout only ,add html by clicking '<>' symbol.
As it is selected , its property will be shown in right side like name, HTML ,etc. Give the appropriate name, write the html code in HTML property -
<img src="images/image_name.gif" height="100" width="100"/>
Before that, paste the images within your pentaho folder-
pentaho/biserver-ce/pentaho-solutions/system/pentaho-cdf-dd/images
Similarily, adding hyperlink is also possible like in html at the same place using <a href="path of the Dasboard output"> Hyperlink Label</a>
Fig: Using image & HyperLink
O. Select Component - Its nothing but the combo box/drop down menu, i.e. selecting a single value from multiple value in drop down box.
The value selected from it can be taken in a parameter, which can be further used like in query if needed.
P. SelectMulti Component - In this case we can select multiple values, the only thing to take is in query . If you are taking these value in a parameter, then it may contain many values. So use 'IN' with statement query for this in DataSource.
e.g. - Select CLASS_NAME,YEARS,COUNT(STUDENT_ID)
FROM
DB_NAME.STUDENT
WHERE
YEARS IN '${paramYEARS}'
GROUP BY (CLASS_NAME,YEARS)
ORDER BY (CLASS_NAME,YEARS)
Here, paramYEARS is the parameter which is catching value from SelectMultiComponent.
This query can be used to display a line/bar chart representing the comparison of no. of students in classes for the selected years.
Query Component can be used to display value of paramter on screen if required -
Q. Fetching values from session -
To get session variable value in the parameter -
Write ${[session:variable_name]} in the value field of parameter in DataSource Section at the displayed below -
As for eg- If you want to set UserRole from session the
write - ${[session:UserRole]} in the value field of parameter
R. how to check the value of parameter by displaying it on screen
Use Query Component to display value of parameter, map it with Layout Name and HTML Object propery in Component section. Give the listener value as the name of that parameter whose value is to be displayed.
Bye ........God bless you...!!!
Things we will learn out of this blog -
_______________________________________
A. How to install pentaho
B. How to run pentaho bi-server (8080)
C. How to work with pentaho(regarding DB)
D. How to start coding
E. How to use different component -
F. Parameters - custom and simple
G. Table component
H. Chart component - Line chart, pie -chart , bar chart
I. Writing query for different chart
K. Drill Down : Linking one chart to other
a) in same dashboards
b) in different dashboards
L. Button component
M. Text Component
N. Putting images
O. Select component
P. SelectMulti component
Q. Fetching values from session
R. how to check the value of parameter by displaying it on screen
For xaction creation for Pentaho using Pentaho Designer Studio(PDS), click http://ranjan-xaction.blogspot.in/
First, we need to install Pentaho software.
A. How to install pentaho -
Installation steps:
1. Create the following directory
sudo mkdir /opt/pentaho
2. Go to the location of the .zip file biserver-ce-4.8.0-stable.zip and unzip.
sudo unzip biserver-ce-4.8.0-stable.zip
two files will be there after unzip - biserver-ce and administration-console
3. Move the file biserver-ce to /opt/pentaho
sudo mv biserver-ce /opt/pentaho/biserver-ce-4.8.0
4. Move the file administration-console to /opt/pentaho
sudo mv administration-console /opt/pentaho/administration-console-ce-4.8.0
5. go to the location /opt/pentaho
cd /opt/pentaho
6. Run the following three commands -
sudo ln -s biserver-ce-4.8.0 biserver-ce
sudo ln -s administration-console-ce-4.8.0 administration-console sudo chown -R pentaho:pentaho /opt/pentaho
7. Change password for pentaho user -
sudo passwd pentaho
enter new password:
retype new password:
sudo passwd pentaho
enter new password:
retype new password:
8. Go to the directory /opt/pentaho/biserver-ce and edit start-pentaho.sh sudo gedit start-pentaho.sh delete the line
CATALINA_OPTS="-Djava.awt.headless=true -Xms256m -Xmx768m -XX:MaxPermSize=256m
-Dsun.rmi.dgc.client.gcInterval=3600000 -Dsun.rmi.dgc.server.gcInterval=3600000"
provide the location of the jdk in the next line JAVA_HOME="/usr/lib/jvm/java-6-openjdk-i386"
9. save and close the file
10. go to the location /opt/pentaho/biserver-ce/tomcat/bin and make the catalina.sh executable. sudo chmod +x catalina.sh
11. Go to the directory cd /opt/pentaho/biserver-ce and run the following command sudo -u pentaho ./start-pentaho.sh
12. Open a web browser to http://localhost:8080. Click Evaluation Login and select a user type to login as user name: joe and password : password.
B. How to run pentaho bi-server (8080) -
The first thing after installation is DB connection and CDE. How to do DB connection is also mentioned below.
The CDE can be added with the means given there.For that first open Pentaho.
To open it, you need to open the folder where you have installed Pentaho. Go to Pentaho/bi-server, you will find two .sh files named as -
start-pentaho.sh
stop-pentaho.sh
Double click start-pentaho.sh ,then click 'run' in the appearing window. In order to stop pentaho, double click another one. Check whether pentaho server started or not by typing this url in browser -
http://localhost:8080/pentaho/
C. How to work with pentaho(regarding DB) -
Before you start using pentaho, you need to create user, you have to run admin console. For this, you need to open the Pentaho folder. Go to Pentaho/admin console, you will find these .sh file there -
start-pac.sh
stop-pac.sh
Double click start-pac.sh ,then click 'run' in the appearing window. In order to stop it, double click another
one. To check whether pentaho admin console started or not, type this url in browser -
http://localhost:8099/
A page will open which ask for username/password, by default username is 'admin' and password is 'password'.
As the web page get opens, you will find 'User' tab. In that tab click '+' to add new user and choose your password.
Next to the user tab is DB connection tab. Click on tab.
In the left frame of web page, it displays the existing connection which is established. Click '+' on the top of the left frame to add new connection. Enter details of DB as for example -
Test the connection by clicking 'Test' below -
After you get finish with creating user in admin console(localhost:8099/), you can login to Pentaho bi-server with that newly created username/password.
[Login Figure]After login, the next page shows the workspace to create your dashboard for charts and reports. Here the journey begins for report creation.
In left frame, folders of pentaho workspace is shown. We can create your own folder by right clicking in the blank area of left frame. In order to create folder within another folder, right click on that folder within which you want to create new folder. Choose 'New Folder'.
(figure)
D. How to start coding -
--------- A Simple Dashboard ---------
Your workspace on page creating a dashboard is divided into three basic section -
1. LayOut
2. Component
3. DataSource
After completing these three, you view the result by clicking the fourth one 'Preview' in top-right.
1. Layout - Layout section is used to design the output screen in form of row and column. Chart,table,Label,etc all are set in row/column with the help of 'htmlObject' property in component section and 'Name' property of row/column of Layout section.
2. Components - This section is used to set the component which you want to use like Charts, table, etc. We need to set its properties which is defined later below.
3. DataSource - It is used to relate components like chart,table,etc with the query which fetches data from the database.
To preview something, design a layout only and click 'Preview'.
In layout section, click '=' button to add row. It is the first row of your dashboard, you can futher divide it in
two or more columns by selecting row which is to be divided and click '||' button.
For each column, you will find different properties in right side.
Instead of adding any column within row,yu can write HTML coding by clicking '<>' for HTML. Click right of 'HTML' property and write your code.
Now 'save' it. Choose your folder and name the file meaningful. Whatever you will write in 'Description' will appear in the below-left frame of pentaho page.
Click 'Preview' to see the result.
F. Parameters - custom and simple
Parameters are of two types -
1. Simple Parameter2. Custom Parameter
The usage of parameter is explained here. Suppose you need to display a record of student whose roll no. is '01', then
we have to execute query like - Select name,class,address,dob from Student_table where roll='01';
So here we will save roll in simple parameter which we can name as - 'paramRoll' or we can get its value from other part of code,
using Custom parameter. With custom parameter we can write this code in its js property -
paramRoll = Dashboards.getQueryParamter("roll");
where roll is the variable which is used to store value of roll no. in some part of code(like on click of table/chart, we can set paramter value, how its done is explained ahead, see 'paramNew' example) .
Here, we are using that value.
After this,in DataSource side, we will mention 'paramRoll' in the property 'Parameters' that we are going to use the parameter in query.
The query would be written in 'Query' property as :-
Select name,class,address,dob from Student_table where roll='${paramRoll}';
Hence, here parameter is declared in component, but is used in DataSource query.
G. Table component -
This is used to display data in form of table. Sometimes, charts contains very less value like 2.5,0.5 for which the bar
is not seen properly if there are large value also existing there. Let me tell you one scenario, suppose there is a bart chart for average marks of different class, where x-axis representing as 'Class' and y-axis as 'Avg Marks'.One can get the detail marks of all students in a class by clicking on the corresponding bar. The application will move to next chart , where marks of each student could be viewed for that class in form of another bar chart. Suppose some exams is not conducted for any class , and its average goes down to 2, while other have 80,90 i.e. big values. In this situation we sometimes may not view bar representing lowest average marks, and hence will be difficult to click too to move to the another chart representing all student marks detail for that class.
Frankly speaking, chart is not the thing to view the exact value, its just for comparison and analysing without going into depth.
If you need to view the exact value , then go for 'Table Component' which displays data in form of table.
The process of using it is simple, just create a row/column for table, give its name, define width and height.
Now, in component section create Table component , give the htmlObject value which you just given as name in 'LayOut' section.
Declare parameter if required for the query,
add that parameter as 'Listener' (its property) if parameter is used.
Go to DataSource section, create jdbc/jndi query, mention parameters if it is there, otherwise move to query property , write the query. Mention parameters if required in the query -
See the Preview.
We can set any new parameter 'paramNew' on clicking any field of any row of the displayed table. It can be used to show other chart/tables
in the query as parameter.The code is simple -
Dashboards.fireChange("paramNew",a.tableData[a.rowIdx][0]);
See a.tableData[a.rowIdx][0] , we used 2nd index as [0] for sending first field of the clicked row, similarily [1] for second one.
Instead of using a.tableData[a.rowIdx][0] , you can also use a.series but it will always send first field of the row clicked by the user, no matters, which field the user clicks. It will check the row only and will send first field value in form of 'paramNew' parameter.
H. Chart component - Line chart, pie -chart , bar chart -
Bar Charts/Line Charts - The process of creating charts is same as above in 'Layout' and 'Component'. Just create the row, give the same name in Component section's 'htmlObject' property of bar chart/pie chart. Declare parameters. If its waiting for any parameter to come then, mention that in 'Listener' and parameter property of chart.
 | |
| The above figure represents the way to set parameters properties of Bar Chart as "Component" . |
The things are different in DataSource query part only, other things are same in DataSource, as listing the 'parameters' used in property
'Parameter'.
In the query, we can list the values in three parts only for Bar chart :-
1. series (s)
2. category (c)
3. value (v)
popularly called s,c,v. Example : suppose we want to list average student marks class wise with year, then we have to write query like -
SELECT CLASS_NAME,YEARS,AVG(TOTAL_MARKS)
FROM
DB_NAME.STUDENT
GROUP BY(CLASS_NAME,YEARS)
ORDER BY(CLASS_NAME,YEARS)
As you can see,here CLASS_NAME is at the place of 's', YEARS at place of 'c' and AVG(TOTAL_MARKS) as value.
We can list three things at a time to generate bar chart.
Write the query in 'Query' property like below in Sql Wizard -
Suppose, on clicking any CLASS_NAME(bar in the chart), we need to view all student marks detail for that class, then pass 'YEARS' as parameters. Write this js code in Onclick property of this Bar-Chart -
function sendYear(s,c,v) {
Dashboards.fireChange("paramYEARS",c); //since, YEARS is at the place of 'category'.
}
and write this query in DataSource and to appropriate parameter works -
SELECT CLASS_NAME,STUDENT_ID,TOTAL_MARKS
FROM
DB_NAME.STUDENT
WHERE
YEARS='${paramYEARS}'
GROUP BY(CLASS_NAME,STUDENT_ID)
ORDER BY(CLASS_NAME,STUDENT_ID)
But for Pie charts, we have two things only- c,v
1. category (c)
2. value (v)
Suppose on clicking on bar represing any student's total marks, we moving to a screen where pie-chart is display. In this pie-chart, subject wise marks is shown for that particular student, then in this case, the query can be -
SELECT SUBJECT_NAME,SUBJECT_MARKS
FROM DB_NAME.STUDENT
WHERE
YEARS='${paramYEARS}' AND
CLASS_NAME='${paramCLASSNAME}' AND
STUDENT_ID='${paramSTUDENTID}'
where we have to take years,class name and student id as parameter. The way of setting parameter studentId is like this -
Write the following js code on OnClick property of Bar Chart
function sendStudentId(s,c,v){
Dashboards.fireChange("paramSTUDENTID",c);
}
Why 'c' here, as I mentioned above we can have only 3 things in bar chart s,c and v. See the position of STUDENT_ID in the query of second Bar chart,
SELECT CLASS_NAME,STUDENT_ID,TOTAL_MARKS
(s) (c) (v)
Its in 2nd place.
So,if we need to send CLASS_NAME , then we can write this code in OnClick property of the 2nd bar chart which will send this parameter to the pie-chart.
function sendClassName(s,c,v){
Dashboards.fireChange("paramCLASSNAME",s);
}
Pie Chart should have this parameter in 'Listener' property.
All the parameters should be declared in Component section with the qualified name.
I. Writing query for different chart
K. Drill Down : Linking one chart to other
a) in same dashboards
b) in different dashboards
Charts can be made in same dashboard or different dashboards and can be linked togather. How they are linked..?
The answer is - they are linked with parameter. Yes, parameter is send from one to the other chart. So, we need to learn how to send parameter and receive parameters if charts are in same or different dashboard.
Same Dashboard-
Dashboard.fireChange() is enough in case of same dashboard to send parameter from 1st chart written in Onclick property of chart.
To recieve parameter for 2nd chart, create custom parameter and write this js code to receive parameter value.
function getStudentId(){
paramStudentId = Dashboards.getQueryParameter("studentId");
}
where paramStudentId is the name of custom parameter you have to create. Now in Listener property of chart, mention this parameter and go through the datasource works for query.
Different Dashboard -
If chart of 1st dashboard is sending parameter to a chart of 2nd dashboard, then it can be send as -
function sendParameter(s,c,v){
windows.location=url+'&studentId='+c; we can send more parameters by appending similarily as - '&variable_name='+ s or c or v
}
to send all three we can write -
window.location= url +'¶mClassName='+s+'¶mStudentId='+c+'¶mMarks='+v ;
Here url is the url of dashboard which will recieve this parameter in its custom parameter, how to get this is mentioned below.
Use same variable name in quote to recieve these parameters like -
paramStudentId = Dashboards.getQueryParameter("studentId");
if only one parameter is to be recieved. If three paramters is to recieve then -
paramClassName = Dashboards.getQueryParameter("paramClassName");
paramStudentId = Dashboards.getQueryParameter("studentId");
paramMarks = Dashboards.getQueryParameter("paramMarks");
The name of left side variable can be different but the name in double quote should be same as windows.location statement in Dashboard which is sending these paramter.
How to get this url..?
You can easily get this url , select your dashboard in left, click the first button, it will get open in form of output in a new tab. double click this tab, it will open this in different tab of browser from where you can copy the url , it will be like this -
http://localhost:8080/pentaho/content/pentaho-cdf-dd/Render?solution=RanjanResearch&path=&file=StudentMarksDashboard.wcdf
so ,the code would be like this -
function sendParameter(s,c,v){
var url='http://localhost:8080/pentaho/content/pentaho-cdf-dd/Render?solution=RanjanResearch&path=&file=StudentMarksDashboard.wcdf';
windows.location=url+'&studentId='+c; we can send more parameters by appending similarily as - '&variable_name='+ s or c or v
}
suppose if you are sending parameters from Table component, i.e. the row value on which the user clicked then write this code in OnClick property of Table component in dashboard which is sending parameter -
function sendStudentId(a){
window.location='http://localhost:8080/pentaho/content/pentaho-cdf-dd/Render?
solution=RanjanResearch&path=&file=StudentMarksDashboard.wcdf&studentId='+a.series+'&stdName='+a.tableData[a.rowIdx][1];
}
here in the query written to generate table has the first field : studentId and 2nd field as : Student Name. Here we are sending both.
a. series - it will send first column data.
a.tableData[a.rowIdx][1] - it will send second column data.
Parameter values will be received in the same way as in case of 'Same Dashboard'
paramClassName = Dashboards.getQueryParameter("paramClassName"); - in javascript property of 'paramClassName' custom parameter
paramStudentId = Dashboards.getQueryParameter("studentId"); - in javascript property of 'paramStudentId' custom parameter
paramMarks = Dashboards.getQueryParameter("paramMarks"); - in javascript property of 'paramMarks' custom parameter
in dashboard which one is receiving these.
L. Button component - This component is used to add button on screen. In ordet to use it, select "Others" in left side in "Components" section page. In it's 'Expression' property, JS code can be written to perform certain action.
M. Text Component - Its used to add Textboxes.
The function written in its property could be like this -
function func(){
return "<h>PopUp</h><p>Hello!! We can write html also</p>"
}
N. Putting Images -
You can use images in Dashboards. by simply adding row/column for image in Layout section. In layout only ,add html by clicking '<>' symbol.
As it is selected , its property will be shown in right side like name, HTML ,etc. Give the appropriate name, write the html code in HTML property -
<img src="images/image_name.gif" height="100" width="100"/>
Before that, paste the images within your pentaho folder-
pentaho/biserver-ce/pentaho-solutions/system/pentaho-cdf-dd/images
Similarily, adding hyperlink is also possible like in html at the same place using <a href="path of the Dasboard output"> Hyperlink Label</a>
Fig: Using image & HyperLink
O. Select Component - Its nothing but the combo box/drop down menu, i.e. selecting a single value from multiple value in drop down box.
The value selected from it can be taken in a parameter, which can be further used like in query if needed.
P. SelectMulti Component - In this case we can select multiple values, the only thing to take is in query . If you are taking these value in a parameter, then it may contain many values. So use 'IN' with statement query for this in DataSource.
e.g. - Select CLASS_NAME,YEARS,COUNT(STUDENT_ID)
FROM
DB_NAME.STUDENT
WHERE
YEARS IN '${paramYEARS}'
GROUP BY (CLASS_NAME,YEARS)
ORDER BY (CLASS_NAME,YEARS)
Here, paramYEARS is the parameter which is catching value from SelectMultiComponent.
This query can be used to display a line/bar chart representing the comparison of no. of students in classes for the selected years.
Query Component can be used to display value of paramter on screen if required -
Q. Fetching values from session -
To get session variable value in the parameter -
Write ${[session:variable_name]} in the value field of parameter in DataSource Section at the displayed below -
As for eg- If you want to set UserRole from session the
write - ${[session:UserRole]} in the value field of parameter
R. how to check the value of parameter by displaying it on screen
Use Query Component to display value of parameter, map it with Layout Name and HTML Object propery in Component section. Give the listener value as the name of that parameter whose value is to be displayed.
Bye ........God bless you...!!!
