sql server reporting services: Missing Dimension in MDX query in RS -- sql server reporting services

Missing Dimension in MDX query in RS

Carmen
5/25/2006 12:55:02 PM

sql server reporting services:

I'm creating a report using an MDX query. The query looks similar to this

SELECT
{ [Measures].[Act], [Measures].[PL] } ON COLUMNS,
{ [Product].[BU Product Cat].[Product1], [Product].[BU Product
Cat].[Product2] } ON ROWS
FROM [Cube]

The result I get in RS is:

[Measures].[Act] [Measures].[PL]
3432 857
9743 56

But no column with the product name!

When I ran it using the MDX sample application, it does return the product
name. I believe it has something to do with the dimension settings. Depending
on the dimension I use, it returns or not the column. Product1, Product2, etc
are calculated members in the cube, by the way.

I tried using dimension properties (MEMBER_CAPTION) but it seems it just
ignores it (I tried cube name for instance -- just to check -- in the MDX
Sample app, and still got the product name).

Does anybody know how to return the product name? Any help is appreciated.

Thanks,

Re: Missing Dimension in MDX query in RS

Carmen
5/26/2006 6:15:01 AM

Kaisa,

Thanks very much for your solution. It works perfectly and it got me out of
a bad spot (this is a very urgent report).

I still don't know why it doesn't work for some dimensions and works for
others, but I have a solution I can work with.

Have a good weekend!

Carmen.

[quoted text, click to view]

Re: Missing Dimension in MDX query in RS

Kaisa M. Lindahl Lervik
5/26/2006 1:47:36 PM

You can calculate the productname as a measures, by using the
[Dimesion].Currentmember.Name syntax:

with member [Measures].[ProductName] as '[Product].currentmember.Name'
SELECT
{ [Measures].[Unit Sales],[Measures].[Store
ost],[Measures].[ProductName] } ON COLUMNS,
{ [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and
Wine],[Product].[All Products].[Drink].[Beverages].[Carbonated Beverages]}
ON ROWS
FROM [Sales]

This returns the numers for Unit Sales and Store Cost and then the name of
the product group.

You should be able to do something like

WIth member [Measures].[ProductName] as '[Product].Currentmember.Name'
SELECT
{ [Measures].[ProductName], [Measures].[Act], [Measures].[PL] } ON COLUMNS,
{ [Product].[BU Product Cat].[Product1], [Product].[BU Product
Cat].[Product2] } ON ROWS
FROM [Cube]

Kaisa M. Lindahl Lervik

[quoted text, click to view]

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.