Hi, I need to add a Parameter which in the user manually could enter the exact number of a customer from a SSAS cube (for example 10200) and when the user hit View Report - the report should look up data for that exact customer - how to do that in Report Builder...? My customer dimension (and the Customer Number level) is structured like this: [Customer].[CustomerNumber].ALLMEMBERS Tx HCMJ

Hi HCMJ, Follow the steps in the url to pass Parameter from ReportBuilder to Analysis services. If you follow the steps there will be a parameter automatically created. You can alter the parameter options as needed. http://msdn.microsoft.com/en-us/library/ms156303.aspx Let me know if you any questions. Thanks, Saikat

Thanks for quick reply, But the above do not give me the answer on how to have the parameter based on a variable input from user in a text box. In the above it's only shown how to create a parameter and how to have the parameter based on a certain value - not how have the value based on a variable input from the user... Do you have an example you can show...? Thanks in advance.

Hi HCMJ, You want to fetch the customer data based on the customer id, user enters and execute the reports for. Please, follow these steps: Create a parameter, named Customer with format as TEXT. Assign no default or available values to it.Create a dataSet named CustomerData and write the following MDX: Select NON Empty { [Measures].[Quantity], [Measures].[No Of ]} on columns, NON Empty { [Customer].[Customer Name].[Customer Name] } on rows from [Cubename] where ( StrToSet(@Customer, Constrained) ) Click okRight-click the dataset (CustomeData) and select propertiesGo to Parameters tab.Select fX of Customer, and write the expression: = "[Customer].[Customer Name].&[" & Parameters!Customer.value & "]" Click Ok. And here you go, execute the report with any valid customer number. Please let me know if it helps you! Regards, Manoj *Happy to help http://experiencingmsbi.blogspot.com/

Hi To my undersanding, your looking for a search report. Here I did one similar to your requirements but bit of including default case so need to change the MDX http://bipassion.wordpress.com/2011/12/10/ssrs-searching-report/ Please let me know If need additional info and I think, it will help youPrav

Thanks for the suggestion and well described solution Though I have another question...What if I have a complex existing dataset should I then build this new dataset (CustomerData) into that - or should I add this as a new dataset..? 'TX

Incase, you have an existing dataset with you just add this where clause in it: where ( StrToSet(@Customer, Constrained) ) and follow all other points mentioned in my previous post. Please let me know if you require more details! Regards, Manoj *Happy to help http://experiencingmsbi.blogspot.com/

I tried inserting your suggestion in my dataset..but I get an error...(here is the where clause:) WHERE ( IIF( STRTOSET(@DistrictDistrict, CONSTRAINED).Count = 1, STRTOSET(@DistrictDistrict, CONSTRAINED), [District].[District].currentmember ), IIF( STRTOSET(@TimeDay, CONSTRAINED).Count = 1, STRTOSET(@TimeDay, CONSTRAINED), [Time].[Day].currentmember ), IIF( STRTOSET(@LastUpdateLastUpdate, CONSTRAINED).Count = 1, STRTOSET(@LastUpdateLastUpdate, CONSTRAINED), [LastUpdate].[LastUpdate].currentmember ), [ProjType].[ProjType].[All ProjType] ) , StrToSet(@Customer, Constrained) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS Can you see advise where there could be an mistake...?

There was a bracket issue (bolded part) Use this: WHERE ( IIF( STRTOSET(@DistrictDistrict, CONSTRAINED).Count = 1, STRTOSET(@DistrictDistrict, CONSTRAINED), [District].[District].currentmember ), IIF( STRTOSET(@TimeDay, CONSTRAINED).Count = 1, STRTOSET(@TimeDay, CONSTRAINED), [Time].[Day].currentmember ), IIF( STRTOSET(@LastUpdateLastUpdate, CONSTRAINED).Count = 1, STRTOSET(@LastUpdateLastUpdate, CONSTRAINED), [LastUpdate].[LastUpdate].currentmember ), [ProjType].[ProjType].[All ProjType] , StrToSet(@Customer, Constrained) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS Though i have one question with your where clause, the use of [ProjType].[ProjType].[All ProjType] ? Otherwise it seems good to me. Regards, Manoj *Happy to help http://experiencingmsbi.blogspot.com/

Thank you very much.. It seems to be working..:-) I though found that if I enter a number which is not in the dimension I got the following error: Query (1, 1953) The restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated. ---------------------------- Is there any way of sending a nice message to the user - like "Customer number does not exist" or something like that..? Thanks again

Remove the CONSTRAINED entry for this parameter. Use this: WHERE ( IIF( STRTOSET(@DistrictDistrict, CONSTRAINED).Count = 1, STRTOSET(@DistrictDistrict, CONSTRAINED), [District].[District].currentmember ), IIF( STRTOSET(@TimeDay, CONSTRAINED).Count = 1, STRTOSET(@TimeDay, CONSTRAINED), [Time].[Day].currentmember ), IIF( STRTOSET(@LastUpdateLastUpdate, CONSTRAINED).Count = 1, STRTOSET(@LastUpdateLastUpdate, CONSTRAINED), [LastUpdate].[LastUpdate].currentmember ), [ProjType].[ProjType].[All ProjType] , StrToSet(@Customer) ) Regards, Manoj *Happy to help http://experiencingmsbi.blogspot.com/

Remove the CONSTRAINED entry for this parameter. Use this: WHERE ( IIF( STRTOSET(@DistrictDistrict, CONSTRAINED).Count = 1, STRTOSET(@DistrictDistrict, CONSTRAINED), [District].[District].currentmember ), IIF( STRTOSET(@TimeDay, CONSTRAINED).Count = 1, STRTOSET(@TimeDay, CONSTRAINED), [Time].[Day].currentmember ), IIF( STRTOSET(@LastUpdateLastUpdate, CONSTRAINED).Count = 1, STRTOSET(@LastUpdateLastUpdate, CONSTRAINED), [LastUpdate].[LastUpdate].currentmember ), [ProjType].[ProjType].[All ProjType] , StrToSet(@Customer) ) Regards, Manoj *Happy to help http://experiencingmsbi.blogspot.com/