Rethinking the Pentaho Report Designer LayoutThe Pentaho Report Designer (PRD) has evolved to a very feature-rich product. In this article I want to point out one problem that I still have with this product from a usability point of view.Imagine following scenario: You are about to create a new report for your CEO. In a nutshell the report should have first a summary cross-tab with the essential KPIs and then below some more detailed product data in a standard table (let's keep it simple). Now currently you would create the product report/table...
Thursday, 10 December 2009
Friday, 4 December 2009
Pentaho Reporting 3.6 Milestone 1 is out!Great news over on the Pentaho Community Forum! The first milestone of PRD 3.6 has been released. Have a look at the release notes here.There have been several bug fixes plus the addition of some great new features. I just want to highlight some that are important for me:Added OLAP4J (Advanced) and Mondrian (Advanced) datasources. These datasources work exactly as the SQL (Advanced) datasource by allowing the query to be computed by a formula.Formulas can be used in parameters now. There are two formula...
Wednesday, 2 December 2009
Exporting characters as UTF-8 from Kettle Recently I took over a project for our Russian office, which strangely enough is part of the UK & International region of the company I am working for. This was the first time I was exposed to handling Cyrillic characters.Basically there are following points to take into concern:Make sure your MySQL table uses the UTF-8 encodingMake sure that in the database connection details in Kettle following options are set: characterEncoding=utf8, characterSetResult=utf8,useUnicode=true.Once you ran...
Tuesday, 3 November 2009
Using Parameters in Pentaho Report DesignerIntroductionHow to define a parameterAdditional info about the new parameter dialogHidden ParametersParameter Validation can be loosenedDisplay-FormulaPost-Processing FormulaUsing parameters with database queriesUsing parameters with Metadata/MQL queriesUsing parameters with Mondrian/OLAP/MDX queriesHow to pass on a single valueHow to pass on multiple valuesMore infoXPathReflection/Java Method CallPentaho Data Integration (Kettle)ScriptableReferencing parameters in the message field IntroductionYou...
Tuesday, 27 October 2009
Send Pentaho Report By EmailThe Pentaho Report Designer 3.5 brought along some important improvements. If you set up Xactions before that sent out reports by email, there are some slight changes now that you want to be aware of.In my case I prepared a report that uses a MDX query as source, which automatically selects the last 5 days of data. The report doesn't use an input, so it is fairly easy to integrate into an Xaction.My idea is to have basically a simplified version of the report in the email body and the full report with a chart as attachment....
Friday, 9 October 2009
Current date function on MondrianPentaho BI Suite - MondrianI create quite a lot of operational dashboard views like "How many users did we acquire through which kind of channels in the last 7 days on a daily level?". In the past my solution was to write some Javascript within an xaction to define the date variables and then to pass on these variables as parameters to an MDX query. The results set of the MDX query would then be passed on to a JPivot component. While you can fairly easily implement this in an xaction, it is probably not the most...
Wednesday, 7 October 2009
How remove the sample users from the login boxPentaho BI Server - Configuration Have you ever wondered how to get rid of the sample users in the Pentaho BI Server login box? Tom Barber pointed out a simple solution recently on the Pentaho forum that was originally posted by Paul Stoellenberger:You can edit the login properties of mantle in:biserver-ce\tomcat\webapps\pentaho\mantleLogin\loginsetting s.propertiesthere is an option:#showUsersList=truechange that toshowUsersList=falseThat's easy enough, if you know it :) ...
Thursday, 1 October 2009
Converting binary data type to String in KettleSometimes you might come across data that is store in in binary form in a table. To convert this data you chose one of the following approaches:Convert directly using SQL in the database input stepOne quick method would be to use the CONVERT or CAST function (test which one works best for you):CONVERT(prodCode USING latin1) AS prodCodeCAST(prodCode AS SIGNED) AS prodCodeSelect Values ... stepGo to the Meta-data tab, choose your binary field and set Binary to Normal to Y.Note: You can only use one tab...
Setting up an "All" ParameterThis short tutorial is fairly similar to the last one, let's say it's an improved version:We work again with Pentaho Report Designer 3.5. Imagine you want to give your users the possibility to choose either one of the parameter values or all. So how can we implement this with a MySQL query? The approach is as follows:Set up the query to for the parameter values like this:SELECT"All Countries" AS country_nameUNIONSELECTcountry_nameFROMtable;We...
Tuesday, 29 September 2009
How to set up not mandatory parametersWhen creating parameters in Pentaho Report Designer 3.5, you can definie if the paramater is mandatory or not.Imagine we have a query like this one to feed our report:SELECT*FROMtableWHEREchannel = ${channel} ANDmedia = ${media};I define two parameters for this report: channel and media. The end user will be able to choose a value for these parameters once they call the report. In the query you define the parameter like this: ${parameter_name}. This is fairly easy so far, but how do we configure the report...
Monday, 28 September 2009
Today I came across a special use case for an outer join. I was planning to set up an ETL process that imports yesterday's revenue summary by summarizing the input of two tables.The main problem was, that we might not have made any revenue yesterday at all, or there might be only revenue in one table but not the other.Basically, if revenue figures exist in both tables, the process would be following:- input data from table 1: date, service name,...
Saturday, 26 September 2009
This is the first book available that describes the functionality of the Pentaho Report Designer in detail. Will Gorman mainly focused on integrating the reports in Java applications and not so much on using it in combination with the Pentaho BI Server.Overall, it is an excellent book. It guides you through various tutorials on how to create more complex reports. All the tutorials are fairly easy to follow. Some chapters of the book give a very detailled description of the available functionality and settings, which serves as an excellent reference.Conclusion:...
Wednesday, 23 September 2009
After coming back from my business trip to South Africa I found a package of new books on my office desk. I finally received a copy of "Pentaho Reporting 3.5 for Java Developers", "Pentaho Solutions" and "The Data Warehouse Toolkit - The Complete Guide To Dimensional Modeling".I think I am going to start with the Pentaho Reporting book, so that I can post a review here quite soon. It was a tough choice, as all the books sound very interesti...
Thursday, 17 September 2009
Packt Publishing Ltd sent me yesterday the chapter 6 "Including Charts and Graphics in Reports" of the recently published "Pentaho Reporting 3.5 for Java Developers" Book to review. Excited as I was, I had to read it on the same day! "Pentaho Reporting 3.5 for Java Developers" was written by Will Gorman, a long time Pentaho developer. I found chapter 6 easy to read, so I would say it is ideal for users that are new to Pentaho Reporting as well as those who have already a bit of experience with it. It's also great that the book covers the very latest...
Wednesday, 16 September 2009
Today I received a nice email from Packt Publishing Ltd asking me if I wanted to write a review about the new Pentaho Reporting 3.5 for Java Developers Book.I've quite some books to read currently about OLAP and Data Warehouse Design, but I'll try to speed up to provide a review about this exciting new book in the next few months. So stay tun...
Saturday, 12 September 2009
There hasn't been much literature around about Pentaho, at least not any that I know of. So far most people would search for info in the pentaho forum or wiki. But recently things have changed. There is now whole book about setting up Pentaho with MySQL called "Pentaho Solutions", which you can order from Amazon. This one tries to cover basically every component of Pentaho, which should be a great support for first time users.Another book was published recently as well, called "Pentaho Reporting 3.5 for Java Developers" by Will Gorman, a long term...
Friday, 24 July 2009
Quick Step By Step GuideYou can find the main Pentaho Wiki doc here. This is an updated (2013-08-19) and extended version of the original tutorial which I posted here a few years ago. It will take you more than 10 seconds now to read through it, but instructions should be more precise now than before.Specifying JNDI and adding the JDBC driverDefine a JDNI Connection. You have to use the same JNDI connection as you specified on the BI Server (in the...
Subscribe to:
Posts (Atom)
