Post reply

Dynamic MDX in RS

  • May 11, 2005 at 2:17 pm

    #113783

    How do you do a dynamic MDX query from RS?

    I have a huge cube that want the user to select what season to create a report of. Have the dataset with the unique seasons, but how does the statement looks like?

    Mine looks like this:

    Select  { [Measures].[SalesTY]}  on columns, 

    {  filter( [Season].Children , [Season] =[Season].[All Season].@ParmSeason )  on rows

    from mycube

    But when I ran it, got an error saying MSOLAP does not support named parameters ??

    Anybody has tried something likethis?

    Thank you in advance.

     

  • May 11, 2005 at 10:53 pm

    #558258

    Welcome to the world of un-named parameters.  I have found to use parameters with MX you'll need to create a dynamic query string that gets executed.  e.g. the query starts with = "select ......" and you then slot in the parm values from the parm collection, like so     = "select {somestuffhere} on rows, {" & Parameters!MyParm.Value & "} on cols from...."

    if you want to multi-line, you'll either need to do single line statements with ampersands at the begining, or multi-line it and then use a Replace(Chr(10)).Replace(Chr(13)) to the string, like this.... 

    =" select blah blah

     still balh blahing

      and some more

      now at the end of the string".Replace(Chr(10), " ").Replace(Chr(13), " ")

    Based on your original post, this could possibly work depending on the parameter name/s

    ="Select  { [Measures].[SalesTY]}  on columns, "

    & " {  filter( [Season].Children , [Season] = [Season].[All Season]." & Parameters!ParmSeason.Value & ")  on rows "

    & " from mycube"

    Cheers,

    Steve.

  • May 13, 2005 at 8:24 am

    #558731

    Wow!, works great!.You're the man!

    Added a few seconds to the execution, but works exceptionally well.

    Thanks a lot!!!

  • January 12, 2006 at 4:50 pm

    #614510

    I would appreciate it if you could explain how to setup the datasource when using this type of query. I have been searching up and down and cannot find out how to get any '="...' to work in RS. Each different iteration I have tried RS ends up telling me I cannot use the =.

  • January 13, 2006 at 1:18 am

    #614541

    Hey Steve,

    You should (in theory) be able to do this (dynamic query) with any datasource.  I'm guessing that the area requiring a mod is not the datasource but the dataset itself. 

    The only requirement that I know of is to ensure that you are editing the dataset using the 'generic query designer'.  This is indicated by the button (in the dataset menu) fourth from the left.  So, it goes drop down list of datasets, then ellipses (...), then delete dataset (red 'x'), then refresh (standard double green arrows) then Generic Query Designer.  You'll know you're in the generic designer because all of the other buttons to the right disappear and you're left with the ones i've already listed plus execute and the drop down for query type (you'll want text).

    Hope this helps,

    Steve.

  • January 13, 2006 at 10:32 am

    #614694

    That helps so I know where I am supposed to be.

    When I paste the static MDX query into the GDQ the "!" is highlighted and lets met execute the query. It returns results. No problem.

    Then when I type in the '=' before the WITH the "!" grays out and I can no longer execute the query in the GDQ. Following the above example.

    Is there some configuration option some where that I have configured wrong? Does the 'Parameter!xxx.value' depend on a datasource name of 'xxx' to know if there is a MDX element?

    I'm doing more research as I need to figure this out. And any help is appreciated. I'll also post my findings.

  • January 14, 2006 at 4:32 am

    #614790

    Hi Steve,

    You have quoted the entire statement? e.g. ="SELECT {[Gender].Members} ON 0 FROM [Sales]"

    Then when you want to us the parameters you end quote, use the ampersand to put the strings together e.g. ="... ..." & Parameters!MyParm1.Value & "...."

    Also, I just noticed that the execute is indeed disabled when using the dynamic query (not sure why, need to look at the BOL), so it sounds like you're going in the right direction. 

    Last note the on the dynamic query, you will probably want to write a non-ynamic query, press the refresh so that the dataset gets the columns populated, then make the query dynamic.  This will then let you use the fields in your report.

    Cheers,

    Steve.

  • June 20, 2008 at 3:33 pm

    #832099

    You guys have no idea how much you've added value to me doing this posting.

    Just a simple comment that you can execute your MDX statement by removing quotes, helped me a lot.

    This is an awesome post and thank you for everyone who contributed to this post.

  • September 8, 2008 at 9:26 am

    #868520

    Hello Steve,

    Any ideas on how to change a sql statement:

    select a, b, c, d from users where userid in ('123','456','789')

    so what I am trying to do is pass multiple parameters to a MDX query. I can get it to work for 1 parameter but when I try multivalue parameters it throws an error. Any help is appreciated. If you could provide a example of the parameter usage in the MDX I would greatly appreciate that as well.

    Thanks,

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply