none
Combine seperate lookupsets - find max(), count()? RRS feed

  • Question

  • Hello, my knowledge of SSRS is intermediate.  Enough to have a plan.  But I need just a little assistance.

    Here are the functions.

    here are my three lookupsets:

    -LookupSet(Fields!name.Value, Fields!name.Value, Fields!act_actualend.Value, “AccountActivities”)

    -LookupSet(Fields!name.Value, Fields!name.Value, Fields!act_actualend.Value, “AccountContactActivities”)

    -LookupSet(Fields!name.Value, Fields!name.Value, Fields!act_actualend.Value, “AccountOpportunityActivities”)

    1) How could I find the 'Max()' of all three of these datasets.  The single highest number.

    2) How could I find the 'COUNT()' of all three of these datasets.   The total number of rows.

    If they can be done separately and then added together using variables, that is an acceptable solution.  Still, I'm just learning syntax here.

    THanks

    Tuesday, January 20, 2015 4:13 PM

Answers

  • Hi ebs_software,

    According to your description, you used three lookupset function to retrieve data from other dataset, now you want to select max value and count of values of all three datasets.

    I tested the issue in my local machine, we could not select sum and max value returned by lookupset function directly. To workaround this issue, we need to use custom code. For detail information, please refer to the following document:

      1. Right-click outside of the report, click Report Properties.
      2. Click Code in left pane, type the code like below:

    Public Function AggLookup(ByVal choice as String, ByVal items as Object)
     
     
     If items is Nothing then
      Return Nothing
     End If
     
     
     Dim current as Decimal
     Dim sum as Decimal
     Dim count as Integer
     Dim min as Decimal
     Dim max as Decimal
     Dim err as String
     
     
     current = 0 
     sum = 0
     count = 0
     err = ""
     
     
     For each item as Object in items
     
      'Calculate count
      count += 1
     
      'Check value is a number
      If IsNumeric(item) then
     
       'Set current
       current = Convert.ToDecimal(item)
     
       'Calculate sum
       sum += current
     
       'Calculate min
       If min = Nothing then
        min = current
       End If
       If Min >current then
        min = current
       End If
     
       'Calculate max
       If max = Nothing then
        max = current
       End If
       If max < current then
        max = current
       End If
     
      'Return NaN if value is not a number
      Else
       err = "NaN"
      End If
     
     Next
     
     'Select and set output based on the user choice or choice passed by function
     If err = "NaN" then
      If choice = "count" then
       Return count
      Else
       Return err
      End If
     Else
      Select Case choice
       Case "sum"
        Return sum
       Case "count"
        Return count
       Case "min"
        Return min
       Case "max"
        Return max
       Case "avg"
        'Calculate the average avoiding divide by zero errors
        If count > 0 then
         Return sum / count
        Else
         Return 0
        End If
      End Select
     End If
     
     End Function

      3. Right-click handle of the column you want to count values returned by lookupset function, click Expression.
      4. In Expression text box, type the expression like below:

    =LookupSet(Fields!name.Value, Fields!name.Value, Fields!act_actualend.Value,"AccountActivities").Length + LookupSet(Fields!name.Value, Fields!name.Value, Fields!act_actualend.Value,"AccountContactActivities").Length + LookupSet(Fields!name.Value, Fields!name.Value, Fields!act_actualend.Value,"AccountOpportunityActivities").Length

      5. Right-click handle of the column you want to calculate total of values returned by lookupset function, click Expression.
      6. In Expression text box, type the expression like below:

    =Code.AggLookup("sum",LookupSet(Fields!name.Value, Fields!name.Value, Fields!act_actualend.Value,"AccountActivities"))

      7. Right-click handle of the column you want to select max value returned by lookupset function, click Expression.
      8. In Expression text box, type the expression like below:

    =Code.AggLookup("max",LookupSet(Fields!name.Value, Fields!name.Value, Fields!act_actualend.Value,"AccountActivities"))

    The following screenshot is for your reference:

    If you have any more questions, please feel free to ask.

    Thanks,
    Wendy Fu

    If you have any feedback on our support, please click here.

    Wendy Fu
    TechNet Community Support



    Wednesday, January 21, 2015 7:35 AM
    Moderator

