Hi, Now I am making a report using Dynamics OLAP to display the sales data for all sales persons and be filtered by Year. but now, I just can enter one value at @Year parameter. How can I do if I want to enter multiple value? How to write it? Followings are the current statement: select NON EMPTY {[Measures].[Total line sales],[Measures].[Total line cost net WAR],[Measures].[Line margin]} ON COLUMNS, NON EMPTY {[NM Commission sales group].[Group name].CHILDREN} ON ROWS FROM [NM Sales cube] WHERE (STRTOMEMBER("[NM Date].[Year].&["+@YEAR+"-01-01T00:00:00]"))

Using this technique where you concatonate the member reference together, this should be manageble but the logic will be more complex. You have to test the parameter for more than one element and then build the set reference in a custom function. I think that a better option is to use a parameter list dataset and multiselect parameter that returns the entire set reference as a string. If the dataset list query returns the members of the [NM Date].[Year] hierarchy, then @Year should be formatted correctly as a fully-qualified set. If you selected three different years, the value should be something like: "[NM Date].[Year].&[2009], [NM Date].[Year].&[2010], [NM Date].[Year].&[2011]" You should be able to pass this to the STRTOSET function rather then STRTOMEMBER in the WHERE clause. If that doesn't work, the other option is to build the entire query string as an expression. In that case, to get the parameter formatted correctly, use: =JOIN(Parameters!Year.Value, ",") The following post doesn't address the multi-value parameter technique specifcally but does demonstrate how to build the query as a strring expression: http://sqlserverbiblog.wordpress.com/2011/03/27/designing-reports-with-custom-mdx-queries-part-1/Paul Turley, MVP, www.SQLServerBIBlog.com *Please vote if helpful*

Hi Paul Thanks for your suggestions. As you said, first I used STRTOSET to replace STRTOMEMBER, but when review the report, there is a error info like, "The report parameter YEAR cannot be multi valued since it provides values to an MDX query. Multi valued parameters are not supported by the ADOMD provider". Then, I rewrite my MDX query as below: select NON EMPTY {[Measures].[Total line sales],[Measures].[Total line cost net WAR],[Measures].[Line margin]} ON COLUMNS, NON EMPTY {[NM Commission sales group].[Group name].CHILDREN } ON ROWS FROM (SELECT ( STRTOSET(JOIN(Parameters!InvoiceLine_YEAR.Value, ","))) ON COLUMNS FROM [NM Sales cube]) But this query couldn't be executed successfully, error info is "syntax error for JOIN"

Hi Vicky, 1.If you use Join your Expression will Look like this.[Used as Expression builder] Select NON EMPTY {[Measures].[Total line sales],[Measures].[Total line cost net WAR],[Measures].[Line margin]} ON COLUMNS, NON EMPTY {[NM Commission sales group].[Group name].CHILDREN } ON ROWS FROM (SELECT JOIN(Parameters!InvoiceLine_YEAR.Value, ",")) ON COLUMNS FROM [NM Sales cube]) Do Achieve the above you have write a MDX Query without the Parameter so that You can Get the required fields in your Report. 2.If You use STRTOSET your expression will look like this. Select NON EMPTY {[Measures].[Total line sales],[Measures].[Total line cost net WAR],[Measures].[Line margin]} ON COLUMNS, NON EMPTY {[NM Commission sales group].[Group name].CHILDREN } ON ROWS FROM (SELECT STRTOSET(Parameters!InvoiceLine_YEAR.Value)) ON COLUMNS FROM [NM Sales cube])Rakesh M J Dont forget to mark it as Answered if found useful MCTS,MCITP,MCSS http://myspeakonbi.blogspot.com/

Hi Vicky, 1.If you use Join your Expression will Look like this.[Used as Expression builder] ="Select NON EMPTY {[Measures].[Total line sales],[Measures].[Total line cost net WAR],[Measures].[Line margin]} ON COLUMNS, NON EMPTY {[NM Commission sales group].[Group name].CHILDREN } ON ROWS FROM (SELECT"+ JOIN(Parameters!InvoiceLine_YEAR.Value, ",")+") ON COLUMNS FROM [NM Sales cube])" Do Achieve the above you have write a MDX Query without the Parameter so that You can Get the required fields in your Report first. 2.If You use STRTOSET your expression will look like this. Select NON EMPTY {[Measures].[Total line sales],[Measures].[Total line cost net WAR],[Measures].[Line margin]} ON COLUMNS, NON EMPTY {[NM Commission sales group].[Group name].CHILDREN } ON ROWS FROM (SELECT STRTOSET(@InvoiceLine_YEAR)) ON COLUMNS FROM [NM Sales cube]) Rakesh M J Dont forget to mark it as Answered if found useful MCTS,MCITP,MCSS http://myspeakonbi.blogspot.com/