MDX Samples – Helping our Business Users see Custom Groupings of Products

One of the more common citations from the desk of the business user, is something that starts with .. “I want to see …”…

Creating a data model that fits the business requirements like a glove might do for now, but the business are great at thinking up new ways of categorising their data.   Top items, these items, those items, those items excluding top items, the list goes on. Whilst some I.T. folk might view business requests as “annoying”, we in the B.I. space love seeing out business users take hold of our collaborations and running with them.

The following snippet demonstrates (using Adventureworks), how to group product lines into 3 custom groupings : Top 2 items, All Other Items, and All Items.  These techniques show you how to include specific line items of interest in your report, whilst grouping the remaining items you are not so interested in, into one total e.g. Other Products … From the Martin Mason Blog …


WITH SET [Top Items] AS

Generate(

{ [Product].[Category].[Category].Members },

TopCount(

EXISTING [Product].[Product].[Product].members,

2,

( [Measures].[Reseller Sales Amount] )

)

)

MEMBER [Product].[Product].[All].[All Other Products] AS

Aggregate( { EXISTING { [Product].[Product].[Product].Members } - [Top Items] } )

SELECT        {

[Measures].[Reseller Sales Amount]

} ON COLUMNS,

{

{ [Product].[Category].[Category].Members }

* {

[Top Items],

[Product].[Product].[All Other Products],

[Product].[Product].[All]

}

} ON ROWS

FROM        [Adventure Works]

Alternatively lets say the business wants to see all of the products that are listed in the current 20% catalogue sale (20% off all Accessories and Products), we can display the line items for all products in the Accessories and Products categories, then show all other items grouped into one total…


WITH SET [Catalog Accessory Products] AS

'Filter([Product].[Product].[Product].Members,

(InStr(1,[Product].[Category].CurrentMember.Name, "Accessories") <> 0)

)'

SET [Catalog Clothing Products] AS

'Filter([Product].[Product].[Product].Members,

(InStr(1,[Product].[Category].CurrentMember.Name, "Clothing") <> 0)

)'

MEMBER [Product].[Product].[All].[All Other Products] AS

Aggregate(

{ EXISTING

{ [Product].[Product].[Product].Members }

- {[Catalog Accessory Products] + [Catalog Clothing Products]} } )

SELECT        {

[Measures].[Reseller Sales Amount]

} ON COLUMNS,

NONEMPTY({

{ [Product].[Category].[Category].Members }

* {

[Catalog Accessory Products],

[Catalog Clothing Products],

[Product].[Product].[All Other Products]

}

}) ON ROWS

FROM        [Adventure Works];

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

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