i'm creating report jasperserver in ireport. creating report top 10 companies product downloads of selected products. users can select in jasperreports products list (parameter {$p{productformat}}).
my dimension products has following structure: [product].[productfactory].[producttype], input parameter example can be: [product].[productcategory1].[product1], [product].[productcategory2].[product2], ... want products available rows can use them fields in ireport. (product in not option)
select non empty { [measures].[orders] } on columns, non empty crossjoin({topcount({[user_company].[company].members}, 10, [measures].[orders])}, {$p{productformat}}) on rows [products] $p{datefilter} this query return top 10 companies of products data filtered per products. want top 10 companies of selected products, can't work topcount , row.
this query against advwrks via ssms seems work ok:
with set [productformat] { [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 [adventure works]; it returns following:
if add where clause following seems context aware:
where [date].[calendar].[calendar year].&[2006]; 