Tip : Pie or Bar Chart : No visulization when the sql result is having all 0 values in Jasper iReport

Hi Guys,

Recently I came across a situation on plotting the 0 values result set on a pie chart and failed to produce the chart with out leaving any message.

Alternatively I have shown a message saying that "All the values in the result set are zero so can not plot the visualization".

Of course, this is not a big matter to blog but I can say that this is a best practice in report designing and would like narrate for the one who wish to follow best practices.

Your end-client can easily understand when the chart is having all zero values.

Example :
Query : SELECT field1, field2, field3 FROM dummyTable
Output : 
field1      field2  field3
----------      ---------   ---------
  0              0           0

For instance assume you are showing data  on pie chart and given expressions in 3 different series lets say "series1", "series2" , "series3".

Now, if you see the preview of the report, you will be getting nothing in output as your query resultset has all values as 0

 

Drag and drop the "Static Text Field" over the chart from palette section and write your message(message is : All values are zero so can not plot the visualization) and Print this field when ever the values are zero.

For doing this, you have to Write similar like below expression for "Static Text Field's" PrintWhenExpression

($F{field1}==0) && ($F{field2}) && ($F{field3})

Observation or Preview.
Now select some input controls for which you will get result set  0 .. You will get the below similar like output.

That's it... When ever your query result set is having all 0's you will be displaying message saying that can not plot visualization.


Sadakar
BI developer

Tool Differnces b/w Jasper and Pentaho - Next post will teach in - detail differnces b/w the tools and features

Hi Guys,

This post will talk about the major differences and features of the soft wares in open source BI world using Jasper & Pentaho.

Please click below link of mine

Differences b/w jasper & Pentaho

Thank you.

Sadakar
BI developer 

Table Component Tooltip in Pentaho CDE - Showing hidden column values as tool tip on 1st column or any column of table component

Hi Guys,

A requirement make you go in-depth of subject. Few days back I have got a requirement to show tool tip on table component column(s).

I'd like to thank you to pentaho forum for their support in working with java script/jQuery in achieving this functionality.

Requirement :
Take a single query of 4 columns, hide 4th column and it's values on table component and show this hidden column values as tool tip on the first column of table component.

In other words, Using CDA get 4 columns output but get only 1st 3 columns on table component and show the 4th column(hidden column) values as tool tip on the 1st column of table component.  

Example Environment :
Pentaho C-Tools of TRUNK version(after 14 version trunk)
Pentaho BA server 5.1.0 stable
PostgreSQL foodmart database for querying purpose.

Here I'd like to concentrate on the core part of the example hence design your layout.

What you can learn in this post ?
1. Query component usage.
2. How to store result set in a parameter using query component.
3. How to hide columns using java script/jQuery script on table component.
4. How to make use of tipsy function to show tooltip on table component.

Layout section :
Design as per your requirement
Data source section :
Query 1 :
SELECT employee_id,full_name,gender,position_title from employee limit 10
Sample image

 Query output:

Components panel section
Here for this post you need to work out with a parameter, a query component and table component.
first let's have a parameter : Name it as tooltipValues

Query component :
You will be storing the query output in parameter using query component.

Write below Code in PostFetch Expressionof Query Component :

function f2(data){

   var tooltipValues = [];

   for(var i=0; i <data.resultset.length; i++){
           tooltipValues.push(data.resultset[i][3]);
   }

   alert(tooltipValues);
   alert("length="+data.resultset.length);

    return tooltipValues;
}
Now the table Component
Add the table component, give a name to it, and the data source for it,(in this example give the same data source i.e., Datasource=query1_table) & the HTML Object here it is Panel_1

Write below code in Post Execution of table component
function myTip()
{

$('th:nth-child(4),td:nth-child(4)').hide();

  $('#Panel_1 tr td:first-child').each(
  function(index){
    $(this).tipsy({title: function(){ return tooltipValues[index][3] ; }});
  }
);

}
 
