Counting Rows on a LookupSet Function in Reporting Services

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » Counting Rows on a LookupSet Function in...

Counting Rows on a LookupSet Function in Reporting Services

Rate Topic

Display Mode

Topic Options

Author

Message

jagnew

Posted Monday, September 10, 2012 12:06 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, November 02, 2012 7:29 AM
Points: 10, Visits: 23

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?

Post #1356938

H4K

Posted Tuesday, September 11, 2012 4:21 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:11 AM
Points: 127, Visits: 507

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

Post #1357276

jagnew

Posted Tuesday, September 11, 2012 5:33 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, November 02, 2012 7:29 AM
Points: 10, Visits: 23

H4K that was it. Thanks so much!

Post #1357332

« Prev Topic | Next Topic »

Permissions