September 23, 2008 at 12:14 pm
Hi,
I am trying to write a dynamic MDX query to be used for a report. There are currently two reports which share similar datasets except for the name of one dimension, which is different for both of them. I am attempting to reduce them to one report by dynamically passing the name of the dimension through a parameter and make the query reusable. I am unable to get it to work that way. The query is below
="SELECT NON EMPTY { [Measures].[Slot Bills], [Measures].[Slot Coin] } ON COLUMNS, {[Slot Bank].[Slot Bank]} ON ROWS FROM (SELECT ( STRTOSET ( '{" & Join(Parameters!subreport.Value,",") & "}', CONSTRAINED) ) ON ROWS FROM [DWS])"
The subreport parameter will pick up the value provided by the user and substitute it in the query. The dataset is an OLE DB type with MSOLAP provider. I am very new to MDX and hence any help, suggestions, corrections would be greatly appreciated.
September 25, 2008 at 5:19 pm
Your FROM clause requires a cube, not a query.
You should be supplying the parameterized dimension to either a row, a column or as a contraint
Kind Regards, Will
September 26, 2008 at 1:29 am
Hi
The above is using a sub cube which is valid, the designer generated mdx uses them all the time. I think what you want is something like this:
WITH
MEMBER [Measures].[SelectedDimensionLabel]
AS StrToMember(@SelectedDimension + '.MEMBER_CAPTION')
SET DynamicDimension
AS StrToSet(@SelectedDimension + '.MEMBERS')
SELECT {[Measures].[SelectedDimensionLabel], [Measures].Other Measures]} ON COLUMNS,
DynamicDimension ON ROWS
FROM MyCube
The value of the query parameter @SelectedDimension would be a string like [MyDimension].[MyHierarchy].
Ben
September 26, 2008 at 3:01 pm
That works great. Thanks so much Ben and Will!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy