Hello, I´m working on some reports with SQL Server 2008 R2 using SSRS. I´m using a parameter to chose whether I want to get the report for the whole company, just one regions or just one team within a region. That works fine as long as I don´t need the aggregate function. If I use an expression containing the aggregate function used on a measure I don´t get back any value in the report if I have not set the parameter to "whole company" (=all). I can see the right values when I run the query in the query-designer, but I don´t get them in the report. I tried everything including deinstalling and then reinstalling the whole SQL Server 2008 R2 from scratch. It still does not work! Even the admin admits he can´t see any possible reason why I shouldn´t be able to use the aggregate function, because colleagues of mine are using the same programms in the same installation and if they set up the same datasets with the very same query as I did futily then it works perfectly well. How can this be? We do certainly all have the same rights on the server and are all using SQL Server 2008 R2. Sincere Regards NuramonTheWanderer

Hi Nuramon, Are you using cube dataset? Could you please give more details in describing how you use the aggregate function on your report? thanks, Jerry

Hi Mr Nee, Yes, I am using cube datasets. The aggregate function I am trying to use looks like: =Iif(Aggregate(Fields!avg_prtime.Value) >= 0, FormatNumber(Aggregate(Fields!avg_prtime.Value),2),"0,00%") In the mean time we tried to run SSRS on a virtual machine and use that or similar expressions - with the same result. I still did not get any values in the report whereas my colleagues had absolutely no problem with that using the very same function. regards Nuramon

Hi Nuramon, From your expression, you just want to format the values. rights? If so, follow these steps: 1.convert null values into 0: =IIF(IsNothing(Aggregate(Fields!Avg_prtime.value)),0,Aggregate(Fields!Avg_prtime.value))) 2.Format them, click the Cell to go to its Format property, and type "P2" (without the double quotation) to format them as displayed with percent symbol and 2 number of decimal places. thanks, Jerry

Hi Nuramon, From your expression, you just want to format the values. rights? If so, follow these steps: 1.convert null values into 0: =IIF(IsNothing(Aggregate(Fields!Avg_prtime.value)),0,Aggregate(Fields!Avg_prtime.value))) 2.Format them, click the Cell to go to its Format property, and type "P2" (without the double quotation) to format them as displayed with percent symbol and 2 number of decimal places. thanks, Jerry Hi Jerry, No, I do not only want to format the values. That part works fine if the function works at all. The problem is, that the function =Iif(Aggregate(Fields!avg_prtime.Value) >= 0, FormatNumber(Aggregate(Fields!avg_prtime.Value),2),"0,00%") does not give back any value apart from "0.00%" in the report, if the report-parameter is not set to "all" - and only if I establish the dataset myself. In the query-designer I can see, that there are values for each hierachy-level but in the report I only get values for parameter=all. If however a colleague of mine builds the very same dataset and uses the same function as I do then not only he gets values for all hierarchie-levels but the report even works, if I copy it and use it from my pc - but only if I never ever run that query myself. So I can use the report, but can neither build nor change it myself. We can exclude problems with active directory, rights on the data concerned and hardware-defects but have absolutely no clue, how to remedy that. All I can say for sure is that the problem is confined to the aggregate-function, if that function is used on calculated measures, especially if that measures contain %-values. Any idea what could possibly cause such a phenomenon? Regards Nuramon