"Aggregate" function in reporting services

I have the problem of aggregating semi-additive measures correct in higher levels in a matrix control of reporting services. I use the "Microsoft SQL Server Analysis Services" data provider. I have a calculated measure saved in the AS2005 cube which looks like:

[Measure].[Net Sales (kg)] = iif( [Measures].[Sales Volume KG] = 0, 0, [Measures].[Net Sales (RLG)] / [Measures].[Sales Volume KG] )

In the matrix, I have a 4 level Product Dimension on the rows (4 groups matrix1_level1 ..... matrix1_level4) and the visibility of the groups is toggled by each upper level, so I can drill in the product dimension. I place this measure in the data part of the matrix with (sum(Fields!Net_Sales__kg_.Value)). On the leave level all values ar correct. But on each other level, I see the sum of the sublevels.

After a lot of pain researching, I found that I have to use the "aggregate" function instead of the "sum" function, because aggregate uses the aggregating which comes from the data provider and the "Microsoft SQL Server Analysis Services" data provider is able to do that. Thats all of documentation I found. I have played around with this function, but always when I use it, I get no values on all levels. I tried the "scope" parameter of the aggregat function, nothing changed.

I tried aggreagate(Fields!Net_Sales__kg_.Value, "matrix1_level1"), aggreagate(Fields!Net_Sales__kg_.Value, "SalesCube") (SalesCub is the name of my Dataset in Reporting Services I use), aggreagate(Fields!Net_Sales__kg_.Value, "matrix1") and last but not least aggregate(Fields!Net_Sales__kg_.Value), all is the same, no values.

It seems to me, that the aggragate function returns "Nothing", because than, according to the Online Help, no values will be shown. But according to some blogs and forum entries, the "Microsoft SQL Server Analysis Services (AdomdClient)" Data provider should deal with aggregate. Have I do some special entries in the "Advanced..." Settings of the data provider?

Can someone tell me, what I'm doing wrong, and did someone know a good documentation (with examples) for the aggregate function on the internet. The Help-file doc is very, very poor!!!

Thanks
Hans

I have solved it now by programming code under report properties and do my own aggregation. But I think this should be done automatically by the "aggregate" function. Does no one know a good site of documentation and what data providers can use them and how?

Thanks
Hans

|||

Can you please add some more details about your custom aggregation? I have a very similar problem and I need to work around it somehow...

Thanks,

Efi

|||

Hi Efi,

I wrote a little Function in the Code-part of the Report Properties like:

Public Function CalcLevelSum(fname as string, svkg as double, svm2 as double, sMeasure as double) as double
Dim sret as double

if fname = "AVWeight" then
sret = iif(svm2 = 0, 0, svkg * 1000 / svm2)
elseif right(fname, 3) = "kg)" then
sret = iif(svkg = 0, 0, sMeasure / svkg)
elseif right(fname, 3) = "m2)" then
sret = iif(svm2 = 0, 0, sMeasure / svm2)
else
sret = sMeasure / 1000
end if

return sret

End Function

And in the cells I changed the generated Sum-Formula to

=Code.CalcLevelSum(Parameters!SelectedMeasure.Value, Sum(Fields!Measures_Ym2SalesVolumeKG.Value), Sum(Fields!Measures_Ym2SalesVolumeM2.Value), Sum(CDbl(Fields!Measures_Ym2Amount.Value)))

With this approach, I calculate on each hierarchie level the devided values by myself. I have done a further standardisation in my cube: all per kg-values have the letters (kg) at the end of the Measure-Name, and all m2 Values (m2). So I can easily determine if the value must be devided or not. The parameters are only the sums which I need for deviding.

One necessary thing is the CDbl Casting of the measure Value. If you do not cast it with CDbl, you get an error!

Hans