Expression - SQL Server Forums

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

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Expression

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2012-10-31 : 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))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-31 : 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
Aged Yak Warrior

555 Posts

Posted - 2012-10-31 : 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

52326 Posts

Posted - 2012-10-31 : 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
Aged Yak Warrior

555 Posts

Posted - 2012-11-05 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-05 : 07:48:09

[code]=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[/code]If there is the possibility that there will be zero rows, you may want to take care of that as well.

sz1
Aged Yak Warrior

555 Posts

Posted - 2012-11-05 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-05 : 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
Aged Yak Warrior

555 Posts

Posted - 2012-11-05 : 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

52326 Posts

Posted - 2012-11-05 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-06 : 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
Aged Yak Warrior

555 Posts

Posted - 2012-11-06 : 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

52326 Posts

Posted - 2012-11-06 : 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

52326 Posts

Posted - 2012-11-06 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources