Tuesday, March 27, 2012

Filter & *

Hi I am wandering if it is possible to achieve the following:

I am using Reporting Services and Reporting services I got a report with a text parameter.

I would like the user to introduce AA and find the product AA and when he introduces AA* it finds AA, AAD, AAC...

I am doing the following:

Filter([DIM Product].[Product].ALLMEMBERS,

,IIF(INSTR(@.Product, "*") > 0

,[DIM Product].[Product].currentmember.name = Replace(@.Product, "%", "")

,INSTR([DIM Product].[Product].currentmember.name, @.Product)

)

)

The problem ist that the function Replace does not work!

Does someone know how to do it?

Smile

I don't think the problem is with the replace, it looks like you have the logic back-to-front, something like the following should get you closer.

Code Snippet

Filter([DIM Product].[Product].ALLMEMBERS,

,IIF(INSTR(@.Product, "*") > 0

,INSTR([DIM Product].[Product].currentmember.name, Replace(@.Product, "*", "")

,[DIM Product].[Product].currentmember.name = @.Product

)

)

However there is an issue that you may or may not be concerned with - If someone types in "A*B" the expression will strip out the "*" and return anything containing "AB".

Another alternative, if you want more sophisticated behaviour might be to look at using a stored procedure. There is a string match example which I wrote in the Analysis Services Stored Procedure project at www.codeplex.com/ASStoredProcedures . I have one in there that reproduces the equivalent of the t-sql LIKE operator.

No comments:

Post a Comment