sql server reporting services: How do I use a parameter in a dataset field value using an MDX query? -- sql server reporting services

You're in the

sql server reporting services

group:

How do I use a parameter in a dataset field value using an MDX query?

Roger
5/23/2007 8:46:55 PM

sql server reporting services:

I am trying to run a report using dynamic MDX dimensions.

The value of my dataset field Dimension_1 is

[SOB Year].[Yr Id].[Yr Id].[MEMBER_CAPTION]

I want to use parameters for SOB Year and Yr Id like I do on the
query.

But if I use

="["& Parameters!dimension_1_table.Value &"].["& Parameters!
dimension_1_column.Value &"].[" & ["& Parameters!
dimension_1_column.Value &"]"

it returns null for the values. I don't know how to make these
parameters

I am passing in parameters for the table and column to build an MDX
query here is my query.

="SELECT NON EMPTY { [Measures].[Liability Amt] } ON COLUMNS, NON
EMPTY { (["& Parameters!dimension_1_table.Value &"].["& Parameters!
dimension_1_column.Value &"].["& Parameters!dimension_1_column.Value
&"].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION,
MEMBER_UNIQUE_NAME ON ROWS FROM [SOB Clean] CELL PROPERTIES VALUE,
BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME,
FONT_SIZE, FONT_FLAGS"

Anyone know what value I need to use on the dataset field to access
the column?

Re: How do I use a parameter in a dataset field value using an MDX query?

Roger
5/24/2007 10:55:26 AM

[quoted text, click to view]

Is it possible to pass parameters to a dataset filed value using MDX?

Re: How do I use a parameter in a dataset field value using an MDX query?

Roger
5/29/2007 11:15:49 AM

I replicated the problem in adventure works the mdx is

="SELECT NON EMPTY { [Measures].[Internet Order Count] } ON COLUMNS,
NON EMPTY { ([" & Parameters!dim_1_table.Value & "].[Country].
[Country].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION,
MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Reseller].[Reseller].
[All Resellers] } ) ON COLUMNS FROM [Adventure Works]) WHERE
( [Reseller].[Reseller].[All Resellers] ) CELL PROPERTIES VALUE,
BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME,
FONT_SIZE, FONT_FLAGS"

the paramater works here but not in the dataset field value of

="[" & Parameters!dim_1_table.Value &"].[Country].[Country].
[MEMBER_CAPTION]"

only only works with the original value of

[Customer].[Country].[Country].[MEMBER_CAPTION]

If anyone could help it would be greatly aprreciated.

thanks

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.

home · blog · groups

Questions? Comments? Contact the dn