expression sum and iif help?

Hi All,

SUM(IIF(Fields!NO.Value >= 1, Fields!tsHours.Value,0))

i write this expression inside the group footer row, it give a #error when generating report.

please help

Cheers

Nick

Hi Nick,

The possible reason for this error is that the Sum function can only add fields of the same data type. It is quite possible in this case that the data types of the fields in the TRUE and FALSe part of the If statement is different. Try making the data types same of both the fields by usinf the Conversion functions available.

Hope this helps.

Regards,

Pradeep

|||

Hi,

I think ure iif() is returning varying datatypes. 0 being a integer and Fields!tsHours.Value returning some other datatype.

Use a conversion function such as CDbl() on both fields.For eg: CDbl(Fields!tsHours.Value) and CDbl(0).This makes either of the values returned as Double.

eg:

Sum(IIf(Fields!NO.value>=1,CDbl(Fields!tsHours.Value),Cdbl(0)))

Somiya

|||

Thanks Chaubey and Somiya,

You guys are right, after i explicitly convert to same type , then it works.

Cheers

Nick

Expression Question

I am working on an aging report and am trying to sum one of the columns that
is the 30-60 Day bucket. I have the expression written as the following;
"=sum(fields!extcost.value) where (fields!interval.value>30 and
Fields!Interval.Value<=60)"
What logic should I be using?
Thank you,
Ryan2 ways to deal with this. In report cell:
=Sum(IIf(Fields!Interval.Value=>30 AND
Fields!Interval.value<=60,Fields!extcost.value,0))
However, I normally try to do this in the SQL with a CASE statement for each
bucket.
SELECT X,Y,Z,Sum(CASE WHEN Interval BETWEEN 30 and 60 THEN extcost ELSE 0
END As Bucket2,A,B,C...
FROM....
Michael C
"Ryan Mcbee" wrote:
> I am working on an aging report and am trying to sum one of the columns that
> is the 30-60 Day bucket. I have the expression written as the following;
> "=sum(fields!extcost.value) where (fields!interval.value>30 and
> Fields!Interval.Value<=60)"
> What logic should I be using?
> Thank you,
> Ryan
>

Expression Problem - GrandTotal

In my SSRS 2005 report I have a column in my table that I cannot get a SUM in my table footer (I'm not referring to report footer!!) due to the way it is calculated. Here's the low down:

I will focus just on the columns in my table unique to this problem. Remember, I'm using a Table Control in SSRS 2005, not just a Body section!!!

In my table I have:

Commission % ProjGross ProjFee

.25 1000 ProjGross * Commission %

.50 2000 ProjGross * Commission %

.7 3000 ProjGross * Commission %

...

ProjFee = ProjGross x Comission %

the values above are in a Group. The column's group values render fine and are correct using this formula in my ProjFee's group textbox:

=(Round((((SUM(Fields!PostedAmount_InHouse.Value) + SUM(Fields!NewPDs_Check.Value) + SUM(Fields!NewCCs_Check.Value)) / Sum(Fields!CurrentPostingDay.Value, "Current_Posting_Day")) * (Sum(Fields!TotalPostingDays.Value, "TotalPostingDays") - Sum(Fields!CurrentPostingDay.Value, "Current_Posting_Day"))) +

(SUM(Fields!PostedAmount_InHouse.Value) + SUM(Fields!OldPDs_Check.Value) + SUM(Fields!NewPDs_Check.Value) + SUM(Fields!OldCCs_Check.Value) + SUM(Fields!NewCCs_Check.Value)))) * (Fields!FeeSchedule.Value / 100)

So, now for the problem. I cannot just create an expression in my table footer like the one this below because it would only take the first commission %25 and wouldn't represent a true some of the column ProjFee. YOu can't total up Commission % or you'd get a wacky % somthing like 500%!

=ProjGross * Commsion %

I don't want to do this totaling in SQL first either...and can't; there's a lot more behind this calculation which is why and the SQL would be horrendous. I'm not quite sure how I'm gonna handle the Commission % in my table footer Total for this column.

Since I cannot just reference the Group column's name in my footer and do a SUM on that field (it's out of scope) I don't know what else to try

check this out:

http://www.photopizzaz.biz/footersum.jpg

http://www.photopizzaz.biz/footersum2.jpg

http://www.photopizzaz.biz/footersum3.jpg

All I want to do is get my Footer GT to work. The formulas I have in both the group field and footer field for ProjGross are as follows:

Group:

=(Round((((SUM(Fields!PostedAmount_InHouse.Value) + SUM(Fields!NewPDs_Check.Value) + SUM(Fields!NewCCs_Check.Value)) / Sum(Fields!CurrentPostingDay.Value, "Current_Posting_Day")) * (Sum(Fields!TotalPostingDays.Value, "TotalPostingDays") - Sum(Fields!CurrentPostingDay.Value, "Current_Posting_Day"))) +

(SUM(Fields!PostedAmount_InHouse.Value) + SUM(Fields!OldPDs_Check.Value) + SUM(Fields!NewPDs_Check.Value) + SUM(Fields!OldCCs_Check.Value) + SUM(Fields!NewCCs_Check.Value)))) * (Fields!FeeSchedule.Value / 100)

