How to make Fields dynamic in a table column?

I am developing a single dynamic report in report designer and I have
a small problem. My report has several parameters and I use MDX to
query from a Cube(Analysis Services). Everything works fine until I
want a field in the report to change dynamically when a user changes a
parameter.
Example:
I have one parameter, dimension, which is a drop down list. If the
user chooses 'region' as the dimension, I want the Field!Region.Value
to be my field in the table column. If he chooses 'store' I want
Field!Store.Value to show instead of Region or any other dimension in
the column. Is there any way to make a report dynamic like this in
report designer?
At this moment I have to define fields with the same adress as the
database location: 'Store' has field expression like this -
[Customer].[Store].[MEMBER_CAPTION] and if I use this expression in
the dimension field when the user chooses 'Store' from the drop down
list, I`ll get all the results displayed in the table. This is what I
want to make more dynamic.
My simplyfied MDX query:
SELECT {[Measure].[Units Ordered]} on Columns,
{[Customer].[" + Parameters!Dimension.Value + "].Members} on Rows
from Orders
Does anyone have a solution to this problem?You can use the collection-based syntax for referencing field names in the
report layout. E.g.
=Fields(Parameters!DimensionSelection.Value).Value
Based on the actual parameter value, the field name will be determined at
report processing time.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Oddvar Brennhovd" <oddvar.brennhovd@.isolutions.no> wrote in message
news:83475cf2.0502230717.48b871f6@.posting.google.com...
>I am developing a single dynamic report in report designer and I have
> a small problem. My report has several parameters and I use MDX to
> query from a Cube(Analysis Services). Everything works fine until I
> want a field in the report to change dynamically when a user changes a
> parameter.
> Example:
> I have one parameter, dimension, which is a drop down list. If the
> user chooses 'region' as the dimension, I want the Field!Region.Value
> to be my field in the table column. If he chooses 'store' I want
> Field!Store.Value to show instead of Region or any other dimension in
> the column. Is there any way to make a report dynamic like this in
> report designer?
> At this moment I have to define fields with the same adress as the
> database location: 'Store' has field expression like this -
> [Customer].[Store].[MEMBER_CAPTION] and if I use this expression in
> the dimension field when the user chooses 'Store' from the drop down
> list, I`ll get all the results displayed in the table. This is what I
> want to make more dynamic.
> My simplyfied MDX query:
> SELECT {[Measure].[Units Ordered]} on Columns,
> {[Customer].[" + Parameters!Dimension.Value + "].Members} on Rows
> from Orders
> Does anyone have a solution to this problem?|||Thank you. Now the problem is solved!

How to limit the MDX dataset for the last six months

I have the following MDX query and I'd like to put in another condition that the "[Account Period].[True Prescription Date].[True Prescription Date].ALLMEMBERS" has to be GREATER than OR EQUAL TO last six months. Eg. using today date as 21-Nov-2006 and the dataset should only include the records from 21-May-2006 onward.

SELECT NON EMPTY { [Measures].[Pharmacy DW Count] } ON COLUMNS, NON EMPTY { ([AgencyID].[Agency Id].[Agency Id].ALLMEMBERS * [Account Period].[True Prescription Date].[True Prescription Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@.DrugProtocolCode, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DrugDrugName, CONSTRAINED) ) ON COLUMNS FROM [Patient Hospital and Drug])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Thanks

It depends how you build your True Prescription Date attribute. If we will assume, that it is rebiult daily, and the last attribute member is today's date (i.e. no dates go to the future), then it would be something like

Lag([Account Period].[True Prescription Date].[All True Prescription Dates].LastChild, 182):[Account Period].[True Prescription Date].[All True Prescription Dates].LastChild)

If the rules are more complex, then it is probably best to build named set inside MDX Script which will resolve to the last 6 months worth of dates, and use it instead in the queries.