Counting Rows on a LookupSet Function in Reporting Services

• jagnew

  Mr or Mrs. 500

  Points: 504

  More actions

  September 10, 2012 at 12:06 pm

  #280990

  I have two data sets.

  - Data1 contains a column (Tech) that has ID's for technicians.

  - Data2 contains a column (Tech) that has ID's for technicians, and a column (TicketID) that contains unique ticket numbers that the tech has worked on. Examples of the data below

  Data1:

  Tech

  _____

  techA

  techB

  techC

  Data2

  _____

  Tech | TicketID

  TechA | 98

  TechB | 201

  TechB | 101

  TechC | 102

  TechA | 205

  The Desired Output table should look like

  Tech | # of Tickets

  TechA | 2

  TechB | 2

  TechC | 1

  In Reporting Services I have a table that the Dataset Name = Data1 in Tablix Properties

  one of the columns is !Value.Tech,"Data1"

  I need to do a lookup or lookupset or something to get the data on Data2 in my table, and return a count of Tickets where !Value.Tech,"Data2" = !Value.Tech,"Data1"

  I have tried this

  =Count(LookupSet(Fields!Tech.Value,Fields!Tech.Value,Fields!TicketID.Value,"Data2")

  This returns #error in all rows on the Table that would return the result

  I have also tried

  =Count(Lookup(Fields!Tech.Value,Fields!Tech.Value,Fields!Ticket.Value,"Data2")

  This outputs 1's and 0's and I'm not sure what's going on.

  I have verified the base query that pulls the data is correct and I should be getting results that are several dozen / tech

  I'm stumped on this one if any one has any ideas?

• H4K

  Hall of Fame

  Points: 3374

  More actions

  September 11, 2012 at 4:21 am

  #1536119

  Use the following expression in the textbox :

  =LookupSet(Fields!PRODUCT_BRAND_CODE.Value, Fields!PRODUCT_BRAND_CODE.Value, Fields!PRODUCT_NUMBER.Value, "Product").Length

  From Ref: http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/135ef7c9-a4d0-4f16-9828-5ca8c2ee00ab

  BI Developer
  SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
  Please visit... ApplyBI

• jagnew

  Mr or Mrs. 500

  Points: 504

  More actions

  September 11, 2012 at 5:33 am

  #1536170

  H4K that was it. Thanks so much!