Search code examples
ssrs-2008

SSRS Calculations with multiple datasets using clng function

I have a report with 3 datasets. Each dataset is pulling data from a different data warehouse but all pulling the same data. At the end of my report, I have a table that is getting the totals from each dataset, and I have successfully done this for normal total fields. However, there are some fields that are percentages. Below is an example of the expression I have in my text box.

I am taking the existing expression from the table with one dataset and trying to add the value from each data set so I can get a total percentage.

Field using 1 dataset

=Code.SafeDivideVariance(Sum(CLng(Fields!TP_Sales_Retail.Value)), 
Sum(CLng(Fields!LY_Sales_Retail.Value)))

Field combining all data sets

=Code.SafeDivideVariance(
Sum(CLng(Fields!Field1.Value, "Dataset1")) +
Sum(CLng(Fields!Field1.Value, "Dataset2")) +
Sum(CLng(Fields!Field1.Value, "Dataset3")) +
, 
Sum(CLng(Fields!Field2.Value, "Dataset1")) +
Sum(CLng(Fields!Field2.Value, "Dataset2")) +
Sum(CLng(Fields!Field2.Value, "Dataset3"))
)

However, when I try this I get the following error. "The value expression for the textrun contains an error: [BC30198] ) expected.

Error

UPDATE I have added two fields one with the code

=Sum(CLng(Fields!TP_Sales_Retail.Value), "Dataset1") + 
Sum(CLng(Fields!TP_Sales_Retail.Value), "Dataset2") +  
Sum(CLng(Fields!TP_Sales_Retail.Value), "Dataset3")

and the other with

=Sum(CLng(Fields!LY_Sales_Retail.Value), "Dataset1") + 
Sum(CLng(Fields!LY_Sales_Retail.Value), "Dataset2") + 
Sum(CLng(Fields!LY_Sales_Retail.Value), "Dataset3")

I am getting the following error

The value expression for the text box 'Textbox11' has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.

I get the same error even if I just try

=Sum(CLng(Fields!TP_Sales_Retail.Value), "Dataset2")

UPDATE 2.0

Here is the report design, dataset 2 and 3 are the same as dataset1 but they look at the respective data sources.

Report design 1

Report design 2

Report Design 3

The first 3 tables are looking at an individual data source. The last table is looking as dataset1 but I am summing the totals of all 3 in each field.

Report Layout

Solution

  • The dataset was typed incorrectly and not matching my actual dataset name. The S in set needed to be a capital.

    =Code.SafeDivideVariance(
Sum(CLng(Fields!Field1.Value), "DataSet1") +
Sum(CLng(Fields!Field1.Value), "DataSet2") +
Sum(CLng(Fields!Field1.Value), "DataSet3") +
, 
Sum(CLng(Fields!Field2.Value), "DataSet1") +
Sum(CLng(Fields!Field2.Value), "DataSet2") +
Sum(CLng(Fields!Field2.Value), "DataSet3")
)