Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Named Set Based on String Filter

Posted by thomasivarssonmalmo on October 21, 2010

This came up as a scenario in real life with a dimension consisting of collection of codes like (00:11:22:33) and where it is important to get all the collections that consist of one code like (11).

I will use the postal code attribute in the customer dimension of the Adventure Works demo cube as an example. I have used VBA functions supported in SSAS 2005 and later but I have tried it on SSAS 2008.

My example use both the LEFT() and the MID() VBA functions.

WITH Set PostalCode3 as Filter([Customer].[Postal Code].members,
LEFT([Customer].[Postal Code].CurrentMember.Name, 1) = “3”)

Set PostalCode30 as Filter([Customer].[Postal Code].members,
MID([Customer].[Postal Code].CurrentMember.Name, 1,2) = “30”)

Select {[Measures].[Internet Order Quantity]} On 0,
–PostalCode3 On 1
PostalCode30 On 1
From [Adventure Works];

I always try to add features like this in the source dimension tables but sometimes you need to help end users quickly with reports and they cannot wait until you add this in a cube as attributes.

For some of you it might be interesting to see that you can use the  MDX FILTER() function without a measure and that you can filter on  strings i a named set. Without a string filter in a named set end users would have to mark all combinations of a single code in a collection that could be several hundreds of members to maintain.

Be careful with string search in SSAS though.

Advertisements

One Response to “Named Set Based on String Filter”

  1. The instr function can also be used if you need to search substrings like shown below

    SELECT [Measures].[Internet Sales Amount] on columns,

    filter([Employee].[Employees].allmembers,

    instr([Employee].[Employees].currentmember.member_caption,’Da’)>0 ) on ROWS

    from [Adventure Works]

    Update (25/08/2010)

    You can also do the same using Stored Procedures. You can create your own Stored Procedures to achieve this or you can also download Analysis Services Stored Procedures project from:
    http://www.codeplex.com/wikipage?ProjectName=ASStoredProcedures

    After that, you can use the code like this:
    SELECT
    {} ON 0
    ,ASSP.Like([Employee].[Employees].Members
    ,”%DA%”
    ,[Employee].[Employees].CurrentMember.Name)
    ON 1
    FROM [Adventure Works];

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: