Aggregete() function doesn't work for SQL query results!

Reporting Services translates null value into blank on SQL query results, but not on MDX query results. But Aggregate() functions is triggered only by null value.

So Aggregate() function only works for MDX query results, not for SQL query results.

MDX example:

select {[Measures].[Sales]} on columns,

{[Account].[Hierarchy].Members} on rows

FROM Cube

SQL example:

SELECT * FROM OPENQUERY(Linked_Cube, '
select {[Measures].[Sales]} on columns,

{[Account].[Hierarchy].Members} on rows

FROM Cube')

Now you build a report with a table, then add a grouping and use "=Aggregate(Fields!Sales.Value)" for the group level cell. If you bind MDX query to this table, then aggregates show up correctly. But if you bind SQL query to this table, there are no aggregates at all.

I need to use SQL query to drive my reports, because MDX query results need to be merged with results from other calculations.

How can I make Aggregate() function to work for SQL query results?

Thanks,

Bo Dong

bo_dong@.yahoo.com

How can I make it to work for SQL as well?

Please read my answer on your other thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=668524&SiteID=1

-- Robert