SSRS subtotalling columns with expressions

Question

Using Visual Studio 2010 and SQL Server 2012

I have made an SSRS report that requires a custom method to subtotal and arrive at a grand total for Percent of Assets and other calculations derived from Percent of Assets. I cannot simply use the Add Total Function because it is grayed out as is typical when summing expressions. I cannot manually calculate the subtotals because I get a nested aggregate error.

The software I'm using to make this SSRS report provides various stored procedures and functions to make reports using derived computations. The stored procedure contain a Percent of Assets column but because the report is filtered to show only common stock, the percentage of Assets function is incorrectly still counting unfiltered assets in its calculation. The assets under consideration make up 59.7 % of total holdings but I need the report to behave as if they were 100% of holdings.

To understand some of the math in this report please look at the following report output. Note that the report I want only includes common stock which make up 59.7 Percent of total assets as mentioned.

This report represents the desired output which is currently being calculated in a different reporting program. The report has two tables

% Company Sales is a hard coded value that represents the percent of sales that are either domestic or international. You see this and % Portfolio Assets in both the international and domestic tables.

% Portfolio Assets is calculated by multiplying the percent of assets of a given holding by the hard coded company sales value. In the first example McDonald's 2.1% Portfolio Assets is 6.6% * 32%.

6.6% is the percent of Assets McDonald's represents in the portfolio is if you only count common stock (exclude cash)

McDonald's Value     Total Portfolio Value       %
 4,950.00         /   73,919.50    =       6.6000%

Domestic/ International Percentage breakdown

Total % Sales       %Split     Region
6.60%   32%        2.1%       Domestic
6.60%   68%        4.5%       International

Now here is my report output. It differs slightly from the design layout I will be showing because I added columns and changed the headers for explanatory purposes in this preview example. So far I am just trying to get the Domestic table to work. Once that is working I will build the international table. I have a few extra colums that I'm using for troubleshooting.

I have tried several methods to Calculate Portfolio Assets. I can get the individual row values to be correct but I cannot get the subtotal value to be correct using different methods due to a nested aggregate error (summing and already summed expression when a data scope is used) The grand total is also important but for now I'm just trying to get subtotal working.

Here is one method to get the row detail value for Percent of Assets. This is used in the calculated Percent Column. This column is the one that won't allow me to subtotal without a nested aggregate error.

When I try to use the following expression to get the subtotal I get the nested aggregate error

=SUM(Fields!MarketValue.Value/Sum(Fields!MarketValue.Value, "DataSet1"))

ERROR Error 2   [rsInvalidNestedDataSetAggregate] The Value expression for the text box ‘Textbox39’ has a nested aggregate that specifies a dataset scope.  Inner aggregates cannot specify a dataset scope.

If use the PercentAssets field which is included in the Stored Procedure I can subtotal and get grand total without errors but the value is the filtered value which only sums to 59.7 if I multiply this filtered percent by the company sales the value is wrong.

Last I tried to use custom VBA code to get my subtotal and grand totals. However this provides a running total of the calculated percents not the subtotals I need for the SectorName groups.

Here is the VBA Code I used which I found on the web here: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/1d3c5ec1-89cb-4108-8637-ff434027b1ec/sum-an-expression-value-ssrs

Dim public nettotal as Double
    Public Function Getvalue (ByVal subtotal AS Double) AS Double
                nettotal = nettotal+ subtotal
                return subtotal
    End Function 
Public Function Totalvalue()
                return nettotal
End Function

Code deployed

In summary I need to multiply the calculated Percent column's values by the Company sales. I need to subtotal the result of these calculations.

If you look in the Information Technology Sector Grouping you can see the original report correctly arrives at 32.8 as the subtotal.

My report incorrectly arrives at 100% because that is the running total tally of the Calculated percents.

Here is a link to the original rdl

https://dl.dropboxusercontent.com/u/87501202/InternationalDomestic.rdl

Answer 1

It looks like your code:

=SUM(Fields!MarketValue.Value/Sum(Fields!MarketValue.Value, "DataSet1"))

isn't quite correct... Try this instead:

=SUM(Fields!MarketValue.Value)/Sum(Fields!MarketValue.Value, "DataSet1")

Breaking it down slightly, the second piece of code is expression / expression; now it's just two SUM expressions with different scopes as required - the first one looks like the syntax is off slightly, hence your error.