Tuesday, November 3, 2009

Using Parameters in Pentaho Report Designer

You wonder what all this fuss is about? Or are you one of the many who have been struggling to use parameters in your data queries?


Using parameters with Pentaho Report Designer 3.5 (PRD) and later versions is not a trivial topic. The main problem is, that for each data source the parameter definition is different. Using parameters with standard database queries is fairly easy, using it with MDX, MQL queries and other data sources is a bit more an advance topic.


We will start off with discussing the easiest implementation and then we will move on to discuss solutions for MQL and MDX queries.

How to define a parameter

PRD 3.5 is a major improvement over previous versions of this software. One of the new features is, that you can define parameters within your report, which means, that you do not have to use Xactions any more. (You can still use Xactions for more complex logic, but generally speaking, defining the parameters within your report will make your life easier).

Within the data tab in PRD you can see a section called "Parameters". Right click on it and select "Add parameter". In the dialog you can define your parameter name, define the input type (drop down menu etc.), add a data source to populate the input dialog and make the parameter mandatory. This is all fairly straight forward and easy to set up.

Note: You have to set up the parameter before you reference it in your data queries.


Additional info about the new parameter dialog

Thomas has a very informative article on his blog about various parameter settings. I have just copied this info into here for a better overview. This section covers settings that were not available in Pentaho Report Designer 3.5, but only later releases.

::start quotation::

Hidden Parameters

By selecting a tiny little check-box, parameters can now be excluded from the UI-generation. The parameter itself still behaves like any other parameter and thus takes part in the formula-calculations and is part of the validation.

Parameter Validation can be loosened

When this flag is set to false, the validator will accept other values than the ones that have been specified in a list-parameter. With relaxed validation, you can easily write WHERE conditions, that accept partial matches or contain “LIKE” clauses. For the sake of security, we none-the-less default this feature to strict-checks.

Display-Formula

Every List-Parameter can now have computed display-texts. This way, you are no longer limited by what your data source can produce, and even a simple data-source like an XML file or a static table-datasource can produce better looking prompts.

Post-Processing Formula

A post-processing formula processes the parameter-input before it gets validated or passed into the report. The post-processing can either be defined on the parameter itself or can be defined as an extra field by using a hidden parameter. The computed value will still be validated by the engine and thus using formula’s does not necessarily mean you have to fear that illegal input comes through. Formulas allow you to access runtime information, like the current user’s roles or the user’s name via the ENV(..) function.


::end quotation::

Using parameters with database queries

From PRD 3 to PRD 3.5 the way you define your parameters in a SQL query has slightly changed.
You define a parameter within a SQL query like this: ${parametername}. Make sure you do not use enclosing single or double quotation marks, as PRD knows already how to display the parameter.

Example:

SELECT
*
FROM
table
WHERE
start_date <= ${my_start_date}
;

Using parameters with Metadata/MQL queries

The easiest way to define the parameter is by using the MQL query builder. Add the data point to the filter and write {VARIABLENAME} in the value field (replace VARIABLENAME with your actual variable name; make sure it is enclosed with curly brackets). Both the parameter and the condition will then be created for you.

If you want to define your parameter in the MQL directly, use following approach:
A parameter for an MQL query can be define as follows:

Example:

<constraints>
<constraint>
<operator>AND</operator>
<condition>[TABLE.COLUMN] = [param:MY_PARAMETER]</condition>
</constraint>
</constraints>

Note: There are no enclosing brackets or a $ for the parameter_name, but the whole reference must have enclosing square brackets!

Let's have a look now at how to use date parameters in MQL. Normally, if you are using a date in MQL, you would have to use DATEVALUE("2009-11-01"), but it seems that if you are using a data parameter, DATEVALUE() shouldn't be used. As Thomas (Taqua) points out "'Normally' probably refers to 'in the old days'. But in the old days, Metadata had no parametrization at all, and all the parametrization that happened in the BI-server was a simple string-manipulation exercise on the XML code that represents the query. Thats why you in reality only passed in strings and then had to convert it via a DATEVALUE function. (And of course, its just a horrible way to do parametrization, as you now have to be careful that your strings do not break the XML or even rewrite the XML for fun and profit.)"

Define your date parameter called i.e. my_date, choose the date picker input and set the type to date. 

In your MQL you reference the paramter like this:

<condition>
[Registration Time.BC_SN_LT_TIME_DIMENSION_THE_DATE] &gt;=[param:my_date]
</condition>

I would also suggest to have a look at the MQL syntax Pentaho Wiki documentation, which is very informative!

NEW: Report Desinger 3.6 (release notes)
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 types: Display-value computation for lists, and for all parameters a post-processor.
Parameter-Support added to OLAP4J (except for members and sets)

Using parameters with Mondrian/OLAP/MDX queries

How to pass on a single value

Imagine you want to pass on a single value to the member of a simple dimension like [Login Date].[Date].  You can use a function called parameter, which basically looks like this: parameter("parametername", dimension, member default value). 

The member default value will be used (you guessed it) in case no parameter value is available. This is very useful if you are still designing your report in PRD.

Here is an example of using this approach:

SELECT
LastPeriods(6,Parameter("my_date",[Login Date],[Login Date].[2009-09-12]))
ON ROWS,
[Measures].[Distinct Users]
ON COLUMNS
from [Logins]

Since PRD 3.6 I've been also using the standard variable approach of ${variable} successfully:

WITH
MEMBER [Measures].[7daysago] AS
(CurrentDateMember([Date.Date Digits],
"[""Date.Date Digits""]\.[""Date Digits""]\.[yyyy-mm-dd]").Lag(7.0),
[Measures].[CTR])
MEMBER [Measures].[6daysago] AS
(CurrentDateMember([Date.Date Digits],
"[""Date.Date Digits""]\.[""Date Digits""]\.[yyyy-mm-dd]").Lag(6.0),
[Measures].[CTR])
MEMBER [Measures].[5daysago] AS
(CurrentDateMember([Date.Date Digits],
"[""Date.Date Digits""]\.[""Date Digits""]\.[yyyy-mm-dd]").Lag(5.0),
[Measures].[CTR])
MEMBER [Measures].[4daysago] AS
(CurrentDateMember([Date.Date Digits],
"[""Date.Date Digits""]\.[""Date Digits""]\.[yyyy-mm-dd]").Lag(4.0),
[Measures].[CTR])
MEMBER [Measures].[3daysago] AS
(CurrentDateMember([Date.Date Digits],
"[""Date.Date Digits""]\.[""Date Digits""]\.[yyyy-mm-dd]").Lag(3.0),
[Measures].[CTR])
MEMBER [Measures].[2daysago] AS
(CurrentDateMember([Date.Date Digits],
"[""Date.Date Digits""]\.[""Date Digits""]\.[yyyy-mm-dd]").Lag(2.0),
[Measures].[CTR])
MEMBER [Measures].[1daysago] AS
(CurrentDateMember([Date.Date Digits],
"[""Date.Date Digits""]\.[""Date Digits""]\.[yyyy-mm-dd]").Lag(1.0),
[Measures].[CTR])
SET [Period] AS
(CurrentDateMember([Date.Date Digits],
"[""Date.Date Digits""]\.[""Date Digits""]\.[yyyy-mm-dd]").Lag(7.0) : CurrentDateMember([Date.Date Digits],
"[""Date.Date Digits""]\.[""Date Digits""]\.[yyyy-mm-dd]").Lag(1.0))
MEMBER [Measures].[Period] AS
Avg([Period],
[Measures].[CTR])
MEMBER [Capsule].[Total Live Capsules] AS
SUM((FILTER([Capsule].[Capsule].Members,
(NOT ([Capsule].[Capsule].CurrentMember.Name MATCHES ".*test.*")))),([Measures].CurrentMember))
SELECT
{[Measures].[7daysago],
[Measures].[6daysago],
[Measures].[5daysago],
[Measures].[4daysago],
[Measures].[3daysago],
[Measures].[2daysago],
[Measures].[1daysago],
[Measures].[Period]}
ON COLUMNS,
NON EMPTY (UNION(({Filter({[Capsule].[Capsule].Members},
(NOT ([Capsule].[Capsule].CurrentMember.Name MATCHES ".*test.*")))} * {[Creative].Children}),
({[Capsule].[Total Live Capsules]} * {[Creative]}),
ALL))
ON ROWS
FROM [FUNNEL CUBE]
WHERE
({[Site].[${VAR_PARTNER_NAME}].[UNKNOWN].[${VAR_CLIENT_NAME}]} * {[Bucket Type].[TREATMENT]} * {[Ad Type].[Ad Type].[Retargeted]})

How to pass on multiple values

It is very likely that you have to populate nearly the whole dimension, i.e. [Login Date.Monthly Calendar].[Monthly Calendar All].[Year].[Quarter].[Month].[Day] which would look like this: [Login Date.Monthly Calendar].[Monthly Calendar All].[2009].[Q3].[Jul].[2]

