Filter data by user on Pentaho CDE


  1. Create a dashboard on CDE
  2. Create a Simple Parameter for example param_user
  3. On Layout Perspective –> Add Code Snippet. We will use  Dashboards.context.user
    $( document ).ready(function() {
    var param_user=Dashboards.context.user;
    Dashboards.fireChange("param_user",param_user);
    });
    
  4. Now on datasources create a query with param_user as parameter
    SELECT * FROM
    tablename
    WHERE user=${param_user}
    
  5.  Use your query in a CDE component (Table, Chart, Text Component)

New Ctools releases 12.07.19 (Lots of them!) by Pedro Alves


Article By Pedro Alves blogspot
New Ctools releases 12.07.19 (Lots of them!)
Lots of new releases, including some new ones!

CDE Release 12.07.19

Major changes:

Allow plugins to register extra cde components
CPF usage as component basis
New Popup Component and Reference
Added new component based on textarea html object.

Full Changelog:

Implemented [REDMINE 281] – Allow plugins to register extra cde components
Support for output index in external CDAs
CPF usage as component basis
New Popup Component and Reference
Added new component based on textarea html object. Added properties of max length and size to text input component edition
Fixed [REDMINE 856] – CDE allows non-privileged users access to edit dashboards.
Fixed [REDMINE 721] – SelectMulti Isn’t populated, feeded by another SelectMulti Component
Fixed [REDMINE 424] – CDA creation sometimes adds a prefix / to catalog that disables Mondrian role mapping
Added start and end Date parameters to DatePicker Component, which can be controlled by the dateFormat parameter.
FileExplorer returns different info from getExtension in fileBased and db rep. Adding both
Allow Icons (.ico) to be downloaded
[PATCH] BulletChart support for the tooltipFormat option
disable edit option in olap wizard file open; prompts a bit less green
SiteMap Component update

CDF Release 12.07.19

Major changes:

Add clippedText AddIn
add column headers support to group headers addIn
Stacked bars with line: plot panel clipping was activated due to forcing orthoFixedMin to 0 and resulted in cut dots

Full changelog:

[Redmine-207] – tableComponent oLanguage fix
Turned pvc.defaultColorScheme public (for readonly access)
[FEAT] Added a hook for plugging in a default chart colorscheme (CvK Feb. 2012)
Add clippedText AddIn
add column headers support to group headers addIn
Added support in fireChange for components to be able to update without triggering increment/decrementRunningCalls.
Changed the behaviour of the clickable “span” for removal. Before, we needed to click on the “x” to get the selection out, now you just need to click on the span of that selection
output-index for external cda’s
jqueryui: use ctrlKey instead of metaKey for multiple selection
set datepicker defaults only if it exists
CDF tutorial needs that both zip and xml files be downloadable by CDF
Patch to fix HeatGrid performance problem.
Added queryState to query component.
MapComponent fix for FF13 – FooComponent as first object in classNames[]
[PATCH] Stacked bars with line: plot panel clipping was activated due to forcing orthoFixedMin to 0 and resulted in cut dots
Added underscore, backbone and mustache
circle addin border width was not working on Chrome
[REDMINE 721, REDMINE 771] selectMultiComponent null-related issues; impromptu css less green
Added override to input[type=text] margin and dataTables min-height
[PATCH] BulletChart support for the tooltipFormat option.
Added TextareaInputComponent
Added erichynds Multiselect JQuery Plugin Files
Fix the Clean template, which wasn’t actually as clean as all that.
changed table click handler to use rawData instead of query.lastResult so we can access postFetch changes
[REDMINE 206] – Dates are always used in iso format on parameters regardless of display date format

CDA Release 12.07.19

Major upgrades:

Now using CPF (Community Plugin Framework)
Integration with CDV

Full changelog:

Fixed [REDMINE CDA797] – ClassLoader issue using CdaQueryComponent in FusionContentGenerator
Fixed [REDMINE CDA787] – use cde ext-editor for cda files
Introduced CPF As plugin base
New Robochef Version – Memory optimization
CDV Integration
print stack trace for hazelcast serialization errors, +MdxDataAccess.ExtraCacheKey.toString()
path issue in cda editor
-Util.isNullOrEmpty, using StringUtils; build was changed to use 4.5 2 commits ago
pathParams->requestParams
cde-editor corrections
Use cde as editor if available; +RepositoryAccess and some cleaning

CDB Release 12.07.19

First release!

CDC Release 12.07.19

First release!

CDV Release 12.07.19

First release!

Beginners Guide to CDF (Community Dashboard Framework)


The Dashboard will allow the user to search for a companies. Companies are arranged in a 3-level hierarchy, and the Dashboard will allow users to navigate down the hierarchy, so if the user clicks a company in one level, the levels below it will show it’s subsidiaries and the levels above it will show its parent companies. When ever a company is selected (in any of the levels) the graphs on the bottom display additional data for the company.

