October 10, 2012 at 9:20 am
I have 2 datasets
The first dataset is a list of staff and the days they worked last month.
The second dataset is a list of staff and the number of helpdesk tickets they resolved last month
DataSet1
Staff | DaysWorked
John | 20
Adam | 15
DataSet2
Staff | TicketsResolved
John | 100
Adam | 50
My Table is using DataSet1
I have a column that calculates Tickets/Day via Lookup on DataSet2 and simple math, and that works fine So the table looks like
Staff | Days| Tickets | TicketsPerDay
John | 20 | 100 | 5
Adam | 15 | 50 | 3.3
A need a new Column in this table that will Have a 1 if Lowest Tickets/Day and 0 Otherwise
What I did was try this function
IIF(TicketsPerDay = Min(TicketsPerDay),1,0)
Logically this seems to me like it should work but it's not. It's actually Returning True for all Staff instead of just for Adam.
Anyone see what I'm doing wrong here?
October 10, 2012 at 11:01 am
Your dealing with a scope issue.
SSRS Is evaluating your statement on each row for the dataset, not the min for all rows.
You can do a few different things.
The easiest is Add a rank column to your dataset, ranking by Tickets/Day and then reference the new field like
iif(Fields!Rankcol.Value = 1, 1, 0)
Or change your expression in your report to:
IIF(TicketsPerDay = Min(Fields!TicketsPerDay.Value, "YourDataSetNameHere"),1,0) I do not know how this will perform, My guess is Not great.
There's a few other things similar to this.
You can put the expression Min(Fields!TicketsPerDay.Value, "YourDataSetNameHere") in a textbox and then set the visiblility to hidden. and then reference the textbox in your expression like.
IIF(TicketsPerDay = ReportItems!HiddenMinTextbox1.Value,1,0).
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply