Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
satsap
Starting Member
9 Posts |
 Posted - 2008-11-13 : 10:38:18
|
| Hello, I have written the below expression in a textbox, which is present on the top of the report body. IIF(Fields!Deptartment.Value<>"Education", "There is no Education department in the workload" & vbcrlf &IIF(Fields!Deptartment.Value<>"Food", "There are no Food department in the workload" & vbcrlf & IIF(Fields!Deptartment.Value<>"Environment", "There are no Environment department in the workload" & vbcrlf &IIF(Fields!Deptartment.Value<>"Transportation", "There are no Transportation department in the workload",System.DBNull),System.DBNull),System.DBNull),System.DBNull) When i preview the report it gives me an error "The value expression for the textbox refers to the field Department. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope"So can anyone of you tell me where can i provide a dataset scope in the above expression or is there any other way to do this? Thanks in advance |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-13 : 10:46:34
|
| actual since this textbox is not inside any container, you shoud be applying some kind of aggregation on fields. Actually how many items (rows) does your dataset contain? and what values do you want to compare? |
 |
|
|
satsap
Starting Member
9 Posts |
Posted - 2008-11-13 : 10:58:49
|
| When the worker selects one of the parameters (Edu, Transport, Health, Food or Environment) and there are no corresponding departments in the worker's workload: I should Display the following text - 'There is no <DeptName> department in the workload', where <deptname> corresponds to the parameter for which there are no departments. (Example: When the worker selects 'Food' as one of the parameters, and there is no Food department in the workload display the text 'There is no Food department in the workload'.This text should display before displaying the workload list. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-13 : 11:19:06
|
| then shouldnt you be taking count of dataset rather than simply field value? |
|
|
|
satsap
Starting Member
9 Posts |
Posted - 2008-11-13 : 11:48:33
|
| how do i take the count? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-13 : 12:20:30
|
| seems like what you want is this=IIF(Sum(IIF(Fields!Deptartment.Value=Parameters!Department.value,1,0),"Yourdatasetnamehere")=0, "There is no " & Parameters!Department.value & "department in the workload",System.DBNull) |
|
|
|
satsap
Starting Member
9 Posts |
Posted - 2008-11-13 : 13:35:15
|
| =IIF(Sum(IIF(Fields!Deptartment.Value=Parameters!Department.value,1,0),"Yourdatasetnamehere")=0, "There is no " & Parameters!Department.value & "department in the workload",System.DBNull)I have to modify this expression a liitle bit and it worked.Thank you |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-13 : 23:22:45
|
Cheers  |
|
|
|
|
|
|
|
|
how to specify dataset scope in IIF()