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.

 All Forums
 Development Tools
 Reporting Services Development
 Conditional expression help

Author  Topic 

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-03 : 15:23:09
Just wondering what the syntax would be if I want to say that if this textbox value is not same then do this else do this. Here's what I have.

=IIF( Fields!OPERATION_SEQ_NO.Value <> Fields!OPERATION_SEQ_NO.Value, SUM( Fields!HOUR_USED.Value), Fields!HOUR_USED.Value)

It seems like it's working if the OPERATION_SEQ_NO is the same, then it will just put in the value, but if it's different then it's not doing what's true.

Another thing is that I have the detail fields group by a couple of fields so I'm not sure if that has anything to do with it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-03 : 15:26:19
This doesn't make sense:

Fields!OPERATION_SEQ_NO.Value <> Fields!OPERATION_SEQ_NO.Value

The field will always be equal to itself. What am I missing?

Tara
Go to Top of Page

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-03 : 15:38:55
LOL!! That's the problem. I'm trying to say that if Fields!OPERATION_SEQ_NO.Value doesn't not equal each other then SUM, else leave it alone and just display regular value.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-03 : 15:40:54
I'm not following. Please provide an example.

Tara
Go to Top of Page

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-03 : 15:54:07
Here's the situation. I have a whole bunch of work orders with Workorder ID and Sequence No related to it. I want to group it by sequence no.

If the sequence nnumber matches then I want it to not sum, but if it is different then sum all the numbers.

Example:

WO----SeqNO-----HRS
2------10-------2.00
2------10-------1.50
Total: 3.50
2------11-------1.20
Total: 1.50
2------14-------3.50
Total: 3.50
Hrs Used: 8.5

The above example is group by sequence number saying that if sequence number is different sum total.

Example:

WO----SeqNO-----HRS
2------10-------2.00
2------10-------1.50
2------10-------1.20
2------10-------3.50
Total: 8.20
Hrs Used: 13.00

Bottom one is that if sequence number is just the same then pull the estimated bid number for that work order, so it does not sum total.

So I need to be able to see that if the sequence no is not same then sum this field, else don't sum it. Any ideas?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-03 : 16:03:45
A field has no idea what the previous value is or even the one after. It only knows about itself in that row. The grouping knows about all of them. So in the grouping, you could do something like this:

If Sum(SeqNo)/Count(SeqNo) = Top(SeqNo) Then whatever you do when they are the same
Else whatever you do when they are not the same

This is pseudocode obviously. The point is that in your last example, the math would be 4(10)/4=10. So you know they are all the same. Else, you've got at least one of them that isn't the same.

Tara
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-03 : 16:07:35
Just realized that my math isn't going to work for this example:

10+9+11+10/4=10

I think you'll need to handle this at the stored procedure layer.

Tara
Go to Top of Page

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-03 : 16:13:18
Yeah that makes sense. So kind of like this right?

=IIF(SUM(FIELDS!OPERATION_SEQ_NO.VALUE)/COUNT(FIELDS!OPERATION_SEQ_NO.VALUE) = TOP(FIELDS!OPERATION.SEQ_NO.VALUE), SUM(FIELDS!HOUR_USED.VALUE), FIELDS!HOUR_USED.VALUE)

You mentioned about grouping, where would this conditional expression go? Or if I do this at SQL lvl, where?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-03 : 16:20:48
It actually isn't going to work as I mentioned in my last post. I can't think of a good way to do this.

Tara
Go to Top of Page

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-03 : 16:23:43
Can I use an IF statement at sql lvl? Saying that if Sequence Number is similar then don't sum, else if it's not then sum?

The problem is that if the sequence number is the same, if I sum then it will sum total number of hours use for that work order, which is not correct....it will be 69.00 hours instead of 23.00 hour for that particular work order.

For example:

WO---SEQ#----HRS-----date
1----10------23.00---01/01/2004
1----10------23.00---01/03/2004
1----10------23.00---01/20/2004

