Geek Answers Handbook

SSRS - Count Columns with Expressions

Using Visual Studio 2010 and SQL Server 2012

I made an SSRS report that requires a custom method for subtotals and reaches the total for Percent of Assets and other calculations derived from Percent of Assets. I can’t just use the Add General Function function because it is grayed out, which is typical for summing expressions. I cannot manually calculate subtotals because I get a nested cumulative error.

The software that I use to make this SSRS report provides various stored procedures and functions for generating reports using derived calculations. The stored procedure contains the Asset Percentage column, but since the report is filtered to display only ordinary shares, the percentage of the Assets function still does not take unfiltered assets into account when calculating it. The assets in question account for 59.7% of the total holdings, but I need a report to act as if they were 100% of the funds.

To understand some of the math in this report, review the following report. Please note that the report I want includes only total stock of 59.7 percent of total assets, as indicated.

Appraisal

, .

intlexample

% - , , . % , .

% . McDonald 2.1% 6,6% * 32%.

6.6% - , McDonald , ( )

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

/

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

. , , . . , . , .

SSRS1

. , , - ( ). , .

Percent of Assets. . , .

SSRS1

,

= SUM (Fields! MarketValue.Value/ (: 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.

PercentAssets, , , - , 59.7, , ,

SSRS1

VBA, . , , SectorName.

VBA, : 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

codedep

Percent . .

" ", , 32.8 .

100%, .

rdl

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

+4
1

:

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

... :

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

, expression/expression; SUM - , , , .

+1

More articles:


All Articles