Hitachi Vantara Pentaho Community Forums
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Passing parameter from multi selector to mdx query

  1. #1
    Join Date
    Dec 2012
    Posts
    121

    Default Passing parameter from multi selector to mdx query

    Hi all,
    i have a multi selector component from which users can choose multiple field values. These values are then saved in a simple parametern and passed to a MDX query. The problem is that if the selection is relative to one element, it works. Instead, if i select at least two choices from the multi selector, it does not work!!
    I post my query:

    WITH SET [mioset]
    AS '{[TipologiaConnessione.default].[tipologiaconnessione].[${p_conn_type}]}'
    SELECT
    NON EMPTY {Hierarchize({[Measures].[avg_latency]})} ON COLUMNS,
    NON EMPTY {Hierarchize({[Operatore.default].[operatore].Members})} ON ROWS
    FROM [WindCube]
    WHERE {
    [mioset]
    }

    My simple parameter is p_conn_type.


    I would like to do something like this(i write it in a sql-like language):

    select ... from ... where [TipologiaConnessione.default].[tipologiaconnessione]IN (p_conn_type)



    Any suggestions are accepted.
    Yamas

  2. #2
    Join Date
    Jul 2007
    Posts
    2,497

    Default

    make sure your parameter is a fully qualified mdx member, and then do WITH SET [mioset] AS '{ ${p_conn_type} }'
    Pedro Alves
    Meet us on ##pentaho, a FreeNode irc channel

  3. #3
    Join Date
    Dec 2012
    Posts
    121

    Default

    Hi pedro,
    thanks for the reply.
    Could you be more accurate?
    What you mean for "fully qualified mdx member"?

    p.S: how the multi selector pass the selected values? Is the format as follow: choice1, choice2, ...,choiceN ?



  4. #4
    Join Date
    Jul 2007
    Posts
    2,497

    Default

    [Full].[Member].[Name]. You can also play with StrToSet stuff

    It joins the elements separated by ','
    Pedro Alves
    Meet us on ##pentaho, a FreeNode irc channel

  5. #5
    Join Date
    Dec 2012
    Posts
    121

    Default

    Ok, but i have not understood how to pass my params to my mdx query.
    From the multi-selector component i receive a list like (1,5,7), that is a list of integer separated by comma.
    The question is: how can i use them to filter on my query on the dimension "[tipologiaconnessione]"??

    Hope i was clear.
    Yamas.

  6. #6
    Join Date
    Jul 2007
    Posts
    2,497

    Default

    You can either use a different id / value, so that id is the correct one, or implement a preChange function in the selector that adds that bit
    Pedro Alves
    Meet us on ##pentaho, a FreeNode irc channel

  7. #7
    Join Date
    Mar 2013
    Posts
    1

    Default

    Hi Marcolino,
    I have the same problem.
    My parameter is param_sexo and this is my MDX:

    ...
    WHERE [Sexo].[${param_sexo}]

    It works fine when I only select one item, but when I select two items it doesn't work. Any ideas?

    Thanks you!
    Carlos.

  8. #8

    Default Passing Multiple Params

    Hi Guys,

    I think this is a repeated post and I know how frustating it must be for Pedro to answer it again and again.. I was trying to achieve the same thing as with so many more people but Pedro's reply on my post might definitely help you guys.

    http://forums.pentaho.com/showthread...ates-using-CDA


    If not, then here is the thing.
    1. Have your MDX in the way Pedro suggested
    WITH SET [mioset] AS '{ ${p_conn_type} }'

    2. Assuming the p_conn_type might be a filter coming from a multi-select box which was mentioned above. Have your multi-select box contain an ID and Value pair. For example as below:-
    with member [Measures].[Id] as [ConnectionType].currentMember.uniqueName
    SELECT
    [ConnectionTyoe].[Name].Members ON ROWS,
    [Measures].[Id] on COLUMNS
    FROM [Cube]

    Output would be something like [ConnectionType].[Choice].[1], [ConnectionType].[Choice].[2] ... etc.

    3. Now use the CDE / multi select box to use the value and send comma separated ID to the p_conn_type variable. This is what Pedro meant by passing fully qualified member in the query replacing parameters.

    Hope this clarifies..

    Quote Originally Posted by carloszzr View Post
    Hi Marcolino,
    I have the same problem.
    My parameter is param_sexo and this is my MDX:

    ...
    WHERE [Sexo].[${param_sexo}]

    It works fine when I only select one item, but when I select two items it doesn't work. Any ideas?

    Thanks you!
    Carlos.

  9. #9
    Join Date
    Jan 2013
    Posts
    135

    Default

    Hi tarun and Pedro,

    thanks for your helpful posts. I got my MDX query working with multiple selector(!) - mostly the same as tarun said, I created SQL table where I have "nice name" and then in [].[] format for mdx. Sending paramXY as [].[] my query works.
    But how to do that with firechange - there I do not have possibilty to send my [].[] format, only values of paramXY in nice format. I was searching wheather it is possible to make concatenate in MDX, but I was not sucessful.

    Thanks a lot for any ideas ! Direction how to get further will be very appreciated.

    Beste regards,
    timfu83


  10. #10
    Join Date
    Jul 2007
    Posts
    2,497

    Default

    Quote Originally Posted by timfu83 View Post
    Hi tarun and Pedro,

    thanks for your helpful posts. I got my MDX query working with multiple selector(!) - mostly the same as tarun said, I created SQL table where I have "nice name" and then in [].[] format for mdx. Sending paramXY as [].[] my query works.
    But how to do that with firechange - there I do not have possibilty to send my [].[] format, only values of paramXY in nice format. I was searching wheather it is possible to make concatenate in MDX, but I was not sucessful.

    Thanks a lot for any ideas ! Direction how to get further will be very appreciated.

    Beste regards,
    timfu83

    Did you try passing an array as the second arg of fireChange?
    Pedro Alves
    Meet us on ##pentaho, a FreeNode irc channel

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.