HI all, I have created report in SSRS R2, in which i have used twi dataset in SSAS cube Dataset contains contain follwong columnas below Dataset1 : player,run A 20 Dataset2 : Player,Run B 10 C 21 D 24 where dataset1 contains run for one player while dataset2 contains runs of all player now i want to write expression which gives me how many player who score more than player a i.e in above case result should be 2. for this i have write below expression in Textbox of report. =Count((sum(Feild!run.vaue),"Dataset1")>feild!run.vaue) then i got an error as below "An error occured during local report processing.the definition of the report '/run ' is invalid. The value expression for the text box'Text Box1' has a nested aggregate that specifies a dataset scope.Inner aggregates cannot specify a dataset scope. Kindly suggest what should i do to resolve the above problems.. Amit

This should probably be written in the MDX rather than an SSRS expression.my blog http://karlberan.wordpress.com/

ya mdx will do but it took lots of time when i used the MDX query in DataSet in SSRS R2 Report . Please suggest in Expression any how Amit

It appears SSRS wont let you access an aggregate in another dataset. Is it possible to return a single dataset with all values and a rank for each as below: player run rank A 20 2 B 10 1 C 21 3 D 24 4 Then use an expression to determine the count along the lines of =Max(Field!Rank.Value,"Dataset1") - Field!Rank.Value In this example the expression would evaluate 4-2 to return 2. You could then simply set an expression to evaluate the Fields!Player.Value against Parameters!prmPlayer.Value to only show the value for Player A as below player run PlayersWithmore A 20 2 B 10 C 21 D 24 my blog http://karlberan.wordpress.com/

No i dnt have rank column,rank is what which i want to populate . Kindly Suggest amit

Hi Amit, If you have to do it, one way might be doable. Create a parameter and set its visibility to Internal, its Avaliable and default values are from the dataset1. So, the parameter value(default) is 20. Then, on the report body, write the expression like this: =Count(IFF(Fields!run.value>20,1,0)) Note: sum(Field!.run.value,"dataset1") can be used for the parameter value, you can do it in the Dataset query. thanks, Jerry

Hi jerry, With the help of above how can i predict the how many person are above player who scored 20 Amit