This is stating that for that sequence the number of hours run is about 23.00 hours total for that particular sequence.


WO---SEQ#----HRS----DATE
1----10------23.00--01/20/2004
1----11------3.00---03/20/2003
1----12------2.00---12/31/2003

This if I sum than it is correct because I want the total hours for all sequence number.

Go to Top of Page

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-03 : 16:31:46
I'm wondering if I do a ReportItem comparison will it work. For example, say that if Reportitem is not same then do this else do that? Either by custom code or using the conditional expression.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-03 : 16:32:35
Would your two examples be different work orders then? So it would be more like this:

WO---SEQ#----HRS-----date
1----10------23.00---01/01/2004
1----10------23.00---01/03/2004
1----10------23.00---01/20/2004

WO---SEQ#----HRS----DATE
2----10------23.00--01/20/2004
2----11------3.00---03/20/2003
2----12------2.00---12/31/2003



Tara
Go to Top of Page

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-03 : 16:38:59
Yes my bad. For the work orders, it gives the total runtime base on the number of sequence. So if one workorder has only 1 sequence, it will give the same total of run time no matter how many number of records. Now another workorder might have 3 different sequence number, so it will show 3 different runtime.

My problem is that if I sum, then all line will sum and this doesn't work for some work order while it will work for others. So I'm hoping to be able to use some kind of grouping or custom coding from RS to determine if the SEQ is same or different then do this or do that.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-03 : 16:54:27
Well I'm stumped.

Tara
Go to Top of Page

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-03 : 17:03:06
Yeah I'm stumped too. Do you know any way of comparing values from within the same field in RS, either by conditional expression or custom coding? From conditional condition, I'm not sure how you can compare the same field within itself. Like what you mentioned earlier, it will be the same no matter what. LOL.

Anyone else got any ideas?
Go to Top of Page

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-03 : 17:13:17
Tara,

I think the example you provided a little while might work.

=IIF(SUM(FIELDS!OPERATION_SEQ_NO.VALUE)/COUNT(FIELDS!OPERATION_SEQ_NO.VALUE) = TOP(FIELDS!OPERATION.SEQ_NO.VALUE), SUM(FIELDS!HOUR_USED.VALUE), FIELDS!HOUR_USED.VALUE)


Because no matter what, if the sequence number is the same, after diving it, it will always be the same. For example:

10+10+10+10/4 = 10 or
12+12+12+12/4 = 12

That will always be true.

So if it's like this 10+9+5/3 = 8 it will not be true because top number will be 10.

Only problem now is how to get it to work. When I put that example in, I'm getting this:

"The value expression for the textbox "hour_Used" refers to the field 'Operation'. Repor item expression can only refer to fields within the current data set scope"

These fields are from one stored procedure so not sure what's the problem.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-03 : 17:16:55
This doesn't work:

10+9+11+10/4=10

So 9 and 11 throw it off.

This also doesn't work:

10+9+11/3=10


If these sequence numbers were ordered, then it might work as 9 would be first and 9<>10. So this might work if you order SeqNo to get the TOP one as part of the expression.

Oh and you'd put this expression in the grouping row.
Tara
Go to Top of Page

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-03 : 17:27:48
Yes that is true. So Tara, using Top, doesn't that just pick the Top number from all the records? I thought it does not matter about the ordering. Or should I use (MAX).

For example 4,8,5,0,23. Wouldn't the Top just be 23? Or would Max be better.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-03 : 17:33:01
Top just grabs the first one depending on the ordering. Yeah Max just might work for this. I can't think of an example where it wouldn't.

Tara
Go to Top of Page

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-03 : 17:45:58
Tara...you are truely "The Almighty SQL Goddess" and "RS Queen".

I try to think of all or at least most sequence that would trip it off by using "Max" and I can't seem to get any either. Another thing is that our company uses the SEQ which goes by 10 for each work order and so most of them will be like that. There are cases where the user can go in and override that, but that's like every blue moon.

Thanks.
Go to Top of Page
   

- Advertisement -