Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Analysis Services (2000)  How to write this in MDX query?

Author  Topic  pmak Starting Member 41 Posts Posted - 2006-09-10 : 14:29:07 I have an OLAP cube for a magazine monthly subscription data warehouse and I was asked to provide the Customer Subscription activity history 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. Paul Mak