Footer (right under group)

=sum((((Fields!PostedAmount_InHouse.Value + Fields!NewPDs_Check.Value + Fields!NewCCs_Check.Value) / Fields!CurrentPostingDay.Value)

* (Fields!TotalPostingDays.Value - Fields!CurrentPostingDay.Value)

+ (Fields!PostedAmount_InHouse.Value + Fields!OldPDs_Check.Value + Fields!NewPDs_Check.Value + Fields!OldCCs_Check.Value + Fields!NewCCs_Check.Value))

* (Fields!FeeSchedule.Value)) / 100

I’ve played around with moving the SUM in the Footer field since that works sometimes but no luck so far. If I could only refrence the Group field and just do SUM(groupfieldname) but that’s something I have asked and nobody seems to know why you can’t do that in a SSRS 2005 table when you’re trying to do that in a Footer field to sum a Group field by referencing the Group field’s label name. SSRS 2005 gives you an error when you try doing that saying that you must stay in scope when referencing names and you can’t reference a Group name forma footer textbox in a table. That would sure make my life easier than trying to fiddle around with the SUM I have in my footer textbox to try to get it to work

|||am I just not explaining myself or is SSRS 2005 ending up to be just like Crystal where you have to figure out quirky ways to get things done? The fact of the matter is, I can't simply do a sum on a column by referencing a group's textbox name from the table footer expression. If you are dealing with very complex expressions and you m ust only use an expression and cannot reference a group field directly from the footer, you are left with what I have tried to do above...which is a nightmare because my GT is not coming out and I don't know if it can because of the Commission % (Feesched) being a % and you can's sum a % in the footer like this for an overall GT

expression problem

Hi There
I am trying to sum the amount value in the budgets dataset based on a sub
catagory within the budgets table called personal loans.
my expression is
=Sum iif(Fields!Sub_Catagory.Value, "budgets" = "Personal Loans",
Fields!Amount.Value, "budgets", Nothing))
this sits within an actuals table that uses a different dataset
could i please have some assistance as to where i am going wrong. error
message is The value expression for the textbox â'total_budgetâ' refers to the
field â'Sub_Catagoryâ'. Report item expressions can only refer to fields
within the current data set scope or, if inside an aggregate, the specified
data set scope.
ThankyouIf I understood you right: You want to sum the Amount (within one dataset)
if the sub_category is "Personal Loans" (within another dataset). try this:
= Sum(iif(Fields!Sub_Category.Value = "Personal Loans", Fields!Amount.Value,
0))
syntax:
iif( expression, what to do if the expression is true, what to do if the
expression is false)
I wasn't able to test the expression, because i have no report server
running at the moment.
"Tango" <Tango@.discussions.microsoft.com> schrieb im Newsbeitrag
news:EE09B408-6D43-4774-BC61-8D5A999EDF4B@.microsoft.com...
> Hi There
> I am trying to sum the amount value in the budgets dataset based on a sub
> catagory within the budgets table called personal loans.
> my expression is
> =Sum iif(Fields!Sub_Catagory.Value, "budgets" = "Personal Loans",
> Fields!Amount.Value, "budgets", Nothing))
> this sits within an actuals table that uses a different dataset
> could i please have some assistance as to where i am going wrong. error
> message is The value expression for the textbox 'total_budget' refers to
> the
> field 'Sub_Catagory'. Report item expressions can only refer to fields
> within the current data set scope or, if inside an aggregate, the
> specified
> data set scope.
> Thankyou
>
>|||The following link discusses conditional expressions:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_3983.asp
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jens Konerow" <keineangabe@.web.de> wrote in message
news:%23RfrfWqwFHA.3556@.TK2MSFTNGP15.phx.gbl...
> If I understood you right: You want to sum the Amount (within one dataset)
> if the sub_category is "Personal Loans" (within another dataset). try
> this:
> = Sum(iif(Fields!Sub_Category.Value = "Personal Loans",
> Fields!Amount.Value, 0))
> syntax:
> iif( expression, what to do if the expression is true, what to do if the
> expression is false)
> I wasn't able to test the expression, because i have no report server
> running at the moment.
> "Tango" <Tango@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:EE09B408-6D43-4774-BC61-8D5A999EDF4B@.microsoft.com...
>> Hi There
>> I am trying to sum the amount value in the budgets dataset based on a sub
>> catagory within the budgets table called personal loans.
>> my expression is
>> =Sum iif(Fields!Sub_Catagory.Value, "budgets" = "Personal Loans",
>> Fields!Amount.Value, "budgets", Nothing))
>> this sits within an actuals table that uses a different dataset
>> could i please have some assistance as to where i am going wrong. error
>> message is The value expression for the textbox 'total_budget' refers to
>> the
>> field 'Sub_Catagory'. Report item expressions can only refer to fields
>> within the current data set scope or, if inside an aggregate, the
>> specified
>> data set scope.
>> Thankyou
>>
>