All replies

  • For Count I have an untested idea.   For Max still brainstorming.

    Count(LookupSet(Fields!name.Value, Fields!name.Value, Fields!act_actualend.Value, “AccountActivities”)) + Count(LookupSet(Fields!name.Value, Fields!name.Value, Fields!act_actualend.Value, “AccountContactActivities”)) + Count(LookupSet(Fields!name.Value, Fields!name.Value, Fields!act_actualend.Value, “AccountOpportunityActivities”))

    Tuesday, January 20, 2015 4:48 PM
  • I'll post my logic.   I think it's obviously what I'm trying to do.   Tell me what's wrong with it if you can.

    1.  Here I’m trying to get the max of three lookupsets, against the main dataset in  table.

    =Max(Max(LookupSet(Fields!name.Value, Fields!name.Value, Fields!act_actualend.Value, "AccountOpportunityActivities")), Max(LookupSet(Fields!name.Value, Fields!name.Value, Fields!act_actualend.Value, "AccountActivities")), Max(LookupSet(Fields!name.Value, Fields!name.Value, Fields!act_actualend.Value, "AccountContactActivities")))

    1.  Here I’m trying to get the Count of three lookupsets, against the main dataset in  table

    =Count(LookupSet(Fields!name.Value, Fields!name.Value, Fields!act_actualend.Value, "AccountOpportunityActivities")) + Count(LookupSet(Fields!name.Value, Fields!name.Value, Fields!act_actualend.Value, "AccountActivities"))  + Count(LookupSet(Fields!name.Value, Fields!name.Value, Fields!act_actualend.Value, "AccountContactActivities"))

    1.  Here I’m getting the sum of one lookupset, against the main dataset in  table

    =Sum(LookupSet(Fields!name.Value, Fields!name.Value, Fields!estimatedvalue.Value, "OpportunityAccount"))


    • Edited by ebs_software Wednesday, January 21, 2015 1:37 PM
    Tuesday, January 20, 2015 10:11 PM
  • Hi ebs_software,

    According to your description, you used three lookupset function to retrieve data from other dataset, now you want to select max value and count of values of all three datasets.

    I tested the issue in my local machine, we could not select sum and max value returned by lookupset function directly. To workaround this issue, we need to use custom code. For detail information, please refer to the following document:

      1. Right-click outside of the report, click Report Properties.
      2. Click Code in left pane, type the code like below:

    Public Function AggLookup(ByVal choice as String, ByVal items as Object)
     
     
     If items is Nothing then
      Return Nothing
     End If
     
     
     Dim current as Decimal
     Dim sum as Decimal
     Dim count as Integer
     Dim min as Decimal
     Dim max as Decimal
     Dim err as String
     
     
     current = 0 
     sum = 0
     count = 0
     err = ""
     
     
     For each item as Object in items
     
      'Calculate count
      count += 1
     
      'Check value is a number
      If IsNumeric(item) then
     
       'Set current
       current = Convert.ToDecimal(item)
     
       'Calculate sum
       sum += current
     
       'Calculate min
       If min = Nothing then
        min = current
       End If
       If Min >current then
        min = current
       End If
     
       'Calculate max
       If max = Nothing then
        max = current
       End If
       If max < current then
        max = current
       End If
     
      'Return NaN if value is not a number
      Else
       err = "NaN"
      End If
     
     Next
     
     'Select and set output based on the user choice or choice passed by function
     If err = "NaN" then
      If choice = "count" then
       Return count
      Else
       Return err
      End If
     Else
      Select Case choice
       Case "sum"
        Return sum
       Case "count"
        Return count
       Case "min"
        Return min
       Case "max"
        Return max
       Case "avg"
        'Calculate the average avoiding divide by zero errors
        If count > 0 then
         Return sum / count
        Else
         Return 0
        End If
      End Select
     End If
     
     End Function

      3. Right-click handle of the column you want to count values returned by lookupset function, click Expression.
      4. In Expression text box, type the expression like below:

    =LookupSet(Fields!name.Value, Fields!name.Value, Fields!act_actualend.Value,"AccountActivities").Length + LookupSet(Fields!name.Value, Fields!name.Value, Fields!act_actualend.Value,"AccountContactActivities").Length + LookupSet(Fields!name.Value, Fields!name.Value, Fields!act_actualend.Value,"AccountOpportunityActivities").Length

      5. Right-click handle of the column you want to calculate total of values returned by lookupset function, click Expression.
      6. In Expression text box, type the expression like below:

    =Code.AggLookup("sum",LookupSet(Fields!name.Value, Fields!name.Value, Fields!act_actualend.Value,"AccountActivities"))

      7. Right-click handle of the column you want to select max value returned by lookupset function, click Expression.
      8. In Expression text box, type the expression like below:

    =Code.AggLookup("max",LookupSet(Fields!name.Value, Fields!name.Value, Fields!act_actualend.Value,"AccountActivities"))

    The following screenshot is for your reference:

    If you have any more questions, please feel free to ask.

    Thanks,
    Wendy Fu

    If you have any feedback on our support, please click here.

    Wendy Fu
    TechNet Community Support



    Wednesday, January 21, 2015 7:35 AM
    Moderator