Santosh Ransubhe


Hi,

I need to convert the following MDX functionality into a Stored Procedure in MSAS 2005:

SELECT
{ FILTER([Store Type].AllMembers, InStr([Store Type].CurrentMember.Name,"*", 0) > 0) }
ON AXIS(0)
FROM [Sales]

Is it possible to use the stored procedure "Like" implemented under the Analysis Services Stored Procedure Project (www.codeplex.com) If yes, what would be the equivalent MDX query for implementing the same functionality

Thanks and Regards,
Santosh.





Re: Equivalent Stored Procedure for Wildcard search.

Darren Gosbell


Hi Santosh,

The Like function was one that I worked on for the Analysis Services Stored Procedure Project. It implements the pattern syntax used in the T-SQL function as much as possible using % as a wildcard character, so the following query should work:

SELECT
{ ASSP.Like([Store Type].AllMembers, "%*%", [Store Type].CurrentMember.Name
) }
ON AXIS(0)
FROM [Sales]

However you should be aware that the using the builtin InStr() function will most likely be noticably faster than using a stored proc. The stored proc really comes into it's own when you have more complicated filters or if it is a user entered filters.







Re: Equivalent Stored Procedure for Wildcard search.

Santosh Ransubhe

Hi Darren,

But can the InStr() function with Filter() be used for wildcharacter search like the one you showed in your example

If yes, can you give me an example

Thanks,
Santosh.







Re: Equivalent Stored Procedure for Wildcard search.

Darren Gosbell

Hi Santosh,

Sorry for the delay - I have just returned from some extended leave.

The example I gave in may post was meant to be equivalent to the first Filter(Inst()) example that you posted

FILTER([Store Type].AllMembers, InStr([Store Type].CurrentMember.Name,"*", 0) > 0)

Which appears to return any Store Type with an asterix in the name.