Thursday, August 31, 2006

Designing Reports in Pentaho: Save As versus Publish

As part of my job at Pentaho, I occasionally get to help build solutions using the Pentaho BI platform and the solution building toolset, Design Studio, Report Wizard and Designer. I really enjoy that time, in part because I miss my developer days, but also because using the software we build makes me feel like I can connect and relate more to our community. The end product is I can do my job better, because I know the project and the software better.

I recently started creating some reports against our case tracking data, which lives in a JIRA database. JIRA is a phenomenal issue tracker, but I just can't get the reports and data analysis out of it that I really need. And that's OK, that's what we have the Pentaho platform for. One thing I came across that I thought would be good to explain a bit is the difference between "Save As" and "Publish" in our report designing tools.

Both the Report Designer and the Report Design Wizard have a "Save As" option and a "Publish" option. The "Save As" option is pretty straight forward, and "Publish" is a very powerful feature, if you know how to use it:) I have to warn you at this point that these options do produce slightly different results when you are in Report Design Wizard than when you are using Report Designer, and I will spell out what those differences are in a moment. If you are wondering WHY this is the case, read my previous blog post:)

Let's start by talking about what Save As and Publish do in Report Design Wizard.

If you choose the Save As option in the Report Design Wizard, you are saving the entire state of the wizard session you are in. The file will have a .xreportspec extension, which is the file extension that the Report Design Wizard expects when you select the File | Open option in the wizard. It may not seem intuitive at first to "save" the state of a wizard. However the wizard allows you to set so many options for your report, that you may want to go back to your wizard session and tweak a few report features. This makes less sense when you have the Report Designer, which gives you much more powerful report tweaking capabilities. The Save As option in the Report Design Wizard was built at a time when Pentaho didn't have a Report Designer, and so, as the two tools become more tightly integrated, I would hazard a guess that this Save As feature may go away.

The real power of the wizard is in its Publish feature. When you choose the Publish option from the Report Design Wizard, you get a number of files that amount to everything you need to run that report in the Pentaho BI platform. Here is a list of the files that would be generated with a Publish, and what they are for:

  • JFreeReport-WizardReport.xreportspec - the saved wizard session. This is the same file you would have if you chose the Save As option.

  • JFreeReport-WizardReport.xml - this is the JFreeReport report definition.

  • JFreeReport-WizardReport.xaction - this file is the action sequence built to run this report in the platform. If you are not familiar with action sequences, you can get a great overview from the Pentaho Creating Solutions Guide.

  • - this is the resource file, used for holding the action sequence's extracted strings. This file facilitates internationalization of your action sequence.

  • PentahoReporting.png - a sample image, referenced in the action sequence, that can be used in to represent this report in your solution's UI.

The Report Designer's Save As and Publish functions serve the same purposes, but result in slightly different outputs.

When you select the Save As option in Report Designer, you are saving the designer session you are working in. The file will have a .report extension, which is the type of file that the Designer expects on opening of a file.

When you choose Publish with the Report Designer, there are fewer files than what the Report Designer produces, but the same end goal is achieved. Here are the files produced by the Report Designer and what they are for:

  • Report.xml - this is the JFreeReport report definition.

  • Report.xaction - this file is the action sequence built to run this report in the platform.

Why, you might ask, does the Report Designer generate less files, but accomplish the same task? There are few differences between the outputs that I should note:

  1. First, the Report Design Wizard still gives you the wizard session (.xreportspec), even when you Publish, whereas the Report Designer does not give you the designer session (.report) on Publish.

  2. The Report Design Wizard extracts strings from the action sequence file for translations into a .properties file, whereas the Report Designer does not.

  3. The Report Design Wizard generates a sample icon for the action sequence where the Designer does not.

  4. Another difference worth noting, that is not apparent by examining just the list of output files, is where the query for the report lives when either tool publishes. The Report Design Wizard will put the query in the action sequence file, which is the best practice. The Report Designer embeds the query in the JFreeReport definition.

These differences constitute minor inconsistencies in implementation that I'm sure will be addressed very soon. That is everything I had hoped to cover.

I hope that this helps a few people get up and running with Pentaho Reporting, because it really is quite an extraordinary set of tools, and just keeps getting better (not that I'm biased or anything :).

Monday, August 28, 2006

Pentaho Report Designer versus Pentaho Report Design Wizard

The Pentaho Reporting client toolset can look a bit confusing at first glance. We have the Pentaho Report Designer, and then we have the Pentaho Report Design Wizard. Two products that perform seemingly very similar functions, but are separate tools. What's THAT all about??

Well, first, I'm here to tell you it won't be this way for very long:) And second, these two tools are powerful and unique, and compliment each other very well. Our vision for Reporting in the Pentaho BI Suite is well, sweet. With a little history, it's easier to understand how we got where we are, what should be used when, and where we are going with all this.

So how did we get here? Well, we really loved JFreeReport. JFreeReport provides a robust, full-featured reporting foundation for the Pentaho BI platform. However, at the time that we brought JFreeReport on board the Pentaho ship, the available open source report-building UIs for JFreeReport were lacking. So, that provoked our captain (Mr. Dick Daley) to hail the crew and shout "Go forth yee and build a Report Design Wizard!". Aargghhh. And thus the Report Design Wizard was born (thanks to the mighty efforts of one rather savvy shipmate, Mike D'Amour).

<Insert A.D.D. tangent here>
If at this point, you all are wondering about the sea-faring dialog, my husband often describes the thoughts in his head as a scene from Moulan Rouge. Mine are a bit more Pirates of the Carribean:)
</Insert A.D.D. tangent here>

At this point in the story, we are very excited about the Report Design Wizard, when we are introduced to a company out of Germany that has built a JFreeReport Report Designer. This tool is pretty spiffy and approaches report design a bit differently than the wizard, so Pentaho purchased the Report Designer, and donated it to the world as open source. And that is how we ended up with two tools, that initially look the same.

In reality, the only similarities that the Report Designer and the Report Design Wizard are their names, and their end goal, being to generate a JFreeReport to use in the Pentaho Open BI Suite. These tools actually bring unique sets of features together to provide a complimentary toolset, that will in the near future be much more tightly coupled. Let's take a look at each tool on it's own.

The Report Design Wizard was designed with to be just that - a wizard. A tool to get you from a dataset to a report in the shortest number of steps possible, easing the startup time for building JFreeReport reports. This makes the Report Design wizard inherently focused on the data. You start building your report by telling the wizard where your data is and what query you wish to perform. The next steps allow you to tweak numerous report features, as well as incorporate charts into your final product.

The Report Designer on the other hand, while it can be dataset-centric, provides more power in allowing you to define your report without specific data, as well as giving you the abillity to customize every feature and aspect of your report. The Report Designer supports the vast majority of JFreeReport features, and can render them through it's UI. It shields the user from the JFreeReport XML, almost completely.

My favorite way to use these tools is to start my report creation with the Report Design Wizard ( I can't think in terms as abstract as a report layout without data - too many years as a programmer, I guess), building up as many report features as I can. If I still need to tweak the report, I import it into the Report Designer and polish it there.

And where is all of this going? In the future, we envision the Report Design Wizard as a feature of the Report Designer, as well as our Pentaho Design Studio. Definitely an integration that makes sense, and will make for a nice experience building Pentaho solutions and reports. As the Pentaho Community leader, it's my job to know what's important to you, our community. I'd love to hear about your experiences using these tools, what you think of our plan, and what great ideas you have for the toolset. Please comment, or feel free to email me at :)

Wednesday, August 23, 2006

Blogging on Pentaho, a Favorite

The Pentaho team spans several countries, and it's not always easy to keep up with the mountainous amount of activity among our projects and people. This has spurred a number of team members to begin blogging.

Here is a short list of Pentaho bloggers, some new, some not so new. These are definitely a few of the people who can tell you where Pentaho is and where we are going:
Nick Goodman is Pentaho Director of Solutions, and just an a
all around knowledgable guy when it comes to open source AND business intelligence.
Matt Casters is the founder of Kettle, the Pentaho Data Integration piece and Chief Architect of Data Integration for Pentaho. In a past life, Matt was an independent BI consultant for many years and implemented numerous data warehouses and BI solutions for large companies.
Julian Hyde is the founder of Mondrian, THE open source OLAP server, and the OLAP server component of Pentaho. Julian brings his expertise to Pentaho as Chief Architect of OLAP Technologies.

Thursday, August 17, 2006

Dynamically Generating a Pentaho Cross Tab Report, Part 1

We recently received a monster (in a great way!!) of a tech tip from Nic Guzaldo, a self-taught Pentaho expert and supporter. I spent a day or so getting my head around what Nic had accomplished (with many exclamations of "wow, I didn't know we could do THAT!"), and figured I should break it up into a series of articles for easier consumption.

This first article of the series focuses on creating a query that will not only demonstrate setting up regular data for a cross tab report, but also use user input to determine the number of columns in the query.

In the second half of this solution, we will continue with Nic's example and use the generated resultset as input to a dynamically generated report spec, to create a Pentaho report that allows a variable number of columns, dynamically generated report name, nice output names for external files and other goodies!

Start test driving this jam-packed solution at here.

Friday, August 11, 2006

Moving Pentaho Sample Data to MySQL

MySQL has been one of the most popular databases amongst the Pentaho community. We receive questions and comments regularly about setting up and writing solutions for Pentaho on MySQL, which tells me people are putting MySQL to work in the business intelligence space. These questions were the catalyst for our Tech Tips, a collection of very useful how-to's for the Pentaho platform and Pentaho tools.

Today, I published a short tech tip regarding how to move the Pentaho sample data from HSQLDb to MySQL. Not rocket science, yet definitely made much easier with a Kettle transformation donated by Nic Guzaldo - thanks again Nic! Check it out, it's a good example of moving data with Kettle and also is a great utility tool to stick in your Pentaho toolbox. That one transformation can be tweaked to move the Pentaho sample data to just about any JDBC compliant database.

Nic's full contribution is actually a complete reporting solution that comprehensively covers a plethora of Pentaho features - secure filters, parameters, variable replacements, more MySQL 5.0 tricks just to name a few. In my next posts I'll talk more about the "Guzaldo mini-series", look for it!