Search code examples
mysqlsqlt-sqlssrs-2008ssrs-2012

How to Sum Value's of a column in a Table using Lookup function

I have two Datasets i.e Dataset1 and Dataset 2 both have columns like (System has Similar Values in Both Dataset)

Dataset1-System , work , food

Dataset2-System , total , complete

Value in Column (DATASET2) "Total" is like 1,18,4 and in "Complete" its like 1,0,4.

I want to use Dataset2 in the same Tablix as Dataset1 and I would like to get the result by summing up total and complete

=Lookup(Fields!SYSTEM.Value ,Fields!SYSTEM.Value, Fields!COMPLETE.Value , "DATASET2"))/Lookup(Fields!SYSTEM.Value ,Fields!SYSTEM.Value, Fields!TOTAL.Value,"DATASET2"))

Tried this as well as included SUM in both like

=SUM(Lookup(Fields!SYSTEM.Value ,Fields!SYSTEM.Value, Fields!COMPLETE.Value , "MANI"))/SUM(Lookup(Fields!SYSTEM.Value ,Fields!SYSTEM.Value, Fields!TOTAL.Value,"DATASET2"))

Tried more combinations Nothing Worked

Required result:- " SUM(TOTAL)/SUM(COMPLETE) " using LOOKUP How can I achieve it?

Solution

  • You're on the right track, but just looks like you're using the wrong function. Also, you may be using too many parenthesis in the top expression. You want to use the SUM on a LookupSet function. This will return all the values of COMPLETE and TOTAL in a list that you can add together. Try the following expression.

    =(SUM(LookupSet(Fields!SYSTEM.Value ,Fields!SYSTEM.Value, Fields!COMPLETE.Value , "DATASET2")))
 /(SUM(LookupSet(Fields!SYSTEM.Value ,Fields!SYSTEM.Value, Fields!TOTAL.Value,"DATASET2")))