none
Refresh Dictionary Values RRS feed

  • Question

  • Hello, 

    Due to the nature of my grouping and using a CSV as oneof my datasource I have needed to use custom code to sum values. 

    My custom code is a nested dictionary that stores the totals of various groups. 

    The issue I'm having now is that whenever I re-run the report the dictionary is not reset to it's initial state, so it will continue to sum values. So if you run the same report with same values twice it will return the values * 2. 

    Is there anyway to run a function so that whenever the view report button is pressed a function will run? 

    Thursday, July 18, 2019 10:48 AM

Answers

  • After longer than I'd like to admit trying to fix this I have. 

    I have essentially added a check into the code after various points 

           


    Public Shared Dim check AS Boolean = true


    Public Function addValue(ByVal year AS String, ByVal code As String, ByVal cust As String,ByVal value As Integer)   
    If check = false Then
     dictionary = New System.Collections.Generic.Dictionary(Of String, System.Collections.Generic.Dictionary(Of String, System.Collections.Generic.Dictionary(Of String, Integer)))
    check = true

    End If

        If dictionary.ContainsKey(code) Then..... 

    public  Function sumCode(ByVal code AS String)

    ...
    check = false
    return total
    End Function

    • Marked as answer by lswaddle Thursday, July 18, 2019 11:19 AM
    Thursday, July 18, 2019 11:19 AM

All replies

  • Can you show the script's code?

    https://techmbabi.info/2015/05/10/ssrs-nested-groups-with-totals/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, July 18, 2019 10:51 AM
  • Here's all the code, I just need that dictionary to be cleared whenever a report is run, if it is done before hand then when I expand any groups I get errors.

    Private Shared Dim dictionary AS New System.Collections.Generic.Dictionary(Of String, System.Collections.Generic.Dictionary(Of String, System.Collections.Generic.Dictionary(Of String, Integer)))


    Public Function addValue(ByVal year AS String, ByVal code As String, ByVal cust As String,ByVal value As Integer)   
    Dim custDict AS New System.Collections.Generic.Dictionary(Of String, System.Collections.Generic.Dictionary(Of String, Integer))
    Dim yearDict AS New System.Collections.Generic.Dictionary(Of String,Integer)

        If dictionary.ContainsKey(code) Then 
            If dictionary(code).ContainsKey(cust) Then
                If dictionary(code)(cust).ContainsKey(year) Then
                    dictionary(code)(cust)(year) = dictionary(code)(cust)(year) + value
                Else
                    dictionary(code)(cust).Add(year, value)
                End If
            Else
                    dictionary(code).Add(cust, New System.Collections.Generic.Dictionary(Of String,Integer))
                    dictionary(code)(cust).Add(year,value)
            End If
        Else
        'Adding The code, year and customer values to the dictionary.

            dictionary.Add(code, New System.Collections.Generic.Dictionary(Of String, System.Collections.Generic.Dictionary(Of String,Integer)))
            dictionary(code).Add(cust, New System.Collections.Generic.Dictionary(Of String, Integer))
            dictionary(code)(cust).Add(year,value)
        End If
    End Function

    public Function getValue(ByVal year AS String, ByVal code AS String,ByVal cust AS String)
        return dictionary(code)(cust)(year)
    End Function

    public Function getSumValue()
        Dim total AS Integer
        For Each code AS System.Collections.Generic.KeyValuePair(Of String, System.Collections.Generic.Dictionary(Of String, System.Collections.Generic.Dictionary(Of String,Integer))) in dictionary
            For Each cust AS System.Collections.Generic.KeyValuePair(Of String, System.Collections.Generic.Dictionary(Of String,Integer)) in code.value
                For Each year AS System.Collections.Generic.KeyValuePair(Of String, Integer) in cust.value
                    total += year.value
                Next
            Next
        Next
        return total
    End Function

    public  Function sumCode(ByVal code AS String)
    Dim total AS Integer
    For Each cust AS System.Collections.Generic.KeyValuePair(Of String, System.Collections.Generic.Dictionary(Of String,Integer)) in dictionary(code)
        For Each year AS System.Collections.Generic.KeyValuePair(Of String,Integer) in cust.value
    total += year.value
        Next
    Next
    return total
    End Function

            
    • Edited by lswaddle Thursday, July 18, 2019 10:54 AM Edit
    Thursday, July 18, 2019 10:53 AM
  • Which function is a problematic?  

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, July 18, 2019 11:01 AM
  • The function works as intended when I run a report for the first time, but then every time after it will continue to sum the values from the first run, to the second run and so on. 

    The initial values on each run should be an empty nested dictionary that is then populated from the SSRS table. 
    Thursday, July 18, 2019 11:05 AM
  • Perhaps  you can set the variables as code =nothing

    or check it

    IF (CODE IS NOTHING) THEN

        CODE=....

    END IF


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, July 18, 2019 11:09 AM
  • After longer than I'd like to admit trying to fix this I have. 

    I have essentially added a check into the code after various points 

           


    Public Shared Dim check AS Boolean = true


    Public Function addValue(ByVal year AS String, ByVal code As String, ByVal cust As String,ByVal value As Integer)   
    If check = false Then
     dictionary = New System.Collections.Generic.Dictionary(Of String, System.Collections.Generic.Dictionary(Of String, System.Collections.Generic.Dictionary(Of String, Integer)))
    check = true

    End If

        If dictionary.ContainsKey(code) Then..... 

    public  Function sumCode(ByVal code AS String)

    ...
    check = false
    return total
    End Function

    • Marked as answer by lswaddle Thursday, July 18, 2019 11:19 AM
    Thursday, July 18, 2019 11:19 AM
  • Great

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, July 18, 2019 12:08 PM