In the above code
Using hide() function you are hiding the result of 4th column
#Panel_1 is the HTML Object
Using tipsy() function you are showing the 4th column values as tooltip on the 1st column.
td:first-child, you are telling the jQuery to show the tooltip values on 1st column of the table component.


That's it you are done...!!!

Save the dashboard and see the preview.

Preview/Observations :
Image 1 :

Image 2:

Image-3:

 
Source Code of this example : 
Download here


Thank you for reading this article. 







 

CGG dial chart component example

Hi Guys,

This post talk about how to implement CGG dial component in pentaho CDE.

There I need to find few more functionalists on it. For instance : Tool tip of the dial value, Outer layer color change, customizing dial shape and/or color and many more.

This post only talk about how available CGG Dial component example.

Refer this link also : 

http://forums.pentaho.com/showthread.php?165485-How-to-implement-gauge-chart-using-Pentaho-CDE-component

Core part :
You need to store the result of query in a variable using Query component.
Define a query and query component and make use of .

Change the query component Priority from 5 to less of it. for example Query component priority 1 or 2

Increase size of the font by modifying dial.js file located at
/opt/pentaho/biserver.ce/pentaho-solutions/system/pentaho-cdf-dd/resources/custom/components/cgg/charts/dial.js 

Press Ctrl+F and serach for the below line
.font("23px sans-serif")

Note that px(pixel) should be given after numeric font size, otherwise the changes will not take place.  

 Find the development image for easy understanding.

Image-1

 Image-2

 Image-3

Image-4 Output

 

 References :
  
http://forums.pentaho.com/showthread.php?132522-How-did-I-got-the-CGG-Dial-Chart-work

http://forums.pentaho.com/showthread.php?159306-Dial-chart-component-is-not-working-in-Pentaho-5-1-CDE&p=368997#post368997

http://forums.pentaho.com/showthread.php?159422-Dial-component-Problem-in-pentaho-5-0

http://forums.pentaho.com/showthread.php?159935-CGG-Dial-Component-Bug

DateRange Component Example in pentaho CDE

Hi Guys,

A small work out on Date Range component in pentaho CDE.

• As shown in images create 2 parameters of "Date Parameters" type from Generic section of Components Panel. 
• lets say one for start date and other for to date.
• Give default values for both of them.
• Click on "Date Range input componet" from selects section of Components panel. 
• These date parameters you have to use in your query and the query dates should be converted to String type before you use.
• i.e., Sample query written in postgresql(foodmart db of jasperserver) is

SELECT p.brand_name "Brand Name",
    SUM(sf7.store_sales) "Store Sales",
    --SUM(sf7.store_cost) "Store Cost",
    SUM(sf7.unit_sales) "Unit Sales"
FROM sales_fact_1997 sf7
INNER JOIN product p ON sf7.product_id=p.product_id
INNER JOIN time_by_day t ON sf7.time_id=t.time_id
INNER JOIN customer c ON sf7.customer_id=c.customer_id
WHERE
to_char(t.the_date,'YYYY-MM-DD') >= ${param1_FromDate}
AND to_char(t.the_date, 'YYYY-MM-DD') <= ${param2_ToDate}
AND c.country=${param3_Country} AND c.state_province=${param4_State}
GROUP BY p.brand_name
ORDER BY 2 DESC,3 DESC
limit 5

Observations:
My default values for start date and end date are :  2012-01-01 and 2012-01-07

Test 1 : Default values

Test 2 : Today

 

Test 3 : Date Range

In this way you can make use of Date Range Component.

Thank you.

Dashboard Example in pentaho CDE with bootstrap css - The beginners guide

Hi Guys,
Recently, I was developing a dashboard in Jasper Server 5.5 Pro and was testing the dashboard in smaller devices like iPad and found there was NO good fit of dashboard with in the smaller browsers in smaller devices especially with iPad. Of course when you see the preview of the dashboard in bigger screens TV like monitors.

Of course , there was a property called "Proportionate" in Jasper which was buggish and not worked.