Now it gets pretty interesting: Download the latest built of PRD from here, as this will not work in PRD 3.5. The latest built includes a post-processing formula option for a parameter. Once you have the latest version installed, create a new report.

Add a parameter called my_date, choose Date Picker as type and String as format type. Now we have to write an open formula for the Post-Processing Formula entry (which is within the parameter definition dialog).

I would advise you to do this step by step, outside of the parameter definition dialog, by defining several open formula functions to extract the year, month, quarter and day part of the date and to prepare all of them separately accordingly. Drag and drop each formula function onto the report header and run your report to see if the results are correct. Once you are sure that all the results are correct, add another open formula function and concatenate all the other formula functions and additional strings that will make your final parameter. Drag and drop this final formula again on the report header and test the output.

In my case, the formula looked like this (and this must be the most impressive open formula that I have ever written):

=("[Login Date.Monthly Calendar].[Monthly Calendar All].[" & YEAR([my_date]) & "].[" & "Q" & IF(MOD(MONTH([my_date]);3 )=0; MONTH([my_date])/3; INT(MONTH([my_date])/3)+1) & "].[" & IF(MONTH([my_date])=1;"Jan";IF(MONTH([my_date])=2;"Feb";IF(MONTH([my_date])=3;"Mar";IF(MONTH([my_date])=4;"Apr";IF(MONTH([my_date])=5;"May";IF(MONTH([my_date])=6;"Jun";IF(MONTH([my_date])=7;"Jul";IF(MONTH([my_date])=8;"Aug";IF(MONTH([my_date])=9;"Sep";IF(MONTH([my_date])=10;"Oct";IF(MONTH([my_date])=11;"Nov";"Dec"))))))))))) & "].[" & DAY([my_date]) & "]")


So now that we are sure that the formula is working, we can go back to our parameter and insert this whole formula into the Post-Processing Formula field. Note: You have to pass the formula itself, and not reference the open formula function!

Now add your data source and include the parameter. 

In my case, the query looks like this:

SELECT
Parameter("my_date",[Login Date.Monthly Calendar],[Login Date.Monthly Calendar].[Monthly Calendar All].[2009].[Q3].[Jul].[4]) ON ROWS,
[Measures].[Sessions] ON COLUMNS
FROM
[Logins]

That's it. I am happy that this finally works within PRD! 

The below info was kindly provided by Thomas (His blog you can find here)

More info

The following info was taken from Thomas Morgner's blog

After a million or so complaints about the complexity of building parametrized queries with the PARAMETER function within MDX, today I indulged to the demands of the crowd.

Beginning with Milestone 2 of the Pentaho Report-Designer 3.6,we now support parameter injection via the${parameter} syntax. Parameter values injected via that syntax will not be checked in any way, so it is the designer's responsibility to ensure that everything is quoted correctly to cause no harm or to break the query. With great powers comes great responsibility. 

The ${parameter} syntax for MDX is not just a toString() conversion. It follows the MessageFormat syntax and thus allows to format Date and Number objects properly before inserting them into the MDX query. An extended format rule allows to produce quoted MDX-string literals by specifying the subformat <string. These strings start and end with a double-quote and all double-quote characters found in the original string get escaped according to the MDX grammar.

So now I can finally answer the question on how to parametrize a Date-Axis from a Date-parameter. To produce a member string like [2009].[10].[4] from a parameter called dateparam use[${dateparam,date,"yyyy"}].[${dateparam,date,"MM"}].[${dateparam,date,"dd"}] in your MDX query.

I still haven't found out how to do the same with the PARAMETER function.

Support for the PARAMETER function will remain there (as in theory it is a good idea to have prepared/explicit parameter).


You can test this functionality with either the latest CI build or with the upcoming Milestone 2 of the Report-Designer 3.6.

This part was originally published by Thomas Morgner on the Mondrian mailing list:

String parameters are passed into Mondrian without any postprocessing

by the reporting engine (if the parameter is a java.lang.String, otherwise we will throw a very visible exception). You are using a StrToMember which probably does not like the "{..}" syntax of the sets. Try a StrToSet instead, if you intend to pass a set.


You can also skip the parameter function and use the classical ${...} parametrization syntax (PRD 3.6 or newer), which performs a simple replacement on the MDX string. To prevent code injections you can specify a subformat along with those parameters: ${param} will include the parameter without any modification.


${param,string} will quote the parameter value as MDX string (slap double quotes around it and escape all inner double quote chars)


