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];