Alternatively, found the bootstrap css working with all kind of devices for dashboards developed in pentaho and here is a trail in Pentaho.

Example :
Before learning about Bootstrap, you need to know on which frame work CDE dashboard lay outs were developed ? It was Blueprint CSS

What is Blueprint CSS ?
 Reference : Click here
 Examples : http://honeycuttlee.com/blueprint/

Why you go for Bootstrap ?
As explained above(start of this article) irrespective of your final device, you can render your dashboards/reports in browsers nicely with the relative width of browser.

What is Bootstrap ?
Reference : Click here

How to get Bootstrap in Pentaho CDE ?
As per this post written date(9th April 14) the latest CDE build is 14.03.07. If you install this/lesser version you will not able to get the Bootstrap functionality(Bootstrap option for Dashobard type in Settings of the dashboard) in your CDE.


You must install all the 3 C-Tools(CDE,CDF,CDA) of TRUNK Versions & you must restart the be-server.

You can find bootstrap installed in below location

G:\Pentaho\biserver-ce-4.8.0-stable\biserver-ce\pentaho-solutions\system\pentaho-cdf\js\bootstrap  
NOTE
The maximum number of columns of CDE dashboard are 24 columns if we use Blueprint css whereas 12 columns for Bootstrap CSS.

i.e., For example, If your layout consists of 3 charts in 3 columns of 1 row then each of the column size would be 4.
i.e, 12/3 ( In general  12/number of columns per a row)

Example :
Environment :
Pentaho 4.8 or 5.0.1 stable, C-Tools of trunk verison, Postgresql foodmart database.


Developing a dashboard of 3 panels in a rows.

Step 1 : Defining Dashboard Settings
1. Open a new CDE dashboard and click on settings. Give the name and select Dashboard type as "bootstrap".

A sample Dashboard Developed using Bootstrap Css.

High lights of the dashboard :
1) Bootstrap panels
2) Relatively adjustment of the charts and other components when preview in different devices like laptop, desktop, ipad, mobile phone(not tested yet but should work).
3) Date Range selector Component is used(Browse my posts for separate example for "Date Range" component.
4) Exploring on Bar Charts  - Vertical Bar chart, Vertical Stacked Bar Chart,Line Chart, Area Chart.
4.A) High lights in Charts
Extension points & relative height and width code(Shared by a community guy).
i) Size of the bars
ii) X-axis labels rotated and X-axis label coloring.
iii) Values on Bars
iv) Work around with legend.

5) CSS for components
6) Cascading parameters concept implemented. 

Smaller View of the same dashboard

Click Here for Source Code of this example: Source Code

Or  open below link using your google accoutn

https://drive.google.com/file/d/0BymV_QP4TGBEc0dWWk5mSUJ1Z2M/edit?usp=sharing


Deployment :
1) Download the zip file and upload it to 5.0.1 BA server
2) Open the file in Edit mode and go to settings and select dashboard type with Bootstrap.

IMP NOTE :
There are few known issues with the example currently - Have to apply css for labels and input selects.

Defects in the dashboard Example :
Input controls css ... relativity of heights and widths of input controls are not implemented. Working on this.. will update you on it in next examples. 


Reference:
http://diethardsteiner.blogspot.in/2014/03/pentaho-cde-and-bootstrap-essential.html

http://forums.pentaho.com/showthread.php?161211-Boot-Strap-CSS-Dashboard-Charts-relative-height-and-width-of-charts

