Passing Blank from Select component - Dispaly All values when you pass Blank(say null) and display specific content when you pass specific value in Pentaho CDE

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.