I have a problem returning the right data when using crossjoin and topcount
in the same query.

I want to return the best shop in each region, separated by line in an chart
of accounts.

THe following statement works, but it returns the shop which is best overall
in all the lines from each region. If shop1 is better than shop2 in line 1,
and shop2 is better than shop1 in line 2, I want it to be returned as

Line1
Region1: Shop1

Line2
Region1: Shop2

How can I make it check for the best shop in each region, based on the line
I'm interested in?

select {[Measures].[Amount],[Measures].[Target Percent], [Measures].[Real
Percent], [Measures].[Calculated Percent]} on columns,

filter(

crossjoin({[Alternate Chart Of Accounts].[Alternate Chart Of Accounts -
Account Interval].[Alternate Chart Of Accounts].&[Rapp3 Res per
region].children},

generate([Company].[Region - Shop - Company].children,
TopCount([Company].[Region - Shop - Company].Currentmember.Children, 1,
[Measures].[Amount]))),

[Measures].[Amount]>0)

on rows

from [HandsRapportering]

where ([Date].[Year - Month - Date].[Month].&[2005-12-01T00:00:00])



With regards,

Kaisa M. Lindahl Lervik

I think you are after something like the following, Inside the
Topcount() I have crossjoined by [Product].[Lines].Members (You will
need to replace this with the actual "lines" attribute). This
effectively gives us the top shop and line combination within each
region. Then I have wrapped this in an Extract() function to only return
the member from the shop level.



select {[Measures].[Amount],[Measures].[Target Percent],
[Measures].[Real
Percent], [Measures].[Calculated Percent]} on columns,

filter(

crossjoin({[Alternate Chart Of Accounts].[Alternate Chart Of Accounts -
Account Interval].[Alternate Chart Of Accounts].&[Rapp3 Res per
region].children},

generate([Company].[Region - Shop - Company].children,
EXTRACT(TopCount(CROSSJOIN([Company].[Region - Shop -
Company].Currentmember.Children, [Product].[Lines].Members), 1,
[Measures].[Amount])
,[Company].[Region - Shop - Company].[Shop])
))),

[Measures].[Amount]>0)

on rows

from [HandsRapportering]

where ([Date].[Year - Month - Date].[Month].&[2005-12-01T00:00:00])



--
Regards
Darren Gosbell [MVP]
Blog: http://geekswithblogs.net/darrengosbell

*** Sent via Developersdex http://www.developersdex.com ***