As suggested in pentaho forum from a community guy, write below code for each of the chart component.
This code will relatively adjust the chart height and width with the browser.
i.e., you are telling the container to adjust the heights and width of the charts relatively with the heights and width of bootstrap panels. 

  var myself = this;
  // Set initial width to match the placeholder
  myself.chartDefinition.width = myself.placeholder().width();

  // Attach the resize handler only on the first execution of the chart component
  if (!this.resizeHandlerAttached){

    // Ensure render is only triggered after resize events have stopped
    var debouncedResize = _.debounce(function(){

      // Show chart again.
      myself.placeholder().children().css('visibility','visible');

      // Change chart width
      myself.chartDefinition.width = myself.placeholder().width();
      myself.render( myself.query.lastResults() );
    }, 200);

    // Attach resize handler
    $(window).resize(function(){

      // Only trigger resize if the container has changed width
      if ( myself.chartDefinition.width != myself.placeholder().width()){

        // Temporarily hide chart so that overflow does not happen
        myself.placeholder().children().css('visibility','hidden');

        // Trigger the resize with debounce
        debouncedResize();
      }    
    });

    this.resizeHandlerAttached = true;
  }

Pentaho C-Tools stable or trunk Manual Installation using ctools-installer.sh in Cent OS OR in any Linux server

Hi Guys,

If your network is bounded by proxy settings .. you can not able to connect to Pentaho Market place from the BI Server.
As an alternative you can download and install all the C-Tools using a simple command.

The procedure to install C-Tools manually is given below.

1) Click on below link to download the C-Tools installer https://github.com/pmalves/ctools-installer
2) At the bottom right corner you can find Download Zip file.
3) Once you download the zip file unzip it using unzip command.
   Eg : unzip ctools-installer-master.zip ( You will find ctools-installer-master folder once unzip done).
4) Now, Open your terminal to run the script file ( I always prefer to run the .sh files from command prompt)
5) From the terminal run the below command.

[a1551@localhost ctools-installer-master]$ 

sh ctools-installer.sh -s /home/a1551/softwares\ installed/Pentaho/biserver-ce/pentaho-solutions -w /home/a1551/softwares\ installed/Pentaho/biserver-ce/tomcat/webapps/pentaho -b stable -c marketplace,cdf,cda,cde,cgg,cfr,sparkl,cdc,cdb,cdv,saiku,saikuadhoc -y -n -r 

NOTE : Note that there should not be spaces b/w the folder name. If you have space add \ to the word.

For example in the above command : /softwares\ installed/  where softwares installed is the name of the folder.

Purpose of each one given in ReadMe.txt file.

Usage : ctools-installer.sh -s solutionPath [-w pentaho WebapPath] [-b branch]

-s   Solution Path (eg: /biserver/petnaho-solutions)

-w  Pentaho webapp server path(required for cgg on versions 

          before 4.5 eg:biserverce/tomcat/webapps/pentaho)

-b  Branch from where to get ctools,stable for release,dev for trunk. Default is stable

-c  Comma-separated list of CTools to install 

         (Supported module-name: marketplace,cdf,cda,cde,cgg,cfr,spark1,cdc,cdb,cdv,saiku,saikuadhoc)

-y  assume yest to all prompts

-n  Add newline to end of prompts(for integration with CBF)

-r  Directory for storing offline files

-h  This help screen. 

Once you done with the above command your installation will start with log saying

CTOOLS

ctools-installer version 1.46

Author: Pedro Alves (webdetails)

Thanks to Analytical Labs for jenkins builds

Copyright Webdetails 2011-2013

Changelog:

v1.46 - Added stable versions for Pentaho 5

v1.45 - Added Marketplace, CFR and Sparkl, currently on -b dev only

v1.44 - Added option -r to specify offline mode

v1.43 - Added option -c to specify ctools list to download - Thanks to Tom

v1.42 - Changed stable CGG download process

v1.41 - Changed dev CGG download process

v1.40 - Changed dev saiku download path

v1.39 - Changed saiku download path to 2.4

v1.38 - Added option -n for CBF integration

v1.37 - plugin-samples/cdv was not deleted. Fixed

v1.36 - CDV now installs samples too

v1.35 - Support for stable CDV, CDC and CDB installation

v1.34 - Support for CDV installation using -b dev switch

v1.33 - Added support for Saiku ad hoc stable (release) installations.

v1.32 - Added windows cr tolerance for this script's auto update

v1.31 - Support for CDB installation using -b dev switch

v1.30 - Support for CDC installation using -b dev switch

v1.29 - Changed saiku download path to 2.3

v1.28 - Support for CGG in 4.5, where webapp path is no longer required

v1.27 - Added support for CGG stable ( release ) installations.

v1.26 - -y flag now also works for ctools-installer update. ctools-installer update is now automated - Thanks to Mark Reid.

v1.25 - Removed overwrite, explicitly deleting marketplace definition

v1.24 - Added force overwrite to unzip to phase out overwrite confirmation

v1.23 - CDF trunk installation change due to js publish

v1.22 - Changed saiku download path to 2.2

v1.21 - Added support for CDE stable (release) installations.

v1.20 - CDF new samples location updated to stable installation.

v1.19 - Corrected installCDF and installCDE to remove samples dir before installing

v1.18 - CDA samples installation to plugin-samples also in stable mode.

v1.17 - Change to CDA and CDE samples installation. Now installs to folder plugin-samples instead of bi-developers (for trunk snapshot only).

v1.16 - Added support for CDC and Saiku-adhoc installation - for now only available in dev/trunk mode.

v1.15 - Change to CDF samples installation. Now installs to folder plugin-samples instead of bi-developers (for trunk snapshot only).

v1.14 - Added support for CDF stable (release) installations.

v1.13 - Fixed issue in CGG download

v1.12 - Fixed typo in -Y option

v1.11 - Added support for -y option (assume yes) - Thanks to Christian G. Warden

v1.10 - Added support for Saiku trunk snapshots installations.

v1.9 - Added support for CDA stable (release) installations.

v1.8 - Added CGG; Script refactor

v1.7 - Changed url locations to new path of analytical labs

v1.6 - Changed saiku download path to 2.1

v1.5 - Changed default indicator values in prompts

v1.4 - Added Saiku for the list of installs

v1.3 - Added support for automatic updates

v1.2 - Silent mode for downloading

v1.1 - Minor bugfixes

v1.0 - First release

Disclaimer: we can't be responsible for any damage done to your system, which hopefully will not happen

Note: ctools-installer.sh will upgrade the plugins under system directory.

Any changes you have made there (eg: cdf templates) will have to be backed up and manually copied after running the script

CGG will need to change the server WEB-INF/lib too. Backup your server

Downloading files

Downloading Marketplace...

End-of-central-directory signature not found. Either this file is not

a zipfile, or it constitutes one disk of a multi-part archive. In the

latter case the central directory and zipfile comment will be found on

the last disk(s) of this archive.

unzip: cannot find zipfile directory in one of .tmp/marketplace/plugin.zip or

.tmp/marketplace/plugin.zip.zip, and cannot find .tmp/marketplace/plugin.zip.ZIP, period.

Done

Downloading CDF...

Done

Downloading CDA...

Done

Downloading CDE...

Done

Downloading CGG...

Done

Downloading Sparkl...

Done

Downloading CDV...

Done

SAIKU [stable] not available for Pentaho 5.x yet.

Done

Installing files

  End-of-central-directory signature not found.  Either this file is not

  a zipfile, or it constitutes one disk of a multi-part archive.  In the

  latter case the central directory and zipfile comment will be found on

  the last disk(s) of this archive.

note:  .tmp/marketplace/plugin.zip may be a plain executable, not an archive

unzip:  cannot find zipfile directory in one of .tmp/marketplace/plugin.zip or

        .tmp/marketplace/plugin.zip.zip, and cannot find .tmp/marketplace/plugin.zip.ZIP, period.

