I need to convert following mdx query to 2005 mdx format. following query is in a string format (sql 2000). i need immediate reply to this problem. it is better if some one can explain the how to pass the parameter to this qury.
="WITH "+
"SET [FilteredBUSet] AS ' "+Parameters!BU.Value+ " ' "+
"SET [FilteredAccountSet] AS '"+Parameters!Account.Value+"'"+
"SET [CustomTimeSet] AS 'Descendants([Date—Fiscal].["+Parameters!FYear.Value+"].["+Parameters!FQuarter.Value+"]:"+
"[Date—Fiscal].["+Parameters!TYear.Value+"].["+Parameters!TQuarter.Value+"],[Date—Fiscal].[month],Leaves)'"+
"SELECT "+
"{NONEMPTYCROSSJOIN([CustomTimeSet],INTERSECT([FilteredBUSet],[FilteredAccountSet]))} ON ROWS,"+
"{[Measures].[Total Revenue],[Measures].[Margin %],[Measures].[Total Expenses]} ON COLUMNS "+
"FROM Profitability"
If you continue using the OLEDB driver to let Reporting Services connect to Analysis Services, then that should continue to work in Reporting Services 2005. Whether the MDX works depends upon whether your new 2005 cube is backward compatible.
If you want to switch to the new Analysis Services driver (for AS2005 only) in Reporting Services, then you have the option of using a better query design interface with real parameters (rather than just concatenating a string which is the MDX command).
I would suggest you install the SQL Server Samples and check out the following sample MDX report which uses parameters and the new Analysis Services driver and query designer:
C:\program files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports\Sales Reason Comparisons.rdl
|||I Went through the sample. bt cant understand how to pass parameter to this query. On behalf of the Parameter!Bu.value , what should i put?|||
This should help:
http://msdn2.microsoft.com/en-us/library/ms155824.aspx
