70595

Summing lookups in SSRS

Question:

I have an SSRS tablix that pulls data from several datasets resembling the following (changed to avoid explaining the exact context) :

There is an employees dataset that gives the employee ID, the employee ID of their manager and the number of tasks they have completed e.g.

Manager Employee Tasks <br /> DATA003 DATA001 118 <br /> DATA003 DATA002 42 <br /> DATA003 DATA003 94 <br /> DATA003 DATA004 118 <br />

From another database there is a dataset that gives the number of complaints against each employee, and the number that were upheld.

The tablix, linked to the employees table that gives the employee number, name, the sum of tasks, then does lookups to find the complaints, upheld complaints and calculate the % of tasks that resulted in complaints or upheld complaints.

e.g.

=lookup(Fields!EMPLOYEE_CODE.Value,Fields!EMPLOYEE_CODE.Value,Fields!Number_of_Complaints.Value,"ComplaintsPerEmployee")

This is then grouped on the managers field.

The figures for individual employees is okay, the problem arises when I try to sum those that are looked up. What I expected to be able to do when I started out was an expression like

=sum(multilookup( Fields!EMPLOYEE_CODE.Value, Fields!EMPLOYEE_CODE.Value, Fields!Number_of_Complaints.Value, "ComplaintsPerEmployee"))

in the row of the tablix that gives the totals for each manager. However, this gives an error. When I try:

=join(Fields!EMPLOYEE_CODE.Value,",")

I get an error. So I'm confused - why does this not give me the array of employee codes to pass to the join or the multilookup the same way I can just do Sum(TASKS) to sum the tasks for the employees under each manager.

I can't easily join the tables in SQL at the source as they are in different databases, and I would really rather not duplicate any of the data in other databases. I know the problem is not with the sum because the join given above doesn't work, and I've already tried the following custom code in place of sum:

Function SumLookup(ByVal items As Object()) As Decimal If items Is Nothing Then Return Nothing End If Dim suma As Decimal = New Decimal() Dim ct as Integer = New Integer() suma = 0 ct = 0 For Each item As Object In items suma += Convert.ToDecimal(item) ct += 1 Next If (ct = 0) Then return 0 else return suma End Function

This does not work as the multilookup is not working to pass values to it. I've tried reading up on tablixes, the expressions and so on and I seem to be swamped by stuff that I either already know or which isn't relevant - can anyone point me in the right direction?

Answer1:

=SUM(lookup(Fields!EMPLOYEE_CODE.Value,Fields!EMPLOYEE_CODE.Value,Fields!Number_of_Complaints.Value,"ComplaintsPerEmployee"))

Recommend

  • MySQL date compare problem? [closed]
  • Pagination for a table contents in yii
  • My app crashes in other languages. Xibs seem to not be loaded. Why?
  • Symfony adding element to form collection in PRE_SUBMIT event
  • Angular - $http.delete returns success but doesn't works
  • How to create datalist dynamically
  • Django graphene relay restricting queries to objects owned the user
  • How to really understand robocopy return code of 2
  • OleDB selecting multiple CSVs with first row as field names in C#
  • Unable to compute a CMAC using Cygwin and OpenSSL
  • angular js '?' in ng-src causes infinite loop
  • How to list languages supported by my application
  • Mapping of character encodings to maximum bytes per character
  • Find out specific weblogic process running
  • Making Toplevel resize itself to fit the title
  • Perl Moose accessors generated on the fly
  • Should an XML file included via have an declaration?
  • gridview on page won't refresh, even when calling databind again
  • AngularFire httpsCallable Object(…) is not a function
  • AutoComplete Texbox error - write to protected memory
  • Issue loading child entity of a parent entity. Unidirectional mapping and 1 to 0..1 relationship wit
  • git-svn: automatically importing/creating svn revisions as git tags
  • DataGrid show an empty row when DataTable is empty
  • How to sort a javascript array by date
  • SSIS package hangs while running
  • ASP.NET Gridview Paging Problem
  • How to get or calculate size of Azure File/Share or Service
  • How to model a mixture of finite components from different parametric families with JAGS?
  • DNS Lookup failed - Error with all browsers [closed]
  • SQL Server re-calculate or not?
  • Receive mouse move even cursor is outside control
  • Excel VBA How to populate a multi-dimensional (3d) array with values from multiple excel ranges?
  • Ruby on Rails App deployed to heroku showing “We're sorry, but something went wrong”
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • Can Jackson SerializationFeature be overridden per field or class?
  • how does django model after text[] in postgresql [duplicate]
  • How can I remove ASP.NET Designer.cs files?
  • python draw pie shapes with colour filled
  • Is there any way to bind data to data.frame by some index?
  • How can i traverse a binary tree from right to left in java?