I have an OLAP cube for a magazine monthly subscription data warehouse and I
was asked to provide the Customer Subscription activity history list of the
Magazine Name(s), a parameter which end user can select from the dropdown and
the Account_Period which could be the current Account_Period or Past
Account_Period , another parameter which end user can select from the
dropdown, that the Customer(s) have subscripted based on this Magazine_Name
and UP to the selected Account_Period. For example the end user selected
Magazine_Name "The People" and the Account_Period "20060705", I need to know
all the Customer(s) that has subscriped to this Magazine "The People" on the
Account_Period "20060705" of thier subscription HISTORY with this Magazine.
Did they subscript to this magazine(s)previously and the details of the
subscription? How many times they have subscripted to this magazine? etc.

OLAP Cube: Monthly Subscription
Measure: Cost
Measure; Unique Customer Count (How many times they have subscripted to this
magazine?)
Dimension: Magazine_Name (Name of the Magazine)
Dimension: Related_Subscription (Actual Magazine Itself, Shipping, Special
Delivery etc one Magazine_Name could have many Related_Subscriptions)
Dimension: Customer_ID (a text sring to ID a customer)
Dimension: Account_Period (the first six digits to ID the Fiscal Year and the
last two digits to ID the month eg 20060701, 20060702, 20060703)
Dimension: Subscription_Date

In SQL database I can construct this query in Store Procedure as follows:

CREATE PROCEDURE subscription_history
@Magazine_Name ntext,
@Account_Period ntext

SELECT Magazine_Name, Related_Subscription,Customer_ID, Account_Period,
Subscription_Date, Cost
FROM [Monthly Subscription]
WHERE Customer_ID = (SELECT Customer_ID FROM [Monthly Subscription] GROUP BY
Customer_ID HAVING Magazine_Name = @Magazine_Name AND Account_Period <=
@Account_Period)

How to do this with MDX query? I need to create a report for this
subscription_history using 2005 SSRS. Can I use the "Report Designer" in VS
2005 to achieve this or I need to write a CUSTOM MDX query? I am not sure
where to start to build this MDX query/report. Thanks.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...-olap/200609/1