To accomplish the task, we are going to use Pentaho BI Server and CDF, which is an open source dashboard framework developed by Pedro Alves from WebDetails.

So first thing first, we have to set up our infrastructure, the good news is that CDF is now an integral part of Pentaho CE, so we just need to download and install Pentaho 3.6:

1 $ wget http: //sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/3.6.0-stable/biserver-ce-3.6.0-stable.tar.gz/download
2 $ tar xzvf biserver-ce-3.6.0-stable.tar.gz
3 $ cd biserver-ce
4 $ ./start-pentaho.sh

Great, you can see that it is working by navigating to port 8080 on the localhost. You can log-in using the default user and password (joe/password), there are many samples, including CDF samples and also CDF documentation is available through the system.

I am going to publish the system on the web so I have to edit tomcat/conf/server.xml and to change the Connector port from 8080 to just 80, and also to edit tomcat/webapps/pentaho/WEB-INF/web.xml and modify the base-url parameter to the url I am going to use.

Next step is to create a datasource to the database, this can be done using the administrator colsole, but for me it is easier to just editing tomcat/webapps/pentaho/META-INF/context.xml and add this:

1 <Resource name="jdbc/datasource" auth="Container" type="javax.sql.DataSource"
2 factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
3 maxWait="10000" username="biserver" password="nottelling"
4 driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/dw"/>

At this point don’t forget to restart the server.

OK, next step is to create a solution and a dashboard, the easiest way is copy-paste from the examples.

1 $ cd pentaho-solutions
2 $ cp -r cdf-samples/20-samples/charts mySolution
3 $ cd mySolution

The solution is defined by the index.xml file and we can edit the index.properties file to give it a name and a description. The Dashboard is defined by the .xcdf file which we should also rename and edit to our own names.

Now you can go back to the web-browser, in order to see the new solution and new Dashboard you have to “refresh the repository cache” (there’s a menu option for that and also a refresh icon) and Voilà – a new Dashboard was born.

The content of the dashboard is stored in a template.html file, there are several parts to it which I would explain herein.

First there is the html template itself which defines the structure of the Dashboard. In my case I needed a search box, 3 multi-select-boxes, two bar-charts and one gauge which I am going to layout as follows:

01 <table align="center" style="border: 0px solid>
02 <tr>
03 <td valign="top">Search:<div id="search_object"></div></td>
04 </tr>
05 <tr>
06 <td valign="top">A Level:</td>
07 <td valign="top">B Level:</td>
08 <td valign="top">C Level:</div></td>
09 </tr>
10 <tr>
11 <td valign="top"><div id="a_object"></div></td>
12 <td valign="top"><div id="b_object"></div></td>
13 <td valign="top"><div id="c_object"></div></td>
14 </tr>
15 <tr>
16 <td valign="top"><div id="channel_object"></div></td>
17 <td valign="top"><div id="dialChart_object"></div></td>
18 <td valign="top"><div id="product_object"></div></td>
19 </tr>
20 </table>

The next step is to create the different components using CDF, the search box is declared using javascript:

01 var query;
02 var searchComponent =
03 {
04 name: "searchComponent",
05 type: "textInputComponent",
06 htmlObject: "search_object",
07 executeAtStart: true,
08 parameter: "query",
09 tooltip: "enter partial company name and press Enter",
10 postChange: function(){ searchFunction(query); }
11 }

The htmlObject property is very important, it tells CDF where in the html template the component is going to reside.

The postChange trigger function is going to be called when the users enters a new search query, and would be described in more details later.

Each of the 3 multi-select-boxes is declared in a similar way:

01 var a;
02 var aComponent =
03 {
04 name: "aComponent",
05 type: "selectMultiComponent",
06 parameters:[],
07 parameter:"a",
08 size: "10",
09 valueAsId: false,
10 htmlObject: "a_object",
11 executeAtStart: true,
12 queryDefinition: aQueryDefinition,
13 listeners: ["event"],
14 postChange: function(){aFunction(a);}
15 };

The queryDefinition object, as you must have already gueesed, is responsible for querying the data that is going to populate the component, it will be described in more details below.

Bar-charts are created using this code:

01 var channelComponent =
02 {
03 name: "channelComponent",
04 type: "jFreeChartComponent",
05 listeners:["selectedRegionMeasure"],
06 chartDefinition: channelDefinition,
07 htmlObject: "channel_object",
08 executeAtStart: true,
09 listeners: ["event"]
10 }

The listeners property lists event names to which this component listens, when ever such event is fired the component will refresh it’s data.

Finally the code for a gauge component:

01 var overallComponent =
02 {
03 name: "overallComponent",
04 type: "dialComponent",
05 listeners:["departmentMeasure"],
06 chartDefinition: dialChartDefinition,
07 htmlObject: "dialChart_object",
08 executeAtStart: true,
09 listeners: ["event"]
10 }

