locked
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:14 PM

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 lookupset 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 lookupset 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 lookupset in  table

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

    Tuesday, January 20, 2015 10:12 PM
  • this should work. Are you getting any errors? if it is a FetchXML based reports then the options are very limited where as in SQL we can temptable to have this data and get the max values too.

    If my response answered your question, please "mark the response as an answer" and also "vote as helpful". Regards, Hari. www.crm2011byhari.blogspot.com

    Wednesday, January 21, 2015 9:19 AM