Dynamic MDX in RS

Question

Dynamic MDX in RS

Cesar-134342

Ten Centuries

Points: 1319
More actions
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.

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

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

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

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.

Cesar-134342 Ten Centuries Points: 1319 More actions · Answer 2

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

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

Thanks a lot!!!

Steve Funk SSC Rookie Points: 30 More actions · Answer 3

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 =.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

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.

Steve Funk SSC Rookie Points: 30 More actions · Answer 5

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.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 6

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.

aMSDeveloper Right there with Babe Points: 718 More actions · Answer 7

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.

woo2gs SSC Rookie Points: 39 More actions · Answer 8

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,