Hi Folks,
Its always a fun to me to explore a functionality in CDE.!
In this post you will see how to pass Blank ( Assume a NULL) value to a CCC Bar chart component that should display all bars in graph.
Few questions ?
1) Did you ever observe Blank/NULL (Say empty value) in a String type select component ?
2) Did you ever pass Blank value to display the whole content ? ( to a chart or to a table component).
( NOT "All" value in drop down)
NOTE : Click on images for the content on it
Here is a USE CASE :
* Display employee "Position Titles" on X axis and "Salaries" on Y-Axis according to "Education Level".
i.e., filter the employee data with "Education Level".
When you pass "BLANK" value you should get the whole content and when you pass "Specific education level" get the data for that specific level. (Here the employee content is : "Position Titles" on X-axis and "Salaries" on Y-axis )
Software Environment for this example :
1) Pentaho BA Server 6.1 CE
2) Pentaho CTools (16.x)
3) Jaspersoft foodmart database - PostgreSQL
1) Dashboard Design
Design the dashboard as shown in the final output image. (Not explaining how to work with rows and columns and create html objects - Assuming that you are aware in designing basic layouts).
2) Components section :
Creating parameter :
Parameter : param_education_level
Default value : ' ' (Single quotes & one space in between)
Creating Select Component :
Name : select_dept_id
Parameter : param_education_level
Listner : param_education_level
Data source : query_param_dept_id
HTML Object : col_param
Creating bar chart component :
3) Data sources section :
Preparing SQL for parameter :
SELECT education_level FROM
(
(SELECT ' ' :: text AS education_level FROM employee limit 1)
UNION ALL
(SELECT DISTINCT education_level::text AS education_level FROM employee )
) a ORDER BY a.education_level
Preparing SQL for Bar Chart Component
SELECT
position_title,
SUM(salary) sal
FROM employee
WHERE
(education_level :: text = ${param_education_level} OR ${param_education_level}=' ')
GROUP BY
position_title
NOTE : Check CDA preview for the confirmation of data source connection and queries result sets.
Save the dashboard and preview it.
TEST- 1: Passing Blank Value
TEST-2 : Passing "Partial High School" value from "Education Level" parameter
Few Points & Limitations on this example :
1) ORDER BY for integer inputs may not be sorted (Its depends on how you concatenate ' ' with integer value in SQL).
2) Some times for the first load of the dashboard it is displaying "No Data". (Oops.!).
3) When you map a parameter to a "Select Component" (Single Select) you wont get any special symbol or blank space in the drop down. ( One can find -- dashed lines in Jaspersoft Server for input controls to pass it as NULL value).
4) This example is not intended to replace "All" functionality in the drop down.
Download Example :
Click Me
Thank you :-) Hope it helps someone.
Its always a fun to me to explore a functionality in CDE.!
In this post you will see how to pass Blank ( Assume a NULL) value to a CCC Bar chart component that should display all bars in graph.
Few questions ?
1) Did you ever observe Blank/NULL (Say empty value) in a String type select component ?
2) Did you ever pass Blank value to display the whole content ? ( to a chart or to a table component).
( NOT "All" value in drop down)
NOTE : Click on images for the content on it
Here is a USE CASE :
* Display employee "Position Titles" on X axis and "Salaries" on Y-Axis according to "Education Level".
i.e., filter the employee data with "Education Level".
When you pass "BLANK" value you should get the whole content and when you pass "Specific education level" get the data for that specific level. (Here the employee content is : "Position Titles" on X-axis and "Salaries" on Y-axis )
Software Environment for this example :
1) Pentaho BA Server 6.1 CE
2) Pentaho CTools (16.x)
3) Jaspersoft foodmart database - PostgreSQL
1) Dashboard Design
Design the dashboard as shown in the final output image. (Not explaining how to work with rows and columns and create html objects - Assuming that you are aware in designing basic layouts).
2) Components section :
Creating parameter :
Parameter : param_education_level
Default value : ' ' (Single quotes & one space in between)
Creating Select Component :
Name : select_dept_id
Parameter : param_education_level
Listner : param_education_level
Data source : query_param_dept_id
HTML Object : col_param
Creating bar chart component :
3) Data sources section :
Preparing SQL for parameter :
SELECT education_level FROM
(
(SELECT ' ' :: text AS education_level FROM employee limit 1)
UNION ALL
(SELECT DISTINCT education_level::text AS education_level FROM employee )
) a ORDER BY a.education_level
Preparing SQL for Bar Chart Component
SELECT
position_title,
SUM(salary) sal
FROM employee
WHERE
(education_level :: text = ${param_education_level} OR ${param_education_level}=' ')
GROUP BY
position_title
NOTE : Check CDA preview for the confirmation of data source connection and queries result sets.
Save the dashboard and preview it.
TEST- 1: Passing Blank Value
Few Points & Limitations on this example :
1) ORDER BY for integer inputs may not be sorted (Its depends on how you concatenate ' ' with integer value in SQL).
2) Some times for the first load of the dashboard it is displaying "No Data". (Oops.!).
3) When you map a parameter to a "Select Component" (Single Select) you wont get any special symbol or blank space in the drop down. ( One can find -- dashed lines in Jaspersoft Server for input controls to pass it as NULL value).
4) This example is not intended to replace "All" functionality in the drop down.
Download Example :
Click Me
Thank you :-) Hope it helps someone.
