2

I have succeeded in building a number of group variables within an SSRS report, however what I want to do now is to use that variable outside of the group.

eg - I have a variable that calculates a payment within one dataset, and another one that calculates a payment within another dataset.

The first would be =Variables!QualityPayment.Value, the second would be =Variables!RevenuePayment.Value. Revenue and Quality are displayed in different SSRS tables.

I want to add the Quality Payment and Revenue Payment together, but when I try and put them outside of the table I get the error message

'Expressions can only refer to a Variable declared within the same grouping scope, a containing grouping scope, or those declared on the report.'

How do I go about adding the two together?

Thanks in advance

Jon

Taryn
  • 242,637
  • 56
  • 362
  • 405
Jonathan Rogers
  • 31
  • 2
  • 3

3 Answers3

2

What I would do would be to use the group variable just as a way to add the values to an external variable, declared on the VB Code section of the report:

On the Report Properties -> Code section you use the following code:

Dim variable1 AS Integer
Function addValue(ByVal value AS Integer)
    variable1 += value
End Function

Function getValue()
    return variable1
End Function

Then on the group variable section of the tablix, you just call addValue(somefield) and when you need to access the calculated value you can do it from outside the group by calling the getValue function.

Joao Leal
  • 5,533
  • 1
  • 13
  • 23
  • i can't believe this amazing answer is upvoted so less. this answer saved me from suicide xD – aspxsushil Sep 29 '18 at 07:24
  • Could you provide more details for how to SUM the group variables? I have a variable I am referencing in a tablix textbox as `Variables!varBudgetVariance.Value`. I want to calculate totals via SUM in a textbox below the group but when I use the expression `SUM(Variables!varBudgetVariance.Value)` I get this error: *"Variable values cannot be used in aggregate functions"*. If I add your functions, `addValue` and `getValue` to the report code, how do I call them in the tablix?? – SherlockSpreadsheets Dec 20 '19 at 23:02
  • You go to your tablix and in Tablix Group Properties create a variable and the value will be the call to the addValue function with whatever fields you need: Code.addValue(Fields!my_field). Then on your textbox you call the getValue function: Code.getValue() – Joao Leal Dec 21 '19 at 00:48
  • Okay thanks. I did this and it worked kind of. Thoughts on how to correct these issues? ***Good:*** (1) The total is now printed in the group footer and at-a-glance looks correct with tablix footer expression `Code.getLWSBeginLifetimeExpense()`. ***Bad:*** There are some odd issues now such as (1) When I print the variable value in the group table `Variables!varLWSBegin_LifetimeExpense.Value` the values are blank throughout the tablix dataset; (2) When I export from browser render to excel the group footer values change and are incorrect (e.g. from "1,214,284" to "0"). – SherlockSpreadsheets Dec 21 '19 at 14:22
  • In comme above, I am using using GroupVar expression `Code.addLWSBeginLifetimeExpense(CDbl( IIF(Parameters!boolUseLwsBalance.Value = false, 0, Lookup(Fields!ProjectId.Value, Fields!ProjectId.Value, Fields!LWSBegin_LifetimeExpense.Value, "dsCAPEXAmountsCustomDataUpload")) ))` and footer textbox expression `Code.getLWSBeginLifetimeExpense()`. – SherlockSpreadsheets Dec 21 '19 at 14:22
  • In comment above, I am using this VB code: `Public totalLWSBeginLifetimeExpense as Integer Public Function getLWSBeginLifetimeExpense() return totalLWSBeginLifetimeExpense End Function Public Function addLWSBeginLifetimeExpense(ByVal thisLWSBeginLifetimeExpense AS Integer ) totalLWSBeginLifetimeExpense = totalLWSBeginLifetimeExpense + thisLWSBeginLifetimeExpense End Function` – SherlockSpreadsheets Dec 21 '19 at 15:12
  • 1
    Fixed the group variable value by returning "thisLWSBeginLifetimeExpense" within the add function like this: `Public Function addLWSBeginLifetimeExpense(ByVal thisLWSBeginLifetimeExpense AS Integer ) totalLWSBeginLifetimeExpense = totalLWSBeginLifetimeExpense + thisLWSBeginLifetimeExpense return thisLWSBeginLifetimeExpense End Function`. Still need to fix totals not printing when exporting to Excel. – SherlockSpreadsheets Dec 21 '19 at 15:28
  • Do you now why export to Excel and the `Code.getTotal` value is 0? The value prints correctly on the interactive page but is 0 when exported. If I change `Dim` to `Dim Public Shared` the zeros go away. But then the variable value are added to each other every time the report is run. Any suggestions? – SherlockSpreadsheets Dec 23 '19 at 00:53
  • Sorry, don't remember that case, so would have to check and don't have my laptop with me right now. – Joao Leal Dec 24 '19 at 02:30
0

Assuming that both datasets are accessing the same database, I suggest combining the two datasets into a single dataset.

0

In your definition of the variables, make sure that any aggregate functions have the dataset specified for the scope, i.e.:

=SUM(Fields!MyFieldName.Value, "DataSet1") / 12
Jamie F
  • 23,189
  • 5
  • 61
  • 77