${param,formattype,formatstyle} will treat the parameter as a message format request, so that you can format numbers, dates and so on properly.


For the grammar of formattype and formatstyle have a look at http://download.oracle.com/doc


How to create a parameter for a set (posted by Bill W.): 


WITH SET [selectedset] AS ${CSVClient}
select [selectedset] ON COLUMNS,
 {[Measures].[Count of Employees], [Measures].[Count of Declined], [Measures].[Count No Record], [Measures].[Count Requested But No Vaccination Record], [Measures].[Count Vaccinated], [Measures].[Count Vaccinated Elsewhere], [Measures].[% Vaccinated], [Measures].[% Declined], [Measures].[% No Record]} ON ROWS
from [Monthly Influenza Summary]
where ([Monthly Period].[${SelectedYear}])

I am using PRD parameters to get the set of customers in a multi-select, then I turn that set into a formatted array which I use in the query. The basics are there now.

XPath


  • also uses the ${param} syntax to have parameters. (Same syntax as JDBC)

Reflection/Java Method Call

  • parameters are given as plain names, no special encoding needed.
  • parameter are separated by comma
  • if the parameter-name itself contains a comma or double-quotes, quote the parameter-name by
    • enclosing it in double quotes
    • any inner double-quote character must be quoted by another double quote. (Parameter name : Joe"A" becomes "Joe""A""")

Pentaho Data Integration (Kettle)

You can only use a Kettle (PDI) data source in version that was released after Pentaho Report Designer 3.5. 
  • Parameter are declared explicitly
  • PDI only accepts strings, so if the parameter is no string, then it will be converted via "toString()"
  • KTR file needs to declare the parameter and arguments

Scriptable

  • Scripts have a "dataRow" variable that implements org.pentaho.reporting.engine.classic.core.DataRow.
  • Scripts also have access to 
    • "configuration" (org.pentaho.reporting.libraries.base.config.Configuration)
    • "resourceBundleFactory" (org.pentaho.reporting.engine.classic.core.ResourceBundleFactory)
    • "resourceManager" (org.pentaho.reporting.libraries.resourceloader.ResourceManager)
    • "contextKey" (org.pentaho.reporting.libraries.resourceloader.ResourceKey)

This article would not have been possible without the help of various members in the Pentaho forum, which you can find here.

Referencing parameters in the message field

Using parameters in message fields works the following way:

Year: $(parameter)

If you want to format the parameter in a special way, you can specify the format like this:

Year: $(parameter, number, $ #,###.##)



65 comments:

  1. Regarding:

    > Open --- issue is that you cannot use a
    > formula, as it is executed after the query.

    I'll ask Thomas about that. Maybe we can fix it.

    Julian

    ReplyDelete
  2. The mdx issue is already implemented. Parameters now support post-execution formulas

    ReplyDelete
  3. Thanks Julian and Pedro for your feedback! I'll download the latest build and have a look.

    ReplyDelete
  4. You are welcome! I hope that (new) users now have an easy start with using parameters, as it took me quite a long time to gather all these information.

    ReplyDelete
  5. hi, is there any document/article that explains the usage of scriptable datasource? Any guidance will be of great help

    ReplyDelete
  6. I don't have any tutorial on this one, maybe you can find something with a Google search.

    ReplyDelete
  7. Hello. I'm trying to use a parameter in with xpath.

    For example : //myrecord[id=${master_id}]/subrecord

    It's doesn't working : javax.xml.xpath.XPathExpressionException

    ReplyDelete
  8. I can use ${myVariable} for a single member MDX query, but how do I get multiple members? Is it possible to simply place a ${variable} in a MDX query to have dynamic rows or columns? Thus,
    select ${myVariable} on COLUMNS,
    {[Measures].[MyMeasure1], [Measures].[MyMeasure2]} on ROWS
    from [Monthly Data]

    Where myVariable can be [Client].[Location].[All Locations] or {[Client].[Location].[WASHINGTON], [Client].[Location].[COLORADO]}

    I have been trying to get this in PRD and have not been able to do so.

    ReplyDelete
  9. I saw that you received the answer already from the Mondrian mailing list ... I added the solution now also to this article.

    ReplyDelete
  10. dear mr.Julian hyde

    how if i want use currentdatemember in
    SELECT
    LastPeriods(6,Parameter("my_date",[Login Date],CurrentDateMember([Time],'"[Time]"\.[yyyy]\."[Q-"q"]"\.[mmm]').lag(1)))
    ON ROWS,
    [Measures].[Distinct Users]
    ON COLUMNS
    from [Logins]

    and then i put clause where parameter("dateparam",[Time],[Time].[All Times])

    so i using the same dimension(Time) in one mdx queries

    ReplyDelete
  11. Hi,
    As far as I know you cannot use the time dimension in both the rows and the where clause. What are you trying to achieve?
    Best regards,
    Diethard

    ReplyDelete
  12. I converted a 3.0 report to a 3.5 adding an external data source (via xaction). In a chart within the report, there was a parameter ${year} in the label of the chart. That very same parameter works in other parts of the report but in the chart, it displays ${year} as if it is text. Any ideas?

    ReplyDelete
  13. Are you sure this worked in 3.0? I tried the same in the latest PDR and it doesn't seem like the x or y label does accept a variable.

    ReplyDelete
  14. It definitely worked in 3.0. I found the solution. Create a function (message format) for the variable ($varYear) in this case) and make the message pattern = the text and the $(varYear) variable. Then in the chart itself, remove the chart title (field should be blank) and instead use the field chart-title-field and put that function in there. Worked like a charm.

    ReplyDelete
  15. I have another conversion issue. Multiple selections in a list of variables worked in 3.0 but does not in 3.5. Instead of showing the variable list selected via xaction, I get this on the report: [Ljava.lang.String;@156ed5d as the data result. Selecting only one variable in the multi selection list works just fine.

    ReplyDelete
  16. Yes, for the chart title this works. You were mentioning label, not title, in your comment, hence it was not quite clear.

    ReplyDelete
  17. Regarding multi selecting: set the parameter type as object.

    ReplyDelete
    Replies
    1. Hi Diethard,

      I am having a master prpt report with a bar chart. The bar chart has a drill down url to another prpt report. The master report has a multi select drop down box and parameter is getting passed to the drill down prpt. I set the parameter type as object as you have suggested in the second report. Still I am getting the value as Ljava.lang.String;@156ed5d in my textbox when I display it. Can you help me out in using this multi select parameter in my second report query. By selecting parameter type as object do we need to do anything else in post processing formula or something?

      Delete
  18. One comment "Using parameters with Metadata/MQL queries" in PRD 3.6.0:

    When using the query designer, the Parameter has to be given as {paramname}. Eventually, this gets in replaced to [param:paramname] in the MQL XML definition. But if you initially use the [param:] syntax, the MQL XML shows the param in quotation marks - which is obviously was not intended.

    ReplyDelete
  19. i am trying to do the same but it does not work.
    My mdx query is: select NON EMPTY {[Measures].[nuevas]} ON COLUMNS,
    NON EMPTY [dactivo.jactivo].[All dactivo.jactivos].Children ON ROWS
    from [Cincidencias] where (Parameter("inicio",[dfincidencia],[dfincidencia].[2000].[1].[5]))

    where inicio is a date picker parameter. My time dimension have the hierarchy like that [dimension].[year].[month].[day]

    I have create the parameter like you say and i am try to put in the post procesing formula:
    =("[dincidencia].[" & YEAR([inicio]) & "].[" & MONTH([inicio]) & "].[" & DAY([inicio]) & "]")

    but it doesn´t work. It says "not enough data for computation"

    Can you give me some advice of what do i have to do? thanks

    ReplyDelete
  20. Did you try to check this formula? Is the output what you expect? The easiest way to check this is to create a formula and drag and drop it onto the report header. Let me know then if the output is correct.

    ReplyDelete
  21. hi,

    I am using pentaho report 3.7 and I would like to be able to chek the parameters before processing them and display a message if they are not valide.

    I have two parameter that the user need to pick up datebegin and dateend:
    i need to have dateend>datebegin

    Could you please help me.
    Thanks

    ReplyDelete
  22. Hi,
    In the simplest case I would use a formula in the report header to figure this out and display a text in case the chosen parameters are wrong.
    Let me know if this works for you!
    Best regards,
    Diethard

    ReplyDelete
  23. Hi,

    thanks for your quick reply and your help. Since I am newby in pentaho, could you pleaseee tell me exactly where in the report header , i should use a formula.
    (the output of the report should be a pdf or html page)

    ReplyDelete
  24. Hi,
    In the data tab, right click on functions > "Add function" > "Common" > double click on "Open Formula". You should see now a Open Formula function under Functions in the data tab. Mark it. In the properties section (just below) click the value field for formula. A small grey icon will appear on the right, click on it and the formula editor will show up. Create a formula like this one (adjust it to your needs):
    =IF([end_date]<[start_date];"Please select a valid time period. Your chosen start date is older than the end date";"")

    Click OK. Now drag and drop the function from the data tab onto the report header. Preview your report and it should work.

    ReplyDelete
  25. thanks a lot! I get it work.
    But Is there a way so that the report don't show up if the parameters are not correct, I get only a message in a pop up or....
    I really appreciate your help.

    ReplyDelete
  26. Well, you can hide any report elements based on a formula. If you want a pop up, I am not too sure if such a functionality currently exists within PRD. Try to do a Pentaho forum search ... maybe somebody else had the same requirement and found a solution.

    ReplyDelete
  27. ok thanks! you helped me a lot

    ReplyDelete
  28. I'm having difficulty with date formatting; it seems that whenever I use the DatePicker widget, once I've selected a date there is a message displayed below the selected value indicating that the format is incorrect, and no results are returned from the query. Any ideas?

    ReplyDelete
  29. As an amendment, I misstated the error message. The exact error is the incomprehensible and uninformative "This parameter value is of an invalid value."

    ReplyDelete
  30. When you set up the parameter, did you choose date as the data type?

    ReplyDelete
  31. Diethard,
    I have one problem, I don't know if you can help me, but I hope.

    I'm using Pentaho 3.5 and I create a report using the Pentaho Report Designer 3.0
    I'm trying to send a parameter to the SQL query, but nothing are happening. When I look at the DB the place where the parameter should be, there is only the command.Neither the defaut value had be used.

    When I use the PRD 3.0 I have to call the parameter like this {PREPARE:param}. right?

    I have already put the started the variable into the input tag of the xaction and into input tag of the action-definition.

    I have already check everything, the query is right, the parameter is send correctly.

    thanks,
    Filipe

    ReplyDelete
  32. Hi Filipe,
    If you are using a SQL query, then you define the parameter like this in Pentaho Report Designer: ${Parametername}
    I hope it helps. This everything first in PRD and only when this works fine, use the report in your Xaction.
    Best regards,
    Diethard

    ReplyDelete
  33. Diethard,
    thanks a lot, worked like I wanted.
    I only had to remove the $.
    but thanks, i was already thinking in a another way to create the report.

    Filipe

    ReplyDelete
  34. Hi Diethard, Filipe,

    I am facing problem in fetching data while using few parameters, the report is not fetching data while using the parameter in the main report query.

    Please find the query I have written as follows:

    select cola,colb,colc
    from table_name
    where
    cola like concat(${param_1}, '%') and
    colb like concat(${param_2}, '%') and
    colc = ${param_3}

    The parameter I have defined using add parameter and it is as follows:

    param_1:
    name : param_1
    label: param_1
    datatype:string
    query: select distinct cola from table_name
    value: cola

    param_2:
    name : param_2
    label: param_2
    datatype:string
    query: select distinct colb from table_name
    value: colb

    param_3:
    name : param_3
    label: param_3
    datatype:date
    query: select distinct colc from table_name
    value: colc

    {;ease help me understanding where things are going wrong for me, in defining the parameter or in some other place.I am really stuck in this issue.

    ReplyDelete
  35. Your problem lies in your query ...
    cola like concat(${param_1}, '%')
    will end up being
    cola like "value"%
    Use a post processing formula to add the % to the parameter value so that its correctly passed to the query. change your query to
    cola like ${param_1}

    You can define the post processing formula where you set up the parameter initially. Good luck!

    ReplyDelete
  36. hi guys
    i am newbie on pentaho reporting. i am trying to parameterize a mdx to select between all places, countries and cities, etc..
    how can i parameterize this way:-

    SELECT ............. ON ROWS
    ............... ON COLUMNS
    WHERE [Places].[${continent}].[${country}].[${city}]

    The problem is when i select "All Places" for continent i should get results for [Places].[All Places] and for specific Continent and country [Places].[Africa].[South Africa]
    But the query becomes [Places].[#null]...
    how can i do such kind of parameterization??

    ReplyDelete
  37. This seems to be a bit tricky. I would probably use a formula to prepare this set. It depends on how you set up your parameters. In the easiest case you could just use a parameter post processing formula.

    ReplyDelete
  38. Hi Diethard,

    Need help with the mql below. I do not get the result set if I enclose the [param:wg_input] with ", nut get "parameter Not Found" error if I exclude ".

    Please guide me.


    steel-wheelsBV_MODEL_1falseCT_CATEGORY1BC_ROLLINGSCORECARD_CURRENT_19_WORKGRPNONECT_CATEGORY1BC_ROLLINGSCORECARD_CURRENT_19_METRICNONECT_CATEGORY1BC_ROLLINGSCORECARD_CURRENT_19_PERIODNONECT_CATEGORY1BC_ROLLINGSCORECARD_CURRENT_19_THISVALUENONEANDEQUALS([CT_CATEGORY1.BC_ROLLINGSCORECARD_CURRENT_19_WORKGRP];"[param:wg_input]")

    Thanks for all your help in advance.

    Regards,
    Rajesh

    ReplyDelete
  39. Hi Rajesh,
    First thing to do is to check if your parameter is working properly. I suggest dragging and dropping the parameter item to the report header and just check if a value shows up when you preview the report and choose a parameter value. If this is ok, try to double check the spelling of your parameter name. 3. Try to use the parameter in the MQL query without quotation marks ... this might be the actual mistake.
    I hope this helps!
    Best regards,
    Diethard

    ReplyDelete
  40. Hi,

    I am facing a problem with parameterized charts.
    Parameterized chart only shows the graph after selecting the value from the drop down box. What needs to be done, if i want to display the full chart(without any parameters) first, and after that display the contents of selected parameter...
    Please guide

    Thanks & Regards
    Neha

    ReplyDelete
  41. It all depends on how your report is set up. You can change your query so that it can work without any parameter or you can define a default value for your parameter. The latter option is the better one. When you set up a parameter, just assign it a default value. Nice and simple.

    ReplyDelete
  42. hi,
    as far as my MDX parametrisation using the ${} syntax goes, the preview does not give me any data( as it reads the parameter as null.) But if I use a static query to fetch the field list, then change my report query to the parametrised one, it seems to work.. is this the way to make it work! i feel its a bit dirty!

    ReplyDelete
  43. Yes, this is quite fine. You can also create two queries: one static and one dynamic. You can either switch between these two ones by right clicking on one of them and choosing "Select", or you could use a go to the Structure View, mark the Master Report element, click on the attributes tab and define the static query name in the value field and the dynamic one in the formula field (as =[dynamicqueryname]). I think this way you can use the static one for design purposes and once you execute the report, the formula will overwrite the static query name with the dynamic one.

    ReplyDelete
  44. It's difficult to say without seeing the report. Can you create a sample based on the sample data sources in the report designer?

    ReplyDelete
    Replies
    1. Sure Diethard. I will try to create a sample prpt.
      I have a prpt report with multi select box filter and the report contains a bar chart which has drill down option. I selected two values say 1 and 2 from the multi select box filter and the selected values are appearing in the drill link like this:

      http://url/report2.prpt?mutliparam=1&multiparam=2 but in the report2.prpt I have declared the parameter as string type, so it showing error like it cannot hold object value or something. So I changed the parameter type to Object type. Now the error is gone but I am getting the value as some Ljava.lang.String;@156ed5d. Basically what we are trying to do here is we need a drill down chart to be opened with values passed from the multi select box via URL.

      Delete
  45. Hi Diethard,

    I have a prpt report with a parameter of type object. There is no query data set associated with that parameter since parameter value is passed to this report from the URL. The passed parameter value is from a multi select drop down so we have set the parameter type as object in the second report. We have used this parameter in the IN clause of the SQL query in the second report but it is not getting substituted due to some reason. But in the first report I am able to declare a parameter of type Object which displays the values from a query and I am able to use that parameter in the IN clause of the SQL query of First Report. To which emailId I need to send this PRPT files to you?

    ReplyDelete
    Replies
    1. Sunil, I tried to find you on the Pentaho forum ... what's your username there? I'll try to send you a message there. Or can you upload the file to dropbox or similar and post a link here?

      Delete
  46. Please upload it somewhere to the cloud and provide a public URL here. Once I have time to look at it I'll let you know.

    ReplyDelete
  47. This comment has been removed by the author.

    ReplyDelete
  48. We can't seem to construct a working Message formula after moving our reports from SQL to Metadata. For example, this message is a simple true/false decision:

    =" for all " & IF([Performing_Parm]="1";"performing ";"")& "ratings"

    A message with "=[Performing_Parm]" displays results like "Ljava.lang.String;@156ed5d" however, as discussed in earlier posts, changing the parameter type from STRING to OBJECT did not fix the issue -- what are we missing?

    In advance, thank you for your assistance!

    ReplyDelete
    Replies
    1. This is a bit difficult to answer as I don't know your underlying metadata model. Is there any useful error message in the log apart from what you posted above?

      Delete
  49. How TO Add Parameter for OPenERP Data Access?
    In Filters of the Query, i added a filter with value "${name}"
    and i added a Parameter as "name". Still, its not working.
    Can u help me?

    Regards,
    Murali

    ReplyDelete
    Replies
    1. Are you connecting via JDBC or JNDI to the openERB postgresql database? You can post your SQL query here and then I can have a look at it.

      Delete
  50. Hi Diethard,

    Can you please post a working example in Pentaho CDE on passing start_date and end_date parameters ?

    I have Google500,Date(Date is having Year,Quarter,Month,DayOfMonth levels)and etc as Dimensions in the Cube. I am writing the below query as you suggested @ MDX section. I'm able to draw a line chart with out where condition from some year to other year?
    How can I display data on line chart from start_date to end_date with in MDX ?

    Could you please help me?

    SELECT
    {[Google500]} ON COLUMNS,[Date].Children ON ROWS FROM [GoogleCube]
    WHERE [${start_date,Date,"YYYY"}].[${start_date,Date,"MM"}].[${start_date,Date,"dd"}]

    (tried with only start_date for testing purpose-but not succeed)

    Thanks,
    Sadakar

    ReplyDelete
  51. hi can any one tell how to make a report...that if i click on one region in chart it has to display that information in it..means it has to go to another query...plz explain me clearly

    ReplyDelete
  52. Diethard, this article is the best documentation I've found yet on MDX parameters in PRD.

    In Mondrian / PRD 5.0, parameters are still not trivial.

    My current problem is in providing the value of the right type. I have a fairly simple WHERE clause:
    WHERE (
    Parameter("nYear", [Dim Date.YearNum], [Dim Date.YearNum].[All Dim Date.YearNums].[2013]),
    [Dim Date.MonthName].[All Dim Date.MonthNames].[October])

    I have created a 2nd datasource to provide all the YearNums defined in my date dimension in a Drop Down list. But ... what to select as the Value? ::column::0 ? The right numbers appear (2009, 2010, etc), but if I select any of them I am told "This prompt is of an invalid type".

    Help with selecting the right options when creating the PRD parameter would be immensely valuable!

    ReplyDelete
    Replies
    1. Thanks for your feedback!
      So you created a parameter called nYear, you have a dedicated query for it which returns all the years, and in the parameter dialog you set value to ::column::0 ? This is the only column in your dataset? ... what type is this parameter set to?

      Delete
    2. Thanks for the reply!

      I have tried Number, Integer, and String for the type.

      Current settings are:
      Name: nYear
      Label: Year
      Value Type: Integer
      Data Format:
      Default Value:
      Default Value Formula: =["[Dim Date.YearNum].[YearNum].[2013]"]
      Post-Processing Formula:
      Display Type: Drop Down
      Query: yearQuery -- that's my dedicated query which returns all the years
      Value: ::column::0
      Display Name: [Dim Date.YearNum].[YearNum]
      Display Value Formula:
      All check boxes are unchecked.

      Am I messing up something simple? I've set up an example with sample data that I detail at
      http://forums.pentaho.com/showthread.php?156687-Reporting-from-MDX-data-source-still-can-t-make-it-work!

      I really think that if you or someone can help me get one of these to work, I'll be able to finish out this project.

      Thanks so much.

      Delete
  53. Hi Mr. Diethard Steiner, nice blog with this kind of nice information. Thank you. I just have a question. For me it's been easy make database sql with parameters in many environment, but using pentaho it looks like hell. I'm trying to make the kind of sql like this: select fields from tables where condition1 and condition2... where the conditions can apppear or dissappear if someone clicks a checkbox, radiobutton, etc... But it has been almost impossible to do that via the parameter stuff in pentaho. I made a test like this: select fields from tables where field1 = ${parameter1} and it works fine, but if I want a more complex example, like: select fields from tables where field1 = condition1 and ${parameter2} and the value of parameter2 is "field2 = condition2" it doesn't work... I want me to do that way because parameter2 appears only when I click on a checkbox or button.

    ReplyDelete
  54. Dear Mr Steiner

    Thanks for your article and your blog. They are very useful.

    I have a requirement where I need to set the default value for a date parameter.

    I need to fetch the date value using a query.

    I am using PRD 3.9.1. Is it possible to do this in PRD?

    Thanks a lot in advance.

    Regards
    Vadi

    ReplyDelete
    Replies
    1. Yes, when you set up the parameter you can set a default value for it (which can be hard coded or dynamic).

      Delete