Tuesday, September 29, 2009

Pentaho Report Designer 3.5: Not mand...

How to set up not mandatory parameters


When 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

*

FROM

table

WHERE

channel = ${channel} AND

media = ${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 if we want to allow the users to choose either one media value or non (=showing all media types)?


This is not difficult to implement, if you know how to do it. Basically, you have to change your query to this:


SELECT

*

FROM

table

WHERE

channel = ${channel} AND

(media = ${media} OR 0 = IFNULL(${media},0)

;


Basically, if the user doesn't choose any value for media, the media value will be set to NULL (as in "no value"). So I changed the query to include "(media = ${media} OR 0 = IFNULL(${media},0)", which basically says: If there is a media value, apply it, if not, return all the media types (=no filter). So depending on which part is TRUE, the query result will change!


Now this is a fairly wonderful way to implement this. The only problems are that the Null value/No filter will be only available when you call the report in the first go, once you changed the Media to "TV" in example, the drop down menu (or whatever display type you chose) doens't offer a NULL value any more. Also, it would be great to be able to rename the NULL value for the end user individually, in example to "All Media Types".


2 comments:

  1. Dear Mr/Mrs Diethardstener

    How about if i want to implemented in the query mdx. now i have more than one parameter, and the value will my input into the MDX query. thank's

    regards

    Yanuar

    ReplyDelete
  2. Hi Yanuar,
    That's a good question. I haven't tried this myself yet. One idea that comes to my mind is to use a post-processing formula. So depending on the input, the open formula could calculate (=prepare) the string that you will need in the MDX query. Does somebody else have a better idea?
    Best regards,
    Diddy

    ReplyDelete