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