Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: PRD 3.6.0 - Problem passign Parameters to Subreport

  1. #1
    Join Date
    Jan 2007
    Posts
    485

    Default PRD 3.6.0 - Problem passign Parameters to Subreport

    Hi all,
    I am working with PRD 3.6.0GA-11125 on W/XP against a MS SQLServer 2000 database. I am trying to build a Master (MASTER) report holding 4 subreports (SUBREPORT). MASTER provides a single Parameter (PARAM) that will be used by the 4 subreports.
    I have followed the examples provided in the following books:

    • Pentaho reporting 3.5 for Java Developers (W.Gorman, pages 211-220); and
    • Pentaho Solutions – BI and Datawarehousing with Pentaho and MySQL (R.Bouman & J.van Dongen, pages 404-406)

    My problem is from the beginning: I can’t get the Parameter from the Master Report to the first subreport.
    The following screenshots explain my construct so far:
    My SUBREPORT has been placed in the Report Header of the MASTER. I have set 2 parameters: PARAMa (aGetCompanyName) which “STRING” value (GetCompanyName) comes from a JDBC Query (GetName) and PARAMb (b GetCompanyName), which is the PARAM for the SUBREPORT.
    screenshot1

    PARAMa is a String value coming from the JDBC Query which must be transformed to MDX syntax.
    screenshot2

    I have put the transformation formula in PARAMa as noted in the following screenshot.
    screenshot3

    PARAMb is to be used in SUBREPORT and should receive the transformed String coming from PARAMa. The following screenshot depicts PARAMb
    screenshot4

    The screenshot for the SUBREPORT is:
    screenshot5

    In the SUBREPORT I have set PARAMa (OuterName) to be imported to PARAMb (Inner Name)
    screenshot6

    The SUBREPORT will use an MDX Query which takes the PARAMb string value. The following screenshot shows the Parameter() function in the MDX Query
    screenshot7

    Testing the above MASTER/SUBREPORT kicksout the data I want for the “default” that is specified in the MDX Parameter() function; when trying to get my data with a different string-value for PARAMb, the parameter is not being sent to SUBREPORT’s MDXQuery, beasue I always get the data for the “default”.

    The MDX statement above is tested and works; furthermore, I have a separate report using that same parameterized statement perfectly working.

    Changing the post-processing formula from PARAMa to PARAMb results in “report cannot be initialized” error.

    Passing the parameter from the master to the subreport should be an easy step. Nonetheless, I have tried many different alternatives without success. Your assistance will be highly appreciated.
    Many thanks & kind regards,
    DMurray3
    Attached Files Attached Files
    Last edited by DMurray3; 03-28-2010 at 04:49 PM. Reason: to indude report file

  2. #2
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    After changing the queries of the parameter and subreport to a more innocent Steelwheels query and dropping a string-field for the parameter into both the master report (GetCompanyName) and subreport (bGetCompanyName) the value shows up nicely.

    So your problem is not the parameter passing but the MDX parameter function - it seems that it does not interpret the values you pass in correctly.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3
    Join Date
    Jan 2007
    Posts
    485

    Default

    Hi Tomas,

    Again many thanks for your quick response.

    I have basically the same set up of string-value and mdx query with the parameter function on a report (a test of the subreport I wanted to use).

    This works correctly, that is: Reading the string-values to provide the parameter list from a jdbc query, post-transforming the string-value received to MDX syntax and further passing this to the Parameter() function of an MDX Query. The only difference would be naming of variables. I am enclosing the report I am making reference to, for your review.

    Looking at the two report files I provide (the first one in my original thread, the s2nd in this), can you point me in some direction to correct my problem, for example:

    Is there any specific order on how to set the queries and param up (ie. 1st do params, then do queries, etc.)?

    Do I need to formaly create 2 PARAMs in the MASTER, or could one suffice?

    In the MASTER, what function do you recommend I use in PARAM2 to say it's value equals PARAM1?

    Where should the post-transform be placed: in PARAM1 or PARAM2?

    Is there any specific place where the JDBC Query and the MDX Query should be placed (ie, both in MASTER, JDBC in MASTER, MDX Query in SUBREPORT, both in SUBREPORT?

    Can you advise where / how can I see how the parameter variable is comming from the MASTER to the SUBREPORT as to diagnose my problem?

    I am really lost on this one.... regardless that I have searched forums, followed contributions made by community members, read the "books"... I honestly think I've done me homework on this one.... so I trust you can provide some additional support....

    Many thanks and kind regards,
    DMurray3
    Attached Files Attached Files

  4. #4
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    To be honest: As you are using PRD 3.6, forget the friggin' PARAMETER function and use the good old ${param} style in the MDX query. This makes life a lot easier and everything so much more predictable.

    Whether you post-transform in one parameter or in two makes no difference at all. But as the report probably is expected to fail if the value is null, you should include a check for that condition into your post-processing formula by making the parameter mandatory and non-strict-value-checking and the post processing formula something like this:

    =IF(ISNA([Parameter]; NA(); "[dimension][" & [Parameter] & "]")

    This makes sure that if the parameter is null, the formula evaluates to null, and thus the mandatory check kicks in and tells the user to set a good value here.


    You would need two parameter, if you enable the strict-value check or if the formula returns a value of a different type than the parameter's declared value, as these checks are performed after the formula is evaluated and thus it would never match anything. With two parameters, the first parameter can be strict and mandatory and happy while the hidden second one would be non-strict and could even be non-mandatory.

    Queries that are used by parameters must be placed in the master report. Also: Placing all queries into the master report makes no difference at all, as the data-sources get inherited to the subreport anyway. Order does not matter as long as the names are unique and you have none of the "catch-all" datasources like "Advanced SQL" or "Advanced MDX" on your report. These datasources must always be placed last.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.