Escape percentage character (%) in a dynamic mdx query formed in windows forms C#

1
votes

How do i escape "%" character in a dynamic MDX. I want "%" to be treated as a literal and not a wild card in MDX. Here is a basic idea of what is happening : I have a windows form (using c#) where user can create a dynamic search expression for MDX query. E.g. Currency Contains US

where "Currency" is static, condition "Contains" is a dropdown selectable value and then a textbox for "US".

So user clicks "Search" and a dynamic MDX is formed with above condition and a cube hit occurs.

Now, i dont get correct result when query is like :

Calculation Contains 50%

Here, % is treated as wild card and anything containing "50" is shown. Please Help. I have tried quotes "", square brackets [], back slash \, double characters %%. but no luck.

UPDATE : Its actually the front end meaning for the user, at the back end i use Analysis Stored Procedures, "IsLike" for this. Query is something like this : { WITH MEMBER [Measures].[Search] AS IIF( [ASSP].[IsLike] ([Calculation].[Calculation].CurrentMember.Properties('MEMBER_CAP‌​‌​TION'),'%50%%')}

Thanks

1
c#dynamicmdx
Maybe this can help you? – EarlGrey
Contains is not an Analysis Services MDX function. Is this implemented as a stored procedure? Then you would have to ask the developer of that procedure how he wants the text to be escaped. – FrankPl
Its actually the front end meaning for the user, at the back end, "IsLike" is used for this. Query is something like this : { WITH MEMBER [Measures].[Search] AS IIF( [ASSP].[IsLike]([Calculation].[Calculation].CurrentMember.Properties('MEMBER_CAP‌​TION'),'%50%%')} – Saurabh Arora
Earl, i went through this, not much helpful.Thanks anyways :) – Saurabh Arora

1 Answers

1
votes

If I understand your question correctly, then you are using the ASSP (Analysis Stored Procedures) IsLike method, and want to know how you can escape a % in the like template so that it is searched for literally. Looking at the source code (http://asstoredprocedures.codeplex.com/SourceControl/latest#ASSP/StringFilters.cs, method LikeToRegEx), I do not see an easy way to achieve this. In this method, a percent is replaced unconditionally by ".*". What you could do is download the ASSP source code and change this method to allow escaping the percent e. g. with "[%]" which would work in the SQL Server relational engine like. E. g. you could add after the

sb.Replace("[.]", @"_");

another line 

sb.Replace("[\.]", @"%");

which would revert the overly aggressive replace of [%] with [.*]. Then you would compile this and re-deploy.