Non-auto parameters in MDX Queries and SSRS2005

Question

Non-auto parameters in MDX Queries and SSRS2005

Sam Peascod

SSCrazy

Points: 2497
More actions
July 30, 2007 at 12:17 am

#117968

I wrote some reports in SSRS 2000 against Analysis Server 2000. I've since upgraded to AS/RS 2005 and am struggling a bit with the MDX query builder.
In 2000 I was able to (nay, forced to) put my parameters into strings into my MDX queries. Despite the drawbacks of doing this (such as having to "un-string" the MDX query to refresh the field list) I like the flexibility this offered. For instance, I was able to create a parameter with non-queried values which I could pass to the MDX query.
I have several hierarchies inside a single time dimension (fiscal month, week and calendar month, week). Under 2000 I was able to generate the hierarchy I wanted to use based on this parameter (using something along the lines of ...[Time].[" & Parameters!HierarchyName.Value & "].AllMembers... in my MDX query).
I want to do the same thing in SSRS2005. Can I do this using the new MDX query pane (even if I can't use the designer as such) or do I have to change to the OLE DB provider (and revert to MDX queries as strings)? I get the feeling that referencing of parameters has changed as well... if this is so, what is the new syntax?
Thanks people.
Sam
P.S. I don't really like the MDX created by the Designer and would prefer to see fewer subqueries and better use of the FILTER() function, for instance. I also don't like that I can't change the MDX query in the builder and return to designer without it falling over. I know, I know, it's an immature product, and arguably an improvement over 2000. Hopefully it'll be better in 2008. Has anyone seen it yet?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 1

First, forget about using the query designer in MSRS. In my humble opinion (ok, not so humble) it's not worth the bother. Hopefully the RS develoment team will spend a day or so with either the AS team or with the ProClarity group before they release the next version.

In RS 2005, the paramters are set up automatically as you create them via parameters in your report queries. Again, I ALWAYS override these default MDX statements, since I build intellegence into the allowed values. Make sure your rename the data sources (I use the standard: parm{parm name}).

You no longer need to use the "string syntax, in fact I'd encourage you not to. The highlighting and ability to better format your syntax for readibility and maintenance (plus liberal coments!) is much etter.

Finally, I layout all of the parameter MDX using the following basic syntax. It makes it easier to change, understand and debug:

with

set theSet as ~ some set syntax ~

Select

{ parmCaption

, parmValue

} on columns

, theSet on Rows

From ~ my cube~

parmCaption & parmValue are defined in the cube, where they pull their values from the rows.

Sam Peascod SSCrazy Points: 2497 More actions · Answer 2

Hi Dave,

Thanks for the response. I'm still not clear though on what the syntax is for referencing report parameters in MDX queries. I recall it was Parameters!p_paramName.Value in 2000, but it appears to have changed in 2005. I can't use parameters in the cube because I want to generate my MDX query dynamically.

Maybe I can rephrase my query. I want to give my reports the options of working by week or month (and then having calculations like "last three periods" to reflect this selection). To this end I've created two time hierarchies: Time.Fiscal Week and Time.Fiscal Month. Each hierarchy has three levels (Year, Week/Month, Date). I want a report parameter to allow the user to pick week or month. I was able to do this in 2000 using a string-based MDX query.

Is there a better way to do this in 2005? If not, can I replicate this in 2005 using the MDX query writer (not the graphical thing)?

Stefan K SSCommitted Points: 1786 More actions · Answer 3

Hi,

I experienced the same problem when migrating 2000er reports to 2005. I did not want to completely rewrite all the reports. This would have been pretty much work.

To use the old fashioned way to create your reports you have to select another provider for your data source. Select OLEDB and then OLEDB Provider for Analysis Services 9.0 in the dialog.

Best regards,

Stefan

Kindest Regards,

SK

Sam Peascod SSCrazy Points: 2497 More actions · Answer 4

Thanks Stefan; so you didn't find a way to do it in the new MDX writer? I would be great if I could refresh the field list without having to "dummy populate" all the parameters beforehand...

Stefan K SSCommitted Points: 1786 More actions · Answer 5

Hi Sam,

no, I did not find a way. Like you I even don't like the clicki-tool they introduced.

In my opinion SQL 2008 is the SP3 for SQL 2005, which is a collection of bugs und solutions which are not well thought-out. I hope that they improve some things without producing too many bugs.

Best regards,

Stefan

Kindest Regards,

SK

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 6

Sorry for the late response. On vacation.

To reference a report parameter, you need to "convert" it to a member. Typically you're using a string, so trhe syntax is: StrToMember(@myParameter)

For example, your MDX statement might look like

Select { [Measures].[Measure1], ...} on Columns

, StrToMember( @TimeFrame ) on Rows

from myCube

Now, to the specifics of your query. I think you'd like to reference two separate hierarchies on our report. The problem is creating / managing the list. There's a couple of ways to approach this:

Option A

Set up two separate parameters and reference them both (i.e. { StrToMember( @Week ), StrToMember( @Month ) } in your MDX statement. The user would have to know one of them should be left to ALL, or you could chain the first parameter selection to the first to limit the allowed values. not terribly satisfactory.

Option B

Set up two separate parameters. In the first parameter, reference the hierarchy you'd like to use. Since you're dealing with a "limited list", I'd use direct values in the parameter:
Weeks [Time].[by Weeks].Members
Months [Time].[by Months].Members
In the second parameter, reference the first parameter in building the values:
StrToSet( @Parameter1 )
Now, you can simply use the second parameter in you target MDX.
Caution: Since the result set on your target MDX will change, avoid trying to format it on to your report. Better to reference the parameter direction ( Parameters!Parameter2.Label) if you want to display it.

Finally, I wasn't suggesting that you add parameters to the cube, just these standard parameter "expressions":

CREATE

MEMBER CURRENTCUBE.[MEASURES].[ParameterValue]

AS Axis(1).Item(0).Item(0).Dimension.CurrentMember.UNIQUENAME,

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[ParameterCaption]

AS String( Axis(1).Item(0).Item(0).Dimension.CurrentMember.Level.Ordinal * 1 , ' ' ) + Axis(1).Item(0).Item(0).Dimension.CurrentMember.MEMBER_CAPTION,

VISIBLE = 1;

RS2005 basically builds / creates these now, but there's an advantage to mantaining direct control.