Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Generic mdx columns

  1. #1
    Join Date
    Jan 2008
    Posts
    17

    Default Generic mdx columns

    Dear all,

    I was wondering if it is possible to create a special report, having following properties:

    - uses an mdx query that contains the generic expression ".children" or ".members".

    - contains the columns created by that generic expression, every time with the actual members in the datasource.

    This would be very usesful as otherwise I would need to manually create a new report every time these members change.

    It might be possible with a formula or subreports but up to now I haven't found anything suitable.

    Thank you a lot for any help or suggestions.

    Best regards

    Benedikt

  2. #2
    Join Date
    Jan 2008
    Posts
    17

    Default Possible Answer

    Hello,

    in order to answer my own question:

    I made it by using Pentah Design Studio and Report Designer and as follows:


    • I created a "template"-prpt-Report in Report Designer that uses a TableModel parameter from an external data source. It has as much columns as there possibly might come. Their width is calculated from another external parameter saying the number of columns in the given in TableModel.
    • I built up an xaction in Design Studio that creates a TableModel (in my case from an MDX-Output, using JavaScript)
    • That TableModel (and the mentioned columnNumber) I give in as parameter to the prpt-report template

    Take care

    Benedikt

  3. #3

    Default

    Is this possible to do in PRD 3.6 without all the extra Design Studio XAction manipulation? I have been racking my brain with a more straight forward approach to dynamic columns for MDX reports. I have tried creating a query that builds up the member clause then passing a paramer (${myMems}) to the MDX query:

    select ${myMems} on Columns,
    [Measures].[My Measure] on rows
    from [my cube]

    but this does not work. Two major issues:

    1) What do you put as the fields in PRD? You cannot make a field with name ${myMems} to hold more than one member value.

    2) The query does not parse in PRD until runtime, so you do not have access to the fields.

    The prebuilt table could work if you are just displaying up to a specified number of members. Still not sure how to direct the appropriate values to the appropriate fields.

    Bill W.

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

    Default

    To question 1:

    Given the fact that the ${..} parametrization is just a string replacement, you can use for instance a formula to get multiple values into a single comma separate string. Something like

    ="{" & CSVTEXT([array]) & "}"

    produces exactly the set syntax Mondrian expects from you. Array is anything containing a array, like a multi-selection parameter.


    Q2: Yes, that is a known limitation but wont be fixed until 4.0. Also be aware that the column names of the result change when you fiddle with the columns axis on a MDX query. With PRD-3.7, we have a more generic index-based access to columns (in addition to the traditional name-based one) which makes those dynamic reporting scenarios a bit easier to implement.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  5. #5

    Default

    Thank you. I finally figured out the CSVArray() expression and got that working. I will post an example using SampleData so that others heading down this path have some additional input. We have lots of examples of SQL based queries, but MDX was a little different. Now that I have the members in an array, I think I can complete 90% of the problem. I can use the members of the array as field variables. This will be completely within PRD which was a goal of mine since my customer is already under water with PDI, PME, Schema Designer, etc.

    One issue that I was having was that query fields were losing connection to report fields. I need to look into this some more, but it seems that once you parameterize a query and the fields are no longer explicitly available there is a connection lost to the report fields.

    Bill W.
    Last edited by billw; 07-21-2010 at 08:03 PM.

  6. #6

    Lightbulb

    Here is my solution using the SampleData database.

    Attached is a very simple report definition file that uses and MDX query as the basis for the data displayed. The problem was to provide a variable for the columns. A user should be able to have one or more columns displayed. This is easy in PAT or JPivot, but a directed adhoc query was needed for a more controlled interface.

    The base query uses a variable for the columns:
    WITH SET [myProducts] AS ${pProductArray}
    select [myProducts] ON COLUMNS,
    {[Time].[All Years].[2003], [Time].[All Years].[2004], [Time].[All Years].[2005]} ON ROWS
    from [SteelWheelsSales]
    where [Measures].[Sales]

    A couple of notes:
    1) A query is used to get a list of distinct product lines: ListProductLines.
    select '[Product].[All Products].['||productline||']' as productmember
    from products
    group by productmember

    2) A parameter multi-select is set up for pulling the query values: pProductLine
    The user can select one or more values to display

    3) A final parameter variable is set for turning the selected values into an MDX query component: pProductArray
    The trick is the formula - ="{" & CSVTEXT([pProductLine];false(); ",") & "}"

    This formula comma separates the values and adds the curly braces required for MDX.

    4) Also, note that when you are creating your own report, you will need to have the columns in your query so that you can use them as display fields. This is not really necessary for those that know how to set up variable based fields, but this will trip up beginners. So, create your query, get your fields on the report canvas, then start setting about parameterizing and variables.

    That's it. The next step is to provide a dynamic set of columns on the report itself. I am testing with indexed array variables in PRD. This may require some javascripting so that I can assure array members and not get outside the index value. Any help is greatly appreciated. Thanks to those who pointed me away from the "Parameter()" function and set me on the correct path.

    BR/Bill W.
    Attached Files Attached Files
    Last edited by billw; 07-22-2010 at 12:23 PM.

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.