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}[/code]
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.
Thanks.