September 10, 2012 at 12:06 pm
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?
September 11, 2012 at 4:21 am
Use the following expression in the textbox :
=LookupSet(Fields!PRODUCT_BRAND_CODE.Value, Fields!PRODUCT_BRAND_CODE.Value, Fields!PRODUCT_NUMBER.Value, "Product").Length
BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
September 11, 2012 at 5:33 am
H4K that was it. Thanks so much!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply