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
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Expression
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 10/31/2012 :  11:04:41  Show Profile  Reply with Quote
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
52309 Posts

Posted - 10/31/2012 :  12:14:54  Show Profile  Reply with Quote
=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/

Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 10/31/2012 :  12:23:26  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/31/2012 :  12:40:13  Show Profile  Reply with Quote
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/

Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 11/05/2012 :  07:24:47  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/05/2012 :  07:48:09  Show Profile  Reply with Quote
=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.
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 11/05/2012 :  08:30:14  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/05/2012 :  09:01:40  Show Profile  Reply with Quote
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.
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 11/05/2012 :  09:17:23  Show Profile  Reply with Quote
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 :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/05/2012 :  21:11:49  Show Profile  Reply with Quote
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/

Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/06/2012 :  06:59:55  Show Profile  Reply with Quote
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 :)
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
392 Posts

Posted - 11/06/2012 :  07:24:59  Show Profile  Reply with Quote
Agh ok I tried all that and it made a mess of things but now I know.

Thanks chaps.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/06/2012 :  12:23:48  Show Profile  Reply with Quote
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/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/06/2012 :  12:24:39  Show Profile  Reply with Quote
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/

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.09 seconds. Powered By: Snitz Forums 2000