Exporting PRPT from saiku adhoc Reporting.

As we all know saiku is a thin client plugin tool which we use to generate Analysis and Adhoc Reports on the top of BI server.

When we create saiku ad-hoc reports using ad-hoc plugin there is an option of exporting PRPT file to BI server so that we can save the adhoc report for the future use. But when we click on export button eventually we come to know that we are unable to save the report.

When I did some R&D, I came to know that there is a missing jar in WEB-INF/lib in tomcat folder under pentaho BI server. so simply copy jar "pentaho-reporting-engine-classic-extensions-cda-5.0.1-stable.jar" from Pentaho report designer's lib folder and paste it to WEB-INF/lib folder and then restart the server, now u will be able to save/export the report. :)

How to conditionally hide the column in pentaho report designer

The requirement is to hide a column and it's label if data in that column is null.

Contional hide of report element:-

To hide report elements based on certain conditions, we can define a formula under 'Style' -> 'visible'.

One interesting use case is to hide the labels of the report if no data is returned.

For example, to hide all labels of the report if the field 'field1' has a null value:

• Add a sub-band on the report and insert the labels that you want to hide in the sub-band. Thus we will not have to define the same formula for each label, but rather only for the sub-band.
• Highlight the sub-band and go to 'Style' -> 'visible' properties.
• Add the following formula

  =IF(ISNA([field1]);"False";"True")

We could also hide the report elements based on whether we have entered a value for a user parameter or not.

• Highlight the report element and go to 'Style' -> 'visible' properties
• Enter the below formula

  =IF(LEN([param1])=0;"False"; "True")

  where param1 is the parameter name.

How to call stored procedure in pentaho report designer.

How to call stored procedure/kettle files in Pentaho designer?

There are two ways of running the stored procedures

If the procedure returns a resultset, then you can use the result like any other query in the report-designer by calling it via

"CALL procedure(${parameter1},${parameter2})"

However, if your procedure just returns a single value and you want to combine that value with results from other procedures or queries, then you have to use Pentaho Data Integration.

Using Message Field and Formatting Date field in message as required

Using message fields

Message Fields let you mix dynamic and static text, sort of like a combined Label and Text Field. Here's an example:

The date is $(report.date, date, d MMMM yyyy)

This will show in the report as:

The date is 6 November 2008

The first part ("The date is") is static. The variable afterwards has three sections:

1. The field to use. Here we are using the internal function 'report.date' to give the current date, but you could also use a field from the database query.
2. The data type. This data is of type 'date'.
3. The format in which to display the data.

Calculate Date Example

Sometimes it is very useful to use a calculated earlier date (e.g. yesterday, a month ago) as a default parameter value on a report. For example, if you want a report to run every day for the previous day, you might want to add a calculation of 'yesterday' for the default value of the parameter.

Implementation Steps

To calculate 'yesterday' in the report designer use the following formula in the 'default value formula' or 'post processing formula' of the parameter:

=DATEVALUE(DATE(YEAR(TODAY()-1);MONTH(TODAY()-1);DAY(TODAY()-1)))

To calculate '12 month ago' in the report designer use the following formula in the 'default value formula' or 'post processing formula' of the parameter:

=DATEVALUE(DATE(YEAR(TODAY());MONTH(TODAY())-12;DAY(TODAY())))

Here is the oiginal Post

1 Format Date with Date Picker
#startfloatingbox()
*Contents:* \\
#toc("2" "6" "false")
#endfloatingbox()
The default date picker in the Pentaho Report Designer, displays the date in the date-time format 'yyyy-MM-dd HH:mm:ss:SSS' by default. If you would like to change the way the selected date is displayed in the date picker, use 'Data Format' under the parameter definition. You can define, for example dd/MM/yyyy.
This will only change the display format on the date picker when the user makes a selection but not the value of the selected parameter.
Often, you will need to format the value of the parameter to match with the format in your database to be able to use the parameter in your SQL query. Here is one way to do it.
1.1 Implementation Steps
* Create another parameter (e.g. $Formatted)
* If you do not want this parameter to be displayed to the user, tick the box 'hidden'.
* Define the 'Post-Processing Formula'. You can use any formula expression there. For the given use case, we recommend to use the MESSAGE() function.
Here is a sample code that will format the date from 'yyyy-MM-dd HH:mm:ss:SSS' (defined in the parameter $today) to 'yyyy-MM-dd'.
{code:none}
=MESSAGE("{0,date,yyyy-MM-dd}"; [$today])
{code}
You can download a sample prpt file {attach:here|demoDateFormatting.prpt}
This is how your $Formatted parameter can be defined:
{image:Screenshot.png}

1.1  Download Sample
*You can download a sample prpt file {attach:here|demoDateFormatting.prpt}.*
1.1 Resources
* [Pentaho wiki>http://wiki.pentaho.com/display/COM/February+3%2C+2010+-+Pentaho+Reporting+3.6+overview+and+demo>_blank] - some videos on Report Designer