cp: target `installed/Pentaho/biserver-ce/tomcat/webapps/pentaho/WEB-INF/lib' is not a directory

unzip:  cannot find or open .tmp/dist/sparkl-??.??.??.zip, .tmp/dist/sparkl-??.??.??.zip.zip or .tmp/dist/sparkl-??.??.??.zip.ZIP.

No zipfiles found.

unzip:  cannot find or open .tmp/dist/cdv-??.??.??.zip, .tmp/dist/cdv-??.??.??.zip.zip or .tmp/dist/cdv-??.??.??.zip.ZIP.

No zipfiles found.

unzip:  cannot find or open .tmp/dist/cdv-samples-??.??.??*zip, .tmp/dist/cdv-samples-??.??.??*zip.zip or .tmp/dist/cdv-samples-??.??.??*zip.ZIP.

No zipfiles found.

unzip:  cannot find or open .tmp/saiku-plugin*zip, .tmp/saiku-plugin*zip.zip or .tmp/saiku-plugin*zip.ZIP.

No zipfiles found.

Done!

References:

1) http://forums.pentaho.com/showthread.php?156604-CTools-for-Pentaho-4-8-manual-install-Help!!

2) http://www.webdetails.pt/ctools/ctools.html

3) https://github.com/pmalves/ctools-installer

Trunk versions :
http://pedroalves-bi.blogspot.in/2013/11/ctools-for-pentaho-5.0-is-available-cdf.html

TIP : Hide the unwanted files/folders in Pentaho CDE - Hiding .cda and _tmp files in pentaho CDE

Hi Guys, 

This post will give you the small tips on hiding the files/folders in the repository.

Tip-1 : Hiding the file/folder

Let's say you are creating a CDE dashboard and as soon as you give all the database connections at least for a query, the CDF framework generates 3 files

.wcdf
.cda and
.cdfde

Some times you may not want to show .cda and .cdfde files but want to show .wcdf files (The dashobard only file).

The trick is : Just you need to check the Hidden check box on click on OK button in properties of that particular file.

(In the folder where you can see all of these 3 files,
a)  select the .cda file as an example(and or .cdfde in another case)
b) then from the File Actions Pane click on properties
c) It'll open up a window
d) At the bottom of the window you can find two options like "Allow","Hide"
e) Check the Hide button and then click on OK
c) Refresh the Browser(Refresh the cache from Tools).
d) Go to the folder where you kept your dashboard, now you can see only the  CDE file

In the same way you can do it for any kind of files/folders in the Repository.

IMP NOTE : I struck in Roll back of the same : Will update how roll back (i.e., Currently, I am not getting the hidden folders back to show as earlier)

Tip 2 : How to make the .cda file not editable ?
From the pentaho community thread, here is the solution for making cda file not editable.

http://forums.pentaho.com/showthread.php?160306-How-to-hide-cda-and-_tmp-cda-files-in-Pentaho-CE-4-8-CDE&p=379891#post379891

You need to comment/remove the code in plugin.xml file.
File location : /home/sadakar/softwares installed/Pentaho/biserver-ce/pentaho-solutions/system/cda

<!--
    <content-types>
        <static-paths>
              <static-path url="/cda/cachemanager" localFolder="cachemanager"/>
              <static-path url="/cda/editor" localFolder="editor"/>
              <static-path url="/cda/previewer" localFolder="previewer"/>
              <static-path url="/cda/resources" localFolder="resources"/>
              <static-path url="/cda/static" localFolder="static"/>
        </static-paths>

       
        <content-type type="cda" mime-type="text/html">
            <title>Data Access</title>
             <description>Community Data Access File</description>
            <icon-url>content/cda/resources/cdaFileType.png</icon-url>
            <meta-provider>pt.webdetails.cda.CdaFileMetaProvider</meta-provider>
              <operations>
                <operation>
                      <id>EDIT</id>
                    <perspective>cda.edit</perspective>
                </operation>
                <operation>
                      <id>RUN</id>
                </operation>
                <operation>
                      <id>NEWWINDOW</id>
                </operation>
              </operations>
        </content-type>

    </content-types>
-->

NOTE :
1) Stop the BA server and start again.
2) Go to one of the CDA files and observe File action for .cda file. There will be no more Edit option for .CDA files as you commented the Editable code in plug-in.xml file.
3) But you can see the code by opening the file in "Open in new Window".


This post is updated with further information on roll backing the hidden files/folders.

Sadakar
BI developer