| Author |
 Topic  |
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
 Posted - 10/31/2012 : 11:04:41
|
Hi
I have a column with a total number of days for each ticket callID and want to sum all tickets < 3 and sum all tickets < 7 in their own fields in a table as a breakdown. I know this is basic but not an avid SRRS user...can someone help?
=IIF(Sum(Fields!NUMBER_OF_DAYS.Value, "DataSet1") < 3, IIF(Sum(Fields!NUMBER_OF_DAYS.Value, "DataSet1") < 7
Treid this too with error: =Sum(IIF(Fields!NUMBER_OF_DAYS.Value =0)) |
Edited by - sz1 on 10/31/2012 11:15:55
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 10/31/2012 : 12:14:54
|
=Sum(IIF(Fields!NUMBER_OF_DAYS.Value < 3,1,0))
and
=Sum(IIF(Fields!NUMBER_OF_DAYS.Value < 7,1,0))
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 10/31/2012 : 12:23:26
|
Thanbks for replying getting
The value expression for the text box uses an aggreagte expression without a scope, scope is required for aggreagtes outside data region.
Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 10/31/2012 : 12:40:13
|
hmm? where are you using the expression?
what about this?
=Sum(IIF(Fields!NUMBER_OF_DAYS.Value < 3,1,0),"DataSet1")
and
=Sum(IIF(Fields!NUMBER_OF_DAYS.Value < 7,1,0),"DataSet1")
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 11/05/2012 : 07:24:47
|
Hi
Thanks that worked I created 2 text fields and added seperate expressions...I'm now trying to get the pecentage based on the above of all tickets and have this but not getting the correct figure back:
can you help with this one?
=Sum(IIF(Fields!NUMBER_OF_DAYS.Value < 3,1,0),"DataSet1")/Sum(Fields!NUMBER_OF_DAYS.Value, "DataSet1")*100
=Sum(IIF(Fields!NUMBER_OF_DAYS.Value < 7,1,0),"DataSet1")/Sum(Fields!NUMBER_OF_DAYS.Value, "DataSet1")*100 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/05/2012 : 07:48:09
|
=Sum(IIF(Fields!NUMBER_OF_DAYS.Value < 3,1,0),"DataSet1")/Count(Fields!NUMBER_OF_DAYS.Value, "DataSet1")*100
=Sum(IIF(Fields!NUMBER_OF_DAYS.Value < 7,1,0),"DataSet1")/Count(Fields!NUMBER_OF_DAYS.Value, "DataSet1")*100 If there is the possibility that there will be zero rows, you may want to take care of that as well. |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 11/05/2012 : 08:30:14
|
Should have known that I had it in a crystal report...when I format the expression now as precentage with 2 decimal places it results in: 6513.09% if no places its 6513%, how can I force it to be 65.13%
Thanks |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/05/2012 : 09:01:40
|
| My immediate reaction would be to suggest that the multiplication by 100 be removed from you expression. That does make sense in Excel - where, if you format as percentages, Excel automatically multiplies it by 100, so you don't need to. But as far as I know, SSRS does not do that - but I might be misaken. |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 11/05/2012 : 09:17:23
|
You are correct that did it, thought I needed the *100, you do in Crystal and as you say Excel...
Thanks again for your help :) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 11/05/2012 : 21:11:49
|
quote:
Originally posted by sunitabeck
My immediate reaction would be to suggest that the multiplication by 100 be removed from you expression. That does make sense in Excel - where, if you format as percentages, Excel automatically multiplies it by 100, so you don't need to. But as far as I know, SSRS does not do that - but I might be misaken.
nope SSRS does that if you select the standard % format from cell properties atleast in 2005
and i dont think it would have got changed in future versions 
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/06/2012 : 06:59:55
|
| You are right. The same behavior in 2008. Not only that, even if you type in a custom format string such as 0.00%;-0.00%;"" into the Format cell of the properties (of a text box, for example), it still scales by 100. SSRS is being a little too helpful for my taste :) |
|
|
|
sz1
Constraint Violating Yak Guru
United Kingdom
294 Posts |
Posted - 11/06/2012 : 07:24:59
|
Agh ok I tried all that and it made a mess of things but now I know.
Thanks chaps. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 11/06/2012 : 12:23:48
|
quote:
Originally posted by sunitabeck
You are right. The same behavior in 2008. Not only that, even if you type in a custom format string such as 0.00%;-0.00%;"" into the Format cell of the properties (of a text box, for example), it still scales by 100. SSRS is being a little too helpful for my taste :)
Yep it is
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 11/06/2012 : 12:24:39
|
quote:
Originally posted by sz1
Agh ok I tried all that and it made a mess of things but now I know.
Thanks chaps.
hmm...it wont unless you changed the native datatype of field while fetching into report dataset
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Expression
