SQL Server Report Builder: how to obtain SUM(Value) of each group

sql-server group-by reportbuilder
nisuyaves picture nisuyaves · Feb 17, 2012 · Viewed 7.6k times · Source

I have a report group by Field1 separated by pages, in each page I group by Field2.

So, my page is:

      Field1     Field2      SUM(Volume)

      ...

                 Total       SUM(Volume) --This is the value that I want to use!!

I have a Volume field that I want to do a percentage:

Sum(Fields!Volume.Value) is the aggregation of the value, that is correct
sum(Fields!Volume.Value, "DataSet2") is total of everything, that is incorrect because I want total of each page.

I am doing:

=(Sum(Fields!Volume.Value) * 100) / sum(Fields!Volume.Value, "DataSet1")

I am using, Report Builder 2.0 and I am not able to change.

I hope I explained myself ok,

Thanks!

Answer

Lamak picture Lamak · Feb 17, 2012

You can do aggregations with the total of a group (in this case, the Field2 group), using it as if it were a dataset. So, try this:

(Sum(Fields!Volume.Value) * 100) / sum(Fields!Volume.Value, "Field2")

But replace Field2 with the actual name of that group.

Related questions

Column "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

I would like to display the column B in my below SQL, but when I add it to the query it gives me the following error: Column T2.B' is invalid in the select list because it is not contained …

Read More
Count number of records returned by group by

How do I count the number of records returned by a group by query, For eg: select count(*) from temptable group by column_1, column_2, column_3, column_4 Gives me, 1 1 2 I need to count the above records to get 1+1+1 = 3.

Read More
GROUP BY to combine/concat a column

I have a table as follow: ID User Activity PageURL 1 Me act1 ab 2 Me act1 cd 3 You act2 xy 4 You act2 st I want to group by User and Activity such that I end up with something like: User Activity …

Read More