CODEWITHSUNDEEP
sqlsql-serverreporting-services
rcarcia02
rcarcia02

Reputation: 967

How to create report textbox aggregate expression in SQL Server Reporting?

I have a SQL Server Reporting Services report that has a table with a ton of fields and then a textbox where I want to display the total weight. Now, the total weight is determined by a huge expression:

=Sum(Round(Code.ConvertWgt(Switch(Parameters!OrderLength.Value = 
"T",Fields!TotalTheorWeight.Value
    ,Parameters!OrderLength.Value = "C",Fields!TotalCutWeight.Value
    ,Parameters!OrderLength.Value = "P",Fields!TotalPayWeight.Value
    ,Parameters!OrderLength.Value = 
"OD",Switch(Fields!Calculate_Weight_Using_ID.Value = 
"T",Fields!TotalTheorWeight.Value                                    
,Fields!Calculate_Weight_Using_ID.Value = "C",Fields!TotalCutWeight.Value
,Fields!Calculate_Weight_Using_ID.Value = "P",Fields!TotalPayWeight.Value
)
),Switch(Parameters!WeightUnit.Value = "orderdflt"
,Fields!Default_Weight.Value,True,Parameters!WeightUnit.Value))
,CInt(IIF(Parameters!WeightPrecision.Value="uomdefault",Fields!Weight_Precision.Value,Parameters!WeightPrecision.Value))))

However, I see the following error when I try to preview the report:

The Value expression for the text box ‘TotalWeight’ uses an aggregate expression without a scope. A scope is required for all aggregates used outside of a data region unless the report contains exactly one dataset.

So I tried fixing it by changing it to:

=Sum(IiF(Round(Code.ConvertWgt(Switch(Parameters!OrderLength.Value = 
"T",Fields!TotalTheorWeight.Value
    ,Parameters!OrderLength.Value = "C",Fields!TotalCutWeight.Value
    ,Parameters!OrderLength.Value = "P",Fields!TotalPayWeight.Value
,Parameters!OrderLength.Value = "OD",Switch(Fields!Calculate_Weight_Using_ID.Value = "T",Fields!TotalTheorWeight.Value
                                            ,Fields!Calculate_Weight_Using_ID.Value = "C",Fields!TotalCutWeight.Value
                                            ,Fields!Calculate_Weight_Using_ID.Value = "P",Fields!TotalPayWeight.Value
    )
),Switch(Parameters!WeightUnit.Value = "orderdflt"
    ,Fields!Default_Weight.Value,True,Parameters!WeightUnit.Value))
    ,CInt(IIF(Parameters!WeightPrecision.Value="uomdefault",Fields!Weight_Precision.Value,Parameters!WeightPrecision.Value)))), "Order")

with Order being the name of the dataset that I want to use. But, then I see this error:

The Value expression for the textrun ‘TotalWeight.Paragraphs[0].TextRuns[0]’ contains an error: [BC30516] Overload resolution failed because no accessible 'IIf' accepts this number of arguments.

How do I go about fixing this? Thanks!

Upvotes: 0

Views: 215

Answers (2)

Steve-o169
Steve-o169

Reputation: 2146

So to address each expression, the first error is indicating that you need to specify a scope for the SUM aggregate. This is as simple as adding the name of the dataset where the data is coming from inside the last parenthesis. You'll need to add ..., "YourDatasetName") so the following should work -- though such a complex expression is bound to have other issues. For example, it's impossible for us to know if ConvertWgt is going to work correctly without an idea of what the parameters require.

=Sum(Round(
    Code.ConvertWgt(
    Switch(
        Parameters!OrderLength.Value = "T", Fields!TotalTheorWeight.Value
        ,Parameters!OrderLength.Value = "C",Fields!TotalCutWeight.Value
        ,Parameters!OrderLength.Value = "P",Fields!TotalPayWeight.Value
        ,Parameters!OrderLength.Value = "OD",
            Switch( Fields!Calculate_Weight_Using_ID.Value = "T",Fields!TotalTheorWeight.Value                                    
                    ,Fields!Calculate_Weight_Using_ID.Value = "C",Fields!TotalCutWeight.Value
                    ,Fields!Calculate_Weight_Using_ID.Value = "P",Fields!TotalPayWeight.Value
                  )
          ),Switch(Parameters!WeightUnit.Value = "orderdflt"
    ,Fields!Default_Weight.Value,True,Parameters!WeightUnit.Value))
    ,CInt(IIF(Parameters!WeightPrecision.Value="uomdefault",
              Fields!Weight_Precision.Value,
              Parameters!WeightPrecision.Value)))
    , "Order")

The second expression, unfortunately, is just a mess and I'm not sure it's salvageable. You're trying to add an IIF but there's no conditional statement to evaluate from what I can tell. You're basically just rounding a huge switch statement and there's no true or false part, nor can the expression evaluate in such a way that it will provide a true or false result.

I see you've already accepted an answer, but I was already halfway through this answer, so I'll post it regardless since the other answer didn't explain how to fix these expressions.

Upvotes: 0

Toni H
Toni H

Reputation: 83

IIF is like IF in Excel - where =IF([some test],[if true do this],[if false do this]) ... so based off of the parentheses and commas you have, you're basically leaving out the [if true do this] and [if false do this] which is why you're getting that second error message about not having enough arguments.

As for the original error message, instead of using a text box, can you enter in a blank table with one row and one column and have it connected to the dataset?

Or have you thought about setting that value in a dataset - then setting that dataset to a parameter value, then using the parameter in your textbox?

You might also dig into this article: https://learn.microsoft.com/en-us/sql/reporting-services/report-design/built-in-collections-reportitems-collection-references-report-builder?view=sql-server-2017

Or this cross posted question here - I think this is what you need to do: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7d1c401c-e31d-4bc5-8c89-8684e63d3cd3/rsfieldreference-the-value-expression-for-the-textbox-8216allcustomcpi18217-refers-to-the?forum=sqlreportingservices

Upvotes: 1

Related Questions