RDL Variables 1

[EricHarrington]

I'm trying to understand how to store a value in a variable in a RDL report. I'm using Microsoft Visual Studio 2005 Tools for Applications.

In the Report Properties >> Code section, I've added:

Public Dim varSales As Decimal

I suspect there is a way to assign a value to this value in an Expression, i.e.

=Code.varSales = 5

What am I missing?

 

[DaveInIowa]

I think in order to accomplish this you need to define a couple of functions; one to set the variable value and one to retrieve it.

Public Dim varSales As Decimal

Public Function SetSales(value as Decimal) As Decimal
    varSales = value
    Return value 'Since we have to return something we may as well return the value
End Function

Public Function GetSales() As Decimal
    Return varSales
End Function

Then the expressions in your report would look something like:

=Code.SetSales(5)

' or

=Code.GetSales()

 

[EricHarrington]

That worked perfectly, thank you. I need to do this for every day of the week and 3 different buckets, so a total of 21 variables. I list 2 of 7 from each bucket below. Is there a more efficient way to handle this, i.e. an array?

Public Dim varSalesExpD1 As Decimal
Public Dim varSalesExpD2 As Decimal

Public Dim varSalesRegD1 As Decimal
Public Dim varSalesRegD2 As Decimal

Public Dim varSalesSclD1 As Decimal
Public Dim varSalesSclD2 As Decimal

 

[DaveInIowa]

This is untested and I have no way of knowing how you intend to identify your bucket and day-of-week, but this should help get you started:

    Public varSales(20) As Decimal

    Public Function SetSales(ByVal value As Decimal, ByVal bucketId As String, ByVal dayOfWeek As System.DayOfWeek) As Decimal
        varSales(GetSalesIndex(bucketId, dayOfWeek)) = value
        Return value 'Since we have to return something we may as well return the value
    End Function

    Public Function GetSales(ByVal bucketId As String, ByVal dayOfWeek As System.DayOfWeek) As Decimal
        Return varSales(GetSalesIndex(bucketId, dayOfWeek))
    End Function

    Private Function GetSalesIndex(ByVal bucketId As String, ByVal dayOfWeek As System.DayOfWeek) As Integer
        Dim b As Integer
        Dim d As Integer

        Select Case bucketId
            Case "E" : b = 0
            Case "R" : b = 1
            Case Else : b = 2
        End Select

        Select Case dayOfWeek
            Case System.DayOfWeek.Sunday : d = 0
            Case System.DayOfWeek.Monday : d = 1
            Case System.DayOfWeek.Tuesday : d = 2
            Case System.DayOfWeek.Wednesday : d = 3
            Case System.DayOfWeek.Thursday : d = 4
            Case System.DayOfWeek.Friday : d = 5
            Case System.DayOfWeek.Saturday : d = 6
        End Select

        Return (b * 7) + d
    End Function

 

[EricHarrington]

Apparently I'm missing something. I had to revert to the original approach. I set the variable in the detail section via the day of the week.

=IIf(Weekday(Fields!forecastdt.Value) =2 AND Fields!forecastamt.Value > 0
AND Fields!categorynm.Value = "Sales" AND Fields!drivernm.Value = "Express",
Code.SetSalesExpD2(Fields!forecastamt.Value),
IIf(Weekday(Fields!forecastdt.Value) =2 AND Fields!forecastamt.Value > 0,
Code.SetSalesD2(Fields!forecastamt.Value), nothing))

The variable appears to be set properly, it returns $154.23 for Monday (day 2 is Monday). When I go to get the variable in the first footer for this (grouping by drivernm) I get zero. In the group footer for the second drivernm (Coupons) I get the value for the last day (day 7) from the previous drivernm (Bottles) of $220.44. The variable is set for $335.79.

What am I missing?

 

[DaveInIowa]

You're not providing enough information. Based on what you had provided I had assumed there were 2 variables to determine what bucket the ForecastAmt belonged to, one obviously being the day-of-week and the second on some other unknown field which would map to 'Exp', 'Reg' or 'Scl'. Now you've introduced a third variable into the equation (categorynm) and I don't know how to process that.

Please explain the exact field name and its values that determine which of the 3 "buckets" you are referring too.

 

[EricHarrington]

Thanks for helping out.

The data is grouped by Category Name (categorynm), then by Driver Name (drivernm).

For example:

categorynm, drivernm
Customers, Express
Customers, Regular
Customers, Service Center
Sales, Express
Sales, Regular
Sales, Service Center

There is a single value for each combination for each day.

categorynm, drivernm, forecastdt, forecastamt
Customers, Express, Sunday, 1893
Customers, Express, Monday, 1411
Customers, Express, Tuesday, 1158 etc...
Customers, Regular, Sunday, 459
Customers, Regular, Monday, 511
Customers, Regular, Tuesday, 642 etc...

I'm not grouping by date. My approach was to list the days in separate fields in a table. I would check the dayofweek and if it matches the day specified then set the value. The reason I'm doing this is because there is one combination that needs to add 3 values together, all other combinations will be set to the forecastamt for that day.

If I get the variables set properly then I can add them together later on in the report.

The group footer (drivernm) would look like this:

=IIf(Fields!categorynm.Value = "Customer Service" AND Fields!drivernm.Value = "Sales" AND Sum(Fields!forecastamt.Value)>0 AND Weekday(Fields!forecastdt.Value) =1,
Code.GetSalesExpD1() + Code.GetSalesRegD1() + Code.GetSalesSCD1(), Code.GetSalesD1)

 

[EricHarrington]

I came across a posting that indicated that RDL evaluates a group header and footer at the same time as the detail section. This appears to explains the issues I was encountering. If I include another group for forecastdt (date field), set the variable there and reference it in the previous group footer everything it works fine.

This solves the variable inconsistencies but isn't a valid approach for other reasons. At this point I've tried a matrix which also works for a single variable but now I have to use an array to complete the solution. Daveiniowa, I've gotten part of your array example working but not the whole thing. Is there any way to contact you for consulting work? If this is not the correct forum to do so, please let me know.