none
Sum Lookup Function does not work as needed RRS feed

  • Question

  • Example, DataSet1 query;

    select 'm' as Total,'1' as ID,'A' as Name,1 as num
    union
    select 'm','1' ,'B' ,1
    union
    select 'm','1' ,'C' ,1
    union
    select 'm','2' ,'A' ,1

    union

    select 'm','2' ,'C' ,1
    union
    select 'n','2' ,'B' ,1
    union
    select 'n','3' ,'B' ,1

    DatSet2 query:

    select '1' as ID,3 as Number

    union

    select '2',6

    union

    select '3',8

    I want to design it like this:group by ID, then group by Total.

    1 expression=lookup(Fields!ID.Value,Fields!ID.Value, Fields!Number.Value, "DataSet2")

    2 and 3 are =sum(lookup(Fields!ID.Value,Fields!ID.Value, Fields!Number.Value, "DataSet2"))

    <sub><sup><strike></strike></sup></sub>It will show as below:

    I do not want to show them as sum, because they're unique in Dataset2. I want red 12 is 6, red 9 is 3 and red 21 should be 9. I know they're multiple times because dataset1 has duplicate records. But this is the real situation and I want to know if it's possible to solve it in SSRS.

    Actually I have thought two workaround for this issue, but want to know more efficient ideas from yours. Thanks.


    • Edited by Riaon Tuesday, November 19, 2019 9:54 AM
    Tuesday, November 19, 2019 9:53 AM

All replies

  • Hi Riaon,

    I have reviewed your post. The <<Expr>> 2 should be easily workable.  Since the data is already group on ID. Then those data from dataset2 that was retrieved by lookup on ID should stay the same. So, the value in Expression 2 box should be the same as expression 1. So you could change the expression 2 to:

    =lookup(Fields!ID.Value,Fields!ID.Value, Fields!Number.Value, "DataSet2")

    Then, about epression 3. From my opinion, this job here we need is to sum the distinct value, that are lookuped from dataset2. In SSRS design, the distinct select is not very handy. It is difficult to deal with it here.

    There is one work around for this is use custom code here, you could refer to  Sum Distinc.

    Above are my humble opinion, we'd like to hear you ideals and hope these are helpful.

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 20, 2019 3:05 AM
  • Thanks, but after testing the mentioned one, I get 27 and 20.

    Wednesday, November 20, 2019 5:45 AM
  • Hi,

    Did you get the 27 / 20 after you implement the code in the provided link?

    Also this time, were all the expression 2 working as you wanted, right?


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 22, 2019 5:50 AM
  • expression 2 is solved. Have you tested the expression 3 as I have provided your example data?
    Friday, November 22, 2019 7:07 AM
  • Hi, 

    It is sum for distinct value in ssrs.

    Have you checked these solutions?

     Sum of the grouped distinct values

    Sum of distinct values in field

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 29, 2019 9:03 AM