Hi

I am a new user to mdx but not to SQL Reporting services


Basically the data I have has monthly sales going back 5 years by
Barcode number.

So I want to end up with barcode on the rows and Year/Month on the
columns.

My question is, How do I firstly only select the most recent 24 months
(I think it might be to do with the .Item() command but the book I have
is a bit lame).

And secondly how do I rename the columns i.e Month-1, Month-2....
Month-24

This way I will be able to produce a table structure in SQL Reporting
Services without having to change the column heads everytime I get a
new data update.

Please help!

Any other ideas will be greatly appreciated

Thanks in advance

Denver

One approach would be to place both Barcode and Time on rows, with a
Calculated Text Measure for the relative month; and use a Matrix Report
to place months on columns.

Here's an example of a Foodmart Sales MDX query:

'Tail(NonEmptyCrossJoin([Time].[Month].Members), 6)'
Member [Measures].[MonthLabel] as
'"Month-" + CStr(6 -
Rank([Time].CurrentMember, [Recent6Months]))'

Select {[Measures].[MonthLabel],
[Measures].[Unit Sales]} on columns,
NonEmptyCrossJoin([Store].[Store Name].Members,
[Recent6Months]) on rows
from Sales

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks Deepak, this works great!

DK