Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Desperately need example of using secure filter result in MDX query

  1. #1
    Join Date
    May 2006
    Posts
    104

    Question Desperately need example of using secure filter result in MDX query

    We're using Pentaho Analysis 2.2.2. How does one pass the user's chosen value from a secure filter, say a company number, on to an MDX query in an action sequence to filter the result accordingly? Does it work in 2.2.2 and/or 2.3.2 and if so, how? I've looked and looked for answers, but to know avail. The question comes up time and again in the forum, but remains unanswered. We and others here in the forum need a how-to for this.

    Thanks.
    Ralph

  2. #2
    wgorman Guest

    Default

    To see how parameters are passed into the MDXLookupRule and PivotViewComponent components, check out these two examples in Pentaho's example solution repository:

    MDXLookupRule Example:
    steel-wheels/homeDashboard/topnmdxquery.xaction

    PivotViewComponent Example:
    samples/analysis/query1.xaction

    First, an action input is defined in the component xml. Second, as part of the MDX query, a reference to the action input is specified via "{ACTION_INPUT_NAME}". These values are resolved and then passed into Mondrian.

    For an example of a secure filter passing action output's to another component, take a look at this example:
    steel-wheels/reports/Sales_by_Product.xaction

    Here is some documentation on secure filters on our wiki:
    http://wiki.pentaho.org/display/Pent...28Prompting%29

    Hope that helps!

    Will

  3. #3
    Join Date
    May 2006
    Posts
    104

    Default

    Thanks for the tips, Will. I know about secure filters, but never got the {my-selected-value-from-the-filter} to work with mdx. I'll immediately take a look at the two examples you suggest.
    Ralph

  4. #4
    Join Date
    May 2006
    Posts
    104

    Default

    Will's suggestions were the most useful and I got it to work. The selected value from the secure filter, in our case the company number, can be used in the Pivot view/mdx query, for example in the WHERE clause like this : ..WHERE ([Company].[All Companies].[{selectedCompany}]). If it's used in the WHERE, then it can't be also listed in the ROWS clause, otherwise JPivot throws an error. It can be listed in the ROWS clause like .., [Company].[All Companies].[{selectedCompany}], ... and not in the WHERE, which results in the selected value being shown like a constant in the JPivot table.

    Thanks again, Will!
    Ralph

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.