SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Reporting Services Development
 Conditional expression help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chriskhan2000
Aged Yak Warrior

USA
544 Posts

Posted - 05/03/2005 :  15:23:09  Show Profile  Reply with Quote
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

USA
36582 Posts

Posted - 05/03/2005 :  15:26:19  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
544 Posts

Posted - 05/03/2005 :  15:38:55  Show Profile  Reply with Quote
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

USA
36582 Posts

Posted - 05/03/2005 :  15:40:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
I'm not following. Please provide an example.

Tara
Go to Top of Page

chriskhan2000
Aged Yak Warrior

USA
544 Posts

Posted - 05/03/2005 :  15:54:07  Show Profile  Reply with Quote
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

USA
36582 Posts

Posted - 05/03/2005 :  16:03:45  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 05/03/2005 16:06:39
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36582 Posts

Posted - 05/03/2005 :  16:07:35  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
544 Posts

Posted - 05/03/2005 :  16:13:18  Show Profile  Reply with Quote
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

USA
36582 Posts

Posted - 05/03/2005 :  16:20:48  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
544 Posts

Posted - 05/03/2005 :  16:23:43  Show Profile  Reply with Quote
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

USA
544 Posts

Posted - 05/03/2005 :  16:31:46  Show Profile  Reply with Quote
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

USA
36582 Posts

Posted - 05/03/2005 :  16:32:35  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
544 Posts

Posted - 05/03/2005 :  16:38:59  Show Profile  Reply with Quote
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

USA
36582 Posts

Posted - 05/03/2005 :  16:54:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
Well I'm stumped.

Tara
Go to Top of Page

chriskhan2000
Aged Yak Warrior

USA
544 Posts

Posted - 05/03/2005 :  17:03:06  Show Profile  Reply with Quote
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

USA
544 Posts

Posted - 05/03/2005 :  17:13:17  Show Profile  Reply with Quote
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

USA
36582 Posts

Posted - 05/03/2005 :  17:16:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 05/03/2005 17:20:15
Go to Top of Page

chriskhan2000
Aged Yak Warrior

USA
544 Posts

Posted - 05/03/2005 :  17:27:48  Show Profile  Reply with Quote
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

USA
36582 Posts

Posted - 05/03/2005 :  17:33:01  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
544 Posts

Posted - 05/03/2005 :  17:45:58  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000