Now that all the components are ready we can go ahead and initiate the Dashboard with this call:

1 Dashboards.init([searchComponent,aComponent,bComponent,cComponent,overallComponent,channelComponent,productComponent]);

CDF includes many components such as Buttons, Combo-boxes, Menus, Tables, Maps, FlashCharts, Pivot views, Traffic lights and more… check out the online documentation for the full list, descriptions and samples.

The next thing we want to define is the behavior of the Dashboard, in my case the state of the Dashboard is going to be managed using 4 variables and is going to use just one event to refresh all the views when the state changes:

1 var queryStatus;
2 var aStatus;
3 var bStatus;
4 var cStatus;

Now we can go back and look at the postChange function of the search component, which stores the query and resets any previous state. The call to fireChanges is will dispatch the event through all our views causing it to change.

1 function searchFunction(val) {
2 queryStatus = val;
3 aStatus = "";
4 bStatus = "";
5 cStatus = "";
6 Dashboards.fireChange("event",queryStatus);
7 }

And for each of the multi-select components, the code is just as simple:

1 function aFunction(val) {
2 queryStatus = "";
3 aStatus = val;
4 bStatus = "";
5 cStatus = "";
6 Dashboards.fireChange("event",queryStatus);
7 }

Now we are approaching the last part of the development, which is to define the data queries in either SQL or MDX. The query is constructed based on the state of the dashboard.

The queryDefinition object for the multi-selects is:

01 var aQueryDefinition = {
02 queryType: 'sql',
03 jndi: "datasource",
04 query: function(){
05 var q= "SELECT distinct A_ID, A_NAME " +
06 "FROM company " +
07 "WHERE 1=1 " +
08 (queryStatus==""?"":("AND A_NAME like '%"+queryStatus+"%' ")) +
09 (aStatus==""?"":("AND A_ID="+aStatus+" ")) +
10 (bStatus==""?"":("AND B_ID="+bStatus+" ")) +
11 (cStatus==""?"":("AND C_ID="+cStatus+" ")) +
12 "LIMIT 100";
13 return q;
14 }
15 }

As you can see, we are using an SQL query to the datasource to pull a list of all the A level companies that match to the criteria that is defined by the status of the Dashboard, so if the status is a query we will pick only companies whose names contains the search query and if the status is a company we will pick only companies on that branch of the hierarchy.

Similarly, the queryDefinition of the bar-charts is:

01 var channelDefinition = {
02 width: 300,
03 height: 250,
04 chartType: "BarChart",
05 datasetType: "CategoryDataset",
06 is3d: "false",
07 isStacked: "true",
08 includeLegend: "false",
09 foregroundAlpha: 0.7,
10 queryType: 'sql',
11 jndi: "datasource",
12 title: "Channel Revenues:",
13 query: function(){
14 var query = "SELECT name, sum(Revenue) " +
15 "FROM company LEFT JOIN revenue ON company.id=COMPANY_SEQ LEFT JOIN channel ON CHANNEL_SEQ=channel.id " +
16 "WHERE " +
17 (aStatus==""&&bStatus==""&&cStatus==""?"1=0 ":"1=1 ") +
18 (aStatus==""?"":("AND A_ID="+aStatus+" ")) +
19 (bStatus==""?"":("AND B_ID="+bStatus+" ")) +
20 (cStatus==""?"":("AND C_ID="+cStatus+" ")) +
21 "GROUP BY id ORDER BY sum(Revenue) DESC LIMIT 10";
22 //query = "SELECT 'London',100";
23 return query;
24 }
25 }

This query will return the total revenues by channel of all the companies that are on the branch of the selected company, if no company is selected the query will return no results.

The gauge query will select the average value over all the companies in the branch.

01 var dialChartDefinition = {
02 width: 300,
03 height: 200,
04 chartType: "DialChart",
05 queryType: 'sql',
06 is3d: 'true',
07 jndi: "datasource",
08 title: "Overall",
09 intervals: [40,60,100],
10 includeLegend: true,
11 query: function(){
12 var query = " SELECT avg(ovrall) FROM overall LEFT JOIN company ON COMPANY_SEQ=id "+
13 " WHERE " +
14 (aStatus==""&&bStatus==""&&cStatus==""?"1=0":"1=1") +
15 (aStatus==""?"":("AND A_ID="+aStatus+" ")) +
16 (bStatus==""?"":("AND B_ID="+bStatus+" ")) +
17 (cStatus==""?"":("AND C_ID="+cStatus+" "));
18 //query= "SELECT 75";
19 return query;
20 }
21 }

That would be all. We have defined the Dashboard’s view by suppling the html template and the components to populate it, the Dashboard’s controller was defined using the components call back functions and the update event and the Dashboard’s data model by defining the dynamic queries to the datasource. The Dashboard is ready to be used. Enjoy!