MDX: Top 10 with filtered members

Question

I'm creating a report for JasperServer in iReport. I am creating a report with the top 10 companies with most product downloads of the selected products. Users can select in JasperReports which products with a list (parameter {$P{ProductFormat}}).

My dimension for products has the following structure: [Product].[ProductFactory].[ProductType], so input for the parameter for example can be: [Product].[ProductCategory1].[Product1], [Product].[ProductCategory2].[Product2], ... I also want those products available as rows so I can use them as fields in iReport. (product in where is not an option)

SELECT
  NON EMPTY {
    [Measures].[Orders]
  } ON COLUMNS,
  NON EMPTY
     Crossjoin({TopCount({[USER_COMPANY].[Company].Members}, 10, [Measures].[Orders])}, {$P{ProductFormat}})
   ON ROWS
FROM [Products]
WHERE $P{DateFilter}

This query return the top 10 companies of all products with the data filtered per products. I want a top 10 companies of the selected products, but I can't get it to work with topcount and as a row.

Answer 1

This query against AdvWrks via SSMS seems to work ok:

WITH 
  SET [ProductFormat] AS 
    {
      [Product].[Product Categories].[Category].[Bikes]
     ,[Product].[Product Categories].[Category].[Clothing]
     ,[Product].[Product Categories].[Category].[Components]
    } 
SELECT 
  [Measures].[Reseller Sales Amount] ON 0
 ,Generate
  (
    [ProductFormat]
   ,
      [Product].[Product Categories].CurrentMember
    * 
      TopCount
      (
        [Geography].[Geography].[City].MEMBERS
       ,5
       ,[Measures].[Reseller Sales Amount]
      )
  ) ON 1
FROM [Adventure Works];

It returns the following:

If I add a WHERE clause like the following then it seems to be context aware:

WHERE [Date].[Calendar].[Calendar Year].&[2006];