Top 10 Customers with Filter criteria

[vincydza@gmail.com]

Hi,

The expression to retrieve top 10 customers based on revenue is as follows.

topCount ([SalesBICube].[All Customers].[By Corporate Group].[Corporate Parent No],10,[Revenue])

How can this expression  be tweaked to retrieve records exclusive for North America or Europe?

Please advise.

Vincent

[MFGF]

Hi,

The expression to retrieve top 10 customers based on revenue is as follows.

topCount ([SalesBICube].[All Customers].[By Corporate Group].[Corporate Parent No],10,[Revenue])

How can this expression  be tweaked to retrieve records exclusive for North America or Europe?

Please advise.

Vincent

Hi,

Rather than using just [Revenue] in the expression, you can use a tuple() of Revenue and the desired Region member instead,

eg

topCount ([SalesBICube].[All Customers].[By Corporate Group].[Corporate Parent No],10,tuple([Your Europe member],[Revenue]))

Cheers!

MF.

[vincydza@gmail.com]

MF,

The requirement is to have market segment as a filter prompt for the report like seen below. However the below expression retrieves records with  null values.

topCount ([SalesBICube].[All Customers].[By Corporate Group].[Corporate Parent No],10, tuple([SalesBICube].[All Market Segment].[All Market Segment].[Market Segment]->?pMarketSegment? ,[Revenue]))

Vincent

[MFGF]

MF,

The requirement is to have market segment as a filter prompt for the report like seen below. However the below expression retrieves records with  null values.

topCount ([SalesBICube].[All Customers].[By Corporate Group].[Corporate Parent No],10, tuple([SalesBICube].[All Market Segment].[All Market Segment].[Market Segment]->?pMarketSegment? ,[Revenue]))

Vincent

Is this where there are fewer than 10 customers with a revenue value for a given market segment?

You could use a filter() function to weed these out if necessary

topCount(filter([SalesBICube].[All Customers].[By Corporate Group].[Corporate Parent No], tuple([SalesBICube].[All Market Segment].[All Market Segment].[Market Segment]->?pMarketSegment? ,[Revenue]) is not null),10, tuple([SalesBICube].[All Market Segment].[All Market Segment].[Market Segment]->?pMarketSegment? ,[Revenue]))

Or do you mean something different?

MF.

[vincydza@gmail.com]

something different, assuming the report was changed to list only top 2 customers and the attached image was a fact table. Then the report should have a filter on Market segment and list only the Top 2 customers for selected market segment.

If user was to select North America as the filter criteria for the report then it should list only the Top 2 customers for North America. In our example "revenue for Red Bull" and "Coca Cola"  should appear on the report.

Vincent

[MFGF]

something different, assuming the report was changed to list only top 2 customers and the attached image was a fact table. Then the report should have a filter on Market segment and list only the Top 2 customers for selected market segment.

If user was to select North America as the filter criteria for the report then it should list only the Top 2 customers for North America. In our example "revenue for Red Bull" and "Coca Cola"  should appear on the report.

Vincent

Assuming this is a crosstab with Customer Name nested below Market Segment (rather than a list report), the expression to retrieve the Market Segment members would be [SalesBICube].[All Market Segment].[All Market Segment].[Market Segment]->?pMarketSegment?

Otherwise, the previous topCount() expression should be fine for your Customer Name members...

Cheers!

MF.

[vincydza@gmail.com]

thanks