SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Counting Rows on a LookupSet Function in Reporting Services


Counting Rows on a LookupSet Function in Reporting Services

Author
Message
jagnew
jagnew
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 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?
H4K
H4K
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1117 Visits: 567
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
jagnew
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 23
H4K that was it. Thanks so much!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search