Dynamix mdx query

I'm not to familiar with mdx, so I don't manage to create a dynamix mdx query in RS against my AS cube. What I'm looking for is having two parameters @.par1 and @.par2 that decide what dimensions to query against. The default query I get when I create a mdx with designer looks like the one below. The problem is that instead of [Product].[Product].[Product] and [Supplier].[Supplier].[Supplier] I would like to use my parameters. The parameters can contain the value Supplier, Product, Customer and all other names of the valid dimensions.I thought maybe it was possible to do somthing like this: [@.par1].[@.par1].[@.par1], but that didnt work :-) I have also tryed using with and set, but havnt managed to make it work. I have seen a couple examples on the forum that I didnt manage to use (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=443401&SiteID=1, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=520011&SiteID=1, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=766277&SiteID=1), but I think these are mostly working with mesures not dimensions.SELECT

NON EMPTY

{

[Measures].[Amount]

}

ON COLUMNS,

NON EMPTY

{

([Product].[Product].[Product].ALLMEMBERS *

[Supplier].[Supplier].[Supplier].ALLMEMBERS)

}

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Purchase]

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGSAny sugestions how to solve my problem?

Starting to get a bit desperat with this problem.

I don't manage to use parameters in my MDX query. I have added them in the parameter in the settings of the dataset, but it doesnt help. In regular SQL's it is very easy but I have problem seeing what wrong in the MDX.

Hopefully somone have the answer

|||

Shamefull *bump*

Do really no one know hwo to do this?

|||

It is possible. You need to use the StrToSet function.

SELECTNON EMPTY

{

[Measures].[Amount]

}

ON COLUMNS,NON EMPTY

StrToSet("[" + @.prm1 + "].[" + @.prm1 + "].[" + @.prm1 "].ALLMEMBERS")
*
StrToSet("[" + @.prm2 + "].[" + @.prm2 + "].[" + @.prm2 "].ALLMEMBERS")

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Purchase]

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

|||

Adam Tappis - IMGroup wrote:

It is possible. You need to use the StrToSet function.

SELECTNON EMPTY

{

[Measures].[Amount]

}

ON COLUMNS,NON EMPTY

StrToSet("[" + @.prm1 + "].[" + @.prm1 + "].[" + @.prm1 + "].ALLMEMBERS")
*
StrToSet("[" + @.prm2 + "].[" + @.prm2 + "].[" + @.prm2 + "].ALLMEMBERS")

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Purchase]

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Thanks alot for the reply! I'm really desperate

It seems I'm closer to a solution but still have one problem. I get the following error message:

Query preparation failed.

Parser: The query contains the pmr1 parameters, wich is not declared.

I tryed to add the parameters manually in the parameters tab in the dataset information tab, but that didnt help. Usally when working with SQL and procedures they usally manage to populate the parameter list them self. I probably need to declare the variables directly in the mdx window. Tryed a couple of combinations using WITH and SET, but didnt really manage to get it to work.

Any more help would be really apreciated!

|||

There's 2 places you need to check:

1. Report Menu -> Report Parameters
Make sure the paramter exists (without the @. sign). This should be enough to run the report and look at it in the preview tab

2. In the Data view, click the button that has an @. sign on the dataset window toolbar
This is where you define values for the parametrs used in the query that will be used when you execute the query in this view. Add the parameter (without the @. sign) and specify a value for it.

Clicking the execute button in the toolbar should show you the results.

Hope this helps.

|||

Adam Tappis - IMGroup wrote:

There's 2 places you need to check:

1. Report Menu -> Report Parameters
Make sure the paramter exists (without the @. sign). This should be enough to run the report and look at it in the preview tab

2. In the Data view, click the button that has an @. sign on the dataset window toolbar
This is where you define values for the parametrs used in the query that will be used when you execute the query in this view. Add the parameter (without the @. sign) and specify a value for it.

Clicking the execute button in the toolbar should show you the results.

Hope this helps.

Nr 1 I had done or else I couldnt add them in the details of the dataset. But I didnt notice the parameter button (nr2) the mdx bar! When I added the name with a default value it worked as I wanted it too! Thanks alot I for the help, your a life saver!

|||

Lol I was a bit too quick to say that the problem was solved! I still have problem getting it to work

The problem is when I add pmr1 to the mdx parameter field is that I have to set default value. But when I change this parameter in the report I get 0 result on the dimension. Since the names of the fields becomes the same as the default value I tryed to create a calculated field to get the data to the report and get the value for the calculated field like this: =Fields(Parameters!pmr1.Value).Value. But this also only work if I use the default values set in the mdx toolbar.

Not sure if what I write here is understandable, feel free to ask question if I didnt make clear my problem. Thanks.

|||

Adam Tappis - IMGroup wrote:

There's 2 places you need to check:

1. Report Menu -> Report Parameters
Make sure the paramter exists (without the @. sign). This should be enough to run the report and look at it in the preview tab

2. In the Data view, click the button that has an @. sign on the dataset window toolbar
This is where you define values for the parametrs used in the query that will be used when you execute the query in this view. Add the parameter (without the @. sign) and specify a value for it.

Clicking the execute button in the toolbar should show you the results.

Hope this helps.

With regard to what I said before, doing number 1 will be enough to get this working if configured correctly.

Number 2 and the default value you define there is ONLY used when you want to preview the results of your query in the data tab and does not effect the report at run time e.g. through browser or preview tab. Only the parameter definition in the report parameters window effects run-time execution.

You shouldn't be using any expression syntax at all (unless you are connecting via OLEDB but let's not get into that)

Have are your parameters configured? Datatypes, options, values, manually defined or dataset driven?

|||

Hi, thanks for the response again.

I still have some problems getting it to work as explained. I'll try to give a bit more info.

If I only do 1. then I get error message when trying to leave the data tab becouse the "Query preparation failed". When I also do 2 it works fine but only with the default values I set as parameter on the mdx toolbar.

I have tryed creating the report parameters first and also tryed to let reporting services create the parameters when I add them in the mdx toolbar. No diffrence. Datype is string. No options set. I have tryed with values manually set and without any values. No diffrence.

Maybe there is a diffrence in version from the one I use and the one you use? My version of Reporting Services is 9.00.2047.00.

Any sugestions?