Problem with searching in Excel's Filter Drop DownList

[Vladimir Cibulka]

I have a problem with searching in Excel's Filter Drop DownList. Search will not find text containing uppercase.

Version ARQUERY XMLA Connect: 1.0.0.103
Version Excel : Microsoft Office Professional Plus 2010
14.0.4760.1000 (64.bit)

                                     Microsoft Office Professional 2013 
                                     (64.bit)

Version Windows : Windows 10 (64.bit)
Version Pentaho : 5.1
Version Mondrian : mondrian-3.7.0.0-752.jar

[Alexandra Davidoiu (Iancu)]

Hi Vladimir,

This is one of the very few case where there is nothing we can do to fix this issue. At least on our side. I will expain as follows:

The filter is implemented by Excel using this type of query:

WITH MEMBER [Measures].cChildren As 'AddCalculatedMembers([Promotion].[Media Type].currentmember.children).count' Set FilteredMembers As 'Head (Filter(AddCalculatedMembers([Promotion].[Media Type].[Media Type].Members), InStr(1, [Promotion].[Media Type].currentmember.properties("caption"), "bu")>0),10001)' Select {[Measures].cChildren} on ROWS, Hierarchize(Generate(FilteredMembers, Ascendants([Promotion].[Media Type].currentmember))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON COLUMNS FROM [Sales]

The problem arises from the following construct:

InStr(1, [Promotion].[Media Type].currentmember.properties("caption"), "bu")>0

Here I was trying to look something that has "Bu" but, as you see, Excel changes this to lower case. If you look here https://msdn.microsoft.com/en-us/library/hh758424.aspx , you will see a warning as follows: "Instr always performs a case-insensitive comparison.". This is why Excel lowers the caps as InStr is expected to be case insensitive.

On the Mondrian repository you will see here: https://github.com/pentaho/mondrian/blob/5743661cfed528829916ddd133420cb0bb9a685e/src/main/mondrian/olap/fun/vba/Vba.java (line 1906 and following) that Mondrian implements InStr by means of IndexOf. And this is case sensitive, as opposite to the Microsoft specification.

In order to make this work we would have to affect Mondrian code and this is somethign we haven't done so far. If you want you can do this on your own:

This piece of code from Vba.java

if (start != -1) {
return stringCheck.indexOf(stringMatch, start - 1) + 1;
} else {
return stringCheck.indexOf(stringMatch) + 1;
}

should change to somethign like:

if (start != -1) {
return stringCheck.toLowerCase().indexOf(stringMatch.toLowerCase(), start - 1) + 1;
} else {
return stringCheck.toLowerCase().indexOf(stringMatch.toLowerCase()) + 1;
}

This should solve your problem. There is a long story about locales here but I don't want to elaborate.

Let me know if you need more help and, if so, I will try to support you, but on private. Contact us using the support from Arquery: http://www.arquery.com/Contact

Thanks.